Thank you in advance ...

(I have paid support with Confluence and I have also posted my question with them.)

Our installation will run for hours or days without issues, and then CPU usage quickly spikes to nearly 100%, with mysqld taking 90% or more of the CPU. Bouncing Confluence and Mysql fixes the problem.

We are running on 2 virtualized CPUs with 2 GB of free RAM (2 GB of the 4 GB total is taken by Confluence). The host is used for Confluence and its Mysql database only.

$ uname -a
Linux dvprwiki1.den.ofi.com 2.6.18-238.5.1.el5 #1 SMP Mon Feb 21 05:52:39 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9895
Server version: 5.0.77 Source distribution

$ cat /etc/my.cnf
[mysqld]
default-character-set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

max_allowed_packet=32000000
transaction_isolation=READ-COMMITTED
default-storage-engine=INNODB
default-table-type=INNODB

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The following snapshot was taken during very high CPU usage:
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
==================================== 110825 20:16:15 INNODB MONITOR OUTPUT
==================================== Per second averages calculated from the last 5 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 41103624, signal count 20688109
--Thread 1177348416 has waited at mem0pool.c line 335 for 0.00 seconds the semaphore:
Mutex at 0xafaf3d8 created file mem0pool.c line 205, lock var 0
waiters flag 0
Mutex spin waits 0, rounds 15308404368, OS waits 26685967
RW-shared spins 44387784, OS waits 10227732; RW-excl spins 29194377, OS waits 2702826
------------
TRANSACTIONS
------------
Trx id counter 0 1985318
Purge done for trx's n:o < 0 1985271 undo n:o < 0 0
History list length 4
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 1985271, not started, process no 4295, OS thread id 1175484736
MySQL thread id 109756, query id 23573855 localhost 127.0.0.1 confluence
---TRANSACTION 0 1985191, not started, process no 4295, OS thread id 1097603392
MySQL thread id 109754, query id 23572572 localhost 127.0.0.1 confluence
---TRANSACTION 0 1985172, not started, process no 4295, OS thread id 1178413376
MySQL thread id 109752, query id 23572650 localhost 127.0.0.1 confluence
---TRANSACTION 0 0, not started, process no 4295, OS thread id 1092864320
MySQL thread id 109736, query id 23573900 localhost root
SHOW INNODB
STATUS
---TRANSACTION 0 1985124, not started, process no 4295, OS thread id 1092598080
MySQL thread id 109705, query id 23572568 localhost 127.0.0.1 confluence
---TRANSACTION 0 1985281, not started, process no 4295, OS thread id 1174686016
MySQL thread id 109700, query id 23573825 localhost 127.0.0.1 confluence
---TRANSACTION 0 1985269, not started, process no 4295, OS thread id 1176815936
MySQL thread id 109681, query id 23573859 localhost 127.0.0.1 confluence
---TRANSACTION 0 1985309, not started, process no 4295, OS thread id 1093396800
MySQL thread id 109615, query id 23573897 localhost 127.0.0.1 confluence
---TRANSACTION 0 1980073, not started, process no 4295, OS thread id 1179212096
MySQL thread id 107223, query id 23442072 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1945259, not started, process no 4295, OS thread id 1100269888
MySQL thread id 107222, query id 22573280 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1980691, not started, process no 4295, OS thread id 1100003648
MySQL thread id 107221, query id 23459487 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1945238, not started, process no 4295, OS thread id 1093929280
MySQL thread id 107220, query id 22572932 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1945245, not started, process no 4295, OS thread id 1094195520
MySQL thread id 107219, query id 22573051 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1980082, not started, process no 4295, OS thread id 1093130560
MySQL thread id 104880, query id 23442447 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1945257, not started, process no 4295, OS thread id 1176017216
MySQL thread id 104879, query id 22573321 localhost 127.0.0.1 wikiuser
---TRANSACTION 0 1985317, ACTIVE 1 sec, process no 4295, OS thread id 1097070912 fetching rows, thread declared inside InnoDB 298
mysql tables in use 1, locked 0
MySQL thread id 109692, query id 23573899 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='qa balancing' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985305
---TRANSACTION 0 1985316, ACTIVE 2 sec, process no 4295, OS thread id 1177614656, thread declared inside InnoDB 107
mysql tables in use 1, locked 0
MySQL thread id 109738, query id 23573860 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='qa balancing' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985305
---TRANSACTION 0 1985315, ACTIVE 2 sec, process no 4295, OS thread id 1178147136 fetching rows, thread declared inside InnoDB 268
mysql tables in use 1, locked 0
MySQL thread id 109704, query id 23573862 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='qa balancing' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985305
---TRANSACTION 0 1985314, ACTIVE 2 sec, process no 4295, OS thread id 1096804672 fetching rows, thread declared inside InnoDB 142
mysql tables in use 1, locked 0
MySQL thread id 109727, query id 23573861 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='webdevlinks' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985305
---TRANSACTION 0 1985312, ACTIVE 3 sec, process no 4295, OS thread id 1176549696 fetching rows, thread declared inside InnoDB 83
mysql tables in use 1, locked 0
MySQL thread id 109743, query id 23573847 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='autosys' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985305
---TRANSACTION 0 1985311, ACTIVE 3 sec, process no 4295, OS thread id 1177348416 fetching rows, thread declared inside InnoDB 416
mysql tables in use 1, locked 0
MySQL thread id 109761, query id 23573898 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='autosys' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985305
---TRANSACTION 0 1985305, ACTIVE 4 sec, process no 4295, OS thread id 1178945856 fetching rows, thread declared inside InnoDB 74
mysql tables in use 1, locked 0
MySQL thread id 109753, query id 23573845 localhost 127.0.0.1 confluence Sending data
select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=7897090 )and(lower(page0_.TITLE)='qa balancing' )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
Trx read view will not see trx with id >= 0 1985318, sees < 0 1985309
--------
=46ILE 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
38712250 OS file reads, 282997 OS file writes, 138330 OS fsyncs
271.95 reads/s, 26057 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
459002 inserts, 459002 merged recs, 16186 merges
Hash table size 17393, used cells 16562, node heap has 78 buffer(s)
535970.01 hash searches/s, 23988.40 non-hash searches/s
---
LOG
---
Log sequence number 4 4029088111
Log flushed up to 4 4029088111
Last checkpoint at 4 4029088111
0 pending log writes, 0 pending chkp writes
75545 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22687402; in additional pool allocated 1043712
Buffer pool size 512
=46ree buffers 0
Database pages 434
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 107929441, created 172292, written 355132
432.71 reads/s, 0.00 creates/s, 0.00 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. 4295, id 1173887296, state: sleeping
Number of rows inserted 1300756, updated 29090, deleted 7697, read 95396090844
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 561820.64 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
===========================
1 row in set, 1 warning (0.00 sec)

------------------------------------------------------------------------------
This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately by return e-mail and delete all copies. OppenheimerFunds may, at its sole discretion, monitor, review, retain and/or disclose the content of all email communications.
=============================================================================

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com