Expert Tips for MySQL Server Optimization

Effective database optimization can help you to avoid many performance issues that can occur while administering or creating applications with MySQL Server. From the initial configuration of the server to the design of the database structure and population of tables, principles of database optimization should be carefully implemented. This article provides proven MySQL optimization techniques that you can use to improve the performance of your database and web applications.

Optimize Database Server Hardware

The projected size of the database and the volume of transactions that will be executed by the database should be used to estimate the hardware requirements of the server. But as the database grows in size and becomes more busy, hardware limitations can reduce its performance. Therefore, it may be necessary to reconfigure the server hardware and add more resources. Increasing the hardware resources will help to overcome bottlenecks caused by disk reading, seeking, and writing as well as increased CPU cycles and the use of swap files.

The four major resources used by a database server are: memory (RAM), CPU, disk drives, and network bandwidth. Adding more memory will make it easier for MySQL Server to cache frequently accessed data and improve performance. Use the fastest CPU you can afford since it will help the queries to be executed faster on a single thread. However, MySQL Server seldom uses high processing power except for running InnoDB tables, or when the memory is insufficient and it has to use swap files.

The third major resource you should increase is the number of disks. With additional disk drives, the server can distribute data into more disks and optimize performance while reading or writing data. Thus, the database will process data simultaneously from multiple disks. In addition, you can improve performance by using solid state device (SSD) disks instead of conventional hard drives. Conventional drives can perform a maximum of 200 operations per second while SSD disks can do up to 20,000 operations per second. A comparison chart can be gained from google to see how SSD drives perform over conventional HDD drives.

Configure Performance Settings for InnoDB Database Tables

MySQL Server 5.5 and newer versions use the InnoDB storage engine by default. InnoDB tables provide better overall performance than MyISAM tables. However InnoDB tables require more server resources for optimum performance. That is why you need to configure it properly with the following performance tuning settings:

* Innodb_buffer_pool_size: This setting determines the size of the storage location or cache where the data and the indexes of the database will be stored. The value of this parameter should be as big as possible so that MySQL Server will use memory instead of disks for read operations. The setting should be based on the amount of RAM you have. A good rule of thumb is to use about 75% percent of your RAM on a dedicated database server. For example, if you have 16 GB RAM, you should use 12 GB, and if you have 32 GB RAM, you can set this parameter to 24 GB.

* Max_connections: The value assigned to this parameter determines the total number of connections that the database can accomodate at once. If you see the “too many connections” database error, it means that the value assigned to this parameter is too small or your application is not managing connections efficiently. Hence, you should change the default 151 connections to a higher value.
However, the maximum number should not be allowed to get to 1000 because at this point the database will not respond properly if an application opens up to 1000 connections within the same session. To enhance database optimization, you should use a thread pool or manage the number of open connections in the application instead of permitting so many open connections on the database server.

* Innodb_log_file_size: This setting determines the redo log size. The redo log is used to ensure that writing to the database is fast and efficient. A larger redo log gives better performance and quicker crash recovery. If you are using a MySQL Server that is older than version 5.6, you may be limited to 4 GB. But from version 5.6 or higher, you can set it to a value higher than 4 GB.

* Innodb_log_buffer_size: This buffer is used to hold transactions that are yet to be committed to your database. With transactions that use large text fields or the blob data type, the default buffer size may be inadequate. This could cause the server to make use of virtual memory and increase the amount of I/O load. To determine whether you need to increase this value, look at the status variable called innodb_log_waits. If the value is not 0, then raise the value of the buffer size to a value that will meet the needs of your application.

Optimal Parameters for /etc/my.cnf

innodb_buffer_pool_size= 24G ## 75% of your physical RAM

innodb_log_file_size= 64M ## Optimal setting

innodb_flush_log_at_trx_commit = 2 ## Optimal setting

innodb_thread_concurrency = 16 ## 2 times the number of CPU cores

innodb_buffer_pool_instances = 16 ## Optimal setting for concurrent workload

innodb_flush_method= O_DIRECT ## Avoids double buffering

innodb_write_io_threads = 8

innodb_read_io_threads = 8

Create a Work Load Profile

To have a good understanding of how the database server uses its time, create a profile of the server workload. This is very important for database application developers. A workload profile will show you the queries that are consuming the most time and also give you a hint on how to fine tune them. Use the query analyzer in the MySQL Enterprise Monitor or other proprietary tools and scripts for this purpose. These tools will keep a record of the queries that the server executes and provide a detailed list of the queries with the most expensive queries (in terms of time) listed first. For maximum performance improvement, you should focus on two sets of queries: those that execute very slowly and those that execute quickly but occur very often.

Improve Performance of Large Tables

As tables increase in size, their performance tends to decrease, even when you have adequate hardware. However, the following ideas can help you to reduce the negative effect of having very large tables:

* Avoid the MyISAM storage engine: Switching over to the InnoDB storage engine will improve overall performance of your large tables. Although MyISAM inserts new records quickly at the end of the table, it slows down deletion and new updates due to its table locking mechanisms. The single lock used to protect the key buffer also causes conflicts when loading data to disk. However, the InnoDB engine has a change buffering system that delays the creation of secondary indexes which are unique. This results in improved performance when inserting new records into the database tables. That is why you should upgrade your old MySQL databases to version 5.6 because it uses an improved version of the InnoDB engine.

* Manage Indexes: While working with very large tables with millions of records, indexes should be properly managed. First, partition the database so that the size of the indexes can be reduced. Thus the database will be able to process smaller indexes simultaneously. Then eliminate all unnecessary indexes. In particular, remove UNIQUE indexes that not needed because they will disable change buffering and have an adverse effect on performance; use an ordinary index instead. In addition, make sure you have a primary key on all your tables and use BIGINT or INT rather than GUID as the datatype.

Conclusion

These are just a few of the database optimization techniques you can apply on your MySQL Server. There are many other settings that can have an impact on performance depending on your hardware and workload. However, the ideas provided here will enable you to start with a suitable MySQL server configuration.