Skip to main content

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

Comments

3 comments

Date Votes
  • subhra
    Also can anyone suggest me how can improve the performance for my website? It is developed using wordpress.
    0
  • 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.
    0
  • 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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post