Mysql – What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow

innodbmariadbMySQLmysql-5.5

Let's say we have a transaction running 50,000 inserts which dirties approximately 600 MB of the database. If the buffer pool size is 128 MB, there will be dirty pages in the buffer pool while the transaction is running, and these will need to be evicted to make space. My question is, what does MySQL/InnoDB do with these dirty uncommitted pages?

Best Answer

I just encountered a question 5 days ago : InnoDB errors and crashes with MyISAM database?

When troubleshooting the issue in a live chat session, I discovered that dirty pages from Temporary InnoDB Tables were occupying the InnoDB Buffer Pool. The file ibtmp was 482MB with 75% of the buffer pool being dirty since innodb_max_dirty_pages_pct was 75. The Buffer Pool was only 128M. So, then, what happens ?

The Buffer Pool has to flush very frequently when the Buffer Pool is too small (which is what I encountered with a virtually empty database).

If you run SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';, you will see something like this:

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 190925  9:50:58 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 247                                              |
| Innodb_buffer_pool_bytes_data         | 4046848                                          |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 36                                               |
| Innodb_buffer_pool_pages_free         | 7945                                             |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 8192                                             |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 1090                                             |
| Innodb_buffer_pool_reads              | 214                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 325                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

mysql>

You can monitor Innodb_buffer_pool_pages_dirty with

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_dirty%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql>

This number may fluctuate with pages being flushed and pages coming in, but this will definitely bottlenck InnoDB. Your solution is just to increase the Buffer Pool size.

I talked about other ill effects before (See my post what happens when InnoDB logspace is too small)

Related Question