MySQL database
As we all know, MySQL is a powerful opensource database and it is one of the world’s most used Relational Database Management System. In this article, I am going to share you how to increase the performance, stability and efficiency for MySQL server along with any applications that are using it.
MySQL Tuner Script
MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, we can tune your my.cnf file to tease out the last bit of performance from our MySQL server and make it work more efficiently.
Configuring MySQL server with MySQLTuner
Step 1: Downloading the MySQLTuner Script
# wget http://mysqltuner.com/mysqltuner.pl
To make the script as executable, do the following
# chmod +x mysqltuner.pl
Step 2: Now run the downloaded script and the output is shown below
# ./mysqltuner.plEnter your mysql username and password while prompting
>> MySQLTuner 0.9.8 - 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
[!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software! Upgrade soon!
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster
[--] Data in MyISAM tables: 301M (Tables: 2074)
[--] Data in HEAP tables: 379K (Tables: 9)
[!!] InnoDB is enabled but isn't being used
[!!] ISAM is enabled but isn't being used
[!!] Total fragmented tables: 215
-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 2.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 320.5M (20% of installed RAM)
[OK] Slow queries: 0% (17/1B)
[OK] Highest usage of available connections: 32% (32/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M
[OK] Key buffer hit rate: 99.9%
[OK] Query cache efficiency: 99.9%
[!!] Query cache prunes per day: 47549
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 28%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 0%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%
[!!] Connections aborted: 20%
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-isam to MySQL configuration to disable ISAM
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
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
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 8M)
tmp_table_size (> 9M)
max_heap_table_size (> 9M)
table_cache (> 64)
Step 3: Setting up recommended values
To change the values of the variables we need to edit the [mysqld] section of your my.cnf file, usually located in /etc/my.cnf, but this may vary for various distros.
Make changes to the global variables suggested by the script and restart the MySQL server.
# /etc/init.d/mysqld restart
We can run the same script again to know variables given correctly, but it is suggested that you wait 24 hours to see how the my.cnf changes affect the performance.
*********************************************************************************
Tips to consider while tuning mysql performance:
Below are notes on some of the important variables while tuning the MySQL performance
1. query_cache_size:
MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
2. key_buffer_size:
The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
3. table_cache:
The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
4. sort_buffer:
The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
5. read_rnd_buffer_size:
The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
6. thread_cache:
If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
7. tmp_table_size:
"Created_tmp_disk_tables" are the number of implicit temporary tables on disk created while executing statements and "created_tmp_tables" are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.
good one..!!
ReplyDelete