Troubleshooting RDS Performance (MySQL, SQL SERVER and MongoDB)

Image for post
Image for post

A lot of questions have been raised by our users concerning why ApsaraDB for RDS sometimes perform worse than self-built databases on RDS.

Firstly, all tests must be fair if the performances are to be compared. As a public database service, Alibaba Cloud ApsaraDB for RDS must prioritize on high-availability and security, which can sometimes work against performance. Users, of course, won’t be willing to use an unstable, unsafe service no matter how powerful it may be. To ensure stability, RDS must have master and slave nodes, even setting them up in different data centers. This means that if an error occurs in one data center, the system can easily switch to another to keep providing services.

ApsaraDB for RDS must also ensure data security. After all, who would want to continue using our services if their data was stolen? As a service vendor, we must consider the security of users’ data. Because of this, ApsaraDB adds an intermediate layer to intercept SQL injection requests, and applies the highest security standards for base-level data writes to prevent data loss if the host doesn’t shut down normally.

Regarding performance, the Alibaba Cloud ApsaraDB for RDS source code team is continually making optimizations to MySQL. Both performance and stability are higher than in the community edition, as evidenced by standard benchmark tests. Here we will summarize some of the issues with the performance tests between RDS and self-built databases.

I. Network Differences

1. Available Zones

2. Network Links

3. Case Study

  1. The application code and database configuration are identical.
Image for post
Image for post

RDS access links:

Image for post
Image for post

II. Configuration Differences

1. Specification Configuration

2. Parameter Configuration

  1. The innodb_flush_log_at_trx_commit parameters specify the log write frequency after the transaction is committed by InnoDB. When the value is 1, the log buffer will be written to the log file and flushed to disk during each transaction commit. 1 is the default value. This configuration is the safest, but there is a certain performance loss due to the operation of disk I/O during each transaction commit.
  2. The sync_binlog parameter is the frequency at which the MySQL binlog syncs to disk. After the binlog is written sync_binlog times, it will be flushed to disk by MySQL. A value of 1 is the safest. It will synchronize the binary log once after writing each statement or transaction, so at most one statement or transaction log will be lost even in the event of total failure. The trade off is that this is the slowest configuration.

(2) Performance configuration: RDS allows user configured parameters except for specification parameters. Most of the parameters have been optimized by the official team, so most users can run it out of the box without having to adjust any parameters. However, while these parameters are suitable for most application scenarios, there will be times where customization is appropriate for performance purposes.

tmp_table_size

Function: This parameter is used to determine the maximum value of the internal temporary memory table, which will be assigned to each thread (The hard caps are the values of tmp_table_size and max_heap_table_size). If the temporary memory table exceeds this limit, MySQL will automatically convert it to the disk-based MyISAM table. When optimizing the query statement, avoid using temporary tables. If you really cannot avoid using them, they must be ensured in memory.

Phenomenon: If temporary tables are used when a complex SQL statement contains group by/distinct, which cannot be optimized through an index, it will lead to longer SQL execution.

Recommendation: If the application involves a lot of group by/distinct statements, and the database has enough memory, you can increase the value of tmp_table_size (max_heap_table_size) to improve query performance.

query_cache_size

Function: This parameter is used to control the size of MySQL query cache memory. If activated, MySQL will first lock the query cache before execution of each query, and then determine whether the query already exists in the query cache. If it is in the query cache, the result will be returned immediately. If not, the engine query and other operations will be carried out. At the same time, operations like insert, update and delete will cause the query cache to fail. The failure also includes any changes in structure or index. Recovering from cache failure can be costly and creates heavy stress on MySQL. Query cache can be very useful if the database is not frequently updated. However, if you write data frequently, and to a small number of tables, the query cache lock function will often cause lock conflicts. A lock conflict occurs when you attempt to write or read a table that is currently in query cache lock. Since you cannot operate on the table until the lock is lifted, this feature can decrease the efficiency of your select queries.

Phenomenon: The database goes through a number of different statuses, including checking query cache, waiting for query cache lock, and storing results in query cache;

Recommendation: The query cache function is disabled in RDS by default. If you enable query cache in your instance, you can choose to disable it when you encounter any of the above statuses. Of course, query cache can be quite useful in some instances, for example it can be a handy tool for resolve database performance issues.

3. Case Study

  1. The execution time on RDS is twice that of the offline self-built database

The user’s local parameter configuration:

join_buffer_size = 128M

read_rnd_buffer_size = 128M

tmp_table_size = 128M

RDS parameter configuration

join_buffer_size = 1M

read_buffer_size = 1M

tmp_table_size =256K

III. Framework differences

1. Master-Slave Mechanism

Image for post
Image for post

Note: The same problems also exist when the high availability SQLSERVER uses a mirror.

Reference:

https://www.alibabacloud.com/blog/Troubleshooting-RDS-Performance-(MySQL-SQL-SERVER-and-MongoDB)_p335405?spm=a2c41.11133700.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store