GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Room for tweaking?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

  • #2
    one thing I suggest is to increase the thread_cache_size.
    Try mysqlreport (http://hackmysql.com/mysqlreport) 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.

    Comment


    • #3
      Looks like a handy tool. Great!

      Comment

      Working...
      X