I have an innodb on a Ubuntu 10.10 server running on an ESXi platform. I have dedicated 8 CPUs, 32 Gigs of ram, and files stored in a Raid 5 HP SAN of SAS 10k rpm disks. I'm expecting FAST queries! However: select count('x') takes 45 seconds for 75 million rows! So, I dug into tuning this beast:
1) Some of my settings cause mysql to bomb out with "Unknown variable" errors. These are:
innodb_file_format = barracuda innodb_read_io_threads = 6 innodb_write_io_threads = 6 innodb_io_capacity = 1000
2) The rest of my settings that do not crash mysql but are probably not optimized..
skip_name_resolve default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_buffer_pool_size = 24G innodb_log_files_size = 1G innodb_log_buffer_size = 8M innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_comit = 1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 32 query_cache_limit = 1M query_cache_size = 0
Is there any additional tuning that you would recommend on these settings? Any additional settings that I am missing?
Thanks for the support!