Innodb bogs down on complicated query(ies)

[Lee Krawczyk]

For some reason I have a fairly complex select query that is causing a massive slow down over a period of 3 to 4 minutes. I've tuned it to the best of my ability, and while it encompases a number of LEFT JOINs I don't see why it would have an effect on other selects, inserts, and updates. I have just loaded 5.0.21, but have noticed this since we converted from Oracle to 5.0.18 some 4 weeks ago.

I am not doing any manual locking, but everything is running through DBD::mysql (latest version). All connections are set with AutoCommit => 0 and innodb_table_locks = 0;

I am not seeing any lock waitings but it seems strange that updates and inserts on innodb tables appear to be setting table locks, and that these updates and inserts are taking any time at all to complete. Normally, they are lightning quick and we have some 20 people banging at the database all day.

The following is an audit of SHOW INNODB STATUS listing over the 3 and half minutes of when the offending query begins and when it ends.

The transaction number of the offending query is 823756.

The transaction number of the updates affected are 823807 and 823814.

We also use stored functions heavily, but any lock issues have supposedly been fixed, and all of the stored functions only perform selects. They do create cursors, though.

Any advice is appreciated since during that 3 to 4 minutes work comes to a complete halt.

Thanks, Lee Krawczyk


mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:33:08 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1075, signal count 966
Mutex spin waits 57705, rounds 45654, OS waits 413
RW-shared spins 1015, OS waits 497; RW-excl spins 211, OS waits 138
------------
TRANSACTIONS
------------
Trx id counter 0 823767
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 2
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13045, OS thread id 81941
MySQL thread id 2988, query id 983360 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823766, ACTIVE 1 sec, process no 13046, OS thread id 86038
mysql tables in use 8, locked 8
MySQL thread id 3134, query id 983359 localhost root
select c1.case_id,
concat_ws(' ',p1.appellation,p1.first,p1.middle,p1.last,p1.designation),
c1.street,
c1.addr2,
c1.city,
c1.state,
c1.zip,
p1.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
c1.disposition,
fnCases_procedur
Trx read view will not see trx with id >= 0 823767, sees < 0 823756
---TRANSACTION 0 823756, ACTIVE 14 sec, process no 11114, OS thread id 45068 fetching rows, thread declared inside InnoDB 123
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8294 OS file reads, 3623 OS file writes, 2597 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 3.67 writes/s, 2.67 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 523718, node heap has 696 buffer(s)
350.88 hash searches/s, 974.34 non-hash searches/s
---
LOG
---
Log sequence number 1 255775900
Log flushed up to 1 255775900
Last checkpoint at 1 255775900
0 pending log writes, 0 pending chkp writes
815 log i/o's done, 0.75 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3037440
Buffer pool size 16384
Free buffers 5227
Database pages 10461
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10452, created 9, written 2663
0.00 reads/s, 0.00 creates/s, 1.50 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
2 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: sleeping
Number of rows inserted 898, updated 558, deleted 407, read 48087330
0.33 inserts/s, 0.00 updates/s, 0.33 deletes/s, 3179.27 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:33:19 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1081, signal count 972
Mutex spin waits 58258, rounds 46065, OS waits 416
RW-shared spins 1019, OS waits 499; RW-excl spins 211, OS waits 138
------------
TRANSACTIONS
------------
Trx id counter 0 823776
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 5
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13045, OS thread id 81941
MySQL thread id 2988, query id 988120 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823756, ACTIVE 25 sec, process no 11114, OS thread id 45068 fetching rows, thread declared inside InnoDB 480
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8294 OS file reads, 3638 OS file writes, 2607 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.36 writes/s, 0.91 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 523957, node heap has 697 buffer(s)
313.34 hash searches/s, 900.74 non-hash searches/s
---
LOG
---
Log sequence number 1 255777616
Log flushed up to 1 255777616
Last checkpoint at 1 255777616
0 pending log writes, 0 pending chkp writes
819 log i/o's done, 0.40 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3036160
Buffer pool size 16384
Free buffers 5226
Database pages 10461
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10452, created 9, written 2673
0.00 reads/s, 0.00 creates/s, 1.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: sleeping
Number of rows inserted 901, updated 561, deleted 409, read 48117093
0.27 inserts/s, 0.27 updates/s, 0.18 deletes/s, 2705.48 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.06 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:33:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1084, signal count 975
Mutex spin waits 58660, rounds 46314, OS waits 417
RW-shared spins 1023, OS waits 501; RW-excl spins 212, OS waits 138
------------
TRANSACTIONS
------------
Trx id counter 0 823785
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13045, OS thread id 81941
MySQL thread id 2988, query id 990893 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823756, ACTIVE 34 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 361
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8295 OS file reads, 3641 OS file writes, 2610 OS fsyncs
0.11 reads/s, 16384 avg bytes/read, 0.33 writes/s, 0.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 525026, node heap has 699 buffer(s)
363.96 hash searches/s, 1044.44 non-hash searches/s
---
LOG
---
Log sequence number 1 255785500
Log flushed up to 1 255785500
Last checkpoint at 1 255777616
0 pending log writes, 0 pending chkp writes
822 log i/o's done, 0.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3042048
Buffer pool size 16384
Free buffers 5223
Database pages 10462
Modified db pages 17
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10453, created 9, written 2673
0.11 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: sleeping
Number of rows inserted 904, updated 569, deleted 412, read 48145557
0.33 inserts/s, 0.89 updates/s, 0.33 deletes/s, 3162.32 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:33:36 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1085, signal count 976
Mutex spin waits 59182, rounds 46707, OS waits 417
RW-shared spins 1025, OS waits 502; RW-excl spins 212, OS waits 138
------------
TRANSACTIONS
------------
Trx id counter 0 823787
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13045, OS thread id 81941
MySQL thread id 2988, query id 993619 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823756, ACTIVE 42 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 211
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8296 OS file reads, 3659 OS file writes, 2620 OS fsyncs
0.12 reads/s, 16384 avg bytes/read, 2.25 writes/s, 1.25 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 525298, node heap has 699 buffer(s)
509.19 hash searches/s, 1179.85 non-hash searches/s
---
LOG
---
Log sequence number 1 255785500
Log flushed up to 1 255785500
Last checkpoint at 1 255785500
0 pending log writes, 0 pending chkp writes
823 log i/o's done, 0.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3042176
Buffer pool size 16384
Free buffers 5222
Database pages 10463
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10454, created 9, written 2690
0.12 reads/s, 0.00 creates/s, 2.12 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: sleeping
Number of rows inserted 904, updated 569, deleted 412, read 48176357
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 3849.52 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.02 sec)

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3171 to server version: 5.0.21-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:34:20 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 44 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1157, signal count 1029
Mutex spin waits 72760, rounds 55952, OS waits 483
RW-shared spins 1032, OS waits 505; RW-excl spins 215, OS waits 139
------------
TRANSACTIONS
------------
Trx id counter 0 823809
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13351, OS thread id 94219
MySQL thread id 3171, query id 1019620 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823807, ACTIVE 10 sec, process no 13044, OS thread id 77844
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 3169, query id 1017115 localhost root Table lock
update STATEMENT ON INNODB TABLE (CONTENT CENSORED)
---TRANSACTION 0 823804, ACTIVE 14 sec, process no 13045, OS thread id 81941, thread declared inside InnoDB 499
mysql tables in use 11, locked 11
MySQL thread id 3165, query id 1019568 localhost root Sending data
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
fnCases_providers_phone(c1.case_id),
fnCases_procedures_desc(c1.case_id)
from cases c
Trx read view will not see trx with id >= 0 823809, sees < 0 823756
---TRANSACTION 0 823803, ACTIVE 18 sec, process no 13034, OS thread id 65553
mysql tables in use 7, locked 7
MySQL thread id 3164, query id 1019617 localhost root Creating tmp table
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
c1.disposition,
datediff(curdate(),c1.review_date),
c1.tickler
fr
---TRANSACTION 0 823801, ACTIVE 26 sec, process no 13037, OS thread id 73747, thread declared inside InnoDB 499
mysql tables in use 7, locked 7
MySQL thread id 3160, query id 1019523 localhost root removing tmp table
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
c1.disposition,
datediff(curdate(),c1.review_date),
c1.tickler
fr
Trx read view will not see trx with id >= 0 823809, sees < 0 823756
---TRANSACTION 0 823756, ACTIVE 87 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 402
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8319 OS file reads, 3687 OS file writes, 2641 OS fsyncs
0.51 reads/s, 38466 avg bytes/read, 0.62 writes/s, 0.47 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 526768, node heap has 702 buffer(s)
8534.42 hash searches/s, 864.25 non-hash searches/s
---
LOG
---
Log sequence number 1 255787630
Log flushed up to 1 255787630
Last checkpoint at 1 255787630
0 pending log writes, 0 pending chkp writes
834 log i/o's done, 0.24 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3085952
Buffer pool size 16384
Free buffers 5165
Database pages 10517
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10508, created 9, written 2705
1.20 reads/s, 0.00 creates/s, 0.33 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
3 queries inside InnoDB, 0 queries in queue
4 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: waiting for server activity
Number of rows inserted 908, updated 570, deleted 417, read 49396347
0.09 inserts/s, 0.02 updates/s, 0.11 deletes/s, 27726.42 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (1.77 sec)

ERROR:
No query specified

mysql> exit
Bye

# ps -eaf
UID PID PPID C STIME TTY TIME CMD
root 11007 1 0 May02 ? 00:00:00 /bin/sh /u01/mysql/bin/mysqld_safe --datadir=/u03/data
mysql 11034 11007 0 May02 ? 00:00:01 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11035 11034 0 May02 ? 00:00:02 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11036 11035 0 May02 ? 00:00:00 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11037 11035 0 May02 ? 00:00:00 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11038 11035 0 May02 ? 00:00:22 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11039 11035 0 May02 ? 00:00:00 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11041 11035 0 May02 ? 00:00:14 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11042 11035 0 May02 ? 00:00:09 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11043 11035 0 May02 ? 00:00:03 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11044 11035 0 May02 ? 00:00:01 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 11114 11035 0 May02 ? 00:02:40 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 12748 11035 0 08:29 ? 00:00:25 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13034 11035 0 09:11 ? 00:00:32 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13036 11035 0 09:12 ? 00:00:31 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13037 11035 0 09:12 ? 00:00:30 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13044 11035 0 09:12 ? 00:00:24 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13045 11035 0 09:12 ? 00:00:23 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13046 11035 0 09:12 ? 00:00:22 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
mysql 13051 11035 0 09:13 ? 00:00:27 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data
root 13261 1 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13264 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13265 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13266 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13267 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13269 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13270 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13271 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13272 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13273 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13274 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13275 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13276 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13277 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
uucp 13278 13261 0 09:58 ? 00:00:03 /usr/sbin/httpd
uucp 13279 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13280 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13281 13261 0 09:58 ? 00:00:02 /usr/sbin/httpd
uucp 13282 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13283 13261 0 09:58 ? 00:00:00 /usr/sbin/httpd
uucp 13284 13261 0 09:58 ? 00:00:01 /usr/sbin/httpd
mysql 13351 11035 0 10:34 ? 00:00:00 /u01/mysql/bin/mysqld --basedir=/u01/mysql --datadir=/u03/data --user=mysql
root 13352 12877 0 10:34 pts/1 00:00:00 ps -eaf




Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3185 to server version: 5.0.21-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:34:53 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1234, signal count 1069
--Thread 45068 has waited at ../include/btr0btr.ic line 28 for 5.00 seconds the semaphore:
S-lock on RW-latch at 0x5182a4a0 created in file buf0buf.c line 490
a writer (thread id 45068) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file not yet reserved line 0
Last time write locked in file buf0buf.c line 1693
Mutex spin waits 74414, rounds 59932, OS waits 553
RW-shared spins 1041, OS waits 510; RW-excl spins 217, OS waits 139
------------
TRANSACTIONS
------------
Trx id counter 0 823817
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13046, OS thread id 86038
MySQL thread id 3185, query id 1025067 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823816, ACTIVE 2 sec, process no 13357, OS thread id 110616
mysql tables in use 6, locked 0
MySQL thread id 3189, query id 1025044 localhost root Creating tmp table
select cb1.benefit_code,
b.description,
b.patient_type,
date_format(cb1.open,'%e-%b-%Y'),
date_format(cb1.closed,'%e-%b-%Y'),
cb1.retro,
cb1.approved,
date_format(cpr.service_date,'%e-%b-%Y') last_dos,
cb1.facility_id,
f.name,
cb1.phone,
cb1.street,
cb1.addr2,
cb1.city,
cb1.state,

---TRANSACTION 0 823815, ACTIVE 8 sec, process no 13356, OS thread id 106512
MySQL thread id 3188, query id 1025022 localhost root closing tables
select request_units,
approve_units,
approve_by,
date_format(approve_date,'%e-%b-%Y'),
track_code,
reason,
case_id,
benefit_code
from cases_durations
where case_id = '997260281'
and benefit_code = '05'
order by approve_date desc
---TRANSACTION 0 823814, ACTIVE 17 sec, process no 13353, OS thread id 98317
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 3183, query id 1023854 localhost root Table lock
update STATEMENT INNODB TABLE (CONTENT CENSORED)
---TRANSACTION 0 823811, ACTIVE 25 sec, process no 13051, OS thread id 90135 fetching rows, thread declared inside InnoDB 334
mysql tables in use 3, locked 0
MySQL thread id 3177, query id 1022821 localhost root Sending data
select concat_ws(' ',p1.appellation,p1.first,p1.middle,p1.last,p1.designation),
date_format(p1.birth_date,'%c-%d-%Y'),
if(p1.plan1_id=c1.plan_id,p1.plan1_type,p1.plan2_type),
c1.phone,
c1.street,
c1.addr2,
concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip),
p1.social_sec_no,
pl1.name,
n.name,

Trx read view will not see trx with id >= 0 823812, sees < 0 823756
---TRANSACTION 0 823809, ACTIVE 31 sec, process no 13036, OS thread id 69650, thread declared inside InnoDB 498
mysql tables in use 11, locked 11
MySQL thread id 3173, query id 1025084 localhost root removing tmp table
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
fnCases_providers_phone(c1.case_id),
fnCases_procedures_desc(c1.case_id)
from cases c
Trx read view will not see trx with id >= 0 823817, sees < 0 823756
---TRANSACTION 0 823807, ACTIVE 42 sec, process no 13044, OS thread id 77844
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 3169, query id 1017115 localhost root Table lock
update STATEMENT INNODB TABLE (CONTENT CENSORED)
---TRANSACTION 0 823804, ACTIVE 46 sec, process no 13045, OS thread id 81941
mysql tables in use 11, locked 11
MySQL thread id 3165, query id 1025097 localhost root Creating tmp table
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
fnCases_providers_phone(c1.case_id),
fnCases_procedures_desc(c1.case_id)
from cases c
---TRANSACTION 0 823803, ACTIVE 50 sec, process no 13034, OS thread id 65553, thread declared inside InnoDB 499
mysql tables in use 7, locked 7
MySQL thread id 3164, query id 1025077 localhost root Sending data
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
c1.disposition,
datediff(curdate(),c1.review_date),
c1.tickler
fr
Trx read view will not see trx with id >= 0 823817, sees < 0 823756
---TRANSACTION 0 823801, ACTIVE 58 sec, process no 13037, OS thread id 73747, thread declared inside InnoDB 499
mysql tables in use 7, locked 7
MySQL thread id 3160, query id 1004395 localhost root
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
c1.disposition,
datediff(curdate(),c1.review_date),
c1.tickler
fr
Trx read view will not see trx with id >= 0 823817, sees < 0 823756
---TRANSACTION 0 823756, ACTIVE 119 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 4
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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: consecutive i/o requests (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 44, 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
8363 OS file reads, 3692 OS file writes, 2646 OS fsyncs
1 pending preads, 0 pending pwrites
1.37 reads/s, 32395 avg bytes/read, 0.16 writes/s, 0.16 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 527857, node heap has 704 buffer(s)
3621.04 hash searches/s, 362.23 non-hash searches/s
---
LOG
---
Log sequence number 1 255787740
Log flushed up to 1 255787740
Last checkpoint at 1 255787740
0 pending log writes, 0 pending chkp writes
836 log i/o's done, 0.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317453464; in additional pool allocated 3180416
Buffer pool size 16384
Free buffers 5037
Database pages 10643
Modified db pages 0
Pending reads 44
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10590, created 9, written 2707
2.48 reads/s, 0.00 creates/s, 0.06 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
7 queries inside InnoDB, 0 queries in queue
8 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: waiting for server activity
Number of rows inserted 908, updated 570, deleted 418, read 49685670
0.00 inserts/s, 0.00 updates/s, 0.03 deletes/s, 8767.10 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (1.87 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:34:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1262, signal count 1083
Mutex spin waits 74534, rounds 60621, OS waits 580
RW-shared spins 1042, OS waits 510; RW-excl spins 217, OS waits 139
------------
TRANSACTIONS
------------
Trx id counter 0 823819
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13046, OS thread id 86038
MySQL thread id 3185, query id 1025859 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823818, ACTIVE 5 sec, process no 13359, OS thread id 118810
MySQL thread id 3191, query id 1025845 localhost root Opening tables
select cx1.ndc_code,
n.description,
date_format(cx1.fill_date,'%e-%b-%Y'),
cx1.case_id,
cx1.benefit_code,
cx1.diag_icd,
cx1.provider_id,
cx1.entry_time
from cases_rxs cx1 left join ndcs n on cx1.ndc_code = n.ndc_code
where cx1.case_id = '997248753'
and cx1.benefit_code = '05'
and cx1.diag_icd = '995
---TRANSACTION 0 823817, ACTIVE 5 sec, process no 13355, OS thread id 102415, thread declared inside InnoDB 499
mysql tables in use 8, locked 8
MySQL thread id 3187, query id 1025838 localhost root Sending data
select c1.case_id,
concat_ws(' ',p1.appellation,p1.first,p1.middle,p1.last,p1.designation),
c1.street,
c1.addr2,
c1.city,
c1.state,
c1.zip,
p1.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
c1.disposition,
fnCases_procedur
Trx read view will not see trx with id >= 0 823819, sees < 0 823756
---TRANSACTION 0 823816, ACTIVE 8 sec, process no 13357, OS thread id 110616
mysql tables in use 3, locked 0
MySQL thread id 3189, query id 1025788 localhost root Sorting result
select date_format(cpr1.service_date,'%e-%b-%Y'),
cpr1.proc_icd,
i.description,
cpr1.proc_cpt,
c.long_desc,
cpr1.cpt_mod,
' ',
cpr1.proc_hcpcs,
h.long_desc,
cpr1.hcpcs_mod,
' ',
cpr1.case_id,

---TRANSACTION 0 823815, ACTIVE 14 sec, process no 13356, OS thread id 106512
MySQL thread id 3188, query id 1025844 localhost root closing tables
select cx1.ndc_code,
n.description,
date_format(cx1.fill_date,'%e-%b-%Y'),
cx1.case_id,
cx1.benefit_code,
cx1.diag_icd,
cx1.provider_id,
cx1.entry_time
from cases_rxs cx1 left join ndcs n on cx1.ndc_code = n.ndc_code
where cx1.case_id = '997260281'
and cx1.benefit_code = '05'
and cx1.diag_icd = '205
---TRANSACTION 0 823814, ACTIVE 23 sec, process no 13353, OS thread id 98317
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 3183, query id 1023854 localhost root Table lock
update STATEMENT INNODB TABLE (CONTENT CENSORED)
---TRANSACTION 0 823811, ACTIVE 31 sec, process no 13051, OS thread id 90135 fetching rows, thread declared inside InnoDB 7
mysql tables in use 3, locked 0
MySQL thread id 3177, query id 1022821 localhost root Sending data
select concat_ws(' ',p1.appellation,p1.first,p1.middle,p1.last,p1.designation),
date_format(p1.birth_date,'%c-%d-%Y'),
if(p1.plan1_id=c1.plan_id,p1.plan1_type,p1.plan2_type),
c1.phone,
c1.street,
c1.addr2,
concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip),
p1.social_sec_no,
pl1.name,
n.name,

Trx read view will not see trx with id >= 0 823812, sees < 0 823756
---TRANSACTION 0 823809, ACTIVE 37 sec, process no 13036, OS thread id 69650, thread declared inside InnoDB 499
mysql tables in use 11, locked 11
MySQL thread id 3173, query id 1025808 localhost root Sending data
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
fnCases_providers_phone(c1.case_id),
fnCases_procedures_desc(c1.case_id)
from cases c
Trx read view will not see trx with id >= 0 823819, sees < 0 823756
---TRANSACTION 0 823807, ACTIVE 48 sec, process no 13044, OS thread id 77844
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 3169, query id 1017115 localhost root Table lock
update STATEMENT INNODB TABLE (CONTENT CENSORED)
---TRANSACTION 0 823804, ACTIVE 52 sec, process no 13045, OS thread id 81941, thread declared inside InnoDB 499
mysql tables in use 11, locked 11
MySQL thread id 3165, query id 1025861 localhost root
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
fnCases_providers_phone(c1.case_id),
fnCases_procedures_desc(c1.case_id)
from cases c
Trx read view will not see trx with id >= 0 823819, sees < 0 823756
---TRANSACTION 0 823803, ACTIVE 56 sec, process no 13034, OS thread id 65553, thread declared inside InnoDB 499
mysql tables in use 7, locked 7
MySQL thread id 3164, query id 1025858 localhost root Sending data
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
c1.disposition,
datediff(curdate(),c1.review_date),
c1.tickler
fr
Trx read view will not see trx with id >= 0 823819, sees < 0 823756
---TRANSACTION 0 823801, ACTIVE 64 sec, process no 13037, OS thread id 73747, thread declared inside InnoDB 499
mysql tables in use 7, locked 7
MySQL thread id 3160, query id 1025841 localhost root removing tmp table
select c1.case_id,
cb.benefit_code,
concat_ws(' ',p.appellation,p.first,p.middle,p.last,p.designation),
p.social_sec_no,
fnCases_diagnoses(c1.case_id),
fnCases_procedures(c1.case_id),
fnCases_providers(c1.case_id),
fnCases_benefits(c1.case_id),
date_format(cb.open,'%e-%b-%Y'),
c1.disposition,
datediff(curdate(),c1.review_date),
c1.tickler
fr
Trx read view will not see trx with id >= 0 823819, sees < 0 823756
---TRANSACTION 0 823756, ACTIVE 125 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 410
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8379 OS file reads, 3692 OS file writes, 2646 OS fsyncs
2.67 reads/s, 44032 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 527857, node heap has 704 buffer(s)
17.00 hash searches/s, 196.13 non-hash searches/s
---
LOG
---
Log sequence number 1 255787740
Log flushed up to 1 255787740
Last checkpoint at 1 255787740
0 pending log writes, 0 pending chkp writes
836 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3263744
Buffer pool size 16384
Free buffers 5033
Database pages 10647
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10638, created 9, written 2707
9.60 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 994 / 1000
--------------
ROW OPERATIONS
--------------
7 queries inside InnoDB, 0 queries in queue
8 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: waiting for server activity
Number of rows inserted 908, updated 570, deleted 418, read 49703103
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2905.02 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.04 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:35:42 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 43 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1394, signal count 1151
Mutex spin waits 76802, rounds 65657, OS waits 708
RW-shared spins 1050, OS waits 514; RW-excl spins 217, OS waits 139
------------
TRANSACTIONS
------------
Trx id counter 0 823831
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 14
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13046, OS thread id 86038
MySQL thread id 3185, query id 1036681 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823756, ACTIVE 168 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 382
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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
8423 OS file reads, 3697 OS file writes, 2651 OS fsyncs
1.02 reads/s, 25693 avg bytes/read, 0.12 writes/s, 0.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 530627, node heap has 709 buffer(s)
390.08 hash searches/s, 581.99 non-hash searches/s
---
LOG
---
Log sequence number 1 255788682
Log flushed up to 1 255788682
Last checkpoint at 1 255787740
0 pending log writes, 0 pending chkp writes
841 log i/o's done, 0.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3269376
Buffer pool size 16384
Free buffers 4959
Database pages 10716
Modified db pages 10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10707, created 9, written 2707
1.60 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: sleeping
Number of rows inserted 910, updated 572, deleted 418, read 50186074
0.05 inserts/s, 0.05 updates/s, 0.00 deletes/s, 11231.62 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:36:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1396, signal count 1153
Mutex spin waits 76804, rounds 65677, OS waits 709
RW-shared spins 1052, OS waits 515; RW-excl spins 217, OS waits 139
------------
TRANSACTIONS
------------
Trx id counter 0 823835
Purge done for trx's n:o < 0 823760 undo n:o < 0 0
History list length 16
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13046, OS thread id 86038
MySQL thread id 3185, query id 1036758 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
---TRANSACTION 0 823833, COMMITTED IN MEMORY, process no 13358, OS thread id 114713 committing
, undo log entries 3
MySQL thread id 3217, query id 1036754 localhost root
COMMIT
---TRANSACTION 0 823756, ACTIVE 195 sec, process no 11114, OS thread id 45068 starting index read, thread declared inside InnoDB 299
mysql tables in use 5, locked 0
MySQL thread id 3124, query id 977033 localhost root Copying to tmp table
select c1.case_id,
p2.social_sec_no,
p1.middle,
p1.last,
c1.street,
c1.addr2,
trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
p1.plan1_type,
pr.name,
trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),

Trx read view will not see trx with id >= 0 823757, sees < 0 823757
--------
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: 1; buffer pool: 0
8428 OS file reads, 3710 OS file writes, 2658 OS fsyncs
0.19 reads/s, 16384 avg bytes/read, 0.48 writes/s, 0.26 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 531626, node heap has 711 buffer(s)
216.44 hash searches/s, 460.43 non-hash searches/s
---
LOG
---
Log sequence number 1 255793542
Log flushed up to 1 255792757
Last checkpoint at 1 255788682
1 pending log writes, 0 pending chkp writes
844 log i/o's done, 0.11 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3284224
Buffer pool size 16384
Free buffers 4952
Database pages 10721
Modified db pages 15
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10712, created 9, written 2717
0.19 reads/s, 0.00 creates/s, 0.37 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 11043, id 32777, state: flushing log
Number of rows inserted 921, updated 576, deleted 423, read 50229637
0.41 inserts/s, 0.15 updates/s, 0.19 deletes/s, 1613.38 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql> SHOW INNODB STATUS\G;
*************************** 1. row ***************************
Status:
=====================================
060503 10:37:08 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1418, signal count 1168
Mutex spin waits 89068, rounds 70780, OS waits 724
RW-shared spins 1065, OS waits 520; RW-excl spins 224, OS waits 141
------------
TRANSACTIONS
------------
Trx id counter 0 823865
Purge done for trx's n:o < 0 823856 undo n:o < 0 0
History list length 14
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13046, OS thread id 86038
MySQL thread id 3185, query id 1064046 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 12748, OS thread id 53262
MySQL thread id 922, query id 764395 192.168.1.11 root
--------
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: 1
8453 OS file reads, 3766 OS file writes, 2697 OS fsyncs
0.42 reads/s, 16384 avg bytes/read, 0.95 writes/s, 0.66 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 534502, node heap has 717 buffer(s)
4124.57 hash searches/s, 537.80 non-hash searches/s
---
LOG
---
Log sequence number 1 255795999
Log flushed up to 1 255795999
Last checkpoint at 1 255795729
0 pending log writes, 0 pending chkp writes
855 log i/o's done, 0.19 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317355144; in additional pool allocated 3292928
Buffer pool size 16384
Free buffers 4921
Database pages 10746
Modified db pages 3
Pending reads 0
Pending writes: LRU 0, flush list 3, single page 0
Pages read 10737, created 9, written 2757
0.42 reads/s, 0.00 creates/s, 0.68 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. 11043, id 32777, state: flushing buffer pool pages
Number of rows inserted 925, updated 580, deleted 425, read 51197550
0.07 inserts/s, 0.07 updates/s, 0.03 deletes/s, 16405.03 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.02 sec)



Edited 1 time(s). Last edit at 05/03/2006 03:41PM by Lee Krawczyk.