- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINS in SQL
Disk I/O & Temporary Tables

InnoDB Log File Size

Rule of Thumb: innodb_log_file_size
MySQL®️ Recommends: “The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O.” A log file size of 1GB is sufficient for most situation. This may need to be raised when database growth exceeded several dozen GB in size.
Notice
MySQL®️ Warns: “Larger log files also make crash recovery slower, although improvements to recovery performance make log file size less of a consideration than it was in earlier versions of MySQL. “
- Stop MySQL Service
service mysql stop
- Update innodb_log_file_size within [mysqld] header in /etc/my.cnf file.
innodb_log_file_size=1G
- Move the existing log files out of the mysql directory
mv /var/lib/mysql/ib_logfile* /backup
- Start MySQL Service
service mysql start
InnoDB Buffer Pool (IBP)
The InnoDB Buffer Pool plays a critical role in MySQL Performance. The IBP is a reserved portion of system memory where InnoDB table and index data are cached. This allows frequently accessed data to be returned quickly, without the need of spinning up a physical hard drive. The more InnoDB tablespace that is cached in memory, the less often MySQL will access physical disks, which manifests as faster query response times and improved overall system performance. There are multiple directives which control IBP behavior. Tuning these to match the servers existing tablespace data, with some room to grow, will optimize the performance of any queries using InnoDB tables. Since InnoDB is the recommended engine for very large tables, optimizing the IBP gives major performance gains.InnoDB Buffer Pool Size

Rule of Thumb: innodb_buffer_pool_size (Dedicated Servers)
‣When MySQL is the only major service on a server.
The recommended configuration by MySQL is for innodb_buffer_pool_size to use as much as 80% of the system’s total physical memory.
awk '
/MemTotal/{
$3="GB"
$2=sprintf("%.0f",$2/1048576)
print
$1=" Mem80%:"
$2=sprintf("%.0f",$2*.8)
print
}' /proc/meminfo
Example Output:
MemTotal: 15 GB
Mem80%: 12 GB
Rule of Thumb: innodb_buffer_pool_size (Shared Servers)
‣When MySQL runs alongside other major services like Web, Email, etc... (e.g. cPanel, Plesk)
Due to their varied resource requirements, there is no one-size-fits-all calculation for shared servers. It becomes necessary to calculate the memory requirement needs of all other critical services on the server and subtract those from total system memory to find a proper amount of available memory which can be assigned to innodb_buffer_pool_size.
A simplified method is to use a generic calculation. A conservative starting point is assigning between 30 and 80 percent of available system memory, instead of total physical memory. However, determining the exact setting may require some guesswork and testing.
The following awk script reads MemAvail from /proc/meminfo and provides a selection of percentage based calculations to choose for the server.
awk '
/MemAvail/{
$3="G";_=$2
$2=sprintf("% 3.0f",_/1048576)
print
for (i=80;i>=25;i-=10) {
$1="MemAvail_"i"%:"
$2=sprintf("% 3.0f",_*(i/100)/1048576)
$4=sprintf("| %.0f M",_*(i/100)/1024)
print
}
}' /proc/meminfo
Example Output:
MemAvailable: 10 G
MemAvail_80%: 8 G | 8405 M
MemAvail_70%: 7 G | 7354 M
MemAvail_60%: 6 G | 6304 M
MemAvail_50%: 5 G | 5253 M
MemAvail_40%: 4 G | 4203 M
MemAvail_30%: 3 G | 3152 M
InnoDB Buffer Pool Instances

Rule of Thumb: innodb_buffer_pool_instances
MySQL®️ Recommends: “For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.”
‣ Rule Exception: innodb_buffer_pool_instances should not exceed Total_IO_Threads
Total_IO_Threads=(innodb_read_io_threads + innodb_write_io_threads)
Preference a 1:1 Ratio for: Total_Instances:Total_IO_Threads

InnoDB I/O Threads


When to change InnoDB I/O Threads
Two conditions should be satisfied before adjusting I/O threads for a server.- When innodb_buffer_pool_size is larger than 8 Gigabytes, this means there are more memory pages than I/O threads to handle them concurrently.
- When the server has more than 8 CPU cores to devote to the MySQL Service.
How to Calculate MySQL Read:Write Ratio
The global statistics kept by a MySQL server can be leveraged to determine a systems Read:Write Ratio. The following MySQL queries can be used to calculate the Total_Reads and Total_Writes of a server. Total_Reads = Com_selectSHOW GLOBAL STATUS LIKE 'Com_select';
Total_Writes = Com_delete + Com_insert + Com_replace + Com_update
SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');
- If the Total_Reads is greater than Total_Writes, the server is considered Read_Heavy.
- If the Total_Writes is greater than Total_Reads, the server is considered Write_Heavy.
mysql -e 'SHOW GLOBAL STATUS;'|\
awk '
$1~/Com_(delete|insert|update|replace)$/{
w += $2
printf $0 "\t + Total_Writes = " w "\n"
}
$1~/Com_(select)/{
r += $2
printf $0 "\t + Total_Reads = " r "\n"
}
END {
printf "\nRead:Write Ratio:\n\t" r ":" w " "
if (r >= w) {
R=sprintf("%.0f",r/w)
print R ":1"
} else {
W=sprintf("%.0f",w/r)
print "1:" W
}
}'
Example Output:
Com_delete 14916 + Total_Writes = 14916
Com_insert 87413 + Total_Writes = 102329
Com_replace 0 + Total_Writes = 102329
Com_select 675528 + Total_Reads = 675528
Com_update 18976 + Total_Writes = 121305
Read:Write Ratio:
675528:121305 6:1
Note:
Adjust the first line’s mysql statement as needed to connect to the appropriate server. e.g.,
mysql -h localhost -u root -p -e 'SHOW GLOBAL STATUS;' |\
Hardware CPU Core Considerations
When adjusting innodb_read_io_threads or innodb_write_io_threads, keep the total threads between the two equal to the number of CPU cores available to MySQL. This ensures maximum concurrency as each memory page can be accessed simultaneously by each individual CPU core.Rule of Thumb: InnoDB I/O Threads (High-Performance) ‣innodb_read_io_threads + innodb_write_io_threads
The total number of InnoDB I/O Threads should not surpass the total number of CPU cores available to MySQL.
Other Directives
There are several more advanced techniques for fine tuning the InnoDB Buffer Pool and its behavior. These are beyond the scope of this article. However, you can find more details about these techniques on the MySQL website here: 15.5.1 Buffer Pool.