high mysql throughput / website load slow

[mahdy_sharifi]

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)




Can You Help ?


Regards

 

[cPanelMichael]

Hello

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

Thank you.

 

[mahdy_sharifi]

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: 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)

 

[cPanelMichael]

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.