Re: Hanging on copying to tmp table
Posted by:
Rick James
Date: April 24, 2012 10:16AM
(Thanks for the detailed info.)
> Sometimes it hangs on "copying to tmp table", and also "sending data", but mostly on "copying...".
> It does not happen everyday, and I have not found a way to recreate it, so I cannot explain why it happens.
You are saying it will be fast for awhile, then slow for awhile? If so, that sounds like the "8 random dives" screwing up sometimes.
Or (more likely), SQL_CACHE is part of the answer. Right after making _any_ change to the table, the query is slow. After that, it stays fast. "Fast" would be because of SQL_CACHE and the Query Cache.
When it runs fast, does the EXPLAIN show that it is using `indx_testcase_lft`?
If so, give this change a try:
> FROM `test_testcase` AS `node`, `test_testcase` AS `parent`
-->
FROM `test_testcase` AS `node` USE INDEX(indx_testcase_lft),
`test_testcase` AS `parent`
If that does not work, change USE to FORCE. If that still does not work, come back, and I will scratch my head some more.
> 'innodb_buffer_pool_size', '2621440000'
I assume you have at least 4GB of RAM.
It is usually "wrong" to do SELECT a,b,c ... GROUP BY a. You can't predict which b,c you will get.