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"??

asked 04 Aug '16, 11:27

timoccci's gravatar image

timoccci
86179
accept rate: 0%


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.

permanent link

answered 04 Aug '16, 11:35

John%20Smirnios's gravatar image

John Smirnios
8.9k377110
accept rate: 39%

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 bugs enhancements have been fixed delivered.

FWIW Foxhound is pretty good at showing what goes on during periods of high stress.

permanent link

answered 04 Aug '16, 16:07

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 04 Aug '16, 16:17

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×128

question asked: 04 Aug '16, 11:27

question was seen: 278 times

last updated: 04 Aug '16, 16:17