# Ibdata is continuously growing

**URL:** <https://forums.percona.com/t/ibdata-is-continuously-growing/6545>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [August 28, 2018, 8:02am UTC](https://forums.percona.com/t/ibdata-is-continuously-growing/6545 "2018-08-28T08:02:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gajendra](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/gajendra/32/44_2.png) [@Gajendra](https://forums.percona.com/u/Gajendra)\
**Post date:** [August 28, 2018, 8:02am UTC](https://forums.percona.com/t/ibdata-is-continuously-growing/6545/1 "2018-08-28T08:02:47Z")

</div>

Hi,  
ibdata file is continuously growing on standalone mysql server (5.7). i dont know why is growing and eating too much space

dbsize : 40 Gb  
Ibdata size : 130 Gb

please advice how can i shrink ibdata file or do i need to optimize any my.cnf parameter

# my.cnf

[client]  
port = 3306  
socket = /var/run/mysqld/mysqld.sock

[mysqld\_safe]  
socket = /var/run/mysqld/mysqld.sock  
nice = 0

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid  
socket = /var/run/mysqld/mysqld.sock  
port = 3306  
basedir = /usr  
datadir = /var/lib/mysql  
tmpdir = /tmp

bind-address = 0.0.0.0  
key\_buffer\_size = 512M  
max\_allowed\_packet = 256M  
thread\_stack = 192K  
thread\_cache\_size = 1M

myisam\_recover\_options = BACKUP  
max\_connections = 1024  
max\_heap\_table\_size = 512M  
tmp\_table\_size = 128M  
table\_open\_cache = 800  
join\_buffer\_size = 8M  
query\_cache\_limit = 64M  
query\_cache\_size = 512M  
query\_cache\_type = 1  
thread\_pool\_size = 16

innodb\_purge\_threads = 4  
innodb\_purge\_batch\_size = 3000  
innodb\_purge\_rseg\_truncate\_frequency = 32  
innodb\_buffer\_pool\_instances = 8  
innodb\_buffer\_pool\_size = 12G  
innodb\_flush\_method = O\_DSYNC  
innodb\_log\_file\_size = 1G  
innodb\_log\_files\_in\_group = 2  
innodb\_log\_buffer\_size = 16M  
innodb\_flush\_log\_at\_trx\_commit = 1  
innodb\_lock\_wait\_timeout = 30  
innodb\_file\_per\_table = 1

log\_error = /var/log/mysql/error.log  
slow\_query\_log = ON  
expire\_logs\_days = 10  
max\_binlog\_size = 100M

[mysqldump]  
quick  
quote-names  
max\_allowed\_packet = 16M

[mysql]  
[isamchk]

key\_buffer\_size = 16M

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [August 28, 2018, 8:34am UTC](https://forums.percona.com/t/ibdata-is-continuously-growing/6545/2 "2018-08-28T08:34:52Z")

</div>

Hi there thanks for the question. There are quite a few blog posts on this subject, here is one that explains what might be happening: [url][https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/[/url]](https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/%5B/url%5D)

Also this one [url][https://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb\_file\_per\_table-is-on/[/url]](https://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/%5B/url%5D)

If these don’t fit your scenario, please come back with version and environment details, plus any error logs that you might have…?

---

<div class="post-metadata">

**Author:** ![Gajendra](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/gajendra/32/44_2.png) [@Gajendra](https://forums.percona.com/u/Gajendra)\
**Post date:** [August 30, 2018, 6:05am UTC](https://forums.percona.com/t/ibdata-is-continuously-growing/6545/3 "2018-08-30T06:05:16Z")

</div>

# mysql\> show engine innodb status \G \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Type: InnoDB Name: Status:

# 2018-08-29 11:13:17 0x7f58ae718700 INNODB MONITOR OUTPUT

## Per second averages calculated from the last 54 seconds

## BACKGROUND THREAD

## srv\_master\_thread loops: 4901253 srv\_active, 0 srv\_shutdown, 468 srv\_idle srv\_master\_thread log flush and writes: 4901705

## SEMAPHORES

OS WAIT ARRAY INFO: reservation count 7309957687  
–Thread 140018856912640 has waited at log0log.cc line 1312 for 0.00 seconds the semaphore:  
Mutex at 0x2ca3008, Mutex LOG\_WRITE created log0log.cc:857, lock var 1

## –Thread 140019415115520 has waited at srv0srv.cc line 3051 for 0.00 seconds the semaphore: X-lock (wait\_ex) on RW-latch at 0x2d1e238 created in file trx0purge.cc line 243 a writer (thread id 140019415115520) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock\_word: ffffffffffffffff Last time read locked in file trx0rec.cc line 2159 Last time write locked in file /mnt/workspace/percona-server-5.7-redhat-binary-rocks/label\_exp/centos7-64/rpmbuild/BUILD/percona-server-5.7.21-20/percona-server-5.7.21-20/storage/innobase/trx/trx0purge.cc line 1848 OS WAIT ARRAY INFO: signal count 1738464296 RW-sx spins 3436013, rounds 45100339, OS waits 371435 Spin rounds per wait: 52158419.00 RW-shared, 115411313.00 RW-excl, 13.13 RW-sx

## TRANSACTIONS

Trx id counter 13358546069  
Purge done for trx’s n:o \< 12350409283 undo n:o \< 0 state: running  
History list length 499120279  
LIST OF TRANSACTIONS FOR EACH SESSION:  
—TRANSACTION 421508165955656, not started  
0 lock struct(s), heap size 1136, 0 row lock(s)  
—TRANSACTION 421508165892480, not started

## FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)  
I/O thread 1 state: waiting for completed aio requests (log thread)

## Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o’s:, sync i/o’s: Pending flushes (fsync) log: 0; buffer pool: 0 45096402 OS file reads, 2118240017 OS file writes, 39207521 OS fsyncs 1 pending preads, 1 pending pwrites 838.41 reads/s, 16384 avg bytes/read, 507.32 writes/s, 9.00 fsyncs/s

## INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 13808, seg size 13810, 152659 merges  
merged operations:  
insert 134704, delete mark 20940602, delete 778239  
discarded operations:  
insert 0, delete mark 0, delete 0  
Hash table size 3187201, node heap has 27 buffer(s)

## Hash table size 3187201, node heap has 339 buffer(s) 7619.93 hash searches/s, 3035.30 non-hash searches/s

## LOG

## Log sequence number 3307888562989 Log flushed up to 3307888561934 Pages flushed up to 3307552857081 Last checkpoint at 3307550712231 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 335705908 Checkpoint age 337850758 1 pending log flushes, 0 pending chkp writes 1883703093 log i/o’s done, 440.61 log i/o’s/second

## BUFFER POOL AND MEMORY

Total large memory allocated 13413384192  
Dictionary memory allocated 1215918  
Internal hash tables (constant factor + variable factor)  
Adaptive hash index 227017664 (203980864 + 23036800)  
Page hash 1594504 (buffer pool 0 only)  
Dictionary cache 52211134 (50995216 + 1215918)  
File system 894152 (812272 + 81880)  
Lock system 31964120 (31875512 + 88608)  
Recovery system 0 (0 + 0)  
Buffer pool size 786336  
Buffer pool size, bytes 12883329024  
Free buffers 7559  
Database pages 777373  
Old database pages 287092  
Modified db pages 31999  
Pending reads 1  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 70606609, not young 624180011  
229.35 youngs/s, 839.87 non-youngs/s  
Pages read 45096277, created 46200577, written 196527414  
838.41 reads/s, 29.81 creates/s, 58.30 writes/s  
Buffer pool hit rate 993 / 1000, young-making rate 2 / 1000 not 7 / 1000

* * *

## INDIVIDUAL BUFFER POOL INFO

—BUFFER POOL 0  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 955  
Database pages 97163  
Old database pages 35881  
Modified db pages 3864  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 9039358, not young 91555740  
30.59 youngs/s, 106.07 non-youngs/s  
Pages read 5668667, created 5702255, written 30968005  
105.89 reads/s, 3.56 creates/s, 5.87 writes/s  
Buffer pool hit rate 980 / 1000, young-making rate 5 / 1000 not 20 / 1000  
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s  
—BUFFER POOL 1  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 936  
Database pages 97193  
Old database pages 35894  
Modified db pages 3891  
26.41 youngs/s, 103.22 non-youngs/s  
Pages read 5743186, created 5757553, written 23141122  
LRU len: 97193, unzip\_LRU len: 0  
I/O sum[44582]:cur[98], unzip sum[0]:cur[0]  
—BUFFER POOL 2  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 920  
Database pages 97197  
Old database pages 35897  
Modified db pages 4044  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 8823205, not young 66775373  
26.48 youngs/s, 105.70 non-youngs/s  
Pages read 5662587, created 5779029, written 23550244  
LRU len: 97197, unzip\_LRU len: 0  
I/O sum[44582]:cur[98], unzip sum[0]:cur[0]  
—BUFFER POOL 3  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 967  
Database pages 97136  
Old database pages 35866  
Modified db pages 3832  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 8960225, not young 90288491  
28.48 youngs/s, 105.79 non-youngs/s  
Pages read 5504960, created 5801345, written 24333494  
105.59 reads/s, 2.37 creates/s, 12.15 writes/s  
LRU len: 97136, unzip\_LRU len: 0  
I/O sum[44582]:cur[98], unzip sum[0]:cur[0]  
—BUFFER POOL 4  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 953  
Database pages 97160  
Pages made young 8899745, not young 97477382  
30.68 youngs/s, 104.46 non-youngs/s  
Pages read 5468308, created 5786110, written 23318331  
LRU len: 97160, unzip\_LRU len: 0  
I/O sum[44582]:cur[98], unzip sum[0]:cur[0]  
—BUFFER POOL 5  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 950  
Database pages 97160  
Old database pages 35885  
Modified db pages 4193  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0

LRU len: 97160, unzip\_LRU len: 0  
I/O sum[44582]:cur[98], unzip sum[0]:cur[0]  
—BUFFER POOL 6  
Buffer pool size 98292  
Buffer pool size, bytes 1610416128  
Free buffers 933  
Database pages 97179  
Old database pages 35892  
Modified db pages 4136  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 8772266, not young 66237361  
31.04 youngs/s, 105.42 non-youngs/s  
Pages read 5687379, created 5793945, written 23680100

## LRU len: 97179, unzip\_LRU len: 0 I/O sum[44582]:cur[98], unzip sum[0]:cur[0] —BUFFER POOL 7 Buffer pool size 98292 Buffer pool size, bytes 1610416128 Free buffers 945 Database pages 97185 Old database pages 35894 0, single page 0 Pages made young 8810089, not young 59120570 27.43 youngs/s, 104.07 non-youngs/s Pages read 5696920, created 5778895, written 23764995 104.05 reads/s, 3.56 creates/s, 7.59 writes/s Buffer pool hit rate 977 / 1000, young-making rate 6 / 1000 not 23 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 97185, unzip\_LRU len: 0 I/O sum[44582]:cur[98], unzip sum[0]:cur[0]

## ROW OPERATIONS

## 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB 0 RW transactions active inside InnoDB —OLDEST VIEW— Read view low limit trx n:o 12350409282 Trx read view will not see trx with id \>= 12350409282, sees \< 12350409282 Read view individually stored trx ids:

Process ID=21363, Main thread ID=140019423508224, state: sleeping  
Number of rows inserted 546203750, updated 4429438242, deleted 55092556, read 2566632224140

---

<div class="post-metadata">

**Author:** ![Gajendra](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/gajendra/32/44_2.png) [@Gajendra](https://forums.percona.com/u/Gajendra)\
**Post date:** [August 30, 2018, 6:06am UTC](https://forums.percona.com/t/ibdata-is-continuously-growing/6545/4 "2018-08-30T06:06:20Z")

</div>

please find show engine innodb stats

---

<div class="post-metadata">

**Author:** ![Gajendra](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/gajendra/32/44_2.png) [@Gajendra](https://forums.percona.com/u/Gajendra)\
**Post date:** [August 31, 2018, 6:25am UTC](https://forums.percona.com/t/ibdata-is-continuously-growing/6545/5 "2018-08-31T06:25:21Z")

</div>

@[lorraine.pocklington](https://www.percona.com/forums/member/41223-lorraine-pocklington) please look into above stats and let me know if you requires more  
details