MYSQL best performance optimized parameters for 4G server

In this article, I illustrate the MYSQL 5.6 best performance parameters for Linux 4G - 4Core - 8 Threads dedicated server (or virtual dedicated server, VPS) based on my personal experience. I have tried many other parameters that I found online until I reached the best performance and stability with the following ones:


You can edit these parameters directly within the my.cnf file. For Linux, it should be somewhere within "etc" folder. Just use the following command to edit the file:


After done editing the my.cnf file, you have to restart the MySQL server in order that the recent changes take effect. For Linux, you can just use the following command:

Please note that these parameter configurations are highly depending on the hardware specifications of your server. For instance, if you have a server memory of 8G (RAM) then, it is totally recommended to increase the size of innodb_buffer_pool_size (may be up to 3.5G). Notice that I have two instances of innodb_buffer_pool. The reason for having more than one instance of the innodb_buffer_pool is increasing the concurrency. Enabling only one innodb_buffer_pool instance (which is the default) can significantly increase the bottlenecks for the threads working the on same Mysql tables.

MySQL Cache Problem

I have the Cache enabled in the mentioned configuration:

The size of the cache is set to

However, I have tried to increase the cache size up to large values such as 100M or even 500M. In the beginning, I noticed a really great improvement in the performance. However, as time goes, the MYSQL starts to slow down and down until you reach a point that you have to restart it. The technical reason behind such a behavior is that the maintenance cost of cache buffer is highly correlated with the size.

Increasing the Join Queries Speed for MySQL:

I have suffered a lot from slow join queries, especially that I have LARGE tables in those queries. The traditional solution for such an issue is to add indexes on the columns used in the join. If you have two columns from the same table make sure also that they have a single index that combines both of them as follows:

However, can also try to increase the speed by adding more memory to the join buffer and sort buffer:

MySQL Storage Engine:

If your tables are still using MyISAM engine, then it is a horrible crime against the scalability of your database performance. I have the default engine set to MyISAM but I have personally changed the engine of the tables to InnoDB. If you changed the default engine of your Mysql to InnoDB, then it does not mean that your old MyISAM tables will automatically switch to InnoDB. However, you have to do it yourself. Innodb has great pros over the classical storage MyISAM and have been just added to MySQL after version 5. So, I highly recommend to use it. Otherwise, my suggested configuration in this article will not help you.



Generally, the best configuration is different from case to case and highly depends on the database schema and usage. There are some tools that can help in the optimization of MySQL parameters. I did not try it myself but I recommend that you give it a try in case that the mentioned configurations in this article did not work. If you have problems with the IO, I highly recommend that you try to SSD drives instead of HDD. The configuration parameters I propose in this article is applied is a server with HDD.

MySQL Version:

The configuration experiments in this article have been done on MySQL 5.6.18. It is important to note that the default value for each configuration parameter can vary from MySQL release to another. You may need to check that If you decided to ignore any of the proposed configuration parameters.