Not the answer you need?
Register and ask your own question!

Ibdata is continuously growing

GajendraGajendra ContributorCurrent User Role Patron
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

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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..?
  • GajendraGajendra Contributor Current User Role Patron
    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
  • GajendraGajendra Contributor Current User Role Patron
    please find show engine innodb stats
  • GajendraGajendra Contributor Current User Role Patron
    @lorraine.pocklington please look into above stats and let me know if you requires more
    details
Sign In or Register to comment.

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