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.