GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

ibdata1 HUGE despite innodb_file_per_table being set

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ibdata1 HUGE despite innodb_file_per_table being set

    Greetings - I have a database with ~120GB of data spread across multiple innodb and archive tables, each in their own tablespace/file. Despite separation via the innodb_file_per_table option, ibdata1 continues to grow at a rate of 1-3GB/day and is, today, 113GB.

    Any thoughts on:

    a) what would cause this; and
    b) preventing further growth?

    I know I can recreate ibdata1 by dumping, removing it, and restoring, but I'd love to get at the underlying issue before doing that to prevent recurrence.

    This is a dual quad-core 3.2GHz Xeon w/ 16GB RAM and a bunch of 15K SAS drives.

    I'd greatly appreciate any thoughts, feedback or insights.

    Thanks,

    Ryan

    Here's some output, potentially helpful:



    mysqlreport:

    MySQL 5.0.67-percona-hiperf-b10-log uptime 6 1:11:51 Sun Jan 25 20:18:27 2009

    __ Key __________________________________________________ __________ _____
    Buffer used 0 of 8.00M %Used: 0.00
    Current 1.46M %Usage: 18.25
    Write hit 0.00%
    Read hit 0.00%

    __ Questions __________________________________________________ _________
    Total 2.02G 3.9k/s
    Com_ 947.75M 1.8k/s %Total: 46.89
    DMS 927.60M 1.8k/s 45.89
    QC Hits 145.68M 278.7/s 7.21
    COM_QUIT 299.91k 0.6/s 0.01
    -Unknown 92.29k 0.2/s 0.00
    Slow 3 s 65.10k 0.1/s 0.00 %DMS: 0.01 Log: ON
    DMS 927.60M 1.8k/s 45.89
    SELECT 502.09M 960.6/s 24.84 54.13
    UPDATE 188.20M 360.0/s 9.31 20.29
    INSERT 170.96M 327.1/s 8.46 18.43
    DELETE 66.35M 126.9/s 3.28 7.15
    REPLACE 0 0/s 0.00 0.00
    Com_ 947.75M 1.8k/s 46.89
    commit 945.84M 1.8k/s 46.80
    set_option 1.27M 2.4/s 0.06
    show_variab 316.84k 0.6/s 0.02

    __ SELECT and Sort __________________________________________________ ___
    Scan 7.31M 14.0/s %SELECT: 1.46
    Range 7.19k 0.0/s 0.00
    Full join 0 0/s 0.00
    Range check 0 0/s 0.00
    Full rng join 0 0/s 0.00
    Sort scan 752 0.0/s
    Sort range 9.38M 17.9/s
    Sort mrg pass 0 0/s

    __ Query Cache __________________________________________________ _______
    Memory usage 1001.48 of 128.00M %Used: 0.76
    Block Fragmnt 14.83%
    Hits 145.68M 278.7/s
    Inserts 371.95M 711.6/s
    Insrt:Prune 371.95M:1 711.6/s
    Hit:Insert 0.39:1

    __ Table Locks __________________________________________________ _______
    Waited 0 0/s %Total: 0.00
    Immediate 927.29M 1.8k/s

    __ Tables __________________________________________________ __________ __
    Open 136 of 384 %Cache: 35.42
    Opened 944 0.0/s

    __ Connections __________________________________________________ _______
    Max used 101 of 100 %Max: 101.00
    Total 353.66k 0.7/s

    __ Created Temp __________________________________________________ ______
    Disk table 4 0.0/s
    Table 634.32k 1.2/s Size: 32.0M
    File 34 0.0/s

    __ Threads __________________________________________________ __________ _
    Running 3 of 37
    Cached 31 of 32 %Hit: 85.41
    Created 51.60k 0.1/s
    Slow 1 0.0/s

    __ Aborted __________________________________________________ __________ _
    Clients 107.49k 0.2/s
    Connects 36.75k 0.1/s
    __ Bytes __________________________________________________ __________ ___
    Sent 1.20T 2.3M/s
    Received 344.76G 659.6k/s

    __ InnoDB Buffer Pool __________________________________________________
    Usage 12.00G of 12.00G %Used: 100.00
    Read hit 99.81%
    Pages
    Free 0 %Total: 0.00
    Data 752.72k 95.71 %Drty: 4.39
    Misc 33709 4.29
    Latched 13 0.00
    Reads 82.12G 157.1k/s
    From file 155.49M 297.5/s 0.19
    Ahead Rnd 312684 0.6/s
    Ahead Sql 51678 0.1/s
    Writes 5.26G 10.1k/s
    Flushes 119.38M 228.4/s
    Wait Free 667 0.0/s

    __ InnoDB Lock __________________________________________________ _______
    Waits 13899153 26.6/s
    Current 0
    Time acquiring
    Total 580796225 ms
    Average 41 ms
    Max 51977 ms

    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
    Reads 159.91M 305.9/s
    Writes 647.41M 1.2k/s
    fsync 4.79M 9.2/s
    Pending
    Reads 2
    Writes 0
    fsync 0

    Pages
    Created 3.61M 6.9/s
    Read 172.82M 330.6/s
    Written 119.38M 228.4/s

    Rows
    Deleted 66.38M 127.0/s
    Inserted 120.95M 231.4/s
    Read 22.82G 43.7k/s
    Updated 238.74M 456.7/s

    my.cnf:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    server-id = 1
    thread_cache=32
    query_cache_size=128M
    default_storage_engine = innodb
    innodb_additional_mem_pool_size = 100M
    innodb_data_home_dir=/var/lib/mysql
    innodb_log_group_home_dir=/mnt/space/mysql
    table_cache = 384
    max_connections = 100
    wait_timeout = 300
    innodb_thread_concurrency=0
    innodb_adaptive_checkpoint = 1
    innodb_io_capacity = 10000
    innodb_write_io_threads = 6
    innodb_read_io_threads = 6
    log-bin=/mnt/space/mysql/mysql-bin
    expire_logs_days=3
    log_warnings
    log_slow_queries
    long_query_time = 3
    log_long_format
    tmpdir = /local/tmp
    binlog_cache_size = 1M
    sort_buffer_size = 8M
    skip-bdb
    innodb_buffer_pool_size=12G
    innodb_log_file_size=1250M
    innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=2
    innodb_flush_method=O_DIRECT
    innodb_file_per_table
    transaction-isolation=READ-COMMITTED


    show innodb status:

    mysql> show innodb status \G
    *************************** 1. row ***************************
    Status:
    =====================================090125 20:29:37 INNODB MONITOR OUTPUT
    =====================================Per second averages calculated from the last 5 seconds
    ----------BACKGROUND THREAD
    ----------fsync callers: 4469116 buffer pool, 3973 other, 2260 checkpoint, 7441 log aio, 317603 log sync, 0 archive
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 303088847, signal count 221723257
    --Thread 1270995264 has waited at btr0cur.c line 366 for 0.00 seconds the semaphore:
    S-lock on RW-latch at 0x2aaab916d720 created in file dict0dict.c line 3706
    a writer (thread id 1134627136) has reserved it in mode exclusive
    number of readers 0, s_waiters flag 1, x_waiters flag 0
    Last time read locked in file btr0cur.c line 366
    Last time write locked in file ibuf0ibuf.c line 307
    --Thread 1288300864 has waited at btr0cur.c line 366 for 0.00 seconds the semaphore:
    S-lock on RW-latch at 0x2aaab916d720 created in file dict0dict.c line 3706
    a writer (thread id 1134627136) has reserved it in mode exclusive
    number of readers 0, s_waiters flag 1, x_waiters flag 0
    Last time read locked in file btr0cur.c line 366
    Last time write locked in file ibuf0ibuf.c line 307
    --Thread 1289099584 has waited at ibuf0ibuf.c line 2739 for 0.00 seconds the semaphore:
    Mutex at 0xc59940 created file ibuf0ibuf.c line 399, lock var 1
    waiters flag 1
    Mutex spin waits 0, rounds 185596770589, OS waits 170116134
    RW-shared spins 107440856, OS waits 52304324; RW-excl spins 71537872, OS waits 26186426
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    090125 10:11:32
    *** (1) TRANSACTION:
    TRANSACTION 3 1102739326, ACTIVE 9 sec, process no 30276, OS thread id 1285638464 fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 36362 lock struct(s), heap size 9828336
    MySQL thread id 352820, query id 1860533167 10.17.28.198 SOMEDBUSER Searching rows for update
    update InboundQueue set epCheckoutID=352215 where epCheckoutID IS NULL LIMIT 2500
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 300 page no 111251 n bits 1616 index `InboundQueue_epCheckoutID` of table `REDACTEDDBNAME/InboundQueue` trx id 3 1102739326 lock_mode X waiting
    *** (2) TRANSACTION:
    TRANSACTION 3 1102762391, ACTIVE 0 sec, process no 30276, OS thread id 1289365824 inserting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1216, undo log entries 18
    MySQL thread id 352776, query id 1860573968 10.17.28.196 SOMEDBUSER update
    INSERT INTO InboundQueue(epCheckoutID, id, appID, eventID) VALUES (NULL, 639267341, 'fffffffff5ad9b74ffffffffe0e16b', 640502174)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 300 page no 111251 n bits 1616 index `InboundQueue_epCheckoutID` of table `REDACTEDDBNAME/InboundQueue` trx id 3 1102762391 lock_mode X locks rec but not gap
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 300 page no 111251 n bits 1616 index `InboundQueue_epCheckoutID` of table `REDACTEDDBNAME/InboundQueue` trx id 3 1102762391 lock_mode X locks gap before rec insert intention waiting
    *** WE ROLL BACK TRANSACTION (1)
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 3 1192493078
    Purge done for trx's n < 3 1192443796 undo n < 0 0
    History list length 9718
    Total number of lock structs in row lock hash table 0
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 30276, OS thread id 1272326464
    MySQL thread id 353657, query id 2023766571 localhost root
    show innodb status
    ---TRANSACTION 3 1192493073, not started, process no 30276, OS thread id 1091385664
    MySQL thread id 353647, query id 2023766568 10.17.28.198 SOMEDBUSER
    ---TRANSACTION 3 1192492283, not started, process no 30276, OS thread id 1277651264
    MySQL thread id 353365, query id 2023765126 10.17.28.196 SOMEDBUSER
    ---TRANSACTION 3 1192490477, not started, process no 30276, OS thread id 1285105984
    MySQL thread id 353359, query id 2023761735 o1.int.REDACTEDDBNAME.com 10.17.28.202 SOMEDBUSER
    ---TRANSACTION 3 1192492027, not started, process no 30276, OS thread id 1280047424
    MySQL thread id 353349, query id 2023764627 10.17.28.200 SOMEDBUSER
    ---TRANSACTION 3 1192492013, not started, process no 30276, OS thread id 1286170944
    MySQL thread id 353341, query id 2023764612 10.17.28.200 SOMEDBUSER
    ---TRANSACTION 3 1192492692, not started, process no 30276, OS thread id 1074616640
    MySQL thread id 353330, query id 2023766011 10.17.28.196 SOMEDBUSER
    ---TRANSACTION 3 1192492195, not started, process no 30276, OS thread id 1289365824
    MySQL thread id 353331, query id 2023764975 10.17.28.196 SOMEDBUSER
    ---TRANSACTION 3 1192490476, not started, process no 30276, OS thread id 1282443584
    MySQL thread id 353321, query id 2023761733 o1.int.REDACTEDDBNAME.com 10.17.28.202 SOMEDBUSER
    ---TRANSACTION 3 1192492026, not started, process no 30276, OS thread id 1279781184
    MySQL thread id 353317, query id 2023764626 10.17.28.200 SOMEDBUSER
    ---TRANSACTION 3 1192492024, not started, process no 30276, OS thread id 1075878208
    MySQL thread id 353301, query id 2023764633 10.17.28.200 SOMEDBUSER
    ---TRANSACTION 3 1192493072, not started, process no 30276, OS thread id 1285372224
    MySQL thread id 353294, query id 2023766570 10.17.28.196 SOMEDBUSER
    ---TRANSACTION 3 1192493075, ACTIVE 0 sec, process no 30276, OS thread id 1282976064
    MySQL thread id 353639, query id 2023766569 10.17.28.196 SOMEDBUSER
    ---TRANSACTION 3 1192493074, ACTIVE 0 sec, process no 30276, OS thread id 1278716224
    MySQL thread id 353306, query id 2023766567 10.17.28.196 SOMEDBUSER
    ---TRANSACTION 3 1192493012, ACTIVE 0 sec, process no 30276, OS thread id 1288300864 estimating records in index range
    mysql tables in use 1, locked 0
    MySQL thread id 353637, query id 2023766454 10.17.28.198 SOMEDBUSER statisticsselect id, eventTime from Event where generatedID = 'a1671cb4fa968265923d52f10dff485b30003c3e' and appID = 1000344 and eventTime < '2009-01-24 00
    :08:05'
    ---TRANSACTION 3 1192492953, ACTIVE 0 sec, process no 30276, OS thread id 1289099584 inserting
    mysql tables in use 1, locked 1
    1 lock struct(s), heap size 368, undo log entries 1
    MySQL thread id 353643, query id 2023766349 10.17.28.198 SOMEDBUSER updateinsert ignore into SummaryTable (uniqueID, generatedID, id, summaryID) values(4581168, '174b1e0a9a814f6ac9aa5e037ca4e95f5b75436a', 2
    28105450, 1175049)
    TABLE LOCK table `REDACTEDDBNAME/SummaryTable` trx id 3 1192492953 lock mode IX
    ---TRANSACTION 3 1192492029, ACTIVE 0 sec, process no 30276, OS thread id 1292028224
    MySQL thread id 353325, query id 2023764630 10.17.28.200 SOMEDBUSER
    ---TRANSACTION 3 1192492019, ACTIVE 0 sec, process no 30276, OS thread id 1078044992
    1 lock struct(s), heap size 368, undo log entries 1
    MySQL thread id 353357, query id 2023764613 10.17.28.200 SOMEDBUSER
    TABLE LOCK table `REDACTEDDBNAME/SummaryTable` trx id 3 1192492019 lock mode IX
    ---TRANSACTION 3 1192490462, ACTIVE 1 sec, process no 30276, OS thread id 1272592704
    1 lock struct(s), heap size 368, undo log entries 1
    MySQL thread id 353345, query id 2023761698 pq1o.int.REDACTEDDOMAIN.com 10.17.28.202 SOMEDBUSER
    TABLE LOCK table `REDACTEDDBNAME/SummaryTable` trx id 3 1192490462 lock mode IX
    ---TRANSACTION 3 1192489386, ACTIVE 1 sec, process no 30276, OS thread id 1273923904
    MySQL thread id 353332, query id 2023759720 pq1o.int.REDACTEDDOMAIN.com 10.17.28.202 SOMEDBUSER
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (read thread)
    I/O thread 4 state: complete io for buf page (read thread) ev set
    I/O thread 5 state: waiting for i/o request (read thread)
    I/O thread 6 state: waiting for i/o request (read thread)
    I/O thread 7 state: waiting for i/o request (read thread)
    I/O thread 8 state: waiting for i/o request (write thread)
    I/O thread 9 state: waiting for i/o request (write thread)
    I/O thread 10 state: waiting for i/o request (write thread)
    I/O thread 11 state: waiting for i/o request (write thread)
    I/O thread 12 state: waiting for i/o request (write thread)
    I/O thread 13 state: waiting for i/o request (write thread)
    Pending normal aio reads: 5, aio writes: 0,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    160071318 OS file reads, 648204637 OS file writes, 4801881 OS fsyncs
    274.55 reads/s, 18627 avg bytes/read, 923.82 writes/s, 10.80 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 195966, free list len 215220, seg size 411187,
    148767609 inserts, 140220142 merged recs, 20426300 merges
    Hash table size 25499819, used cells 15297390, node heap has 34452 buffer(s)
    31903.42 hash searches/s, 9762.65 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 694 1140334668
    Log flushed up to 694 1138248853
    Last checkpoint at 694 1054872350
    Max checkpoint age 2122655294
    Modified age 71216258
    Checkpoint age 85462318
    0 pending log writes, 0 pending chkp writes
    556780642 log i/o's done, 896.78 log i/o's/second, 317603 syncs, 13028 checkpoints
    log sync callers: 119621846 buffer pool, background 253865 sync and 0 async, 6 internal, checkpoint 13028 sync and 0 async, 0 archive, commit 0 sync and 556456142 async
    log sync syncers: 63751 buffer pool, background 253851 sync and 0 async, 0 internal, checkpoint 1 sync and 0 async, 0 archive, commit 0 sync and 0 async
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 14424640956; in additional pool allocated 15401728
    Internal hash tables (constant factor + variable factor)
    Adaptive hash index 768447944 (203998552 + 564449392)
    Page hash 12750664
    Dictionary cache 153382672 (153001160 + 381512)
    File system 99752 (82672 + 17080)
    Lock system 31882872 (31875512 + 7360)
    Recovery system 0 (0 + 0)
    Threads 411256 (406936 + 4320)
    innodb_io_pattern 0 (0 + 0)
    Buffer pool size 786432
    Buffer pool size, bytes 12884901888
    Free buffers 0
    Database pages 751980
    Modified db pages 36931
    Pending reads 7
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 172990976, created 3616425, written 119621846
    397.90 reads/s, 17.25 creates/s, 260.68 writes/s
    Buffer pool hit rate 998 / 1000
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    2 read views open inside InnoDB
    Main thread process no. 30276, id 1270995264, state: doing insert buffer merge
    Number of rows inserted 121191856, updated 239010958, deleted 66431605, read 22848007125
    430.71 inserts/s, 256.35 updates/s, 65.39 deletes/s, 47239.75 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

    mysqltuner:

    >> MySQLTuner 1.0.0 - Major Hayden
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.67-percona-highperf-b10-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in InnoDB tables: 98G (Tables: 33)
    [--] Data in ARCHIVE tables: 21G (Tables: 3)
    [OK] Total fragmented tables: 0

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6d 1h 32m 13s (2B q [3K qps], 353K conn, TX: 1208B, RX: 345B)
    [--] Reads / Writes: 54% / 46%
    [--] Total buffers: 12.3G global + 8.8M per thread (100 max threads)
    [OK] Maximum possible memory usage: 13.1G (83% of installed RAM)
    [OK] Slow queries: 0% (65K/2B)
    [!!] Highest connection usage: 100% (101/100)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/171.5G
    [OK] Query cache efficiency: 22.5% (146M cached / 649M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9M sorts)
    [OK] Temporary tables created on disk: 0% (4 on disk / 634K total)
    [OK] Thread cache hit rate: 85% (51K created / 353K connections)
    [!!] Table cache hit rate: 14% (136 open / 944 opened)
    [OK] Open file limit used: 0% (8/1K)
    [OK] Table locks acquired immediately: 100% (930M immediate / 930M locks)
    [!!] Connections aborted: 10%
    [!!] InnoDB data size / buffer pool: 98.1G/12.0G


    show variables like 'inno%';

    +---------------------------------+------------------------+
    | Variable_name | Value |
    +---------------------------------+------------------------+
    | innodb_additional_mem_pool_size | 104857600 |
    | innodb_autoextend_increment | 8 |
    | innodb_buffer_pool_awe_mem_mb | 0 |
    | innodb_buffer_pool_size | 12884901888 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:10M:autoextend |
    | innodb_data_home_dir | /var/lib/mysql |
    | innodb_adaptive_hash_index | ON |
    | innodb_doublewrite | ON |
    | innodb_fast_shutdown | 1 |
    | innodb_file_io_threads | 4 |
    | innodb_file_per_table | ON |
    | innodb_flush_log_at_trx_commit | 2 |
    | innodb_show_locks_held | 10 |
    | innodb_show_verbose_locks | 0 |
    | innodb_flush_method | O_DIRECT |
    | innodb_force_recovery | 0 |
    | innodb_lock_wait_timeout | 50 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | innodb_log_arch_dir | |
    | innodb_log_archive | OFF |
    | innodb_log_buffer_size | 8388608 |
    | innodb_log_file_size | 1310720000 |
    | innodb_log_files_in_group | 2 |
    | innodb_log_group_home_dir | /mnt/space/mysql |
    | innodb_max_dirty_pages_pct | 90 |
    | innodb_max_purge_lag | 0 |
    | innodb_mirrored_log_groups | 1 |
    | innodb_open_files | 300 |
    | innodb_rollback_on_timeout | OFF |
    | innodb_support_xa | ON |
    | innodb_sync_spin_loops | 20 |
    | innodb_table_locks | ON |
    | innodb_thread_concurrency | 0 |
    | innodb_thread_sleep_delay | 10000 |
    | innodb_io_capacity | 10000 |
    | innodb_read_ahead | 3 |
    | innodb_adaptive_checkpoint | 1 |
    | innodb_read_io_threads | 6 |
    | innodb_write_io_threads | 6 |
    | innodb_io_pattern_trace | 0 |
    | innodb_io_pattern_trace_running | 0 |
    | innodb_io_pattern_size_limit | 0 |
    +---------------------------------+------------------------+


    show status like 'Inno%';

    +-----------------------------------+---------------+
    | Variable_name | Value |
    +-----------------------------------+---------------+
    | Innodb_buffer_pool_pages_data | 749851 |
    | Innodb_buffer_pool_pages_dirty | 201610 |
    | Innodb_buffer_pool_pages_flushed | 119737751 |
    | Innodb_buffer_pool_pages_free | 0 |
    | Innodb_buffer_pool_pages_latched | 14 |
    | Innodb_buffer_pool_pages_misc | 36582 |
    | Innodb_buffer_pool_pages_total | 786432 |
    | Innodb_buffer_pool_read_ahead_rnd | 313043 |
    | Innodb_buffer_pool_read_ahead_seq | 51882 |
    | Innodb_buffer_pool_read_requests | 82444555081 |
    | Innodb_buffer_pool_reads | 155968244 |
    | Innodb_buffer_pool_wait_free | 670 |
    | Innodb_buffer_pool_write_requests | 5284546898 |
    | Innodb_data_fsyncs | 4806558 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 3 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 2840614096896 |
    | Innodb_data_reads | 160439748 |
    | Innodb_data_writes | 649826996 |
    | Innodb_data_written | 4456199910912 |
    | Innodb_dblwr_pages_written | 119737751 |
    | Innodb_dblwr_writes | 1227960 |
    | Innodb_log_waits | 0 |
    | Innodb_log_write_requests | 578440697 |
    | Innodb_log_writes | 558317241 |
    | Innodb_os_log_fsyncs | 327787 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 532729091072 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 3636656 |
    | Innodb_pages_read | 173377338 |
    | Innodb_pages_written | 119737751 |
    | Innodb_row_lock_current_waits | 0 |
    | Innodb_row_lock_time | 581360187 |
    | Innodb_row_lock_time_avg | 41 |
    | Innodb_row_lock_time_max | 51977 |
    | Innodb_row_lock_waits | 13904715 |
    | Innodb_rows_deleted | 66587129 |
    | Innodb_rows_inserted | 121548831 |
    | Innodb_rows_read | 22916100586 |
    | Innodb_rows_updated | 239719343 |
    | Innodb_scan_pages_contiguous | 0 |
    | Innodb_scan_pages_jumpy | 0 |
    | Innodb_scan_data_in_pages | 0 |
    | Innodb_scan_garbages_in_pages | 0 |
    +-----------------------------------+---------------+

  • #2
    Sorry to bump. Anyone have any thoughts?

    Comment


    • #3
      I'm wondering: could this be a bug specific to the Percona b10 build? I've been searching for an answer to this all around and really haven't seen this as an issue except in cases where many large, temp tables are created.

      Percona folks - seen this before? I'm fine w/ paying for support time if need be.

      Comment


      • #4
        Hi friend,

        The InnoDB tablespace continues to be used by the tables, regardless use its own tablespace for each table.

        In the shared tablespace, when you configure MySQL to use innodb_per_table option, InnoDB stores the index and metadata about the tables, because of this, the shared tablespace keeps growing.

        Ok?

        Bye and best regards!
        WB

        Comment


        • #5
          I have this same issue. We have an ibdata1 file growing at about 4G per day. I can't believe that with our amount of db usage that a file would need to grow like that. Something is off the rails!

          We were about to run out of space with an insane ibdata1 file at 175G. So what I did was take the server down, did a dump of the data and restarted. I set up two slaves from the dump with innodb_file_per_table setting on them and they were both replicating flawlessly for a day and were caught up.

          After they caught up I stopped the master, promoted a slave to master and now have the other slave replicating from the promoted server.

          What is strange is that the new master has an ibdata1 file growing at 4G per day, but the slave ibdata1 file is NOT growing. Can anyone explaing what might be going on?

          Comment


          • #6
            Did this problem get resolved? I'm seeing something like this myself using the Percona "hiperf" build 19 version of 5.0.86.

            When innodb_file_per_table is on, one thing that still gets stored in the ibdata files is the "rollback segment". As a transaction runs, it records data about how to undo its changes to the "rollback segment". The rollback segment consumes space on disk and has recently used pages cached in the buffer pool, just like a table.

            The job of pruning data from the rollback segment belongs to the "main thread", I believe. If the main thread isn't doing its job or can't keep up, the rollback segment can grow without bound.

            My problem with expanding ibdata files seems to be due to rollback segment growth. Pages in ibdata files have a header with a "page type" value (at offset FIL_PAGE_TYPE in the source). Almost all the pages in my ibdata files are of type FIL_PAGE_UNDO_LOG, which I believe means they belong to the rollback segment.

            "Show innodb status" includes a "history list length" value, which I believe is directly related to the size of the content of the rollback segment. For me it is monotonically increasing without bound. Looking at the value of "history list length" in your output makes me think something else is going on to cause your problem, because the value for you is relatively low. For me it's pushing 20 million.

            Comment


            • #7
              Re: Wagner Bianchi who stated index and metadata for innodb tables is stored in the ibdata files even when innodb_file_per_table is on...

              I believe Wagner is incorrect about indexes still being stored in the ibdata files (aka "system tablespace")

              I've examined the content of my ibdata files page-by-page and I find no evidence of indexes. My dataset is quite large and my ibdata files are relatively tiny. (Or at least they were until my rollback segment started blowing up in size.) I'm quite confident in saying there's no way secondary indexes are getting stored in the system tablespace.

              Comment


              • #8
                I just fixed my problem of having the autoextend ibdata file grow like crazy despite having innodb_file_per_table on. My particular problem was due to the rollback segment, which gets stored in the ibdata file(s) regardless of the setting of innodb_file_per_table, growing without bound.

                The problem was a week old open transaction. The fix was to kill this transaction. (Well, the real fix is to fix the in-house application program that ran this transaction. The programmers I work with are extremely astute and a problem of this nature was last on my list of suspects.)

                A piece of code started a transaction, ran a bunch of SELECTs, and never bothered to COMMIT or ROLLBACK. Innodb is going to keep all the UNDO generated since the transaction started days ago in the rollback segment. If the transaction wants to view a page that has been modified by another transaction, Innodb needs the UNDO to be able create a copy of the page as it was when the transaction started. That's the mechanism Innodb uses to be able to provide a consistent point-in-time "snapshot" for query transactions without doing any read locking.

                The thing to look for in "show innodb status" is something akin to this:

                ---TRANSACTION 0 1682481376, ACTIVE 723800 sec, process no 23229, OS thread id 1269274944
                MySQL thread id 2969437, query id 867160090 foobar.com 172.17.2.71 killroy
                Trx read view will not see trx with id >= 0 1682481377, sees < 0 1682467782


                The "ACTIVE 723800 sec" is the smoking gun.

                --Ed Walker

                Comment


                • #9
                  Thanks for the reply Ed. That really makes sense. I will investigate further.

                  Comment

                  Working...
                  X