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 will be hugely appreciated!

Cheers,

Gunnar

Hi,

On Jan 1, 2008 6:31 PM, Gunnar R. <gr@eternia.no> 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?

It might an application problem, not MySQL. You could have some
config problems too, but it's hard to give advice on that. Just as a
very basic first step, are you using a default configuration for
MySQL, or have you customized it? Have you watched the processlist to
see what's going on? Is the query cache enabled?

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@eternia.no> 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 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=a...sono@gmail.com
>
>


--
Regards,

Ady Wicaksono
Email:
ady.wicaksono at gmail.com
http://adywicaksono.wordpress.com/

Gunnar R. wrote:

> 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.

I think you've got an application problem somewhere which you should
look into first. Hardware-wise I think you're doing fine, except you
could probably increase overall performance with more memory. MySQL is
pretty good at query-caching.


/Per Jessen, Zürich

-----Original Message-----
From: Per Jessen [mailto:per@computer.org]
Sent: Wednesday, January 02, 2008 7:51 AM
To: mysql@lists.mysql.com
Subject: Re: Performance problem - MySQL at 99.9% CPU

Gunnar R. wrote:

> 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.

I think you've got an application problem somewhere which you should look
into first. Hardware-wise I think you're doing fine, except you could
probably increase overall performance with more memory. MySQL is pretty
good at query-caching.

Just for general info I tested Heap tables vs the query cache, query cache
one and it makes a lot of sense why once I saw that. Even in-memory tables
can't be as fast(giving queries in the cache) because of the cost of parsing
and optimization of the query. The query cache being basicly a fast in
memory hash lookup. However, if you have a system that doesn't have a lot of
repetative queries, the Heap table would win again that just makes sense,
but my little test proved the query cache is pretty good for most things.

Hi,

If you can follow this document:

http://www.ufsdump.org/papers/uuasc-june-2006.pdf

You should be able to figure out what's happening.

Cheers,

Andrew

-----Original Message-----
From: Gunnar R. [mailto:gr@eternia.no]
Sent: Tue, 01 January 2008 23:31
To: mysql@lists.mysql.com
Subject: Performance problem - MySQL at 99.9% CPU

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 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=a...e@lovefilm.com



LOVEFiLM International Limited is a company registered in England and Wales.. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom.

This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately.

This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com

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 < 0 0 undo n < 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 [ker]
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@eternia.no> 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 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=a...sono@gmail.com
>>
>>
>
>
> --
> Regards,
>
> Ady Wicaksono
> Email:
> ady.wicaksono at gmail.com
> http://adywicaksono.wordpress.com/
>

Hello,

Thanks. I read the document, but unfortunately it didn't tell me anything
new..

One of the things I am a bit confused about is:

top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84
Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si
Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1034280k total, 942780k used, 91500k free, 34252k buffers
Swap: 2031608k total, 104k used, 2031504k free, 278788k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld

How come the CPUs can have idle time even though mysqld is running at
99.9%, AND there's a processor queue (4.36)?

Cheers,

Gunnar R.

On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote:
> Hi,
>
> If you can follow this document:
>
> http://www.ufsdump.org/papers/uuasc-june-2006.pdf
>
> You should be able to figure out what's happening.
>
> Cheers,
>
> Andrew
>
> -----Original Message-----
> From: Gunnar R. [mailto:gr@eternia.no]
> Sent: Tue, 01 January 2008 23:31
> To: mysql@lists.mysql.com
> Subject: Performance problem - MySQL at 99.9% CPU
>
> 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 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=a...e@lovefilm.com
>
>
>
> LOVEFiLM International Limited is a company registered in England and
> Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way,
> London W3 6RU, United Kingdom.
>
> This e-mail is confidential to the ordinary user of the e-mail address to
> which it was addressed. If you have received it in error, please delete it
> from your system and notify the sender immediately.
>
> This message has been scanned for viruses by BlackSpider MailControl -
> www.blackspider.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gr@eternia.no
>
>

Gunnar,

us = user (things like MySQL/PHP/Apache)
sy = system (memory management / swap space / threading / kernel
processes and so on)
ni = nice (apps running only when nothing else needs the resource)
id = idle (extra cpu cycles being wasted)
wa = wait state (io wait for disk/network/memory)
hi & si - interrupts

