Room for tweaking?

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?

one thing I suggest is to increase the thread_cache_size.
Try mysqlreport ([URL]http://hackmysql.com/mysqlreport[/URL]) to report the mysql status every hour, you could see how well your mysql server running. I’m using this tool and it help me a lot in tuning mysql parameter.

Looks like a handy tool. Great!