The major advantage of identifying the performance-driving factor for a database is that it allows you to avoid over-provisioning and reduce costs by right-sizing your servers. It also gives you insights into whether moving data storage or adding server capacity will improve performance and, if so, how much it will be.
The tuning database for MySQL query performance optimization doesn’t come with pale challenges. However, once tuned properly, the database gives worthwhile performance results with great functionalities. It lowers unwanted task load and optimizes the MySQL database for faster data retrieval.
I am sharing some of the parameters that have worked for me:
| Parameter | Workstation | Server Shared | Database Server Dedicated |
| max_connections | 170 | 800 | 800 |
| query_cache_size | 8M | 33M | 79M |
| table_cache | 340 | 1520 | 1520 |
| tmp_table_size | 9M | 16M | 28M |
| thread_cache | 8 | 38 | 38 |
| myisam_sort_buffer_size | 2M | 12M | 28M |
| key_buffer_size | 2M | 5M | 13M |
| read_rnd_buffer_size | 252k | 256k | 256k |
| sort_buffer_size | 189k | 256k | 256k |
| innodb_additional_mem_pool_size | 2M | 4M | 10M |
| innodb_log_buffer_size | 1M | 2M | 4M |
| innodb_buffer_pool_size | 512M | 2G | 3G |
| innodb_log_file_size | 10M | 36M | 89M |
Other settings:
INNODB specific options
innodb_additional_mem_pool_size=2M
Additional memory pool, which is used by InnoDB to store metadata information. If InnoDB requires more memory it will start to allocate it from the OS. As this is fast enough on most recent operating systems, you normally do not need to change this value. SHOW INNODB STATUS will display the current amount used. Please increase this value slowly to see, if it takes affect for your performance problem.
innodb_flush_log_at_trx_commit=1
If set to 1, InnoDB will flush (fsync) the transaction logs to the disk at each commit, which offers full ACID behavior. If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 or 2 to reduce disk I/O to the logs. Set this value to 0 if you want to restore a database.
innodb_log_buffer_size=10M
The size of the buffer InnoDB uses for buffering log data. As soon as it is full, InnoDB will have to flush it to disk. As it is flushed once per second anyway, it does not make sense to have it very large (even with long transactions). Make sense to increase if you do a database restore.
innodb_buffer_pool_size=92M
InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and row data. The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. Note that on 32bit systems, you might be limited to 2-3.5G of user-level memory per process, so do not set it too high.