From RDS to optimizing a MySQL Server on AWS

“Don’t optimize unless there is a problem” is one of the mantras of software development. But when we were using RDS as our DB server, we had lots of problems. Little control and hhhhhhhigh latency between EC2 and RDS.

So, in order to improve the performance, we decided to move from AWS’s RDS to our own DB server. First we tried setting up a DB server on an EC2 large instance. After a few days testing, we found to our dismay that although the end user experience was faster (low latency), running some heavy jobs like importing a long list of products would take even more time than using RDS.

After some research, we found the CPU and I/O Capacity to be the bottleneck. The CPU bottleneck was easy to fix. Upgrade. So we setup another DB server with an EC2 High CPU instance. But unfortunately the region we were using doesn’t yet offer “Provisioned IOPS volumes”, so we couldn’t just improve the I/O by upgrading.

Our other option for tuning MySQL performance were mysqld variables. There is no sure fire way to fix things this way, you have to optimize it for your own environment and usage. But luckily enough someone else made tools available to ease the pain here considerably.

First of all, we needed a tool to measure the efficiency of our optimizations. Here I recommend ‘sysbench’, because it can simulate a real world environment very well. For example, you can setup a test table equal to the production database size, set testing rules with Read / Write rates as in a real environment and more (please check the offical documentation).

Another handy tool is “MySQLTuner”. This is a Perl script that analyzes your MySQL performance and based on the statistics it gathers, gives recommendations on which variables you should adjust in order to increase performance. That way, you can “tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.” - An evaluation from HowToForge. You can simply run mysqltuner, and wait for the recommendations!

Here is an example given by MySQLTuner:

<code>[--] Data in InnoDB tables: 697M (Tables: 178)   [0/107]
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 178

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 18h 52m 36s (53M q [164.433 qps], 356K conn, TX: 426B, RX: 8B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 432.0M global + 2.8M per thread (800 max threads)
[OK] Maximum possible memory usage: 2.6G (35% of installed RAM)
[OK] Slow queries: 0% (1K/53M)
[OK] Highest usage of available connections: 4% (34/800)
[OK] Key buffer size / total MyISAM indexes: 256.0M/99.0K
[OK] Key buffer hit rate: 100.0% (112M cached / 654 reads)
[OK] Query cache efficiency: 35.7% (8M cached / 22M selects)
[!!] Query cache prunes per day: 1949391
[OK] Sorts requiring temporary tables: 1% (2K temp sorts / 126K sorts)
[OK] Temporary tables created on disk: 3% (1M on disk / 34M total)
[OK] Thread cache hit rate: 99% (51 created / 356K connections)
[!!] Table cache hit rate: 0% (128 open / 43K opened)
[OK] Open file limit used: 0% (6/4K)
[OK] Table locks acquired immediately: 99% (15M immediate / 15M locks)
[!!] InnoDB data size / buffer pool: 697.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    table_cache (> 128)
    innodb_buffer_pool_size (>= 697M)

The above recommendations should be enough for most people, you just need to go ahead to change mysql’s configuration after you fully understand each change you are going to make, and measure it with sysbench to check if it is good. It is so easy!

As you may have noticed, the DB server has been running for 3 days since we migrated from RDS, so maybe it hasn’t been able to gather enough information. So I dug for more resources to make our DB server run more efficiently.

1, Enable slow query log
2, After setting up the query cache, a first look at the db status:

<code>   mysql> SHOW GLOBAL STATUS LIKE '%qcache%';
   | Variable_name           | Value    |
   | Qcache_free_blocks      | 755      |
   | Qcache_free_memory      | 2456400  |
   | Qcache_hits             | 15324662 |
   | Qcache_inserts          | 16804628 |
   | Qcache_lowmem_prunes    | 12949720 |
   | Qcache_not_cached       | 5020442  |
   | Qcache_queries_in_cache | 1929     |
   | Qcache_total_blocks     | 5502     |

The most useful valuable for tuning is Qcache_lowmem_prunes. Each time a cached query is removed from the query cache, this value will be incremented. So if this value increases quickly, you need to increase query_cache_size, you can’t know the best value, you just have to change it and keep tracking the status.

3, Setting up Table Cache

You can determine whether your table cache is too small by checking the status of Opened_tables:

<code>    mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
    | Variable_name | Value |
    | Opened_tables | 84990 |

If the value is large or increases quickly, you should increase your table cache size.

4, Increase innodb_buffer_pool_size

You could increase innodb_buffer_pool_size up to 60–80% of your RAM if your are running a dedicated MySQL server. Since we wanted to run some background jobs in this server later, so didn’t give MySQL that much memory.

If you have enough memory to allocate a large enough innodb_buffer_pool_size to load your entire database into memory, MySQL will read less from disk. I/O problem fixed!

5, Use innodb_flush_method=O_DIRECT to avoid double buffering and reduce swap pressure.

6, Increase tmp_table_size to prevent disk writes.

First check the DB status, if Created_tmp_disk_tables is big, then you may want to increase the tmp_table_size value.

7, Optimize all databases by running mysqlcheck --all-databases --optimize -uroot -pxxxxx

8, There are many more variables you can use to optimize MySQL, like read_buffer_size, sort_buffer_size, read_rnd_buffer_size..., do some research on the meaning of these values, and then tune them and start measuring results!

9, Set vm.swappiness=0 in /etc/sysctl.conf to prevent swapping. Please do it after ensuring the “Maximum possible memory usage” of MySQL hasn’t exceed the total memory!

10, Last, but maybe the most effective change. Upgrade mysql to 5.6 ! or give Percona/Mariadb a try!

But please remember “Don’t over-optimise” ;)

That’s all I learned from optimizing our db server, please correct me if you find anything wrong!