Mysql performance lagging in Azure VM

Question

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.