MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Gunnar R. Date:January 3 2008 9:38pm
Subject:Re: Performance problem - MySQL at 99.9% CPU
View as plain text  
Hi,

Thanks.

mysql> show processlist;
+--------+---------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id     | User          | Host      | db        | Command | Time | State 
              | Info                                                      
                                          |
+--------+---------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 698938 | vpopmail_edit | localhost | vpopmail  | Sleep   | 4068 |       
              | NULL                                                      
                                          |
| 704841 | vpopmail_edit | localhost | vpopmail  | Sleep   |  723 |       
              | NULL                                                      
                                          |
| 704995 | vpopmail_edit | localhost | vpopmail  | Sleep   |  648 |       
              | NULL                                                      
                                          |
| 705040 | vpopmail_edit | localhost | vpopmail  | Sleep   |  627 |       
              | NULL                                                      
                                          |
| 705145 | root          | localhost | NULL      | Query   |    0 | NULL  
              | show processlist                                          
                                          |
| 706248 | vpopmail_edit | localhost | vpopmail  | Sleep   |   51 |       
              | NULL                                                      
                                          |
| 706291 | vpopmail_edit | localhost | vpopmail  | Sleep   |   26 |       
              | NULL                                                      
                                          |
| 706292 | bmwguiden     | localhost | bmwguiden | Query   |    0 |
Sending data         | SELECT u.username, u.user_id, u.user_posts,
u.user_from, u.user_interests, u.user_website, u.user_em |
| 706293 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | SELECT u.username, u.user_id, u.user_posts, u.user_from,
u.user_interests, u.user_website, u.user_em |
| 706295 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706296 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706297 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706298 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706303 | bmwguiden     | localhost | bmwguiden | Query   |   12 |
Sending data         | SELECT t.*, u.username, u.user_id, u2.username as
user2, u2.user_id as id2, p.post_username, p2.post |
| 706306 | bmwguiden     | localhost | bmwguiden | Query   |    0 |
Sorting result       | SELECT t.*, u.username, u.user_id, u2.username as
user2, u2.user_id as id2, p.post_username, p2.post |
| 706309 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706310 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706311 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | DELETE FROM phpbb_sessions
                                                WHERE session_time <
1199391883
                                                        AND session_id <>
'f378eae |
| 706312 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706313 | bmwguiden     | localhost | bmwguiden | Query   |    0 |
Sending data         | SELECT t.forum_id, t.topic_id, p.post_time
                        FROM phpbb_topics t, phpbb_posts p
                        WHERE p.post_i |
| 706317 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706320 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706321 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_sessions
                SET session_user_id = 4134, session_start = 1199395894,
session_time = 11993 |
| 706322 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | DELETE FROM phpbb_sessions
                                                WHERE session_time <
1199391881
                                                        AND session_id <>
'd9e73e0 |
| 706323 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706325 | bmwguiden     | localhost | bmwguiden | Query   |    0 |
Copying to tmp table | SELECT t.*, f.forum_id, f.forum_name, u.username,
u.user_id, u2.username as user2, u2.user_id as id2 |
| 706324 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706326 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706327 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706328 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_users
                                                        SET
user_session_time
= 1199395888,
user_session_page
= 6
                                                        WHERE u |
| 706329 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706330 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_users
                                                        SET
user_session_time
= 1199395889,
user_session_page
= 6
                                                        WHERE u |
| 706331 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_users
                                                        SET
user_session_time
= 1199395890,
user_session_page
= 9
                                                        WHERE u |
| 706332 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706333 | bmwguiden     | localhost | bmwguiden | Query   |    0 |
Sending data         | SELECT u.username, u.user_id, u.user_posts,
u.user_from, u.user_interests, u.user_website, u.user_em |
| 706334 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_sessions
                                                SET session_time =
1199395891, session_page =
14
                                                WHERE session_i |
| 706335 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706336 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_sessions
                                                SET session_time =
1199395892, session_page =
1
                                                WHERE session_id |
| 706337 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_sessions
                                                SET session_time =
