Unfortunately that doesn't appear to be it. Some more interesting facts are
that the query doesn't actually lock up the database but take over 8 seconds
to complete although all (foreign) keys (joins) are indexed. I think that
is why when I run the query concurrently it seems to stop the database and
MySQL eventually times out the queries.
Also, if I remove the (log.seq) from the query, it's time drops to .77
seconds. This is better, but still not too good considering the WEBSTATS
table has only 6000 rows, the AGUSER table only 100 and the SYNCLOG table
Any ideas on what am I missing? I don't want to have to break up the
From: apachev@stripped [mailto:apachev@stripped] On Behalf
Of Sasha Pachev
Sent: Thursday, April 08, 1999 6:03 PM
Cc: mysql distribution lists
Subject: Re: query locks up the database
David Johnson wrote:
> I'm running 3.22.16-gamma on FreeBSD,. When I run the following query
> *concurrently* against my database it locks it up for a period of minutes:
> SELECT user.id, user.auth, user.nonce, user.syncstart, max(log.seq),
> sum(stats.outpagesize + stats.outimagesize) from AGUSER as user left join
> SYNCLOG as log on user.id = log.fk_aguser left join as stats on user.id =
> stats.fk_aguser and UNIX_TIMESTAMP(stats.syncstart) > UNIX_TIMESTAMP() -
> 86400 where user.uid = "davej" and user.deleted = 0 GROUP BY user.id"
> One user at a time works fine. The table sizes and result sizes are all
> very small. I have tried bumping the sort_buffer as well as the
> tmp_table_size up to 10M a piece but it didn't help.
> We have had other similar problems with other join/group by queries. In
> fact another similar query causes the CPU to max out when it is run
> concurrently. Any suggestions?
check your disk space on the device where /tmp is mounted.