Edit

MySQL status and variables

1. Execute the following command on the database instance and provide the output:

   mysql -u $wfdb_user -p -e "show variables" > /tmp/mysql_valiables.tsv
   

   Variables	Description
   table_cache key_buffer_size	If Opened_tables is high, then the table_cache variable is probably too low. table_cache 64 Open_tables 64 Opened_tables 544468 This is the first serious problem. The table_cache is the number of open tables for all threads. Being multi-threaded, MySQL can be running many queries on the table at a time, and each of these will open a table. Therefore, even though you have only a few tables, you will need many more open_tables. The Opened_tables value is high and shows the number of cache misses. Getting the table_cache size correct is one of the two best things you can do to improve performance. If Key_reads is big, then your key_buffer_size variable is probably too small. The cache hit rate can be calculated based on the Key_reads/Key_read_requests ratio. key_buffer_size 16M Key_read_requests 2973620399 Key_reads 8490571 (cache hit rate = 0.0028) The key_buffer_size affects the size of the index buffers and the speed of the index handling, particularly reading. The MySQL manual (and other sources) say that the Key_reads/Key_read_request ratio should normally be < 0.01. This is the other most important thing to get correct. Here, the value seems to be correct (< 0.01). Also, check key_write_requests and key_writes. The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine). Here is a very interesting web pointer : table_cache and key_buffer_size.
   max_connections table_cache wait_timeout thread_cache_size	Generally, you have a lot of MySQL processes that are sleeping because wait_timeout is not set low. So, make sure that the wait_timeout is set to a very low value, for instance, 15 seconds. That means MySQL would close any connection that was idle for more than 15 seconds. The problem is you also have to increment your max_connection (for instance, to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done. The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That can sucks up a measurable amount of CPU time. So, the solution is to use Thread_cache . When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty, a new thread is created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examining the difference between the Connections and Threads_created, you can see how efficient the current thread cache is for you. If Threads_created is high, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections. thread_cache_size 0 Threads_created 150022 Connections 150023. This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf, but the recommended size in my-large.cnf is 8. You may try this formula : table_cache = opened table / max_used_connection.
   key_buffer_size query_cache_size tmp_table_size	If Created_tmp_disk_tables is high, you may want to increase the tmp_table_size variable to get the temporary tables memory-based instead of disk based. tmp_table_size 32M Created_tmp_disk_tables 3227 Created_tmp_tables 159832 Created_tmp_files 4444 Created_tmp_disk_tables stands for the number of implicit temporary tables on the disk created while executing statements, and Created_tmp_tables is memory-based. Obviously, it is bad if you have to go to the disk instead of memory. About 2% of temp tables go to the disk, which doesn't seem too bad, but increasing the tmp_table_size probably couldn't hurt either. If Handler_read_rnd is high, you probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly. Handler_read_rnd 27712353 Handler_read_rnd_next 283536234. These values are high, which you could probably stand to improve the indexes and queries. If you change tmp_table_size, don't forget to carry about the max_heap_table_size value, too.

2. Execute the following command on the database instance and provide the output:

   mysql -u $wfdb_user -p -e "show engine innodb status" > /tmp/innodb_status.tsv