eliko

Member
Jun 11, 2014
6
2
3
cPanel Access Level
Website Owner
Hey Everyone

We currently have an issue with the settings in our MySQL database:

Stats:
VPS
24 cores
4GB ram

Below are the results from mysqltuner:

Code:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 5G (Tables: 162)
[--] Data in InnoDB tables: 1G (Tables: 186)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 23

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 11h 44m 58s (17M q [83.298 qps], 571K conn, TX: 53B, RX: 1B)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 1.4G global + 1.1M per thread (250 max threads)
[OK] Maximum possible memory usage: 1.6G (41% of installed RAM)
[OK] Slow queries: 0% (385/17M)
[OK] Highest usage of available connections: 15% (38/250)
[OK] Key buffer size / total MyISAM indexes: 32.0M/19.4M
[OK] Key buffer hit rate: 100.0% (90M cached / 9K reads)
[OK] Query cache efficiency: 36.1% (5M cached / 14M selects)
[!!] Query cache prunes per day: 299228
[OK] Sorts requiring temporary tables: 4% (71K temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 2% (4K on disk / 173K total)
[OK] Thread cache hit rate: 99% (94 created / 571K connections)
[OK] Table cache hit rate: 41% (523 open / 1K opened)
[OK] Open file limit used: 2% (415/20K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
[OK] InnoDB buffer pool / data size: 1.1G/1.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
    query_cache_size (> 160M) [see warning above]

Please advice?

- - - Updated - - -

The my.cnf :

Code:
[mysqld]
slow-query-log=1
long_query_time=1
long_query_time=4
default_storage_engine=InnoDB
key_buffer_size=32M
query_cache_type=1
query_cache_size=160M
query_cache_limit=2M
max_allowed_packet=268435456

max_connections=250
table_open_cache=10000

innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=1152M
innodb_log_buffer_size=16M
innodb_log_file_size=128M

max_heap_table_size=32M
tmp_table_size=64M

connect_timeout=30
max_connect_errors=1000
open_files_limit=10000
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
Hello :)

Could you elaborate on the specific issue you are facing with MySQL? Or, do you simply want to optimize the performance?

Thank you.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
Note that you may need to post the results on another forum such as WHT if you don't receive enough user-feedback on our forums here.

Thank you.