Hi, and thank you both for valuable tips.
The MySQLd in question runs on a mailserver, and a large amount of
processes (Postfix, Maildrop and Squirrelmail) connect to it, run one
or two simple queries, and then disconnects.
There is only one client that is constantly connected, namely a
configuration server. It does not have a cache, and only asks a few
small questions every now and then. I have modified the code so that
it logs any SQL-queries that take more than 2 clock seconds, which
should show me if the config server is the culprit.
I tried SHOW INNODB STATUS when MySQLd was taking 100% of one CPU,
and the "Main thread state" was "sleeping". Also from the same
command all TRANSACTIONs seemed idle except for one, which was
running my SHOW INNODB STATUS command.
I draw the conclusion that whatever is happening, it's not InnoDB.
Since all the tables that the configuration server uses are in
InnoDB, it also seems likely that the config server is not the
culprit either.
Is there any way for me to find out exactly what queries have been
run in the last X minutes? When the load goes up, I could check to
see what queries ran before, to possibly find a pattern. If I could
temporarily log queries and the time they took to complete, that
would also be a good way forward. The General Query log doesn't seem
to log the time a query took (as I read the manual).
I have a cron-job that logs the current machine load and a "SHOW
STATUS" every five minutes. I just awk:ed through it, and I might
imagine it, but there is a possible connection between the
"Max_used_connections" parameter increasing and the machine's load
going up. Could a massive storm of connections be causing the
slowdown? Some form of lock contention having to do with new
connections or similar?
Can I reset the Max_used_connections parameter so I get a maxlevel
for the last five minutes rather than since the last restart?
Lots of questions, so I am deeply grateful for any insights into any
of them,
/Viktor...
On Nov 16, 2005, at 00:48, Heikki Tuuri wrote:
Hi!
Also look at
SHOW INNODB STATUS\G
during the slow phase.
What does it say about the 'Main thread ... state'?
What does it say about transactions?
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs
up MyISAM tables
http://www.innodb.com/order.php
----- Original Message ----- From: "Bruce Dembecki"
<[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, November 15, 2005 5:08 PM
Subject: Re: Background tasks performed by MySQL?
I would expect this to finally be something on the client end,
rather than the server end... is there a search index that gets
rebuilt periodically? Maybe some reports that get generated against
the data? The last example that comes to my mind is if you use a
client that caches data, does the cache get dumped or the client
restarted at some point?
These are the sort of things that we found led to the type of
behavior you are talking about. Let me give clearer examples... Our
databases typically do Discussion Boards... usually very large scale
discussion boards (think eBay or HBO scales). The discussion board
server (in this case the database client) keeps it's own search
index, but need to update it on a regular basis to keep it current.
If that period is too infrequent or the queries poorly optimized,
they can generate a lot of load on the database, and you get the type
of results you are seeing. Or if the discussion board tries to
analyze the stats for the last day (or week or month etc) to provide
information for reports... in our example a million page views a day
means a million stats records a day, and any analysis can be quite
the load generator. Same thing with our cache on our discussion
board... if our discussion board has been up for some time it has all
the messages most frequently used already in local cache, it doesn't
do a query to recover each message in this situation... an instance
of the discussion board going live into production with no data in
the cache can mean a huge database hit for a few minutes while the
caches in the discussion board get populated.
These are just examples from our life, but I'm pretty sure when al is
said and done that the cause will be some process that your client is
generating to do something periodic, rather than the MySQL Server
running some sort of process, which we've never seen.
Take a look at the process list when it is in one of these cycles
(from the mysql command line client type "show processlist;"). it
should give you a pretty good idea of what's doing what at the time
and will give you some idea on where to look.
Best Regards, Bruce
On Nov 15, 2005, at 2:20 AM, Viktor Fougstedt wrote:
Hi.
We have a MySQLd with both MyISAM and InnoDB tables that at uneven
intervals stops responding correctly to connections.
At all times, about one connection per minut fails, regardless of
which database and/or user and/or remote host is connecting. The
same connection parameters (and same queries) work correctly 99.9%
of the time, and it is entirely random which connections time out
and when.
We can live with that problem, which does not seem to have any
explanation.
But some times, MySQLd starts taking all the CPU it can get, and
gets extremely sluggish for a few minutes. At these times, several
connections every second are rejected because of timeouts. These
rejections we can't live with.
To attempt solving the problem, I've started thinking that there
might be some form of periodical cleanup that MySQLd or InnoDB
performs automatically, and that we could force it to perform at
night when the expected load is lower.
Is there any such background cleanup performed? It could be
periodical, when a certain number of queries/updates/inserts have
been run, or when some query cache or similar gets full?
If these problems or descriptions somehow ring a bell, I would
welcome any insight I could get from the list.
Thanks in advance,
/Viktor...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
