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

Question

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?

Answer 1

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)

Answer 2

I think I have seen cases where

• buffer_pool is set really low (much less than 128M)
• The transaction was complex, not simply "big".
• Errors indicated that the buffer_pool was out of space (or something like that). You are checking for errors, yes?

"Big" is not a problem -- InnoDB is designed to handle arbitrarily many rows being held for ROLLBACK, etc. But it has to work harder -- flushing dirty pages before finishing the query, storing undo stuff in the table, not just the undo logs, etc.

"Complex" can be a problem -- Suppose it needs to lock lots of blocks in several BTrees of several tables and their indexes.

I don't think I have seen 128M get into trouble (other than being slow). I think the previous default (8M) did cause trouble. That was back in the days when MyISAM was king, and InnoDB was the new fledgling on the block.

Think of it like a 5-course meal. The kitchen can prepare the courses one at a time. But what if you demanded that all 5 be delivered simultaneously?