MySQL Performance: InnoDB Buffers & Directives

As discussed earlier in our MySQL Performance series, the InnoDB storage engine is designed to be a high-performance database for very large datasets. The row-locking technique it uses allows for many read and write requests to occur on a single table concurrently. This is a vast improvement in speed over traditional table-locking of the MyISAM engine. This part of our MySQL Performance series will focus on configuring InnoDB tables for maximum concurrency with minimal disk input/output (I/O).

Disk I/O & Temporary Tables

A simple and effective change that can provide a noticeable performance boost immediately is the practice of forcing all temporary table writes into memory instead of writing to a hard disk. Writing temp tables to RAM reduces the impact MySQL has on overall system disk I/O, which greatly improves performance. This change is done by leveraging the /dev/shm tmpfs partition in Linux. This special device gives server processes the ability to write files directly into RAM as if it were a standard disk drive.

InnoDB Log File Size

The InnoDB log file is not your traditional log file. It’s a special binary redo log, which InnoDB uses to store redo activity for every write request handled by the server. Every write query executed gets a redo entry in the log file so that change can be recovered in the event of a crash. Because of this reason, the InnoDB log file size plays a critical role in MySQL write query performance, particularly on write-heavy databases.

Due to the special nature of the InnoDB Redo Logs, this setting cannot be changed without first deleting the existing InnoDB log files. The following procedure for adjusting the innodb_log_file_size directive must be followed before MySQL will start again.

Changing innodb_log_file_size Procedure:

1. Stop MySQL Serviceservice mysql stop
2. Update innodb_log_file_size within [mysqld] header in /etc/my.cnf file.innodb_log_file_size=1G
3. Move the existing log files out of the mysql directorymv /var/lib/mysql/ib_logfile* /backup
4. Start MySQL Serviceservice 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

The larger the IBP, the more InnoDB functions as a high-performance in-memory database. However, setting the size too large can be detrimental to overall system performance. A balance must be struck between the IBP size and needed memory for other system services. In an ideal configuration, the IBP should be as large as possible, without causing excessive swapping (i.e., Thrashing) to occur. There are some simple rules to follow when making these determinations.

Calculate innodb_buffer_pool_size Script: (Dedicated Server)

awk '
/MemTotal/{
$3="GB"
$2=sprintf("%.0f",$2/1048576)
print
$1="  Mem80%:"
$2=sprintf("%.0f",$2*.8)
print
}' /proc/meminfoExample Output:

MemTotal: 15 GB
Mem80%: 12 GB

Calculate innodb_buffer_pool_size Script: (Shared 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

The innodb_buffer_pool_instances directive controls the number of memory pages Innodb creates. MySQL ignores this directive unless the innodb_buffer_pool_size is greater than 1G/1024M. When larger than 1G, the buffer pool is divided up into a number of equal sized memory pages specified by this directive.

InnoDB I/O Threads

Input/Output threads are sub-processes of MySQL which directly access the IBP memory pages. There are two types of I/O threads: innodb_read_io_threads and innodb_write_io_threads. These threads read and write, respectively, to the individual memory pages created by innodb_buffer_pool_instances.

The default value for both of these thread types is 4, making a total of 8 threads running concurrently. This pairs up with the default number of memory pages created when innodb_buffer_pool_size is larger than 1G (x8). This synergy is a key factor when improving InnoDB performance on very large database servers with large volumes of RAM and CPU cores. When increasing innodb_buffer_pool_instances past the default of 8, it becomes optimal to increase the number of threads to handle the extra memory pages concurrently. The goal being, to keep the synergy of total I/O threads equal to total memory pages:

( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffer_pool_instances

When to change InnoDB I/O Threads

Two conditions should be satisfied before adjusting I/O threads for a server.

1. 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.
2. When the server has more than 8 CPU cores to devote to the MySQL Service.

It is not enough to merely divide the innodb_buffer_pool_instances in half to get equal numbers of read and write threads. The goal is to increase the number of threads primarily used by the specific server’s query workload. For instance, if your server reads more data than it writes, increasing write threads would be counter-productive. The same holds true in reverse.

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_select

SHOW 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.

The following awk script will print the necessary statistics variables from MySQL and automatically calculate a simplified Read:Write Ratio to help determine whether the system in question is Read_Heavy or Write_Heavy.

Calculate Read:Write Ratio Script:

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

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.

When increasing Innodb I/O Threads, increase only the read or write threads depending on whether the server is Read_Heavy or Write_Heavy.

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.