On 03/07/2013 07:40 AM, Filip Rembiałkowski wrote:
> Why do you assume that opening a cursor with ORDER BY is equivalent
> to fetching all rows? It is not.
It is when 90% of a table's 65M rows are part of the result set.
Calculating the cursor puts the retrieved rows in a temporary space
until the cursor is freed. Calculating that many rows with that order
by, on our particular system took about 75 seconds. The problem wasn't
really that it was fetching the rows, but that it was closing the cursor
*right* afterwards. Like this:
2013-03-04 07:46:06 CST DEBUG1 remoteHelperThread_1_1: 78.010 seconds
until close cursor
We saw that in the slave log about every 80 seconds. When I set up slony
on our staging system, it only closed the cursor once all 20M rows were
fetched and deleted from the subscriber node. Clearly whatever was
happening in our production environment wasn't normal. But at the time,
it *looked* like slony was forcing it to calculate and fill the cursor,
getting the top 500 rows, and closing it right away on purpose. Turns
out, that was abnormal. :)
So, I'm not shocked anymore. A one time cursor calculation is fine. Now
I'm confused as to what broke it or why it was acting that way when that
clearly isn't the design goal.
Ah well.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email