Oops! It appears that you have disabled your Javascript. In order for you to see this page as it is meant to appear, we ask that you please re-enable your Javascript!
Home / Wordpress / Improve database Performance MySQL Tuner
MySQL Tuner Database Optimization
MySQL Tuner Database Optimization

Improve database Performance MySQL Tuner

MySQL Tuner Improve database Performance

Running MySQL at optimal settings for specific resources helps handle larger server loads and prevents server slowdown. Generally, after tuning Apache to handle larger loads, it is beneficial to tune MySQL to additional connections. Most of the times when you have issue with MySQL due to overloading or bulk amount of visitors on website causes issues with server overloading SQL shutdown.  MySQL Tuner script assesses your MySQL installation, and then outputs suggestions for increasing your server’s performance and stability.

How to Download MySQL Tuner 

The latest stable MySQL Tuner script is available in plaintext (36K)and gzipped (10K) versions. Beta versions are available via subversion, and you can find the instructions in the Trac repository.

Contact in case of any issue 

MySQL Tuner has a low-traffic mailing list for new version announcements and bug fixes. To get on the list, simply send an e-mail to mysqltuner-announce-request@mysqltuner.com with SUBSCRIBE in the subject line. The e-mails will appear to come from MySQL Tuner-announce@mysqltuner.com, so please be sure to add it to your e-mail provider’s white list.

Installation and Requirements:
The script is a basic Perl script that has no additional requirements. At this time, MySQL Tuner is compatible with Linux/Unix based operating systems with Perl 5.6 or later. MySQL versions 3.23 and later are supported by the script.To get started, simply download MySQL Tuner and make it executable:

# wget http://mysqltuner.com/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

Major Features:

NEW! Remote server checks: Connects to remote servers to perform tests.

NEW! Convenient MySQL logins: MySQLTuner will automatically log in to MySQL on servers running Plesk, servers with ~/.my.cnf files, and server without MySQL root passwords. If you need to authenticate manually, you can pass the username and password on the command line or provide the credentials interactively.

NEW! Manually set memory sizes: Users on virtual environments can now specify how much RAM and swap memory are present so that the script’s calculations will be accurate.

Memory Usage: Calculates MySQL memory usage at max load and makes recommendations for increasing or decreasing the MySQL memory footprint. Per-thread and server-wide buffer data is calculated separately for an accurate snapshot of the server’s configuration.

Slow Queries: Reviews the amount of slow queries relative to the total queries. Slow query time limits are also analyzed and recommendations are made.

Connections: Current and historical connection counts are reviewed.

Key Buffer: Takes configuration data and compares it to the actual indexes found in MyISAM tables. Key cache hit rates are calculated and variable adjustments are suggested.

Query Cache: Query cache hit rates and usage percentages are used to make recommendations for the query cache configuration variables.

Sorting & Joins: Per-thread buffers that affect sorts and joins are reviewed along with the statistics from the queries run against the server.

Temporary Tables: Variable recommendations are made to reduce temporary tables that are written to the disk.

Table Cache: Compares total tables opened to the currently open tables. Calculates the table cache hit rate in order to make suggestions.

Open Files: Determines if the server will approach or run into the open file limit set by the operating system or the MySQL server itself.

Table Locks: Finds table locking that forces queries to wait and makes suggestions for reducing locks that require a wait.

Thread Cache: Calculates how many times MySQL must create a new thread to respond to a query.

Aborted Connections: Finds applications that are not closing connections to MySQL properly.

Read/Write Ratios: Calculates the percentage of read and write operations on your MySQL installation.

Tuning  Of MySQL

When altering the MySQL configuration, be alert to the changes and how they affect your database. Even when following the instructions of programs such as MySQL Tuner, it is best to have some understanding of the process.

The file you are changing is located at /etc/mysql/my.cnf.

Note

Prior to updating the MySQL configuration, create a backup of the my.cnf file:

cp /etc/mysql/my.cnf ~/my.cnf.backup

Best practice suggests that you make small changes, one at a time, and then monitor the server after each change. You should restart MySQL after each change:

For systems without systemd:

systemctl restart mysqld

For distributions which don’t use systemd:

service mysql restart

When changing values in the my.cnf file, be sure that the line you are changing hasn’t been commented out with the pound (#) prefix.

key_buffer

Changing the key_buffer allocates more memory to MySQL, which can substantially speed up your databases, assuming you have the memory free. The key_buffer size should generally take up no more than 25 percent of the system memory when using the MyISAM table engine, and up to 70 percent for InnoDB. If the value is set too high, resources are wasted.

According to MySQL’s documentation, for servers with 256MB (or more) of RAM with many tables, a setting of 64M is recommended. Servers with 128MB of RAM and fewer tables can be set to 16M, the default value. Websites with even fewer resources and tables can have this value set lower.

max_allowed_packet

This parameter lets you set the maximum size of a sendable packet. A packet is a single SQL state, a single row being sent to a client, or a log being sent from a master to a slave. If you know that your MySQL server is going to be processing large packets, it is best to increase this to the size of your largest packet. Should this value be set too small, you would receive an error in your error log.

thread_stack

This value contains the stack size for each thread. MySQL considers the default value of the thread_stack variable sufficient for normal use; however, should an error relating to the thread_stackbe logged, this can be increased.

thread_cache_size

If thread_cache_size is “turned off” (set to 0), then any new connection being made needs a new thread created for it. When the connections disengage the thread is destroyed. Otherwise, this value sets the number of unused threads to store in a cache until they need to be used for a connection. Generally this setting has little affect on performance, unless you are receiving hundreds of connections per minute, at which time this value should be increased so the majority of connections can be made on cached threads.

max_connections

This parameter sets the maximum amount of concurrent connections. It is best to consider the maximum amount of connections you have had in the past before setting this number, so you’ll have a buffer between that upper number and the max_connections value. Note, this does not indicate the maximum amount of users on your website at one time; rather it shows the maximum amount of users making requests concurrently.

table_cache

This value should be kept higher than your open_tables value. To determine this value use:

SHOW STATUS LIKE 'open%';

 

About admin

Check Also

website performance testing tools

best Website Speed test tools

Pingdom Website Speed Test Pingdom tools is one of the best website speed analysis tool that …

Leave a Reply

Your email address will not be published. Required fields are marked *

Optimization WordPress Plugins & Solutions by W3 EDGE