1199395892, session_page =
1
                                                WHERE session_id |
| 706338 | bmwguiden     | localhost | bmwguiden | Query   |    0 | Locked
              | UPDATE phpbb_sessions
                                                SET session_time =
1199395892, session_page =
6
                                                WHERE session_id |
| 706339 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
| 706340 | bmwguiden     | localhost | bmwguiden | Sleep   |    0 |       
              | NULL                                                      
                                          |
+--------+---------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
42 rows in set (0.04 sec)

mysql> show innodb status;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Status                                                                  
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================
080103 22:31:48 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 4732928
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2410, OS thread id 2675956656
MySQL thread id 705145, query id 10184031 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
26 OS file reads, 3 OS file writes, 3 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 242442355
Log flushed up to   0 242442355
Last checkpoint at  0 242442355
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17395088; in additional pool allocated 864256
Buffer pool size   512
Free buffers       492
Database pages     20
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 20, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2410, id 2979703728, state: waiting for server
activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 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 (0.01 sec)

mysql>

-----------------------

[root@xxx ~]# ps aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0   1744   580 ?        Ss    2007   0:05 init [3]
root         2  0.0  0.0      0     0 ?        S     2007   0:00
[migration/0]
root         3  0.0  0.0      0     0 ?        SN    2007   0:00
[ksoftirqd/0]
root         4  0.0  0.0      0     0 ?        S     2007   0:00 [watchdog/0]
root         5  0.0  0.0      0     0 ?        S     2007   0:05
[migration/1]
root         6  0.0  0.0      0     0 ?        SN    2007   0:00
[ksoftirqd/1]
root         7  0.0  0.0      0     0 ?        S     2007   0:00 [watchdog/1]
root         8  0.0  0.0      0     0 ?        S<    2007   0:00 [events/0]
root         9  0.0  0.0      0     0 ?        S<    2007   0:00 [events/1]
root        10  0.0  0.0      0     0 ?        S<    2007   0:00 [khelper]
root        11  0.0  0.0      0     0 ?        S<    2007   0:00 [kthread]
root        18  0.0  0.0      0     0 ?        S<    2007   0:05 [kblockd/0]
root        19  0.0  0.0      0     0 ?        S<    2007   0:01 [kblockd/1]
root        20  0.0  0.0      0     0 ?        S<    2007   0:00 [kacpid]
root       229  0.0  0.0      0     0 ?        S<    2007   0:00 [khubd]
root       231  0.0  0.0      0     0 ?        S<    2007   0:00 [kseriod]
root       294  0.0  0.0      0     0 ?        S     2007   0:04 [pdflush]
root       295  0.0  0.0      0     0 ?        S     2007   0:08 [pdflush]
root       296  0.0  0.0      0     0 ?        S     2007   1:12 [kswapd0]
root       297  0.0  0.0      0     0 ?        S<    2007   0:00 [aio/0]
root       298  0.0  0.0      0     0 ?        S<    2007   0:00 [aio/1]
root       460  0.0  0.0      0     0 ?        S<    2007   0:00 [kpsmoused]
root       485  0.0  0.0      0     0 ?        S<    2007   0:00 [scsi_eh_0]
root       545  0.0  0.0      0     0 ?        S<    2007   0:00 [kmirrord]
root       559  0.0  0.0      0     0 ?        S<    2007   9:03 [kjournald]
root       745  0.0  0.0   1644   428 ?        S<s   2007   0:00 udevd -d
root      1203  0.0  0.0      0     0 ?        S<    2007   0:00 [kedac]
root      1229  0.0  0.0      0     0 ?        S     2007   0:00 [khpsbpkt]
root      1236  0.0  0.0      0     0 ?        S     2007   0:00
[knodemgrd_0]
root      1240  0.0  0.0      0     0 ?        S<    2007   0:00 [kauditd]
root      1701  0.0  0.0      0     0 ?        S<    2007   0:00 [kjournald]
root      1704  0.0  0.0      0     0 ?        S<    2007   0:38 [kjournald]
root      2002  0.0  0.0   1612   560 ?        Ss    2007   0:24 syslogd -m 0
root      2004  0.0  0.0   1564   396 ?        Ss    2007   0:00 klogd -x
named     2022  0.2  0.7  52416  7744 ?        Ssl   2007  22:57
/usr/sbin/named -u named -t /var/named/chroot
bin       2069  0.0  0.0   1688   616 ?        Ss    2007   0:00 portmap
rpcuser   2086  0.0  0.0   1736   720 ?        Ss    2007   0:00 rpc.statd
root      2094  0.0  0.0  12984   720 ?        S<sl  2007   0:01 auditd
root      2122  0.0  0.0   4380   380 ?        Ss    2007   0:00 rpc.idmapd
root      2229  0.0  0.0   1828   700 ?        Ss    2007   0:00
/usr/sbin/automount --timeout=60 /net program /etc/auto.net
root      2237  0.0  0.0   2736   432 ?        Ss    2007   0:04 nifd -n
nobody    2269  0.0  0.0  13436   956 ?        Ssl   2007   0:00
mDNSResponder
root      2277  0.0  0.0   1556   460 ?        Ss    2007   0:00
/usr/sbin/acpid
root      2328  0.0  0.1   4404  1060 ?        Ss    2007   0:23
/usr/sbin/sshd
root      2356  0.0  0.1   4376  1248 ?        S     2007   0:00 /bin/sh
/usr/bin/mysqld_safe --datadir=/var/lib/mysql
--socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log
--pid-file=
mysql     2410 46.4 28.7 460360 297732 ?       Sl    2007 4232:17
/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/run/mysqld/mysqld.pid --skip-external-lock
root      2456  0.0  0.0   1664   516 ?        S     2007   0:01
/usr/local/courier-imap/libexec/couriertcpd -address=0
-stderrlogger=/usr/local/courier-imap/sbin/courierlogger
-stderrloggername=im
root      2458  0.0  0.0   1548   460 ?        S     2007   0:01
/usr/local/courier-imap/sbin/courierlogger imapd
root      2465  0.0  0.0   1660   504 ?        S     2007   0:00
/usr/local/courier-imap/libexec/couriertcpd -address=0
-stderrlogger=/usr/local/courier-imap/sbin/courierlogger
-stderrloggername=im
root      2467  0.0  0.0   1420   312 ?        S     2007   0:00
/usr/local/courier-imap/sbin/courierlogger imapd-ssl
root      2473  0.0  0.0   1664   504 ?        S     2007   0:00
/usr/local/courier-imap/libexec/couriertcpd -address=0
-stderrlogger=/usr/local/courier-imap/sbin/courierlogger
-stderrloggername=po
root      2475  0.0  0.0   1420   312 ?        S     2007   0:00
/usr/local/courier-imap/sbin/courierlogger pop3d-ssl
nobody    2481  0.0  0.1   5136  1408 ?        Ss    2007   0:01 proftpd:
(accepting connections)
nobody    2489  0.0  3.1  37736 33040 ?        Ss    2007   0:03
/usr/bin/spamd -d -a -m5 -H -u nobody -x -q
root      2497  0.0  1.3  24196 14100 ?        Ss    2007   0:11
/usr/sbin/httpd
root      2504  0.0  0.0   4568   952 ?        Ss    2007   0:00 crond
xfs       2547  0.0  0.1   2932  1168 ?        Ss    2007   0:00 xfs
-droppriv -daemon
root      2561  0.0  0.0   1808   432 ?        Ss    2007   0:00
/usr/sbin/atd
root      2612  0.0  0.0   1580   352 ?        S     2007   0:00 svscan
root      2620  0.0  0.0   1416   304 ?        S     2007   0:00 supervise
qmail-smtpd
root      2621  0.0  0.0   1412   300 ?        S     2007   0:00 supervise
log
root      2622  0.0  0.0   1416   304 ?        S     2007   0:00 supervise
qmail-pop3d
root      2623  0.0  0.0   1416   304 ?        S     2007   0:00 supervise
log
root      2624  0.0  0.0   1412   300 ?        S     2007   0:00 supervise
qmail-send
root      2625  0.0  0.0   1416   304 ?        S     2007   0:00 supervise
log
root      2626  0.0  0.1   5516  1144 ?        S     2007   0:36
/usr/local/bin/tcpserver -H -R -v -c100 0 pop3 /var/qmail/bin/qmail-popup
xxx /home/vpopmail/bin/vchkpw /var/qmail/bin/q
qmails    2627  0.0  0.0   1588   432 ?        S     2007   0:19 qmail-send
qmaild    2628  0.0  0.1   5516  1160 ?        S     2007   0:04
/usr/local/bin/tcpserver -p -R -x /home/vpopmail/etc/tcp.smtp.cdb -u 504
-g 504 -v -c100 0 smtp rblsmtpd -r relays.ordb.org:Your mes
qmaill    2629  0.0  0.0   1556   368 ?        S     2007   0:04
/usr/local/bin/multilog t s2500000 n10 /var/log/qmail/qmail-send
qmaill    2630  0.0  0.0   1560   368 ?        S     2007   0:02
/usr/local/bin/multilog t s2500000 n10 /var/log/qmail/qmail-smtpd
qmaill    2631  0.0  0.0   1556   368 ?        S     2007   0:21
/usr/local/bin/multilog t s2500000 n10 /var/log/qmail/qmail-pop3d
root      2634  0.0  0.0   1564   348 ?        S     2007   0:02
qmail-lspawn ./Maildir/
qmailr    2635  0.0  0.1   2748  1548 ?        S     2007   0:04 qmail-rspawn
qmailq    2636  0.0  0.0   1548   348 ?        S     2007   0:02 qmail-clean
nobody    2651  0.0  0.1  35068  1168 ?        Sl    2007   0:00
/usr/sbin/in.imapproxyd
root      2658  0.0  0.0   1548   424 tty1     Ss+   2007   0:00
/sbin/mingetty tty1
root      2659  0.0  0.0   1552   428 tty2     Ss+   2007   0:00
/sbin/mingetty tty2
root      2660  0.0  0.0   1552   432 tty3     Ss+   2007   0:00
/sbin/mingetty tty3
root      2661  0.0  0.0   1548   428 tty4     Ss+   2007   0:00
/sbin/mingetty tty4
root      2662  0.0  0.0   1548   432 tty5     Ss+   2007   0:00
/sbin/mingetty tty5
root      2663  0.0  0.0   1552   432 tty6     Ss+   2007   0:00
/sbin/mingetty tty6
root      5944  0.0  0.1   8256  1884 ?        SNs   2007   0:00 cupsd
root     17752  0.0  0.1   5096  1080 ?        Ss    2007   0:02 SCREEN -S
bck
root     17753  0.0  0.1   4736  1508 pts/1    Ss+   2007   0:00 /bin/bash
vpopmail 30648  0.0  0.1   2316  1308 ?        S    21:23   0:04
/usr/local/courier-imap/bin/imapd Maildir
root     30975  0.0  0.2   7436  2348 ?        Ss   21:29   0:00 sshd:
root@pts/0
root     30981  0.0  0.1   4736  1528 pts/0    Ss   21:29   0:00 -bash
qmaild     683  0.0  0.0   1560   344 ?        S    22:00   0:00
/var/qmail/bin/qmail-smtpd
qmaild    2318  0.0  0.0   1560   340 ?        S    22:18   0:00
/var/qmail/bin/qmail-smtpd
vpopmail  2409  2.2  0.1   2236  1264 ?        S    22:19   0:20
/usr/local/courier-imap/bin/imapd Maildir
vpopmail  2509  0.0  0.1   2200  1120 ?        S    22:20   0:00
/usr/local/courier-imap/bin/imapd Maildir
apache    2891  0.9  0.0      0     0 ?        Z    22:23   0:06 [httpd]
<defunct>
apache    2895  1.1  1.4  30180 15216 ?        S    22:23   0:07
/usr/sbin/httpd
apache    3093  1.0  1.5  30628 15628 ?        S    22:26   0:05
/usr/sbin/httpd
apache    3144  0.8  1.5  28372 15572 ?        S    22:26   0:04
/usr/sbin/httpd
apache    3214  0.6  1.3  27008 14076 ?        S    22:27   0:02
/usr/sbin/httpd
apache    3383  0.8  1.3  26484 13596 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3388  0.7  1.2  26152 13288 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3411  0.8  1.3  30152 14412 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3413  0.7  1.2  26240 13348 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3424  0.9  1.2  26256 13372 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3430  0.6  1.2  26284 13356 ?        S    22:29   0:01
/usr/sbin/httpd
apache    3434  0.9  1.3  29292 14276 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3436  0.8  1.3  26836 13928 ?        S    22:29   0:02
/usr/sbin/httpd
apache    3438  0.8  1.4  27956 15052 ?        S    22:29   0:02
/usr/sbin/httpd
vpopmail  3517  0.0  0.0   1936   788 ?        S    22:30   0:00
/usr/local/courier-imap/bin/imapd Maildir
apache    3524  0.7  1.3  26856 13988 ?        S    22:30   0:01
/usr/sbin/httpd
apache    3533  1.0  1.2  26204 13336 ?        S    22:30   0:02
/usr/sbin/httpd
apache    3537  0.7  1.3  26364 13524 ?        S    22:30   0:01
/usr/sbin/httpd
apache    3538  0.5  1.2  26212 13380 ?        S    22:30   0:01
/usr/sbin/httpd
apache    3545  0.6  1.4  30712 14912 ?        S    22:30   0:01
/usr/sbin/httpd
apache    3548  1.0  1.2  26280 13412 ?        S    22:30   0:02
/usr/sbin/httpd
apache    3551  0.6  1.2  26092 13196 ?        S    22:30   0:01
/usr/sbin/httpd
apache    3552  0.8  1.2  26292 13408 ?        S    22:30   0:01
/usr/sbin/httpd
apache    3553  0.8  1.2  26276 13444 ?        S    22:30   0:01
/usr/sbin/httpd
vpopmail  3574  0.0  0.1   2328  1260 ?        S    22:31   0:00
/usr/local/courier-imap/bin/imapd Maildir
apache    3601  0.8  1.3  26468 13556 ?        S    22:31   0:01
/usr/sbin/httpd
apache    3602  0.7  1.2  26244 13384 ?        S    22:31   0:01
/usr/sbin/httpd
apache    3607  1.1  1.2  26268 13424 ?        S    22:31   0:02
/usr/sbin/httpd
apache    3608  0.7  1.2  26264 13416 ?        S    22:31   0:01
/usr/sbin/httpd
qmaild    3658  0.0  0.0   5320   944 ?        S    22:32   0:00 rblsmtpd
-r relays.ordb.org
apache    3722  1.1  1.2  26252 13312 ?        S    22:32   0:01
/usr/sbin/httpd
apache    3726  0.6  1.2  26600 12976 ?        S    22:32   0:00
/usr/sbin/httpd
apache    3727  1.0  1.3  26900 13960 ?        S    22:32   0:01
/usr/sbin/httpd
apache    3733  0.7  1.2  26196 13304 ?        S    22:32   0:00
/usr/sbin/httpd
apache    3734  0.5  1.2  26520 12884 ?        S    22:32   0:00
/usr/sbin/httpd
apache    3735  0.8  1.2  26680 13040 ?        S    22:32   0:00
/usr/sbin/httpd
apache    3736  0.9  1.3  26468 13568 ?        S    22:32   0:01
/usr/sbin/httpd
qmaild    3740  0.0  0.0   5320   940 ?        S    22:32   0:00 rblsmtpd
-r relays.ordb.org
apache    3745  0.6  1.3  28636 13576 ?        S    22:32   0:00
/usr/sbin/httpd
apache    3748  0.9  1.2  26280 13360 ?        S    22:32   0:00
/usr/sbin/httpd
apache    3759  0.5  1.2  26104 13236 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3766  0.2  1.1  25668 11880 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3768  1.0  1.2  26200 12568 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3769  0.8  1.3  26620 13736 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3775  0.4  1.2  26152 13216 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3783  0.7  1.3  26388 13460 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3785  0.4  1.1  25520 11744 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3788  0.8  1.2  26084 13100 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3789  0.8  1.1  26056 12400 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3790  0.5  1.2  26164 12412 ?        S    22:33   0:00
/usr/sbin/httpd
apache    3791  0.6  1.2  26396 12704 ?        S    22:33   0:00
/usr/sbin/httpd
root      3836  0.0  0.0   4664   872 pts/0    R+   22:34   0:00 ps aux
[root@xxx ~]#

