2

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!

coderego
  • 175
  • 2
  • 8

1 Answers1

1
innodb_file_format = barracuda
innodb_read_io_threads = 6
innodb_write_io_threads = 6
innodb_io_capacity = 1000

These setting are MySQL 5.5 specific. If you are using MySQL 5.5 against data that was wriiten in a MySQL 5.0/5.1, you need to run the old version of MySQL, mysqldump everything out, and reload the data into mysql again. The barracuda format is another InnoDB File Format. You must migrate out from the previous version of MySQL and InnoDB. I wrote something on this awhile back.

To double check the file format, do "SHOW VARIABLES LIKE 'innodb_file_format';" You should see Antelope.

Once you get this issue straightened out, crank up the other variables to this

innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 20000

After all, you have 8 CPUs.

MyISAM Tuning Options
InnoDB Tuning Options
InnoDB Specific Tuning
InnoDB and MyISAM Cache Configuration Forecasting

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80