[/caption]If any of you own a dedicated server Or VPS and want to reduce the MySQL load hopefully I can help. Before doing the following steps it is advised to have had the MySQL service running for at least 24 hours, preferred 48 hours +. In the following tutorial i will describe the steps to optimize the performance of a MySQL database with the mysqltuner script:
Method 1
CentOS:
Download MySQLTuner:
wget http://mysqltuner.com/mysqltuner.pl
Make executable:
chmod 755 mysqltuner.pl
Run MySQLTuner:
./mysqltuner.pl
Then the output will display what you need to do/can do to increase performance. Most of the configuration will lay in the following file:
/etc/my.cnf
If you can't understand its again
Method 2
Step1. Download the mysqltuner script:
cd /usr/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl
Step2. Run mysqltuner:
root@deb:~# /usr/bin/mysqltuner.pl
Enter the root as username and the password and you will get a output like this:
root@deb:~# /usr/bin/mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.58-1~dotdeb.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 414M (Tables: 532)
[--] Data in InnoDB tables: 5M (Tables: 1)
[!!] Total fragmented tables: 17
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19d 13h 22m 29s (35M q [20.806 qps], 1M conn, TX: 1427B, RX: 8B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (4/35M)
[!!] Highest connection usage: 100% (152/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/63.7M
[OK] Key buffer hit rate: 99.9% (2B cached / 1M reads)
[OK] Query cache efficiency: 65.4% (17M cached / 26M selects)
[!!] Query cache prunes per day: 30445
[OK] Sorts requiring temporary tables: 0% (100 temp sorts / 910K sorts)
[!!] Temporary tables created on disk: 39% (375K on disk / 950K total)
[OK] Thread cache hit rate: 99% (6K created / 1M connections)
[!!] Table cache hit rate: 0% (64 open / 121K opened)
[OK] Open file limit used: 11% (122/1K)
[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
[OK] InnoDB data size / buffer pool: 5.5M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
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:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 64)
root@deb:~#
0 comments:
Post a Comment