high mysql throughput / website load slow

mahdy_sharifi

• December 29, 2014 03:36

Hello : on a big server with many user and website from 11AM - 00AM our mysql throughput reaised in max (60 - 80M) this timestamp our customer website load slowing . we have,'t any Disk IO, and server HDD : 6 * 600 G SAS 15K + 64 G RAM mysql config : innodb_buffer_pool_size=8192M (2 G above mysqltuner recommendation) .vB Can You Help ? Regards

• 

  cPanelMichael

  • January 05, 2015 16:36

  Hello :) I've moved this thread to our "Optimization" forum. Could you post your MySQL tuner results here? Thank you.

  0
• 

  mahdy_sharifi

  • January 05, 2015 19:44

  thanks, output : -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 21h 39m 30s (388M q [2K qps], 7M conn, TX: 9588B, RX: 72B) [--] Reads / Writes: 80% / 20% [--] Total buffers: 8.7G global + 114.8M per thread (299 max threads) [OK] Maximum possible memory usage: 42.2G (67% of installed RAM) [OK] Slow queries: 0% (1K/388M) [!!] Highest connection usage: 100% (300/299) [OK] Key buffer size / total MyISAM indexes: 128.0M/10.8G [OK] Key buffer hit rate: 99.3% (24B cached / 164M reads) [OK] Query cache efficiency: 68.7% (226M cached / 329M selects) [!!] Query cache prunes per day: 20287144 [OK] Sorts requiring temporary tables: 0% (7K temp sorts / 19M sorts) [!!] Joins performed without indexes: 622042 [!!] Temporary tables created on disk: 43% (3M on disk / 8M total) [OK] Thread cache hit rate: 99% (329 created / 7M connections) [!!] Table cache hit rate: 0% (12K open / 97M opened) [OK] Open file limit used: 2% (21K/900K) [OK] Table locks acquired immediately: 99% (147M immediate / 147M locks) [OK] InnoDB buffer pool / data size: 8.0G/6.4G [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce or eliminate persistent connections to reduce connection usage Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog Variables to adjust: max_connections (> 299) wait_timeout (< 30) interactive_timeout (< 120) query_cache_size (> 512M) [see warning above] join_buffer_size (> 98.0M, or always use indexes with joins) tmp_table_size (> 32M) max_heap_table_size (> 16M) table_open_cache (> 12288)
  

  0
• 

  cPanelMichael

  • January 07, 2015 16:17

  You could try modifying your /etc/my.cnf file based on the recommendations from the tuner and then running the tuner again after 24 hours. However, you may want to consult with a qualified system administrator if you do not receive user-feedback to your results. Thank you.

  0

Please sign in to leave a comment.