* http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
* http://mysqltuner.pl/mysqltuner.pl
* comments on ''sync_binlog'' and ''innodb_flush_log_at_trx_commit''
* http://www.mysqlperformanceblog.com/2006/05/27/jeremy-cole-on-mysql-replication/
* http://www.chriscalender.com/?tag=sync_binlog
* [[http://www.mysqlperformanceblog.com/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/|ext3 and sync_binlog don't work well together, try using xfs]]
* [[http://virtualgeek.typepad.com/virtual_geek/2009/06/a-multivendor-post-to-help-our-mutual-nfs-customers-using-vmware.html|esx with nfs]]
^ variable ^ ent55 ^ hippo ^ testing ^ notes ^
| binlog_cache_size | 32K | 1M | | |
| binlog_format | STATEMENT | MIXED | | |
| bulk_insert_buffer_size | 8M | 32M | | |
| expire_logs_days | 0 | 1 | | |
| ft_min_word_len | 4 | 4 | | |
| innodb_additional_mem_pool_size | 8M | 16M | | |
| innodb_buffer_pool_size | 128M | 3G | 2G | |
| innodb_file_per_table | Off | On | | |
| innodb_flush_log_at_trx_commit | 1 | 2 | 2 | This has a **big** effect on performance |
| innodb_lock_wait_timeout | 50 | 120 | | should be set LOWER than 50 for most oltp systems |
| innodb_log_buffer_size | 8M | 8M | | |
| innodb_log_files_in_group | 3 | 3 | | |
| innodb_log_file_size | 64M | 64M | | |
| innodb_max_dirty_pages_pct | 75 | 90 | | |
| innodb_read_io_threads | 4 | 8 | 8 | |
| innodb_thread_concurrency | 0 | 16 | 16 | The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks. The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. |
| innodb_write_io_threads | 4 | 8 | 8 | |
| join_buffer_size | 128K | 4M | default | There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it. |
| key_buffer_size | 8M | 128M/1G | 256M | |
| max_allowed_packet *fix in rolling | 1M | 16M | | You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple. |
| max_heap_table_size | 16M | 128M/16M | 128M | |
| query_cache_limit | 1M | 16M | | |
| query_cache_size | 0 | 1G | 256M | |
| read_buffer | unset | 4M | unset | |
| read_buffer_size | 128K | 1M | 1M | |
| read_rnd_buffer_size | 256K | 4M | 4M | |
| sort_buffer_size | 2M | 128M/256K | default (2M) | On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values |
| sync_binlog | 0 | 1 | 0!!!!! or 1 in all the files please | this really hurts disk i/o, like, really really lots and lots |
| table_open_cache | 400 | 16384 | 16384 | Look into this, might be too big: [[http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_table_open_cache]] |
| thread_cache_size | 0 | 8 | 8 | try increasing? |
| thread_concurrency | 10 | 8 | default | only affects solaris |
| thread_stack | 256K | 64K | default | |
| tmp_table_size | 16M | 128M | | |
| transaction_isolation | unset | REPEATABLE-READ | | |
| write_buffer | unset | 4M | default | |