List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:April 9 1999 1:02am
Subject:Re: query locks up the database
View as plain text  
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?
> 
> DJ
>

check your disk space on the device where /tmp is mounted. 

-- 
Sasha Pachev
http://www.sashanet.com
Thread
query locks up the databaseDavid Johnson9 Apr
  • Re: query locks up the databaseSasha Pachev9 Apr
    • RE: query locks up the databaseDavid Johnson9 Apr
      • RE: query locks up the databaseDavid Johnson9 Apr
      • RE: query locks up the databaseJani Tolonen9 Apr