Mysql – Innodb_buffer_pool_bytes_data increases day by day even after delete the data

buffer-poolinnodbMySQL

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    |
+-------------------------------------+----------------+

Best Answer

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.

MONITORING

There are so many writes to keep track of

Data
- Innodb_data_writes : The total number of data writes.
- Innodb_data_written : The amount of data written so far, in bytes
SQL
- Innodb_rows_deleted : The number of rows deleted from InnoDB tables
- Innodb_rows_inserted : The number of rows inserted into InnoDB tables
- Innodb_rows_updated : The number of rows updated in InnoDB tables
Double Write Buffer
- Innodb_dblwr_writes : The number of doublewrite operations that have been performed
- Innodb_dblwr_pages_written : The number of pages written to the doublewrite buffer
Operating System
- Innodb_os_log_written : The number of bytes written to the InnoDB redo log files
- Innodb_data_fsyncs : The number of fsync() operations so far. The frequency of fsync() calls is influenced by the setting of the innodb_flush_method configuration option.
MyISAM-related writes

Data writes come in the form of

External sources

DB Connections From Your Apps
Data Restorations
- Reloading of mysqldumps
- LOAD DATA INFILE
MySQL Replication via incoming I/O Threads (if DB Server is a Master)

Internal Sources

Read and Write I/O Threads for Writing to Transaction Logs
Insert Buffer from the InnoDB Buffer Pool
Double Write Buffer
MySQL Replication
- IO Thread (if the DB Server is a Slave)
- SQL Thread
Log Buffer Write
Individual Tablespace Files (.ibd)
FSyncs (pushing changes to disk)

InnoDB Architecture

SSD LIFESPAN

If you more concerned with the SSD's longevity, try moving some of the InnoDB parts away from the SSD over to a fast HDD. Which parts go where ?

HDD (RAID 10)
- InnoDB System Tablespace (ibdata1)
- InnoDB Transaction Logs (ib_logfile0,ib_logfile1)
- Binary Logs
- Relay Logs (if your server is a MySQL Replication Slave)
SSD
- .frm files
- .ibd files
See this good blog entry from a MySQL FaceBook Engineer on this suggestion

Here are some of my earlier posts on moving splitting logging mechanisms between SSD and HDD

Jun 25, 2013 : Postgres Write Performance on Intel S3700 SSD
Jun 18, 2013 : How big can innodb_doublewrite_file grow?

GIVE IT A TRY !!!

MySQL Performance – Why Simple SELECTs on InnoDB Are 100x Slower Than on MyISAM

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

Best Answer

Related Solutions

MySQL – How to Determine Data Written Per Day Through Insert, Update, and Delete Operations

MONITORING

External sources

Internal Sources

InnoDB Architecture

SSD LIFESPAN

GIVE IT A TRY !!!

MySQL Performance – Why Simple SELECTs on InnoDB Are 100x Slower Than on MyISAM

YOUR QUERY

REALITY CHECK

CLUSTERED INDEX

REDUNDANT INDEXES

BUFFER CACHING

TABLE LAYOUT

CONCLUSION

UPDATE 2014-08-28 11:27 EDT

GIVE IT A TRY !!!

Related Question