Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Newbie problem with AUTO-INC Table Lock

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

  • MySQL Newbie problem with AUTO-INC Table Lock

    Hi All,

    I'm pretty new to mysql so please bear with me. I think I have a problem with my table locking when I'm inserting metrics data into a table. Everything seems pretty quick when the insert load is low, but when it ratchets up, my queries take several minutes to run (yikes!) I've got the output of some diagnostic commands here. can someone with some experience with this help me confirm that it is the auto_increment field that is causing all my problems? or maybe i don't have enough memory allocated to the right place? Any help would be appreciated. Thx.


    ps. I'm using MySQL 5.0.45 on the x86_64 platform.


    -- ================================================== ========== ====================
    -- The Table
    -- ================================================== ========== ====================

    mysql> desc metrics;
    +----------------+---------------------+------+-----+------- ------------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------+---------------------+------+-----+------- ------------+----------------+
    | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
    | account_id | int(10) unsigned | NO | | | |
    | project_id | int(10) unsigned | NO | MUL | | |
    | event_type_id | int(10) unsigned | NO | MUL | | |
    | event_interval | int(10) unsigned | NO | | 0 | |
    | event_data | varchar(255) | NO | MUL | | |
    | created_at | timestamp | NO | | CURRENT_TIMESTAMP | |
    +----------------+---------------------+------+-----+------- ------------+----------------+
    7 rows in set (0.00 sec)


    -- ================================================== ========== ====================
    -- The Table Indexes
    -- ================================================== ========== ====================


    mysql> show index from metrics;
    +---------+------------+---------------------------+-------- ------+---------------+-----------+-------------+----------+ --------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +---------+------------+---------------------------+-------- ------+---------------+-----------+-------------+----------+ --------+------+------------+---------+
    | metrics | 0 | PRIMARY | 1 | id | A | 33604135 | NULL | NULL | | BTREE | |
    | metrics | 1 | IDX_METRICS_project_id | 1 | project_id | A | 9111 | NULL | NULL | | BTREE | |
    | metrics | 1 | IDX_METRICS_event_type_id | 1 | event_type_id | A | 19 | NULL | NULL | | BTREE | |
    | metrics | 1 | IDX_METRICS_event_data | 1 | event_data | A | 19 | NULL | NULL | | BTREE | |
    | metrics | 1 | IDX_METRICS_pid_type_data | 1 | project_id | A | 19 | NULL | NULL | | BTREE | |
    | metrics | 1 | IDX_METRICS_pid_type_data | 2 | event_type_id | A | 19 | NULL | NULL | | BTREE | |
    | metrics | 1 | IDX_METRICS_pid_type_data | 3 | event_data | A | 15895 | NULL | NULL | | BTREE | |
    +---------+------------+---------------------------+-------- ------+---------------+-----------+-------------+----------+ --------+------+------------+---------+
    7 rows in set (0.26 sec)





    -- ================================================== ========== ====================
    -- THE slow query
    -- ================================================== ========== ====================
    # Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 17229
    SELECT
    Placement.id,
    Placement.name,
    Placement.status,
    sum((SELECT count(Metrics.event_type_id)
    FROM metrics Metrics
    WHERE Metrics.project_id = Variant.id
    AND Metrics.event_type_id = 1
    AND Metrics.event_data = 'START'
    GROUP BY Variant.id)) as impressions,
    sum((SELECT count(Metrics.event_type_id)
    FROM metrics Metrics
    WHERE Metrics.project_id = Variant.id
    AND Metrics.event_type_id = 3
    GROUP BY Variant.id)) as click_throughs,
    sum((SELECT sum(Metrics.event_interval)
    FROM metrics Metrics
    WHERE Metrics.project_id = Variant.id
    AND Metrics.event_type_id = 1
    AND Metrics.event_data = 'CONTINUE'
    GROUP BY Variant.id)) as time_with_brand,
    count(Variant.id) as variant_total,
    Template.width,
    Template.height

    FROM files AS Campaign
    LEFT JOIN files AS Placement ON
    Placement.parent_id = Campaign.id
    LEFT JOIN files AS Variant ON
    Variant.parent_id = Placement.id
    LEFT JOIN project_templates AS pt ON
    pt.project_id = Variant.id
    LEFT JOIN files AS Template ON
    pt.file_id = Template.id

    WHERE Campaign.id = 11485
    AND Placement.file_type_id = 11
    AND Placement.is_active = 1
    AND Variant.file_type_id = 16
    AND Variant.is_active = 1

    GROUP BY Placement.id;


    -- ================================================== ========== ====================
    -- EXPLAIN (eg. show plan) of query
    -- ================================================== ========== ====================
    mysql> explain
    -> SELECT
    -> Placement.id,
    -> Placement.name,
    -> Placement.status,
    -> sum((SELECT count(Metrics.event_type_id)
    -> FROM metrics Metrics
    -> WHERE Metrics.project_id = Variant.id
    -> AND Metrics.event_type_id = 1
    -> AND Metrics.event_data = 'START'
    -> GROUP BY Variant.id)) as impressions,
    -> sum((SELECT count(Metrics.event_type_id)
    -> FROM metrics Metrics
    -> WHERE Metrics.project_id = Variant.id
    -> AND Metrics.event_type_id = 3
    -> GROUP BY Variant.id)) as click_throughs,
    -> sum((SELECT sum(Metrics.event_interval)
    -> FROM metrics Metrics
    -> WHERE Metrics.project_id = Variant.id
    -> AND Metrics.event_type_id = 1
    -> AND Metrics.event_data = 'CONTINUE'
    -> GROUP BY Variant.id)) as time_with_brand,
    -> count(Variant.id) as variant_total,
    -> Template.width,
    -> Template.height
    ->
    -> FROM files AS Campaign
    -> LEFT JOIN files AS Placement ON
    -> Placement.parent_id = Campaign.id
    -> LEFT JOIN files AS Variant ON
    -> Variant.parent_id = Placement.id
    -> LEFT JOIN project_templates AS pt ON
    -> pt.project_id = Variant.id
    -> LEFT JOIN files AS Template ON
    -> pt.file_id = Template.id
    ->
    -> WHERE Campaign.id = 11485
    -> AND Placement.file_type_id = 11
    -> AND Placement.is_active = 1
    -> AND Variant.file_type_id = 16
    -> AND Variant.is_active = 1
    ->
    -> GROUP BY Placement.id;
    +----+--------------------+-----------+--------+------------ ------------------------------------------------------------ ---------------------------+---------------------------+---- -----+-------------------------------------+------+--------- --------------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+--------------------+-----------+--------+------------ ------------------------------------------------------------ ---------------------------+---------------------------+---- -----+-------------------------------------+------+--------- --------------------------------------------------+
    | 1 | PRIMARY | Campaign | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index; Using temporary; Using filesort |
    | 1 | PRIMARY | Placement | ref | PRIMARY,IDX_FILES_parent_id,IDX_FILES_file_type_id | IDX_FILES_parent_id | 5 | const | 3 | Using where |
    | 1 | PRIMARY | Variant | ref | IDX_FILES_parent_id,IDX_FILES_file_type_id | IDX_FILES_parent_id | 5 | flashadstore.Placement.id | 11 | Using where |
    | 1 | PRIMARY | pt | ALL | NULL | NULL | NULL | NULL | 4952 | |
    | 1 | PRIMARY | Template | eq_ref | PRIMARY | PRIMARY | 4 | flashadstore.pt.file_id | 1 | |
    | 4 | DEPENDENT SUBQUERY | Metrics | ref | IDX_METRICS_project_id,IDX_METRICS_event_type_id,I DX_METRICS _event_data,IDX_METRICS_pid_type_data | IDX_METRICS_pid_type_data | 265 | flashadstore.Variant.id,const,const | 2114 | Using where; Using temporary; Using filesort |
    | 3 | DEPENDENT SUBQUERY | Metrics | ref | IDX_METRICS_project_id,IDX_METRICS_event_type_id,I DX_METRICS _pid_type_data | IDX_METRICS_project_id | 4 | flashadstore.Variant.id | 3688 | Using where; Using temporary; Using filesort |
    | 2 | DEPENDENT SUBQUERY | Metrics | ref | IDX_METRICS_project_id,IDX_METRICS_event_type_id,I DX_METRICS _event_data,IDX_METRICS_pid_type_data | IDX_METRICS_pid_type_data | 265 | flashadstore.Variant.id,const,const | 2114 | Using where; Using index; Using temporary; Using filesort |
    +----+--------------------+-----------+--------+------------ ------------------------------------------------------------ ---------------------------+---------------------------+---- -----+-------------------------------------+------+--------- --------------------------------------------------+
    8 rows in set (0.01 sec)

    mysql>







    --
    -- OUTPUT of 'show innodb status\g'
    --





    =====================================
    090218 18:31:02 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 31 seconds
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 1011062, signal count 609140
    Mutex spin waits 0, rounds 815728231, OS waits 368073
    RW-shared spins 1357619, OS waits 506361; RW-excl spins 1293148, OS waits 83957
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    090218 18:06:13
    *** (1) TRANSACTION:
    TRANSACTION 0 32053995, ACTIVE 0 sec, process no 26527, OS thread id 1222187328 setting auto-inc lock
    mysql tables in use 1, locked 1
    LOCK WAIT 1 lock struct(s), heap size 368
    MySQL thread id 16768395, query id 51097441 192.168.100.28 flashadtracker update
    INSERT INTO metrics(
    created_at,
    account_id,
    project_id,
    event_type_id,
    event_interval,
    event_data
    ) VALUES (
    '2009-02-18 18:02:23',
    706,
    15517,
    1,
    5,
    'CONTINUE'
    )
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    TABLE LOCK table `flashadstore/metrics` trx id 0 32053995 lock mode AUTO-INC waiting
    *** (2) TRANSACTION:
    TRANSACTION 0 32053994, ACTIVE 0 sec, process no 26527, OS thread id 1183050048 inserting, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    2 lock struct(s), heap size 368, undo log entries 1
    MySQL thread id 16768394, query id 51097439 192.168.100.28 flashadtracker update
    INSERT INTO metrics(
    created_at,
    account_id,
    project_id,
    event_type_id,
    event_interval,
    event_data
    ) VALUES (
    '2009-02-18 18:02:23',
    757,
    16243,
    1,
    0,
    'START'
    )
    *** (2) HOLDS THE LOCK(S):
    TABLE LOCK table `flashadstore/metrics` trx id 0 32053994 lock mode AUTO-INC
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    TABLE LOCK table `flashadstore/metrics` trx id 0 32054266 lock mode AUTO-INC waiting
    TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH
    *** WE ROLL BACK TRANSACTION (2)
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 32108885
    Purge done for trx's n < 0 32106772 undo n < 0 0
    History list length 1
    Total number of lock structs in row lock hash table 0
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 32106522, not started, process no 26527, OS thread id 1195563328
    MySQL thread id 16818784, query id 51263864 localhost root
    show innodb status
    --------
    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 (write thread)
    Pending normal aio reads: 0, 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
    45136 OS file reads, 19431865 OS file writes, 17046333 OS fsyncs
    0.06 reads/s, 16384 avg bytes/read, 22.75 writes/s, 22.22 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 5, seg size 7,
    1459 inserts, 1459 merged recs, 457 merges
    Hash table size 6225619, used cells 6173511, node heap has 43899 buffer(s)
    111.35 hash searches/s, 2.52 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 5 2050090675
    Log flushed up to 5 2050090675
    Last checkpoint at 5 2048028336
    0 pending log writes, 0 pending chkp writes
    16879349 log i/o's done, 22.06 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 3532057594; in additional pool allocated 1048576
    Buffer pool size 192000
    Free buffers 0
    Database pages 148101
    Modified db pages 298
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 430822, created 257832, written 2888473
    0.06 reads/s, 0.22 creates/s, 0.97 writes/s
    Buffer pool hit rate 1000 / 1000
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    1 read views open inside InnoDB
    Main thread process no. 26527, id 1167075648, state: sleeping
    Number of rows inserted 30010071, updated 27804, deleted 46073, read 9654389800
    22.64 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
    1 row in set, 1 warning (1.36 sec)

    mysql>

  • #2
    You should probably upgrade to 5.1 to avoid this problem.

    Comment

    Working...
    X