Generally acceptable load should be < #processors (so in your case 2
is okay - machine is performing well - 4 somethings being over
utilized somewhere)
Also in top 100% = 100% of one processor, so in a dual processor (or
core) setup, you can actually go to 200%

Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59%
overall) of system resources.
Your system processes are taking up 66.2% (of 200% or 33% overall)
and it's leaving about 14% (of 200% - so 7% overall) of the system idle.
The remainders are I/O waits etc (your numbers look pretty good there,
but IO wait can spike and so may be misleading without using other
tools.

You may be encountering a thrashing problem with the amount of memory
left or any number of things, but I would look at memory use on this
box, because your load is pretty high and your performance is
suffering if it's staying there. Your memory is at about 92% utilized
too... while 91Mb seems like a lot of memory - it's easily consumed by
a couple of large queries, sorts and so on which then goes right to
disk swapping for virtual memory - never good for performance. It
might also be impacted by IO and you just can't see it in the one
slice of top we have here. If that number spikes up to 5% and then
falls back down - it might be time spent going to disk with temp
tables etc.

Also turn on slow query logging (yes, I know it's another performance
hit) and see if there is one query that's particularly problematic,
perhaps optimizing the indexes etc on the table might with the
performance.

Also, make sure your HD's aren't full... that will kill performance
very quickly if the needed disk space isn't there.

Erik


On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote:

> Hello,
>
> Thanks. I read the document, but unfortunately it didn't tell me
> anything
> new..
>
> One of the things I am a bit confused about is:
>
> top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30,
> 2.84
> Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
> Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi,
> 0.7% si
> Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi,
> 0.0% si
> Mem: 1034280k total, 942780k used, 91500k free, 34252k
> buffers
> Swap: 2031608k total, 104k used, 2031504k free, 278788k
> cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld
>
> How come the CPUs can have idle time even though mysqld is running at
> 99.9%, AND there's a processor queue (4.36)?
>
> Cheers,
>
> Gunnar R.
>
> On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote:
>> Hi,
>>
>> If you can follow this document:
>>
>> http://www.ufsdump.org/papers/uuasc-june-2006.pdf
>>
>> You should be able to figure out what's happening.
>>
>> Cheers,
>>
>> Andrew
>>
>> -----Original Message-----
>> From: Gunnar R. [mailto:gr@eternia.no]
>> Sent: Tue, 01 January 2008 23:31
>> To: mysql@lists.mysql.com
>> Subject: Performance problem - MySQL at 99.9% CPU
>>
>> 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 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=a...e@lovefilm.com
>>
>>
>>
>> LOVEFiLM International Limited is a company registered in England and
>> Wales. Registered Number: 04392195. Registered Office: No.9, 6
>> Portal Way,
>> London W3 6RU, United Kingdom.
>>
>> This e-mail is confidential to the ordinary user of the e-mail
>> address to
>> which it was addressed. If you have received it in error, please
>> delete it
>> from your system and notify the sender immediately.
>>
>> This message has been scanned for viruses by BlackSpider
>> MailControl -
>> www.blackspider.com
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=gr@eternia.no
>>
>>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=giberti@mac.com
>

Thank you Erik!

HDs are OK, a couple of GB free. Not that it's a lot, but I can't imagine
it being too low for MySQL..

I'm aware memory is a bit low, but RAMBUS chips are hard to come by. They
don't have them in stock anywhere anymore. Also they are quite expensive.
It's almost like you could've bought 1/3rd of a new cheap Dell server for
2x512MB RAMBUS. But if a new box for a reasonable price wouldn't be any
faster it's no use anyway...

Concerning slow queries, it seems there's a couple of different queries
that's being logged.

This is one, taking 66 seconds:

# Query_time: 66 Lock_time: 0 Rows_sent: 0 Rows_examined: 15857680
SELECT word_id
FROM phpbb_search_wordmatch
GROUP BY word_id
HAVING COUNT(word_id) > 263916;

Usual time for this seems to be from 12 to 66 seconds.

And then there's this, usually taking 10-20 seconds:

# Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 395960
SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as
id2, p.post_username, p2.post_username AS post_username2, p2.post_time,
f.forum_name
FROM phpbb_topics t, phpbb_users u,
phpbb_posts p, phpbb_posts p2, phpbb_users
u2, phpbb_forums f
WHERE t.topic_poster = u.user_id
AND t.forum_id NOT IN (16, 17)
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND t.forum_id = f.forum_id
AND u2.user_id = p2.poster_id




AND t.topic_status <> 1
AND t.topic_status <> 2

ORDER BY t.topic_last_post_id DESC
LIMIT 10;


In the evenings there seems to be 10-20 slow queries every hour, time
between them varying from seconds to usually 5-10 minutes.

Cheers,

Gunnar


On fre, januar 4, 2008, 05:55, Erik Giberti wrote:
> Gunnar,
>
> us = user (things like MySQL/PHP/Apache)
> sy = system (memory management / swap space / threading / kernel
> processes and so on)
> ni = nice (apps running only when nothing else needs the resource)
> id = idle (extra cpu cycles being wasted)
> wa = wait state (io wait for disk/network/memory)
> hi & si - interrupts
>
> Generally acceptable load should be < #processors (so in your case 2
> is okay - machine is performing well - 4 somethings being over
> utilized somewhere)
> Also in top 100% = 100% of one processor, so in a dual processor (or
> core) setup, you can actually go to 200%
>
> Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59%
> overall) of system resources.
> Your system processes are taking up 66.2% (of 200% or 33% overall)
> and it's leaving about 14% (of 200% - so 7% overall) of the system idle.
> The remainders are I/O waits etc (your numbers look pretty good there,
> but IO wait can spike and so may be misleading without using other
> tools.
>
> You may be encountering a thrashing problem with the amount of memory
> left or any number of things, but I would look at memory use on this
> box, because your load is pretty high and your performance is
> suffering if it's staying there. Your memory is at about 92% utilized
> too... while 91Mb seems like a lot of memory - it's easily consumed by
> a couple of large queries, sorts and so on which then goes right to
> disk swapping for virtual memory - never good for performance. It
> might also be impacted by IO and you just can't see it in the one
> slice of top we have here. If that number spikes up to 5% and then
> falls back down - it might be time spent going to disk with temp
> tables etc.
>
> Also turn on slow query logging (yes, I know it's another performance
> hit) and see if there is one query that's particularly problematic,
> perhaps optimizing the indexes etc on the table might with the
> performance.
>
> Also, make sure your HD's aren't full... that will kill performance
> very quickly if the needed disk space isn't there.
>
> Erik
>
>
> On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote:
>
>> Hello,
>>
>> Thanks. I read the document, but unfortunately it didn't tell me
>> anything
>> new..
>>
>> One of the things I am a bit confused about is:
>>
>> top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30,
>> 2.84
>> Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
>> Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi,
>> 0.7% si
>> Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi,
>> 0.0% si
>> Mem: 1034280k total, 942780k used, 91500k free, 34252k
>> buffers
>> Swap: 2031608k total, 104k used, 2031504k free, 278788k
>> cached
>>
>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld
>>
>> How come the CPUs can have idle time even though mysqld is running at
>> 99.9%, AND there's a processor queue (4.36)?
>>
>> Cheers,
>>
>> Gunnar R.
>>
>> On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote:
>>> Hi,
>>>
>>> If you can follow this document:
>>>
>>> http://www.ufsdump.org/papers/uuasc-june-2006.pdf
>>>
>>> You should be able to figure out what's happening.
>>>
>>> Cheers,
>>>
>>> Andrew
>>>
>>> -----Original Message-----
>>> From: Gunnar R. [mailto:gr@eternia.no]
>>> Sent: Tue, 01 January 2008 23:31
>>> To: mysql@lists.mysql.com
>>> Subject: Performance problem - MySQL at 99.9% CPU
>>>
>>> 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 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=a...e@lovefilm.com
>>>
>>>
>>>
>>> LOVEFiLM International Limited is a company registered in England and
>>> Wales. Registered Number: 04392195. Registered Office: No.9, 6
>>> Portal Way,
>>> London W3 6RU, United Kingdom.
>>>
>>> This e-mail is confidential to the ordinary user of the e-mail
>>> address to
>>> which it was addressed. If you have received it in error, please
>>> delete it
>>> from your system and notify the sender immediately.
>>>
>>> This message has been scanned for viruses by BlackSpider
>>> MailControl -
>>> www.blackspider.com
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=gr@eternia.no
>>>
>>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=giberti@mac.com
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gr@eternia.no
>
>

Thank you Erik!

HDs are OK, a couple of GB free. Not that it's a lot, but I can't imagine
it being too low for MySQL..

I'm aware memory is a bit low, but RAMBUS chips are hard to come by. They
don't have them in stock anywhere anymore. Also they are quite expensive.
It's almost like you could've bought 1/3rd of a new cheap Dell server for
2x512MB RAMBUS. But if a new box for a reasonable price wouldn't be any
faster it's no use anyway...

Concerning slow queries, it seems there's a couple of different queries
that's being logged.

This is one, taking 66 seconds:

# Query_time: 66 Lock_time: 0 Rows_sent: 0 Rows_examined: 15857680
SELECT word_id
FROM phpbb_search_wordmatch
GROUP BY word_id
HAVING COUNT(word_id) > 263916;

Usual time for this seems to be from 12 to 66 seconds.

And then there's this, usually taking 10-20 seconds:

# Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 395960
SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as
id2, p.post_username, p2.post_username AS post_username2, p2.post_time,
f.forum_name
FROM phpbb_topics t, phpbb_users u,
phpbb_posts p, phpbb_posts p2, phpbb_users
u2, phpbb_forums f
WHERE t.topic_poster = u.user_id
AND t.forum_id NOT IN (16, 17)
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND t.forum_id = f.forum_id
AND u2.user_id = p2.poster_id




AND t.topic_status <> 1
AND t.topic_status <> 2

ORDER BY t.topic_last_post_id DESC
LIMIT 10;


In the evenings there seems to be 10-20 slow queries every hour, time
between them varying from seconds to usually 5-10 minutes.

Cheers,

Gunnar


On fre, januar 4, 2008, 05:55, Erik Giberti wrote:
> Gunnar,
>
> us = user (things like MySQL/PHP/Apache)
> sy = system (memory management / swap space / threading / kernel
> processes and so on)
> ni = nice (apps running only when nothing else needs the resource)
> id = idle (extra cpu cycles being wasted)
> wa = wait state (io wait for disk/network/memory)
> hi & si - interrupts
>
> Generally acceptable load should be < #processors (so in your case 2
> is okay - machine is performing well - 4 somethings being over
> utilized somewhere)
> Also in top 100% = 100% of one processor, so in a dual processor (or
> core) setup, you can actually go to 200%
>
> Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59%
> overall) of system resources.
> Your system processes are taking up 66.2% (of 200% or 33% overall)
> and it's leaving about 14% (of 200% - so 7% overall) of the system idle.
> The remainders are I/O waits etc (your numbers look pretty good there,
> but IO wait can spike and so may be misleading without using other
> tools.
>
> You may be encountering a thrashing problem with the amount of memory
> left or any number of things, but I would look at memory use on this
> box, because your load is pretty high and your performance is
> suffering if it's staying there. Your memory is at about 92% utilized
> too... while 91Mb seems like a lot of memory - it's easily consumed by
> a couple of large queries, sorts and so on which then goes right to
> disk swapping for virtual memory - never good for performance. It
> might also be impacted by IO and you just can't see it in the one
> slice of top we have here. If that number spikes up to 5% and then
> falls back down - it might be time spent going to disk with temp
> tables etc.
>
> Also turn on slow query logging (yes, I know it's another performance
> hit) and see if there is one query that's particularly problematic,
> perhaps optimizing the indexes etc on the table might with the
> performance.
>
> Also, make sure your HD's aren't full... that will kill performance
> very quickly if the needed disk space isn't there.
>
> Erik
>
>
> On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote:
>
>> Hello,
>>
>> Thanks. I read the document, but unfortunately it didn't tell me
>> anything
>> new..
>>
>> One of the things I am a bit confused about is:
>>
>> top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30,
>> 2.84
>> Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
>> Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi,
>> 0.7% si
>> Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi,
>> 0.0% si
>> Mem: 1034280k total, 942780k used, 91500k free, 34252k
>> buffers
>> Swap: 2031608k total, 104k used, 2031504k free, 278788k
>> cached
>>
>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld
>>
>> How come the CPUs can have idle time even though mysqld is running at
>> 99.9%, AND there's a processor queue (4.36)?
>>
>> Cheers,
>>
>> Gunnar R.
>>
>> On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote:
>>> Hi,
>>>
>>> If you can follow this document:
>>>
>>> http://www.ufsdump.org/papers/uuasc-june-2006.pdf
>>>
>>> You should be able to figure out what's happening.
>>>
>>> Cheers,
>>>
>>> Andrew
>>>
>>> -----Original Message-----
>>> From: Gunnar R. [mailto:gr@eternia.no]
>>> Sent: Tue, 01 January 2008 23:31
>>> To: mysql@lists.mysql.com
>>> Subject: Performance problem - MySQL at 99.9% CPU
>>>
>>> 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 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=a...e@lovefilm.com
>>>
>>>
>>>
>>> LOVEFiLM International Limited is a company registered in England and
>>> Wales. Registered Number: 04392195. Registered Office: No.9, 6
>>> Portal Way,
>>> London W3 6RU, United Kingdom.
>>>
>>> This e-mail is confidential to the ordinary user of the e-mail
>>> address to
>>> which it was addressed. If you have received it in error, please
>>> delete it
>>> from your system and notify the sender immediately.
>>>
>>> This message has been scanned for viruses by BlackSpider
>>> MailControl -
>>> www.blackspider.com
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=gr@eternia.no
>>>
>>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=giberti@mac.com
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gr@eternia.no
>
>

At 6:47a -0500 on 08 Jan 2008, Gunnar R. wrote:
> Concerning slow queries, it seems there's a couple of different queries
> that's being logged.

I haven't tried it yet, but this recently went by on debaday.debian.net:

mytop: a top clone for MySQL

http://debaday.debian.net/2007/12/26...one-for-mysql/

Kevin