Optimizing my-huge.cnf
SuperMicro SuperServer 6014H-T 1U
Dual Intel Xeon 3.2 Ghz | 800 Mhz FSB | 1 Mb Cache | x86_64
4x 512 Mb DDR-333 RAM
3x 74.6 Gb 10000RPM WD Raptor SATA RAID5
3Ware 9500S-4LP Controller Card
Anyway, I've been working with the my.cnf file for the past two days trying to find a good setup. I'm as far as my knowledge has gotten me, so I'm asking for anyone else to make recommendations to the config below.
Code:
[mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking connect_timeout = 2 interactive_timeout = 10 wait_timeout = 10 key_buffer = 1000M #have_raid = YES max_allowed_packet = 8M max_connect_errors = 500 table_cache = 500 sort_buffer_size = 64M read_rnd_buffer_size = 16M max_connections = 500 back_log = 100 read_buffer_size = 16M myisam_sort_buffer_size = 32M thread_cache = 64 query_cache_type = 1 query_prealloc_size = 128M query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 126 server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 6M write_buffer = 6M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 6M write_buffer = 6M [mysqlhotcopy] interactive-timeout
Code:
[root@hinata mysql]# status
20:34:13 up 2:01, 1 user, load average: 1.75, 1.64, 1.62
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
--- pts/0 0-1pool211-5.nas 18:33 0.00s 26:08 0.01s sshd: --- [priv]
total used free shared buffers cached
Mem: 2007 1304 703 0 44 914
-/+ buffers/cache: 344 1662
Swap: 2047 0 2047
Uptime: 4734 Threads: 42 Questions: 1511483 Slow queries: 0 Opens: 2929
Flush tables: 14 Open tables: 188 Queries per second avg: 319.282
Code:
[root@hinata mysql]# estatus +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 18850 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 226411431 | | Bytes_sent | 2197250171 | | Com_admin_commands | 11216 | | Com_alter_db | 0 | | Com_alter_table | 3 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 33233 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 21089 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 26 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 2742 | | Com_insert_select | 49 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 606 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 850521 | | Com_set_option | 20 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 10 | | Com_show_collations | 10 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 3 | | Com_show_databases | 6 | | Com_show_errors | 0 | | Com_show_fields | 8 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 3 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 16 | | Com_show_storage_engines | 0 | | Com_show_tables | 25 | | Com_show_variables | 23 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 19423 | | Com_update_multi | 0 | | Connections | 15015 | | Created_tmp_disk_tables | 316 | | Created_tmp_files | 10 | | Created_tmp_tables | 18745 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 14 | | Handler_commit | 6 | | Handler_delete | 2288 | | Handler_discover | 0 | | Handler_read_first | 12599 | | Handler_read_key | 6227885 | | Handler_read_next | 478951980 | | Handler_read_prev | 194381765 | | Handler_read_rnd | 8806447 | | Handler_read_rnd_next | 597448230 | | Handler_rollback | 7 | | Handler_update | 488096 | | Handler_write | 3303898 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 899971 | | Key_blocks_used | 7744 | | Key_read_requests | 65248144 | | Key_reads | 88444 | | Key_write_requests | 29376 | | Key_writes | 20654 | | Max_used_connections | 57 | | Not_flushed_delayed_rows | 0 | | Open_files | 309 | | Open_streams | 0 | | Open_tables | 192 | | Opened_tables | 2933 | | Qcache_free_blocks | 203 | | Qcache_free_memory | 19568064 | | Qcache_hits | 592764 | | Qcache_inserts | 827402 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 23106 | | Qcache_queries_in_cache | 8599 | | Qcache_total_blocks | 17611 | | Questions | 1526115 | | Rpl_status | NULL | | Select_full_join | 58 | | Select_full_range_join | 0 | | Select_range | 69971 | | Select_range_check | 0 | | Select_scan | 19810 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 87475 | | Sort_rows | 9137975 | | Sort_scan | 22423 | | Table_locks_immediate | 1031420 | | Table_locks_waited | 7233 | | Threads_cached | 11 | | Threads_connected | 43 | | Threads_created | 277 | | Threads_running | 5 | | Uptime | 4790 | +--------------------------+------------+
The next ratio I focused on was the Table_locks_immediate to Table_locks_waited. In general, I just wanted a huge ratio between the two as that means there are fewer waiting threads. As it stands, 1:143 ratio exists. It was 1:9 two hours ago. I achieved that ratio jump by adding --low-priority-updates to the startup. SELECT queries are far more important that maintenance queries.
The final area I will focus in on is converting the most active databases to using InnoDB format.
Aside from what I have said, can anyone make any other recommendations? Much appreciated.
Thanks much!
