I've been having a big mysql performance problem for a while now after a migration from a dedicated server to a cloud VM.
The issue ultimately requires production load to test, so it has been difficult to control all the variables. However, I have not been able to find any recommendations that have had a significant positive effect on our performance.
Azure support recommended creating a RAID array, a handful of innodb config changes, and a change to process limits and disk scheduling algorithm. This was done on test but it was difficult to measure any difference in speed. At some point while trying to develop a fully fleshed-out process for deploying these changes to production, the test server started exhibiting similar behavior. As a temporary fix, a feature which depended on a slow query was removed. This brought the test server back to normal speed, but only got the production server about 1/2way there.
Since nothing is improving, I'm tempted to just assume that Azure is inherently slower, but that doesn't make sense with the discrete drop in performance the test server exhibited... as if it "caught" whatever was wrong with production. Unfortunately I can't remember all the details of the state of the server before that happened.
Another issue is that we don't have a test server that's the same size as production, so we can only make a 4-disk raid array, and it's hard to tell how a performance difference on the 4-disk array would compare to a 16-disk array. However since most of our DB should be in memory at any time, I don't really know that IO is the issue. So I don't necessarily see this being a very productive line of investigation.
The way we are using our server is sort of queue-like which I know is frowned upon, but it will be difficult to rebuild this system in any other way in the immediate future. I've got an idea that maybe the Microsoft MySQL optimization recommendations do not take this sort of usage into account .
Here's the relevant parts of the DB config:
key_buffer_size = 64M max_allowed_packet = 16M thread_stack = 192K thread_stack = 512K thread_cache_size = 16 myisam_recover_options = BACKUP max_connections = 200 table_open_cache = 4096 table_definition_cache = 4096 query_cache_limit = 1M query_cache_size = 64M slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow-log log_slow_slave_statements long_query_time = 10 server-id = 732807 log_bin = /var/log/mysql/bin-log relay_log_space_limit = 16G expire_logs_days = 10 max_binlog_size = 100M binlog_format = MIXED skip_name_resolve sql_mode=NO_ENGINE_SUBSTITUTION sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 1M tmp_table_size = 64M max_heap_table_size = 16M max_heap_table_size = 64M back_log = 100 max_connect_errors = 10000 interactive-timeout = 3600 wait-timeout = 600 bind-address = 127.0.0.1 ### Storage Engines default_storage_engine = InnoDB #innodb = FORCE ## MyISAM myisam_sort_buffer_size = 128M ## InnoDB innodb_buffer_pool_size = 5G innodb_log_file_size = 100M innodb_file_per_table = 1 ## Replication ## Logging [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer_size = 16M
Any advice on config, or more tips for debugging would be appreciated.