What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow?
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&gt; 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&gt;


You can monitor Innodb_buffer_pool_pages_dirty with

mysql&gt; 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&gt;


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)