MySQL Newbie problem with AUTO-INC Table Lock
Posted by: marcus alday
Date: February 23, 2009 06:27PM
Date: February 23, 2009 06:27PM
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,IDX_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,IDX_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,IDX_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:o < 0 32106772 undo n:o < 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>
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,IDX_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,IDX_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,IDX_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:o < 0 32106772 undo n:o < 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>
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.