Mysql important queries

Mysql important queries

if you use formula Cache hit rate = ((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100) and get 70% I think it is fair enough. However, if you have enough free memory you can increase query_cache_size=128 and obersve some days if hit ratio increases .


2. show global variables like ‘max_connections’;


3. mysqldump -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz mysqldump -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz binlog_format = ‘MIXED’  expire_logs_days=10

  1. Now you can restore the files when needed (remember to do this as user postgres after recreating the empty database):

# gunzip -c postgresql_db_2015-09-02.Wednesday.sql.gz | psql postgresql_db








Performance Tuning

  1. Enable InnoDB file-per-table


The innodb_file_per_table is enabled by default in MySQL 5.6 and above. You can see that in /etc/my.cnf file. The directive looks like this:



2.     Store MySQL Database Data on Separate Partition



To complete, this you will need to attach the new drive to your computer/server. For the purpose of this article, I will assume that the drive will be under /dev/sdb.

The next step is to prepare the new drive:

# fdisk /dev/sdb# mkfs.ext4 /dev/sdb1# mount /dev/sdb1  /ssd//dev/sdb1 /ssd ext3 defaults 0 0# service mysqld stop# service httpd stop# service nginx stop# cp /var/lib/mysql /ssd/ -Rp# mv /var/lib/mysql /var/lib/mysql-backup# ln -s /ssd/mysql /var/lib/mysql

Now you are ready to start your MySQL and web service:

# service mysqld start# service httpd start# service nginx start


3. Optimizing InnoDB buffer pool Usage

Here is what you need to consider:

  1. How much memory you need for other processes. This includes your system processes, page tables, socket buffers.
  2. Is your server dedicated for MySQL or you will be running other memory hungry services.

On a dedicated box, you would probably want to give about 60-70% of the memory to the innodb_buffer_pool_size. If you plan on running more services on a single box, you should re-consider the amount of memory you dedicate for your innodb_buffer_pool_size.

The value that you should edit in my.cnf is:


4.Avoid Swappiness in MySQL

By default the option is enabled:

# sysctl vm.swappiness vm.swappiness = 60

To disable swappiness, run the following command:

# sysctl -w vm.swappiness=0


5. Set MySQL Max Connections

Keep in mind that too many connections can cause high RAM usage and lock up your MySQL server. Usually small websites will require between 100-200 connections while larger may require 500-800 or even more. The value you apply here strongly depends on your particular MySQL/MariaDB usage.

You can dynamically change the value of max_connections, without having to restart the MySQL service by running:

# mysql -u root -p

mysql> set global max_connections := 300;


6. Configure MySQL thread_cache_size


improve your performance you can set the thread_cache_size to a relatively high number. To find the thread cache hit rate, you can use the following technique:

mysql> show status like ‘Threads_created’;

mysql> show status like ‘Connections’;

Now use the following formula to calculate the thread cache hit rate percentage:

100 – ((Threads_created / Connections) * 100)



The good thing here is that the thread_cache_size can be dynamically changed without having to restart the MySQL service. You can achieve this by running:

mysql> set global thread_cache_size = 16;

7. Disable MySQL Reverse DNS Lookups

This unfortunately may cause delays in case of badly configured DNS or problems with DNS server. This is why you can disable the reverse DNS lookup by adding the following in your configuration file:


[mysqld]# Skip reverse DNS lookup of clientsskip-name-resolve

You will have to restart the MySQL service after applying these changes.




8. Configure MySQL query_cache_size



The reason behind that is the fact that threads need to lock the cache during updates. Usually value of 200-300 MB should be more than enough. If your website is relatively small, you can try giving the value of 64M and increase in time.

You will have to add the following settings in the MySQL configuration file:

query_cache_type = 1

query_cache_limit = 256K

query_cache_min_res_unit = 2k

query_cache_size = 80M

  9. Configure tmp_table_size and max_heap_table_size


This will affect the database performance. Administrators usually recommend giving 64M for both values for every GB of RAM on the server.


tmp_table_size= 64M

max_heap_table_size= 64M


10. Enable MySQL Slow query Logs

Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:

slow-query-log = 1

slow-query-log-file = /var/lib/mysql/mysql-slow.log

long_query_time = 1

The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use long_query_time to define the amount of time that is considered long for MySQL query to be completed.




11. Check for MySQL idle Connections

# mysqladmin processlist -u root -p | grep “Sleep”

The first thing you would do here is to check the code and fix it. If you don’t have access to the code that is being ran, you can change the wait_timeout directive. The default value is 28800 seconds, while you can safely decrease it to something like 60:




12. Choosing Right MySQL Filesystem


As per MariaDB’s recommendations, the best file systems are XFS, Ext4 and Btrfs. All of them are enterprise journaling filesystems that can be used with very large files and large storage volumes.

Below you can find some useful information about the three filesystems:

Filesystems XFS Ext4 Btrfs
Maximum filesystem size 8EB 1EB 16EB
Maximum file size 8EB 16TB 16EB



13. Set MySQL max_allowed_packet

MySQL splits data into packets. Usually a single packet is considered a row that is sent to a client. The max_allowed_packet directive defines the maximum size of packet that can be sent.

Setting this value too low can cause a query to stall and you will receive an error in your MySQL error log. It is recommended to set the value to the size of your largest packet.


14. Check MySQL Performance Tuning

There are plenty of tools available for benchmarking, but I would like to suggest you one that is simple and easy to use. The tool is called mysqltuner.

To download and run it, use the following set of commands:

# wget

# tar xf master

# cd major-MySQLTuner-perl-993bc18/

# ./


15. Optimize and Repair MySQL Databases


Surprisingly, there is a free open source tool called ‘mysqlcheck‘, which automatically check, repair and optimize databases of all tables in Linux.

# mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

# mysqlcheck -u root -p –auto-repair –check –optimize databasename

That’s it! I hope you have found the above article useful and help you tune up your MySQL server. As always if you have any further questions or comments, please submit them in the comment section below.



4 Useful Commandline Tools to Monitor MySQL Performance in Linux


1. Mytop

2. Mtop

3. Innotop

4. mysqladmin

[root@localhost ~]# mysqladmin -u root -p version

Enter password:







Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s