I run a moderately busy SMF forum which generates approximately 1M queries per day. I use only InnoDB tables (MyISAM is of course still used by MySQL internally). Things are running great now, but I’m always interested in making them faster still!
I have about 500 MB of RAM for use by MySQL (rest goes to PHP + apc cache, nginx, etc). Most of the queries consist of SELECTs (often with multiple joins) and simple UPDATEs. I’m mostly interested in reducing query latency, if possible. Any suggestions? Here’s my cropped SHOW STATUS.
±----------------------------------±-----------+| Variable_name | Value |±----------------------------------±-----------+| Aborted_clients | 6692 || Aborted_connects | 13 || Binlog_cache_disk_use | 2 || Binlog_cache_use | 10563522 || Compression | OFF || Connections | 3608 || Created_tmp_disk_tables | 0 || Created_tmp_files | 513 || Created_tmp_tables | 1 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Innodb_buffer_pool_pages_data | 16139 || Innodb_buffer_pool_pages_dirty | 40 || Innodb_buffer_pool_pages_flushed | 7346864 || Innodb_buffer_pool_pages_free | 6 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 239 || Innodb_buffer_pool_pages_total | 16384 || Innodb_buffer_pool_read_ahead_rnd | 395 || Innodb_buffer_pool_read_ahead_seq | 1051 || Innodb_buffer_pool_read_requests | 3501300205 || Innodb_buffer_pool_reads | 222583 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 100636072 || Innodb_data_fsyncs | 17574485 || Innodb_data_pending_fsyncs | 0 || Innodb_data_pending_reads | 0 || Innodb_data_pending_writes | 0 || Innodb_data_read | 661716992 || Innodb_data_reads | 264561 || Innodb_data_writes | 23367666 || Innodb_data_written | 3365293568 || Innodb_dblwr_pages_written | 7346691 || Innodb_dblwr_writes | 196613 || Innodb_log_waits | 0 || Innodb_log_write_requests | 20216582 || Innodb_log_writes | 17001514 || Innodb_os_log_fsyncs | 17170639 || Innodb_os_log_pending_fsyncs | 0 || Innodb_os_log_pending_writes | 0 || Innodb_os_log_written | 3060873216 || Innodb_page_size | 16384 || Innodb_pages_created | 38798 || Innodb_pages_read | 302532 || Innodb_pages_written | 7346864 || Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 2380021 || Innodb_row_lock_time_avg | 13 || Innodb_row_lock_time_max | 5934 || Innodb_row_lock_waits | 182364 || Innodb_rows_deleted | 489705 || Innodb_rows_inserted | 8687733 || Innodb_rows_read | 1131330067 || Innodb_rows_updated | 7790645 || Key_blocks_not_flushed | 16 || Key_blocks_unused | 14481 || Key_blocks_used | 1512 || Key_read_requests | 1872484 || Key_reads | 11571 || Key_write_requests | 537762 || Key_writes | 64 || Last_query_cost | 0.000000 || Max_used_connections | 18 || Not_flushed_delayed_rows | 0 || Open_files | 26 || Open_streams | 0 || Open_tables | 64 || Opened_tables | 0 || Prepared_stmt_count | 0 || Qcache_free_blocks | 2679 || Qcache_free_memory | 10535504 || Qcache_hits | 17852871 || Qcache_inserts | 19669677 || Qcache_lowmem_prunes | 586426 || Qcache_not_cached | 585632 || Qcache_queries_in_cache | 3368 || Qcache_total_blocks | 9440 || Questions | 57178634 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 1 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 0 || Slow_queries | 0 || Sort_merge_passes | 0 || Sort_range | 0 || Sort_rows | 0 || Sort_scan | 0 || Table_locks_immediate | 66591870 || Table_locks_waited | 753 || Tc_log_max_pages_used | 0 || Tc_log_page_size | 0 || Tc_log_page_waits | 3 || Threads_cached | 0 || Threads_connected | 17 || Threads_created | 66 || Threads_running | 1 || Uptime | 1491742 |±----------------------------------±-----------+
I’ve also got the following set in my.cnf
thread_stack = 128Kthread_cache_size = 8innodb_flush_method = O_DIRECTinnodb_buffer_pool_size = 256Minnodb_log_file_size = 256Minnodb_log_buffer_size = 4Minnodb_thread_concurrency = 8query_cache_limit = 1Mquery_cache_size = 16M
I realize that 99.9936% of my reads are cached, but what about the query settings? Is there anything I can tweak there?