0

We have MySQL server V5.7.11 in which the Innodb_buffer_pool_bytes_data is getting increasing day by day. Initially we have set innodb_buffer_pool_size to 8G but we got error of max connection reached, after that we come to know that Data was increased drastically and reached to 6-7G due to some transit tables in production site, so we have set innodb_buffer_pool_size to 40G. After 10-12 days some of tables a specially two tables contains approx 20G data at that point Innodb_buffer_pool_bytes_data was approx 53% of total allocated innodb_buffer_pool_size. So on hourly basis we have Drop those two tables as we are generating data from these tables to other tables. After Dropping those tables DB size was reduced to 300M but the Innodb_buffer_pool_bytes_data was not reduced as per our expectation it remains same, so dropping of tables didn't flush out the buffer data of dropped table. Current status is DB size is 6G and Innodb_buffer_pool_bytes_data 30G approx which is approx 70% of total buffer pool configured.

I have googled and refer official doc for related issue but not found any solution.

Below are some status from server..

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

+---------------------------------------+----------------------------------------------------+
| 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 160830 21:18:14   |
| Innodb_buffer_pool_resize_status      | Completed resizing buffer pool at 160909 17:09:23. |
| Innodb_buffer_pool_pages_data         | 1849541                                            |
| Innodb_buffer_pool_bytes_data         | 30302879744                                        |
| Innodb_buffer_pool_pages_dirty        | 8962                                               |
| Innodb_buffer_pool_bytes_dirty        | 146833408                                          |
| Innodb_buffer_pool_pages_flushed      | 84983304                                           |
| Innodb_buffer_pool_pages_free         | 765962                                             |
| Innodb_buffer_pool_pages_misc         | 5864                                               |
| Innodb_buffer_pool_pages_total        | 2621367                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                  |
| Innodb_buffer_pool_read_ahead         | 37302                                              |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                  |
| Innodb_buffer_pool_read_requests      | 566182954928                                       |
| Innodb_buffer_pool_reads              | 272185                                             |
| Innodb_buffer_pool_wait_free          | 0                                                  |
| Innodb_buffer_pool_write_requests     | 1505673609                                         |
+---------------------------------------+----------------------------------------------------+

+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 42949672960    |
+-------------------------------------+----------------+
1
  • How much RAM do you have?
    – Rick James
    Sep 13, 2016 at 18:18

1 Answer 1

1

InnoDB's buffer_pool is a cache area for its use. You can control the maximum size via innodb_buffer_pool_size. A good setting for that is about 70% of available RAM.

The buffer_pool contains 16KB blocks of data being read (and cached) and blocks waiting to be flushed to disk. It also contains the "change buffer", which is a list of index changes waiting to be flushed to disk.

InnoDB has control over when it frees up any of the space in the buffer_pool, but it tends to fill up the available size before getting serious about freeing space. That is, you should not be expecting it to shrink.

It is very typical for Innodb_buffer_pool_bytes_data to be more than 90% of innodb_buffer_pool_size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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