innodb_io_capacity
This defines the capacity of the MySQL to use the output and input operations. This innodb_io_capacity controls the number of I/O operations that innodb performs per second.
innodb_io_capacity=21000
innodb_buffer_pool_size
To enhance the performance of the application, MySQL maintains a store, wherein it caches the data and indexes in its memory instead of using the disc. The default value of the innodb buffer pool is 128M. The more the buffer pool size, the more the in-memory space.
buffer_pool_size=378M
innodb_flush_method
This controls the process of how the data and log files are opened and flushed. The setting O_DIRECT assists in enhancing the performance by neglecting the double buffering and reducing swap pressure.
innodb_flush_method=O_DIRECT
Innodb_log_buffer_size
The innodb log buffer grants the transaction to run without the need to write the log to disk. This process occurs even before the transactions are committed. Hence, configuring the lag buffer size to a larger value for a larger transaction helps to save the I/O disk. The default value of the innodb log buffer size is 1MB.
innodb_log_buffer_size=8M
Innodb_flush_log_at_trx_commit
To maintain a balance between performance and data safety, a configurable variable innodb_flush_log_trx_commit is provided by the innodb. The main purpose of this variable is to control the flushing frequency of the log buffer.
innodb_flush_log_at_trx_commit=1
innodb_file_per_table
This configuration helps the tables to use its own .ibd file and thereby assists in recovering the space during the table truncation process. Further, the tablespace will not be shared among different tables and different schemas. The size of the tablespace will not be reduced and each file is considered to be a tablespace of its own.
innodb_file_per_table=ON
innodb_thread_concurrency
To process the requests from the user transactions, innodb needs operating system threads. Usually, innodb is subject to various issues before the transactions are rolled back or committed. One can adjust the number of threads by configuring the innodb thread concurrency.
innodb_thread_concurrency=0
join_buffer_size
It is the size of the buffer which is used for joins and leaves a huge impact over the results when it comes to I/O optimization and newer joins.
read_buffer_size
The read_buffer_size is tuned so as to scan the fast full table for specifically larger tables. To do this, you have to set the variable to a higher value. The default value of the read buffer size is 128K.
read_buffer_size=32M
read_rnd_buffer_size
To avoid the disk’s assistance, the rows are read through this buffer at the end of the reading rows in sorted order. Tuning the read_rnd_buffer_size helps to improve the ORDER BY performance.
sort_buffer_size
A buffer is allocated with this amount of memory for the session to perform each of the sorts. In case of high sort_merge_passes, you have to raise the query indexes whereas, for small sorts like OLTP, you have to reduce it. The default value of the sort_buffer_size is 2M.
sort_buffer_size=1M
Note: Both the Basic Setting and Logging Setting are available by default and therefore, it is optional.
It is to be noted that the above-given values are not constant and varies. Based on the traffic that the site receives, the parameters will see subsequent changes. Similarly, to attain a stable, effective and high-performing server, fine-tune your server by adjusting the parameters’ values depending on the traffic your site receives.