tencent cloud


Enhancing Business Load Capacity with TencentDB for MySQL

Last updated: 2022-02-15 15:15:48

    Databases with excellent performance and scalability can help you quickly increase the load capacity of your existing systems. With the same size of database, TencentDB for MySQL, if appropriately used, can significantly improve database concurrence for higher QPS.

    1. Select a Proper Database Configuration

    1.1 Select the database version

    TencentDB for MySQL is currently available in v5.5, v5.6, v5.7, and v8.0, all of which are fully compatible with native MySQL. We recommend that you choose v5.6 and later, as they use more stable database kernels, deliver better system performance by optimizing the design of v5.5 and earlier, and come with a lot of appealing new features.

    This document takes MySQL v5.7 as an example to illustrate the features of the new versions. This version is widely recognized for its impressive performance, reliability, and ease of use. Some of its improvements and new features are as shown below:

    • Native JSON support
      In MySQL v5.7, a new data type has been added to store data in the native JSON format in MySQL tables, which has the following advantages:
    • Document verification: only data segments in line with JSON rules can be written to JSON-type columns, which means that there is automated JSON syntax verification.
    • Efficient access: when a JSON document is stored in a JSON-type column, the data will not be stored as plain text; instead, it will be stored in the optimized binary format, so that its object members and array elements can be accessed more quickly.
    • Performance enhancement: an index can be created on data in JSON-type columns so as to improve the query performance. Such indexes can be implemented through the "function index" created on virtual columns.
    • Convenience: the inline syntax attached to JSON-type columns can be naturally integrated into document queries in SQL statements such as "features". "feature" is a JSON field:
      SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

    With MySQL v5.7, you can seamlessly integrate the best relational samples with the best document samples in one tool so as to use the most appropriate ones out of them in different applications and use cases, which greatly expands your range of applications.

    • SYS Schema
      MySQL SYS Schema is a database schema consisting of a set of objects such as views, stored procedures, storage methods, tables, and triggers. It gives easy, readable, DBA- and developer-friendly access to the wealth of monitoring data stored in various tables in Performance Schema and INFORMATION_SCHEMA.
      It is included in MySQL v5.7 by default and provides summary views to answer the following common questions:
    • What is taking up all the resources of the database service?
    • Which CVM instance accesses the database server most frequently?
    • How is the instance memory used?
    • InnoDB improvements
    • Online operations in InnoDB (Online DDL): you can dynamically adjust the buffer pool size to make it adaptive to the change of your business needs without restarting MySQL. InnoDB now can automatically empty its undo logs and tablespace online, thus eliminating one of the most common reasons for large shared tablespace files (ibdata1). In addition, MySQL v5.7 supports renaming indexes and changing the varchar size, both of which could be done only by recreating indexes or tables in previous versions.
    • InnoDB native partitioning: in MySQL v5.7, InnoDB includes the native support for partitioning, which can reduce the load and lower the memory usage by up to 90%.
    • InnoDB cache prefetching: when MySQL restarts, InnoDB will automatically retain 25% of the hottest data in the buffer pool, eliminating your need to preload or prefetch the data cache and preventing potential performance loss caused by MySQL restart.

    For more information on improvements and new features in MySQL v5.7, see MySQL's official documentation.

    1.2 Select database memory

    Currently, TencentDB for MySQL doesn't offer separate CPU options; instead, the CPU will be allocated proportionally according to the memory specification. You can purchase database specifications based on your business characteristics. We have conducted thorough benchmark tests on each type of instance so as to provide performance information for your reference when you select specifications.
    However, it should be noted that the Sysbench-enabled tests cannot represent all business scenarios. You are recommended to perform stress testing on your instance before launching it officially, so that you can better understand how TencentDB for MySQL performs in your business scenario. For more information, see Performance White Paper.

    Memory is one of the core instance metrics, which features an access speed much higher than that of a disk. Generally, the more data cached in the memory, the faster the database response. If the memory is small, after the stored data exceeds a certain amount, the excessive data will be stored to the disk. After that, when a new request accesses the data again, the data will be read from the disk into the memory, consuming disk IO and leading to slower database response.

    For businesses with high read concurrence or sensitive to read delay, it is recommended to choose a higher memory specification so as to ensure high database performance.

    1.3 Select disk capacity

    The disk capacity of a TencentDB for MySQL instance includes only the MySQL data directories but not the logs such as binlog, relaylog, undolog, errorlog, and slowlog. When the amount of written data exceeds the instance disk capacity, if the instance is not upgraded, instance lock may be triggered. Therefore, when you purchase a disk, you are recommended to take into account the possible data volume increase in the future and select a larger disk, which helps prevent your instance from being locked or frequently upgraded due to insufficient disk capacity.

    1.4 Select a proper data replication mode

    TencentDB for MySQL provides three replication modes: async, semi-sync, and strong sync. For more information, see Database Instance Replication Mode. If your business is sensitive to write latency or database performance, you are recommended to choose the async replication mode.

    1.5 High availability of TencentDB

    High availability of TencentDB for MySQL is guaranteed by the source-replica architecture. Source-replica data sync is achieved through binlogs. In addition, the database can be rolled back to any previous point in time, which relies on backups and logs. Therefore, you generally do not need to set up a backup and restoration system on your own or pay additional fees to keep your instance highly available.

    1.6 Scalability of TencentDB

    All the different database versions and memory/disk specifications of TencentDB for MySQL support hot upgrade. The upgrade process will not interrupt your business, eliminating your concerns over any database bottlenecks caused by business growth.

    1.7 Use CVM and TencentDB for MySQL together

    After a purchase is made, you generally need to use CVM and TencentDB for MySQL together. For more information, see Connecting to MySQL Instances.

    2. Take Read-Only Replicas as Read Extension

    In common internet-based businesses, the read/write ratio of databases generally ranges from 4:1 to 10:1, which means that the read load of databases is much higher than the write load. When a performance bottleneck occurs, a common solution is to enhance the ability to handle read load.
    TencentDB for MySQL read-only instances are ideal for such issues. For more information, see Creating Read-Only Instances.
    Read-Only instances can also be used for read-only access in various businesses; for example, the source instance undertakes read/write access for online businesses, while the read-only instance provides read-only query for internal businesses or data analysis platforms.

    3. TencenDB Disaster Recovery Solutions

    TencentDB for MySQL provides disaster recovery instances, helping you quickly set up remote disaster recovery for databases.

    With the help of disaster recovery instances, multiple data centers in different regions can act as redundancy of each other, so that when one data center cannot provide a service due to failures or force majeure events, the service can be quickly switched to another data center. Disaster recovery instances use Tencent Cloud private network to sync data and the replication is optimized at the level of MySQL kernel, which can minimize the impact of delayed sync on your business when a disaster occurs. As long as the remote service logic is ready, the disaster recovery switchover can be completed in seconds.

    4. Two-Region-Three-DC Program

    With TencentDB for MySQL, it only takes several simple steps to configure the 2-region-3-DC scheme:

    • Purchase a TencentDB for MySQL intra-city strong-consistency cluster and select multi-AZ deployment (currently in beta test) which provides the 1-region-2-DC capacity.
    • Add remote disaster recovery nodes to the cluster in order to build the 2-region-3-DC architecture.

    5. Use Disaster Recovery Instances to Provide Users with Nearby Access

    A disaster recovery instance also adopts the high-availability source-replica architecture. In addition, it can be accessed in a read-only manner, which helps enable local access to your businesses for end users in different regions.

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support