Christian, can you post the output of
SHOW VARIABLES;
and
SHOW STATUS;
please?
I see you're servicing 761 queries per second on average, which is
pretty good. However, it appears to me your server has performed over
120 million operations (Opens: 120224674) to open tables in 10 days of
uptime, so your
table_cache or some other related variable may be too low for your
situation. As a result your server is perhaps having to close tables
only to re-open them very soon, introducing a lot of overhead.
Dan
Christian Hammers wrote:
Hello
We have the problem that on one of our database server
(Quad-Dualcore-Hyperthreading Xeon system with 8GB RAM) the database
performance suddenly goes down and stays so for a while.
There is no significant memory or CPU activity but the Load goes up to
200 which indicates to me that there must be many mysql threads (hidden
in the process list) which are calculated in the Load value but are
effectively doing nothing as CPU/RAM stays.
The mysqladmin processlist output looks suspicious, *lots* of queries in
the opening+closing tables state which should, according to the docs,
be happen in fractions of a second.
There is no visible disc activety during that time so definetly no I/O
bottleneck (8GB RAM is enough to hold all data).
The system is running Debian stable, further infos below.
Thanks in advance for any ideas!
bye,
-christian-
db:~# mysqladmin status version
Uptime: 898475 Threads: 16 Questions: 684283929 Slow queries: 7335 Opens:
120224674 Flush tables: 11 Open tables: 64 Queries per second avg: 761.606
mysqladmin Ver 8.41 Distrib 4.1.11, for pc-linux-gnu on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.11-Debian_4sarge4-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 10 days 9 hours 34 min 35 sec
| 93702 | XXXXX | localhost:41097 | XXXXX | Query | 0 |
Opening tables | select mt_votinga0_.id as id1
60_, mt_votinga0_.votingId as votingId160_, mt_votinga0_.answer as answ |
| 93704 | XXXXX | localhost:40998 | XXXXX | Query | 3 | Opening tables | SELECT SUM(e.count) AS votes
FROM MT_Vote AS e WHERE e.answerId = 29 GROUP by e.answerId |
| 115252 | XXXXX | localhost:33701 | XXXXX | Query | 0 |
Opening tables | select mt_voting0_.id as col_
0_0_ from MT_Voting mt_voting0_ where mt_voting0_.id=36 and mt_voting0_ |
| 306188 | XXXXX | localhost:39702 | XXXXX | Query | 0 |
Opening tables | select mt_voting0_.id as col_
0_0_ from MT_Voting mt_voting0_ where mt_voting0_.id=36 and mt_voting0_ |
| 306194 | XXXXX | localhost:43074 | XXXXX | Query | 0 |
Opening tables |
...
| 3216567 | XXXXX | web1.intern:40079 | XXXXX | Query | 1 |
Opening tables | SELECT
data.id,
data.xms_created created,
data.xms_modified modified,
data.xms_l |
| 3216644 | XXXXX | web1.intern:40148 | XXXXX | Query | 1 |
Opening tables | SELECT
data.id,
data.xms_created created,
data.xms_modified modified,
data.xms_l |
| 3216677 | XXXXX | web3.intern:45182 | XXXXX | Query | 1 |
Opening tables | SELECT
data.id,
data.xms_created created,
data.xms_modified modified,
data.xms_l |
| 3216694 | XXXXX | web3.intern:45191 | XXXXX | Query | 3 |
closing tables | SELECT
data.id,
data.xms_created created,
data.xms_modified modified,
data.xms_l |
| 3216819 | XXXXX | web3.intern:45257 | XXXXX | Query | 4 |
closing tables | SELECT
data.id,
data.xms_created created,
data.xms_modified modified,
data.xms_l |
| 3216830 | XXXXX | web3.intern:45263 | XXXXX | Query | 3 |
closing tables | SELECT
data.id,
data.xms_created created,
data.xms_modified modified,
data.xms_l |
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
