List:General Discussion« Previous MessageNext Message »
From:David Johnson Date:April 8 1999 11:04pm
Subject:query locks up the database
View as plain text  
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

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