Ibdata is continuously growing

MySQL & MariaDB Percona Server for MySQL 5.7
#1

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

#2

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: https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

Also this one https://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/

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

#3

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

#4

please find show engine innodb stats

#5

@lorraine.pocklington please look into above stats and let me know if you requires more
details

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.