Optimizing MySQL with MySQLTuner

[subhra]

Last month I was facing frequent MySQL crash problem. I tried to optimize MySQL with MySQLTuner, it started working fine.

But from last 2-3 days, I am facing the server down problem frequently. If I power cycle the server and restart MySql, then only it works properly. I am not sure whether it is MySQL crash problem or not.

My MySQLTuner report,

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 617K (Tables: 126)
[--] Data in InnoDB tables: 73M (Tables: 155)
[!!] Total fragmented tables: 166

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 30m 26s (2K q [1.514 qps], 107 conn, TX: 9M, RX: 410K)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 132.0M global + 2.7M per thread (50 max threads)
[OK] Maximum possible memory usage: 269.5M (27% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 6% (3/50)
[OK] Key buffer size / total MyISAM indexes: 8.0M/419.0K
[!!] Key buffer hit rate: 80.0% (65 cached / 13 reads)
[OK] Query cache efficiency: 64.9% (1K cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 180 sorts)
[!!] Temporary tables created on disk: 30% (60 on disk / 200 total)
[OK] Thread cache hit rate: 97% (3 created / 107 connections)
[!!] Table cache hit rate: 1% (67 open / 3K opened)
[OK] Open file limit used: 1% (17/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 73.7M/75.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_cache (> 67)

And my current my.cnf file settings:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
default-storage-engine=InnoDB
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=50
wait_timeout=30
query_cache_size=15M
tmp_table_size=32M
max_heap_table_size=32M
thread_cache_size=4
innodb_buffer_pool_size=75M
table_cache=67

slow-query-log=1
slow_query_log_file=/var/log/mysqld/slow-query.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Can anyone suggest what I need to do to improve the server performance?

My server configuration:
CentOS
1 GB RAM
512 MB swap memory

I get average 3000 visits per day

 

[subhra]

Also can anyone suggest me how can improve the performance for my website? It is developed using wordpress.

 

[cPanelMichael]

I suggest letting MySQL run for at least 24 hours before running the tuner. This will ensure more accurate results that you can provide here.

Thank you.

 

[subhra]

Hi,

Here is the latest MySQLTuner report,

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 569K (Tables: 126)
[--] Data in InnoDB tables: 72M (Tables: 155)
[!!] Total fragmented tables: 156

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 18m 13s (187K q [2.144 qps], 3K conn, TX: 411M, RX: 21M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 140.0M global + 2.7M per thread (50 max threads)
[OK] Maximum possible memory usage: 277.5M (27% of installed RAM)
[OK] Slow queries: 0% (0/187K)
[OK] Highest usage of available connections: 26% (13/50)
[OK] Key buffer size / total MyISAM indexes: 8.0M/414.0K
[!!] Key buffer hit rate: 83.3% (2K cached / 397 reads)
[OK] Query cache efficiency: 79.9% (137K cached / 171K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[!!] Temporary tables created on disk: 34% (2K on disk / 5K total)
[OK] Thread cache hit rate: 99% (21 created / 3K connections)
[!!] Table cache hit rate: 4% (70 open / 1K opened)
[OK] Open file limit used: 1% (19/1K)
[OK] Table locks acquired immediately: 100% (42K immediate / 42K locks)
[OK] InnoDB data size / buffer pool: 72.2M/75.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    tmp_table_size (> 40M)
    max_heap_table_size (> 40M)
    table_cache (> 70)

And current my.cnf file settings:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
default-storage-engine=InnoDB
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=50
wait_timeout=30
query_cache_size=15M
tmp_table_size=40M
max_heap_table_size=40M
thread_cache_size=4
innodb_buffer_pool_size=75M
table_cache=70

slow-query-log=1
slow_query_log_file=/var/log/mysqld/slow-query.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid