[rt-users] MySQL Performance
ronald higgins
ronald.higgins at gmail.com
Thu Jul 22 05:34:51 EDT 2010
Hi RT Users,
We've started experiencing degraded performance within RT over the
last few days and I am struggling to pin point where exactly the
problem is lying.
I suspect it might be MySQL, i've inserted a "show innodb status"
below if anyone could perhaps take a gander and read something out of
the stats as to where we can look to better tune. Currently loading a
ticket is taking about a minute.
Tasks: 186 total, 5 running, 181 sleeping, 0 stopped, 0 zombie
Cpu(s): 26.6%us, 1.6%sy, 0.0%ni, 70.2%id, 1.6%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 24686080k total, 22107928k used, 2578152k free, 205156k buffers
Swap: 9961464k total, 148k used, 9961316k free, 9817792k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4585 mysql 15 0 11.5g 9.6g 4768 S 465.4 40.8 2443:05 mysqld
710 apache 16 0 369m 97m 4264 R 61.8 0.4 0:33.07 httpd
1621 apache 16 0 351m 86m 4104 R 38.6 0.4 0:04.06 httpd
1614 apache 15 0 358m 87m 4220 R 32.8 0.4 0:05.08 httpd
1667 apache 15 0 367m 101m 4112 R 23.2 0.4 0:02.16 httpd
885 apache 16 0 366m 94m 4224 S 1.9 0.4 0:27.48 httpd
mysql> show innodb status\G;
*************************** 1. row ***************************
Status:
=====================================
100722 11:27:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 126073166, signal count 79431641
--Thread 1185212736 has waited at row0sel.c line 3326 for 0.00 seconds
the semaphore:
S-lock on RW-latch at 0x2aaaaf1770b8 created in file btr0sea.c line 139
a writer (thread id 1181399360) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 773
Last time write locked in file btr0sea.c line 516
--Thread 1186216256 has waited at row0sel.c line 3326 for 0.00 seconds
the semaphore:
S-lock on RW-latch at 0x2aaaaf1770b8 created in file btr0sea.c line 139
a writer (thread id 1181399360) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 773
Last time write locked in file btr0sea.c line 516
--Thread 1193240896 has waited at buf0buf.c line 1125 for 0.00 seconds
the semaphore:
Mutex at 0x2aaaaf176cb8 created file buf0buf.c line 545, lock var 0
waiters flag 0
--Thread 1180997952 has waited at row0sel.c line 3326 for 0.00 seconds
the semaphore:
S-lock on RW-latch at 0x2aaaaf1770b8 created in file btr0sea.c line 139
a writer (thread id 1181399360) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 773
Last time write locked in file btr0sea.c line 516
--Thread 1186015552 has waited at row0sel.c line 3326 for 0.00 seconds
the semaphore:
S-lock on RW-latch at 0x2aaaaf1770b8 created in file btr0sea.c line 139
a writer (thread id 1181399360) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 773
Last time write locked in file btr0sea.c line 516
--Thread 1182603584 has waited at row0sel.c line 3326 for 0.00 seconds
the semaphore:
S-lock on RW-latch at 0x2aaaaf1770b8 created in file btr0sea.c line 139
a writer (thread id 1181399360) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 773
Last time write locked in file btr0sea.c line 516
--Thread 1179793728 has waited at row0sel.c line 3326 for 0.00 seconds
the semaphore:
S-lock on RW-latch at 0x2aaaaf1770b8 created in file btr0sea.c line 139
a writer (thread id 1181399360) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 773
Last time write locked in file btr0sea.c line 516
Mutex spin waits 0, rounds 49935462809, OS waits 70792871
RW-shared spins 52889597, OS waits 16186940; RW-excl spins 71311363,
OS waits 4180724
------------
TRANSACTIONS
------------
Trx id counter 0 1484982159
Purge done for trx's n:o < 0 1484978453 undo n:o < 0 0
History list length 58
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 1484980746, not started, process no 4585, OS thread
id 1181800768
MySQL thread id 1795, query id 13936848 localhost rt_user
---TRANSACTION 0 1484980364, not started, process no 4585, OS thread
id 1180797248
MySQL thread id 1794, query id 13934899 localhost rt_user
---TRANSACTION 0 0, not started, process no 4585, OS thread id 1179392320
MySQL thread id 1793, query id 13924290 localhost rt_user
---TRANSACTION 0 0, not started, process no 4585, OS thread id 1186416960
MySQL thread id 1790, query id 13942372 localhost root
show innodb status
---TRANSACTION 0 1484981576, not started, process no 4585, OS thread
id 1189628224
MySQL thread id 1789, query id 13940210 localhost rt_user
---TRANSACTION 0 1484980447, not started, process no 4585, OS thread
id 1179994432
MySQL thread id 1788, query id 13935065 localhost rt_user
---TRANSACTION 0 1484981472, not started, process no 4585, OS thread
id 1075669312
MySQL thread id 1787, query id 13939769 localhost rt_user
---TRANSACTION 0 1484981852, not started, process no 4585, OS thread
id 1188022592
MySQL thread id 1785, query id 13941857 localhost rt_user
---TRANSACTION 0 1484981921, not started, process no 4585, OS thread
id 1193441600
MySQL thread id 1779, query id 13941993 localhost rt_user
---TRANSACTION 0 1484979078, not started, process no 4585, OS thread
id 1192438080
MySQL thread id 1776, query id 13928137 localhost rt_user
---TRANSACTION 0 1484981694, not started, process no 4585, OS thread
id 1184008512
MySQL thread id 1774, query id 13940541 localhost rt_user
---TRANSACTION 0 1484982158, not started, process no 4585, OS thread
id 1178589504
MySQL thread id 1772, query id 13942372 localhost rt_user
---TRANSACTION 0 1484981756, not started, process no 4585, OS thread
id 1196452160
MySQL thread id 1766, query id 13941056 localhost rt_user
---TRANSACTION 0 1484981858, not started, process no 4585, OS thread
id 1188624704
MySQL thread id 1765, query id 13941967 localhost rt_user
---TRANSACTION 0 1484981890, not started, process no 4585, OS thread
id 1081493824
MySQL thread id 1764, query id 13941947 localhost rt_user
---TRANSACTION 0 1484981842, not started, process no 4585, OS thread
id 1195247936
MySQL thread id 1761, query id 13941412 localhost rt_user
---TRANSACTION 0 1484981513, not started, process no 4585, OS thread
id 1085479232
MySQL thread id 1758, query id 13940137 localhost rt_user
---TRANSACTION 0 1484981840, not started, process no 4585, OS thread
id 1190431040
MySQL thread id 1756, query id 13941375 localhost rt_user
---TRANSACTION 0 1484981502, not started, process no 4585, OS thread
id 1182804288
MySQL thread id 1754, query id 13940193 localhost rt_user
---TRANSACTION 0 1484981683, not started, process no 4585, OS thread
id 1183406400
MySQL thread id 1753, query id 13940485 localhost rt_user
---TRANSACTION 0 1484980753, not started, process no 4585, OS thread
id 1178188096
MySQL thread id 1751, query id 13937210 localhost rt_user
---TRANSACTION 0 1484974938, not started, process no 4585, OS thread
id 1184811328
MySQL thread id 1747, query id 13888992 localhost rt_user
---TRANSACTION 0 1484982144, ACTIVE 3 sec, process no 4585, OS thread
id 1185012032 fetching rows, thread declared inside InnoDB 346
mysql tables in use 1, locked 0
MySQL thread id 1792, query id 13942230 localhost rt_user Sending data
SELECT count(main.id) FROM Tickets main WHERE (main.Status !=
'deleted') AND (main.Owner = '10' AND ( main.Status = 'new' OR
main.Status = 'open' ) ) AND (main.Type = 'ticket') AND
(main.EffectiveId = main.id)
Trx read view will not see trx with id >= 0 1484982145, sees < 0 1484978174
---TRANSACTION 0 1484981804, ACTIVE 7 sec, process no 4585, OS thread
id 1192036672 starting index read, thread declared inside InnoDB 453
mysql tables in use 3, locked 0
MySQL thread id 1780, query id 13941120 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484981805, sees < 0 1484977794
---TRANSACTION 0 1484981429, ACTIVE 12 sec, process no 4585, OS thread
id 1182603584 starting index read, thread declared inside InnoDB 179
mysql tables in use 3, locked 0
MySQL thread id 1796, query id 13938273 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484981430, sees < 0 1484977739
---TRANSACTION 0 1484981175, ACTIVE 18 sec, process no 4585, OS thread
id 1186015552 starting index read, thread declared inside InnoDB 314
mysql tables in use 3, locked 0
MySQL thread id 1752, query id 13936820 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484981176, sees < 0 1484977576
---TRANSACTION 0 1484980796, ACTIVE 22 sec, process no 4585, OS thread
id 1184409920 starting index read, thread declared inside InnoDB 192
mysql tables in use 3, locked 0
MySQL thread id 1775, query id 13935057 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484980797, sees < 0 1484977576
---TRANSACTION 0 1484980703, ACTIVE 24 sec, process no 4585, OS thread
id 1186216256 starting index read, thread declared inside InnoDB 343
mysql tables in use 3, locked 0
MySQL thread id 1746, query id 13934166 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484980704, sees < 0 1484977576
---TRANSACTION 0 1484979990, ACTIVE 31 sec, process no 4585, OS thread
id 1192839488 fetching rows, thread declared inside InnoDB 319
mysql tables in use 3, locked 0
MySQL thread id 1760, query id 13929467 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484979991, sees < 0 1484977327
---TRANSACTION 0 1484979619, ACTIVE 33 sec, process no 4585, OS thread
id 1181399360 fetching rows, thread declared inside InnoDB 341
mysql tables in use 3, locked 0
MySQL thread id 1791, query id 13927577 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484979620, sees < 0 1484977327
---TRANSACTION 0 1484979450, ACTIVE 37 sec, process no 4585, OS thread
id 1180997952 starting index read, thread declared inside InnoDB 183
mysql tables in use 3, locked 0
MySQL thread id 1749, query id 13926215 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484979451, sees < 0 1484977270
---TRANSACTION 0 1484978924, ACTIVE 44 sec, process no 4585, OS thread
id 1185212736 starting index read, thread declared inside InnoDB 349
mysql tables in use 3, locked 0
MySQL thread id 1771, query id 13923999 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484978925, sees < 0 1484977270
---TRANSACTION 0 1484978498, ACTIVE 53 sec, process no 4585, OS thread
id 1193240896 starting index read, thread declared inside InnoDB 330
mysql tables in use 3, locked 0
MySQL thread id 1773, query id 13922701 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484978499, sees < 0 1484977270
---TRANSACTION 0 1484978174, ACTIVE 58 sec, process no 4585, OS thread
id 1179793728 starting index read, thread declared inside InnoDB 103
mysql tables in use 3, locked 0
MySQL thread id 1748, query id 13920916 localhost rt_user Copying to tmp table
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id
Trx read view will not see trx with id >= 0 1484978175, sees < 0 1484976871
--------
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
3530827 OS file reads, 233377 OS file writes, 78230 OS fsyncs
0.74 reads/s, 56524 avg bytes/read, 10.89 writes/s, 3.37 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
1567 inserts, 1567 merged recs, 183 merges
Hash table size 17700857, used cells 14678563, node heap has 46130 buffer(s)
313058.44 hash searches/s, 240583.79 non-hash searches/s
---
LOG
---
Log sequence number 70 816935317
Log flushed up to 70 816935317
Last checkpoint at 70 816913120
0 pending log writes, 0 pending chkp writes
63595 log i/o's done, 2.78 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9556719232; in additional pool allocated 16776960
Buffer pool size 524288
Free buffers 0
Database pages 478158
Modified db pages 60
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12654194, created 37132, written 214243
2.56 reads/s, 1.11 creates/s, 9.70 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
12 queries inside InnoDB, 0 queries in queue
13 read views open inside InnoDB
Main thread process no. 4585, id 1177385280, state: sleeping
Number of rows inserted 115155, updated 79870, deleted 4302, read 30792655080
7.18 inserts/s, 4.22 updates/s, 0.07 deletes/s, 929862.97 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.12 sec)
ERROR:
No query specified
More information about the rt-users
mailing list