-------------

I don't really know what to look for when it comes to the show-commands in
MySQL, except that it seems I can now be sure that it's the phpBB board
that's causing the problems. Not a lot of other processes, and they're
sleeping.

Anyone wanna explain what else to look for?

Cheers,

Gunnar R.

On ons, januar 2, 2008, 07:10, Ady Wicaksono wrote:
> Hi, please monitor what happened with mysql
>
> show processlist
> show innodb status
>
> and also ps aux
>
> because maybe some application makes your mysql busy
>
> On Jan 2, 2008 7:31 AM, Gunnar R. <gr@stripped> wrote:
>
>> Hello,
>>
>> I am running a community site mainly based on phpBB. It has about 9.300
>> registered users, 650.000 posts and about 200.000 visitors/month (12
>> mill
>> "hits"). The SQL database is about 700MB.
>>
>> It's all running on a couple of years old Dell box with two P4 Xeon
>> 1.7Ghz
>> CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache.
>>
>> The last year the server has been having huge performance problems, and
>> MySQL (5.0.45) seems to be the problem. It's almost constantly running
>> at
>> 99.9% CPU ("measured" using 'top').
>>
>> I know the hardware isn't too hot, but either way I am a bit confused by
>> the
>> fact that I can't seem to get MySQL to run smoothly. Is this just too
>> big
>> a
>> database for this kind of box, or could this be a configuration issue?
>>
>> I am thinking about buying a new dual core box (with IDE disks?), but I
>> have
>> to make sure this really is a hardware issue before I spend thousands of
>> bucks.
>>
>> Any help will be hugely appreciated!
>>
>> Cheers,
>>
>> Gunnar
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>
>
> --
> Regards,
>
> Ady Wicaksono
> Email:
> ady.wicaksono at gmail.com
> http://adywicaksono.wordpress.com/
>


Thread
Performance problem - MySQL at 99.9% CPUGunnar R.2 Jan
  • Re: Performance problem - MySQL at 99.9% CPUBaron Schwartz2 Jan
  • Re: Performance problem - MySQL at 99.9% CPUAdy Wicaksono2 Jan
    • Re: Performance problem - MySQL at 99.9% CPUGunnar R.3 Jan
  • Re: Performance problem - MySQL at 99.9% CPUPer Jessen2 Jan
    • RE: Performance problem - MySQL at 99.9% CPUEric Frazier2 Jan
  • RE: Performance problem - MySQL at 99.9% CPUAndrew Braithwaite2 Jan
    • RE: Performance problem - MySQL at 99.9% CPUGunnar R.3 Jan
      • Re: Performance problem - MySQL at 99.9% CPUErik Giberti4 Jan
        • Re: Performance problem - MySQL at 99.9% CPUGunnar R.8 Jan
        • Re: Performance problem - MySQL at 99.9% CPUGunnar R.8 Jan
          • Re: Performance problem - MySQL at 99.9% CPUKevin Hunter8 Jan
          • Re: Performance problem - MySQL at 99.9% CPUErik Giberti9 Jan
Re: Performance problem - MySQL at 99.9% CPUKevin Hunter9 Jan