In this blog post, I’m gonna talk about MySQL Memory Issues. What causes High memory usage and what is the impact on MySQL database instances.
But first, we can do a quick review on the following topics:
- How MySQL uses memory? (Log files, buffers, InnoDB buffer pool size)
- How MySQL allocates memory?
- Swap memory
- Troubleshooting
- Best practices.
Example: thread cache, query cache, buffer pool, and log buffer.
Private memory is used to allocate cache upon connection to MySQL server. It is allocated for each thread. For example; sort buffer, join buffer, and temporary table.
InnoDB maintains one or more buffer pools that cache frequently used data and indexes in the main memory.
So, when a read query is executed from the client program, InnoDB checks to see if the required data pages are in the buffer pool. If it is not in the buffer pool, InnoDB requests the data from the tablespace. Then it will put the data pages in the buffer pool. And then, MySQL will return the results to the client.
When transactions are performed, data changes are written to the log buffer in memory. then every time a transaction is committed, the transaction logs get flushed to the disk, but this behavior can be changed (InnoDB_flush_log_at_trx_commit). If a crash occurs (while the tables are being modified), the redo log files are used for recovery.
On the other hand, Dirty pages in the buffer pool get written to the disk at checkpoints. This is to ensure that the cache has enough free space.
innodb_buffer_pool_size is the most important tuning parameter. It caches table and index data. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing.
Setting it too low can degrade the performance. Setting it too high can increase the memory consumption causing the DB to crash.
MySQL allocation is not only from innodb_buffer_pool_size, but also from other buffers in the database such sort_buffer_size, read_buffer_size, read_rnd_buffer, join_buffer_size, and tmp_table_size and this will require additional 5-10% extra memory
Below is a formula to calculate the approximate memory usage for your MySQL:
Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)
Swapping can happen when a system requires more memory than is allocated .A large buffer may lead to swapping in the operating system and make the performance slow.
To avoid your MySQL/MariaDB data being SWAP instead of RAM, you have to play with a kernel parameter called swappiness.
A swappiness value is used to change the balance between swapping out runtimememory and dropping pages from the system page cache. The higher the value, the more the system will swap. The lower the value, the less the system will swap. The maximum value is 100, the minimum is 0, and 60 is the default.
I performed a test in AWS RDS MySQL. I ran a stored procedure that consumed a lot of memory until the server crashed. We will notice that when memory gets low, Swap usage will increase. We will also see a spike in disk IO usage.
When MySQL crashed, we will lose connection to the database. In AWS RDS, we can see the logs and EVENTs to identify the cause of the crash. The log indicates that the database process was killed by the OS due to excessive memory consumption.
I also did the same test in Azure MySQL. The difference between Azure and AWS is that when the DB crash, in Azure, it will failover to the Standby replica, but in AWS, a mysqld process crash would not trigger a failover.
In Azure, under Private DNS Zone, if we see that the IP address changed, then it means that it failed over to the standby replica successfully.
Cheers!
