Обсуждение: 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
RedhatLinux 7.3.
Long vacuum times should have little effect on applications, as the new VACUUM implementation does not exclusively lock
thetable for any great length of time. However, I found that near the end of the vacuum operation, the vacuuming
postgresbackend 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
explainswhy my insert operations stopped. Looking further, I found a place where the comment and conditional logic
didnot 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
isnot within the min-max range that was handled in logic before this snippet. However, the code records free space if
EITHERof 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
performanceproblem.
----
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'vesubmitted 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
performanceproblem.
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
This message: NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost I get more or less frequently. what to do? regards fredrik chabot
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 > 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
Tom Lane wrote:
fredrik chabot <fredrik@f6.nl> writes:
What version of Postgres? What platform (Linux, etc)?
psql (PostgreSQL) 7.2.1SMP Linux 2.2.18pre22
I seem to recall that SMP support was very flaky in the 2.2 serieskernels, and even in 2.4 up to 2.4.10 or something
likethat.Perhaps a ker<pre
wrap="">nel update would make life better.
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
Kernel 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 :-<
It is less frequent than on the smp machine.
regards fredrik chabot
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's >> >>Kernel 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. > > > 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) >