MYSQL best performance optimized parameters for 4G server

Mon Mar 20, 2017 6:03 pm

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:
Code:
[mysqld]
performance-schema=0

query_cache_type=1
default-storage-engine=MyISAM
interactive_timeout=100
key_cache_block_size=2096
max_heap_table_size=32M
max_join_size=1000000000
max_allowed_packet=40M
open_files_limit=27099
query_cache_min_res_unit=50K
query_cache_limit=1M
query_cache_size=42M
thread_cache_size=400
tmp_table_size=32M
wait_timeout=60
max_connections=400
max_user_connections=500
myisam_recover_options=FORCE
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
innodb_purge_threads=1
innodb_support_xa=0
innodb_thread_concurrency=20
innodb_buffer_pool_size=2200M
innodb_buffer_pool_instances=2
innodb_read_io_threads=14
innodb_write_io_threads=14
key_buffer_size=100M
innodb_log_file_size=2G
join_buffer_size=600M
read_buffer_size=6M
table_open_cache=4000
table_definition_cache=10000
innodb_checksum_algorithm=crc32
table_open_cache_instances=2
sort_buffer_size=12M


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:
Code:
vi my.cnf


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:
Code:
service MySQL restart

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:
Code:
query_cache_type=1

The size of the cache is set to
Code:
query_cache_size=42M

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:
Code:
ALTER TABLE `tableName` ADD INDEX `IndexName` (`Col1`,`Col2`)

However, can also try to increase the speed by adding more memory to the join buffer and sort buffer:
Code:
join_buffer_size=600M
sort_buffer_size=12M



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.


  Related Posts  to : MYSQL best performance optimized parameters for 4G server
 MySQL server information getting in php     -  
 How to Connect to MySql Server     -  
 C++ & Java Portability and performance!!!!     -  
 jobs-130748-Performance Tester in Bangalore, Kolkata     -  
 Sql server or windows server question?     -  
 Encoding parameters in URL     -  
 Reference Parameters     -  
 Post parameters using JQuery     -  
 Method Parameters,,,is it by value or reference???     -  
 get url, title and url sent parameters with JQuery     -