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?