Optimizing MySQL with MySQLTuner

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,

Code:

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

Code:

[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

 

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

 

Hi,

Here is the latest MySQLTuner report,

Code:

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

Code:

[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