|
We are currently testing an upgrade from SQL Anywhere 10 to 16, and I've found that certain queries hang up after the appearance of 8 "INT: Exchange" connections. I've asked about these previously, but today, I left it running for a particularly long time, it was using 100% of the CPU and huge gobs of RAM (machine has 32G). I noticed that a connection "INT: Cleaner" appeared. I ended up closing to the connection to try changing the query to create statistics before the choke point and it is again running, and hasn't yet got to the choke point. So my question is, what exactly is this "INT: Cleaner"?? |
|
The Cleaner is a background task that does things like physically remove rows from table pages some time after the row was deleted. When you delete a row, it is just marked as deleted in the database file. At COMMIT time, we can either make the committer wait until the rows are physically removed from the page or just queue the physical deletion for another task (the cleaner) to do later. The presence of Exchange threads suggests you have a parallelized query running. Those are much more likely to be the ones to be consuming CPU & memory -- the cleaner is tiny. You want to find out what query is running the parallelized query. We've noticed some queries run faster in SQL Anywhere 16, and appear to be running parallelized, and with others, the attempted parallelization seems to bog down the queries and use massive resources. Can anyone explain what causes certain queries to be parallelized in this way and how we can avoid getting stuck like this? These are queries some of which run quickly in SQL Anywhere 10.
(04 Aug '16, 11:41)
timoccci
Putting a "create statistics" statement directly before the first use of certain temp tables has fixed this problem in one case so far.. I'm now fixing around for other cases of this to see if I can repeat the success.
(04 Aug '16, 13:42)
timoccci
|
|
See this old blog post on max_query_tasks. Intra-query parallelism remains a common cause for runaway queries in spite of recent claims that all the FWIW Foxhound is pretty good at showing what goes on during periods of high stress.
|
