Bug #769: Slow vacuuming due to error in optimization
Steve Marshall (smarshall@wsi.com) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
Slow vacuuming due to error in optimization
Long Description
I have found very long vacuuming times when vacuuming large tables in Postgres 7.2.1, running on i686 hardware under Redhat Linux 7.3.
Long vacuum times should have little effect on applications, as the new VACUUM implementation does not exclusively lock the table for any great length of time. However, I found that near the end of the vacuum operation, the vacuuming postgres backend began using 100% of one of the system CPUs, and all insert operations on the table being vacuumed stopped. This problem occurred after all the CPU times were reported for the VACUUM, but before the ANALYZE step began.
To identify the source of the problem, I inserted some additional log statements into the source file backend/commands/vacuumlazy.c, and was able to track down the problem to the function that updates the free space map (i.e. the function lazy_update_fsm). This in turn calls the function MultiRecordFreeSpace() in storage/freespace/freespace.c.
Looking at the code in MultiRecordFreeSpace(), I found that this function imposes an exclusive lock. This locking explains why my insert operations stopped. Looking further, I found a place where the comment and conditional logic did not seem to say the same thing, and hence looked suspicious. Here is the code snippet:
------
/*
* Add new entries, if appropriate.
*
* XXX we could probably be smarter about this than doing it
* completely separately for each one. FIXME later.
*
* One thing we can do is short-circuit the process entirely if a
* page (a) has too little free space to be recorded, and (b) is
* within the minPage..maxPage range --- then we deleted any old
* entry above, and we aren't going to make a new one. This is
* particularly useful since in most cases, all the passed pages
* will in fact be in the minPage..maxPage range.
*/
for (i = 0; i < nPages; i++)
{
BlockNumber page = pages[i];
Size avail = spaceAvail[i];
if (avail >= fsmrel->threshold ||
page < minPage || page > maxPage)
fsm_record_free_space(fsmrel, page, avail);
}
-------
The comment indicates that free space is recorded for a page if the available space is above the threshold AND the page is not within the min-max range that was handled in logic before this snippet. However, the code records free space if EITHER of these criteria are true.
Therefore I tried changing the logic to an AND, e.g.:
if (avail >= fsmrel->threshold &&
(page < minPage || page > maxPage))
fsm_record_free_space(fsmrel, page, avail);
This reduced my processing time in lazy_update_fsm() from about 2 minutes to nearly nothing, effectively solving my performance problem.
----
I'm a newbie to the Postgres source code, so I don't know if this is the proper place to submit this information, If I've submitted incorrectly, please let me know, so I can do it right next time.
I'd also be interested in knowing if this change has some hidden or long term effect I just don't see.
Sample Code
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
I have found very long vacuuming times when vacuuming large tables in
Postgres 7.2.1, running on i686 hardware under Redhat Linux 7.3.
How large is "large", and what FSM parameters are you using? Do you
know how many pages were getting passed into MultiRecordFreeSpace?
To identify the source of the problem, I inserted some additional log
statements into the source file backend/commands/vacuumlazy.c, and was
able to track down the problem to the function that updates the free
space map (i.e. the function lazy_update_fsm). This in turn calls the
function MultiRecordFreeSpace() in storage/freespace/freespace.c.
The implementation of that function could stand to be improved, all
right; it's probably O(N^2) in the number of pages processed, if you
have a large enough FSM to let all the pages be stored.
Looking further, I found a place where the
comment and conditional logic did not seem to say the same thing, and
hence looked suspicious.
No, they are the same. Perhaps it's more apparent if you apply De
Morgan's law to the condition:
if (avail >= fsmrel->threshold ||
page < minPage || page > maxPage)
fsm_record_free_space(fsmrel, page, avail);
becomes
if (! (avail < fsmrel->threshold &&
page >= minPage && page <= maxPage))
fsm_record_free_space(fsmrel, page, avail);
or even more verbosely,
if (avail < fsmrel->threshold &&
page >= minPage && page <= maxPage)
/* ignore page */;
else
fsm_record_free_space(fsmrel, page, avail);
which agrees with
* One thing we can do is short-circuit the process entirely if a
* page (a) has too little free space to be recorded, and (b) is
* within the minPage..maxPage range --- then we deleted any old
* entry above, and we aren't going to make a new one.
Therefore I tried changing the logic to an AND, e.g.:
if (avail >= fsmrel->threshold &&
(page < minPage || page > maxPage))
fsm_record_free_space(fsmrel, page, avail);
This reduced my processing time in lazy_update_fsm() from about 2 minutes to nearly nothing, effectively solving my performance problem.
Unfortunately, you created a functionality problem: in this version
MultiRecordFreeSpace will fail to record any free space at all. As
the comment points out:
* ... in most cases, all the passed pages
* will in fact be in the minPage..maxPage range.
so the above condition always fails. Which indeed makes it quick,
but your tables will be bloating for lack of any FSM entries.
A more useful fix probably involves (a) requiring the input to be in
sorted order, and then (b) instead of using the general-purpose
subroutines, keeping track of where in the relation's free-space list
we are currently inserting, to save a fresh search for each insertion.
I had meant to do this but never got round to it.
regards, tom lane
Stephen Marshall <smarshall@wsicorp.com> writes:
Tom Lane wrote:
How large is "large", and what FSM parameters are you using? Do you
know how many pages were getting passed into MultiRecordFreeSpace?
vacuum_mem is 16384
max_fsm_relations is 100
max_fsm_pages is 300000
pg_largeobject data size = 4211617.693 K
pg_largeobject file size = 7932208.000 K
number of large objects = 870
number of lo pages = 2106287
number of messages = 469458
Upon vacuuming, I found 300000 pages were passed to MultiRecordFreeSpace,
with the minPage specified as 0 and the maxPage as -2. The pages passed
exactly equaled the max_fsm_pages parameter.
Yeah; if you look at vacuumlazy.c you'll see it doesn't bother keeping
track of more than max_fsm_pages, since it knows the FSM will not
remember more than that either.
This vacuum took over 30 minutes, with more than 25 minutes spent in
MultiRecordFreeSpace.
Yow. Definitely need to fix that code ...
regards, tom lane
Import Notes
Reply to msg id not found: 3D864356.28C53E57@wsicorp.com
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
<big><small>This message:</small><br>
<tt><br>
NOTICE: Message from PostgreSQL backend:<br>
The Postmaster has informed me that some other backend<br>
died abnormally and possibly corrupted shared memory.<br>
I have rolled back the current transaction and am<br>
going to terminate your database system connection and exit.<br>
Please reconnect to the database system and repeat your query.<br>
server closed the connection unexpectedly<br>
This probably means the server terminated abnormally<br>
before or while processing the request.<br>
connection to server was lost<br>
</tt></big><br>
I get more or less frequently.<br>
<br>
what to do?<br>
<br>
regards fredrik chabot<br>
</body>
</html>
fredrik chabot wrote:
connection to server was lost
I get more or less frequently.
what to do?
Start by giving us more information.
What version of Postgres? What platform (Linux, etc)?
What does the relevant portion of your database schema look like?
What repeatable series of actions causes the problem?
Is there a core file and have you looked at it in a debugger?
Joe
Joe Conway <mail@joeconway.com> writes:
what to do?
Start by giving us more information.
What version of Postgres? What platform (Linux, etc)?
What does the relevant portion of your database schema look like?
What repeatable series of actions causes the problem?
Is there a core file and have you looked at it in a debugger?
Also, make sure you are logging the postmaster log output (send its
stderr to a file, not to /dev/null), and look to see what shows up
in the postmaster log when this happens.
regards, tom lane
Joe Conway wrote:
fredrik chabot wrote:
connection to server was lost
I get more or less frequently.
what to do?
Start by giving us more information.
What version of Postgres? What platform (Linux, etc)?
psql (PostgreSQL) 7.2.1
SMP Linux 2.2.18pre22
What does the relevant portion of your database schema look like?
I do not know what the relevant portion is.
What repeatable series of actions causes the problem?
Access the database with multible users, twice in 15 minutes, and then
not once in three hours with similar load.
Is there a core file and have you looked at it in a debugger?
no core file to be found
Show quoted text
Joe
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
fredrik chabot <fredrik@f6.nl> writes:
What version of Postgres? What platform (Linux, etc)?
psql (PostgreSQL) 7.2.1
SMP Linux 2.2.18pre22
I seem to recall that SMP support was very flaky in the 2.2 series
kernels, and even in 2.4 up to 2.4.10 or something like that.
Perhaps a kernel update would make life better.
regards, tom lane
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body>
Tom Lane wrote:<br>
<blockquote type="cite" cite="mid25853.1032724594@sss.pgh.pa.us">
<pre wrap="">fredrik chabot <a class="moz-txt-link-rfc2396E" href="mailto:fredrik@f6.nl"><fredrik@f6.nl></a> writes:<br></pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">What version of Postgres? What platform (Linux, etc)? <br></pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<pre wrap="">psql (PostgreSQL) 7.2.1<br>SMP Linux 2.2.18pre22<br></pre>
</blockquote>
<pre wrap=""><!---->I seem to recall that SMP support was very flaky in the 2.2 series<br>kernels, and even in 2.4 up to 2.4.10 or something like that.<br>Perhaps a ker<pre
wrap="">nel update would make life better.</pre></pre>
</blockquote>
I turned on loggin and it appeard to be a shared memory locking problem,
so I migrated the database to a non-smp machine running RH 7.2 and the postgres
7.2.1-5 rpm's<br>
<br>
Kernel 2.4.7-10<br>
<br>
and I still get
<blockquote type="cite" cite="mid25853.1032724594@sss.pgh.pa.us">
<pre wrap="">FATAL 1: LWLockAcquire: can't wait without a PROC structure</pre>
</blockquote>
The code tells me this should never heppen :-<<br>
<br>
It is less frequent than on the smp machine.<br>
<br>
regards fredrik chabot<br>
</body>
</html>
fredrik chabot <fredrik@f6.nl> writes:
so I migrated the database to a non-smp machine running RH 7.2 and the postgres
7.2.1-5 rpm's<br>
<br>
Kernel 2.4.7-10<br>
<br>
and I still get
<blockquote type="cite" cite="mid25853.1032724594@sss.pgh.pa.us">
<pre wrap="">FATAL 1: LWLockAcquire: can't wait without a PROC structure</pre>
</blockquote>
The code tells me this should never heppen :-<<br>
I don't think it should happen either ;-) When do you see it exactly
--- is it during startup of a fresh connection, or while executing
queries in an existing backend, or ...?regards, tom lane
PS: please don't send HTML mail to the lists, it's a pain to quote ...
Tom Lane wrote:
fredrik chabot <fredrik@f6.nl> writes:
so I migrated the database to a non-smp machine running RH 7.2 and the postgres
7.2.1-5 rpm'sKernel 2.4.7-10
and I still get
FATAL 1: LWLockAcquire: can't wait without a PROC structure
The code tells me this should never heppen :->
I don't think it should happen either ;-) When do you see it exactly --- is it during startup of a fresh connection, or while executing queries in an existing backend, or ...?
Ok, short answer I don't know; long answer:
Most connections are "long-lived" connections staying up for hours on
end. There are somewhere form 6 upto ~75 connections open at a time
about 6 at night > 50 during "office hours". Both failures today where
in office hours and It happend about every hour in office hours on the
smp system and only once in the off hours.
I got it BTW twice while loading the data in the database. Simple stuff
begin; -thousands of inserts; commit; and then the next table.
I did not once fail during the test fase of the system. (one or two
users at a time)
So my hunch is that it happens when there has been a lot of operations
in one connection and a lot of other connections at the same time.
Show quoted text
regards, tom lane
PS: please don't send HTML mail to the lists, it's a pain to quote ...
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)