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,
And my current my.cnf file settings:
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
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
My server configuration:
CentOS
1 GB RAM
512 MB swap memory
I get average 3000 visits per day