Viktor Fougstedt wrote:
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...
Hi!
I'm not sure if You've got any answers (I don't read the list
systematically), but for me this behaviour seems a bit like locking issue.
A good way to look at it is looking at processlist (SHOW PROCESSLIST) -
where processes have their status displayed - look for "Locked" status.
I administer one db, which is based on MyISAM tables. Sometimes it
experienced similar behaviour - first comes one time consuming query,
after it some DML, then other queries - all must wait for the first query.
In my opinion first query acquire READ lock, then DML wait for WRITE
lock and may be if it waits, other queries can not be performed due to
this wait for WRITE lock.
The solution here could be redesigning of database by limiting DML
operations or using LOW PRIORITY DML queries
Any thoughts or comments?
Regards,
Remigiusz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
