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?


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)


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)


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)

| improve this answer | |
  • I'm experimenting with InnoDB with limited buffer pool size (so increasing buffer pool size is not an option). So according to you, dirty uncommitted pages are flushed to ibtmp1 during eviction from the buffer pool? – Aarati K Sep 25 '19 at 14:38
  • Only if there are temporary tables involved. In the case of real tables, it will flush to the .ibd file of each table whose pages are stored in the InnoDB Buffer Pool – RolandoMySQLDBA Sep 25 '19 at 15:03
  • Thanks for your reply. So what would happen in case the transaction is rolled back midway? All the changes made to the database file will need to be undone, right? Its okay to flush dirty buffer pool pages corresponding to committed transactions, but is that okay in case of an uncommitted transaction? – Aarati K Sep 25 '19 at 17:22
  • That's what the undo logs are for. Stuff should still work even if the InnoDB Buffer Pool is too small (but please take that with a grain of salt). Just make the Buffer Pool big enough and the storage engine with make the dirty pages and undo logs behave as it should. – RolandoMySQLDBA Sep 25 '19 at 17:59

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?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.