Thread: query performance
I have a largish (pg_dump output is 4G) database. The query: select count(*) from some-table was taking 120 secs to report that there were 151,000+ rows. This seemed very slow. This db gets vacuum'd regularly (at least once per day). I also did a manual 'vacuum analyze', but after it completed, the query ran no faster. However, after dumping the database and recreating it from the backup, the same query takes 2 secs. Why the dramatic decrease? Would 'vacuum full' have achieved the same performance improvements? Is there anything else that needs to be done regularly to prevent this performance degradation? postgresql 8.1.3 running on redhat es 4. Thanks, Brian
Brian Cox <brian.cox@ca.com> writes:
> I have a largish (pg_dump output is 4G) database. The query:
> select count(*) from some-table
> was taking 120 secs to report that there were 151,000+ rows.
> This seemed very slow. This db gets vacuum'd regularly (at least once
> per day). I also did a manual 'vacuum analyze', but after it completed,
> the query ran no faster. However, after dumping the database and
> recreating it from the backup, the same query takes 2 secs.
> Why the dramatic decrease?
Presumably, the table was really bloated (lots of unused space).
> Would 'vacuum full' have achieved the
> same performance improvements?
It would've compacted the table all right, but probably left the indexes
worse off.
> Is there anything else that needs to be done
> regularly to prevent this performance degradation?
I suspect that your FSM settings are too low, causing free space found
by VACUUM to be forgotten about. You might also need to consider
vacuuming more than once a day (there's a tradeoff between how often
you vacuum and how much FSM space you need).
regards, tom lane
Tom Lane [tgl@sss.pgh.pa.us] wrote: > I suspect that your FSM settings are too low, causing free space found > by VACUUM to be forgotten about. I read in another thread that vacuum verbose would tell me how much FSM is needed, but I ran it and didn't see any output about this. What is the way to determine how much FSM is needed (other than wait for hints in the log). You might also need to consider > vacuuming more than once a day (there's a tradeoff between how often > you vacuum and how much FSM space you need). What is the trade-off? Anyway to predict how much more (presumably) FSM is needed if you vacuum more often? Thanks, Brian
Brian Cox <brian.cox@ca.com> writes:
> I read in another thread that vacuum verbose would tell me how much FSM
> is needed, but I ran it and didn't see any output about this.
You need a database-wide vacuum verbose (not just 1 table) to get that
output ...
regards, tom lane
Tom Lane [tgl@sss.pgh.pa.us] wrote: > You need a database-wide vacuum verbose (not just 1 table) to get that > output ... I ran: > pgsql -U admin -d cemdb -c 'vacuum verbose' > /tmp/pgvac.log 2>&1 the output file has 2593 lines and, while I haven't looked at all of them, a: > fgrep -i fsm /tmp/pgvac.log returns no lines. Any hints as to where the FSM info is in this file? Thanks, Brian
On Thu, Apr 24, 2008 at 6:23 PM, Brian Cox <brian.cox@ca.com> wrote: > Tom Lane [tgl@sss.pgh.pa.us] wrote: > > > You need a database-wide vacuum verbose (not just 1 table) to get that > > output ... > > > > I ran: > > > pgsql -U admin -d cemdb -c 'vacuum verbose' > /tmp/pgvac.log 2>&1 > > the output file has 2593 lines and, while I haven't looked at all of them, > a: > > > fgrep -i fsm /tmp/pgvac.log > > returns no lines. > > Any hints as to where the FSM info is in this file? There's bits spread throughout the file, but the summary is at the bottom.
Scott Marlowe [scott.marlowe@gmail.com] wrote: > There's bits spread throughout the file, but the summary is at the bottom. Here's a tail of the 'vacuum verbose' output: INFO: vacuuming "pg_toast.pg_toast_797619965" INFO: index "pg_toast_797619965_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_797619965": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM I don't see anything that looks like a "summary". Thanks, Brian
Brian Cox <brian.cox@ca.com> writes:
> Any hints as to where the FSM info is in this file?
At the very end ... you're looking for these messages:
ereport(elevel,
(errmsg("free space map contains %d pages in %d relations",
storedPages, numRels),
errdetail("A total of %.0f page slots are in use (including overhead).\n"
"%.0f page slots are required to track all free space.\n"
"Current limits are: %d page slots, %d relations, using %.0f kB.",
Min(needed, MaxFSMPages),
needed,
MaxFSMPages, MaxFSMRelations,
(double) FreeSpaceShmemSize() / 1024.0)));
if (numRels == MaxFSMRelations)
ereport(elevel,
(errmsg("max_fsm_relations(%d) equals the number of relations checked",
MaxFSMRelations),
errhint("You have at least %d relations. "
"Consider increasing the configuration parameter \"max_fsm_relations\".",
numRels)));
else if (needed > MaxFSMPages)
ereport(elevel,
(errmsg("number of page slots needed (%.0f) exceeds max_fsm_pages (%d)",
needed, MaxFSMPages),
errhint("Consider increasing the configuration parameter \"max_fsm_pages\" "
"to a value over %.0f.", needed)));
regards, tom lane
Tom Lane [tgl@sss.pgh.pa.us] wrote:
> At the very end ... you're looking for these messages:
>
> ereport(elevel,
> (errmsg("free space map contains %d pages in %d relations",
> storedPages, numRels),
> errdetail("A total of %.0f page slots are in use (including
> overhead).\n"
> "%.0f page slots are required to track all free space.\n"
> "Current limits are: %d page slots, %d relations, using %.0f
> kB.",
> Min(needed, MaxFSMPages),
> needed,
> MaxFSMPages, MaxFSMRelations,
> (double) FreeSpaceShmemSize() / 1024.0)));
>
> if (numRels == MaxFSMRelations)
> ereport(elevel,
> (errmsg("max_fsm_relations(%d) equals the number of
> relations checked",
> MaxFSMRelations),
> errhint("You have at least %d relations. "
> "Consider increasing the configuration
> parameter \"max_fsm_relations\".",
> numRels)));
> else if (needed > MaxFSMPages)
> ereport(elevel,
> (errmsg("number of page slots needed (%.0f) exceeds
> max_fsm_pages (%d)",
> needed, MaxFSMPages),
> errhint("Consider increasing the configuration
> parameter \"max_fsm_pages\" "
> "to a value over %.0f.", needed)));
>
> regards, tom lane
>
The following greps of the vacuum verbose output return no lines:
fgrep -i fsm
fgrep 'free space'
fgrep 'page slots'
fgrep 'relations'
I've already posted the tail of this output previously.
I conclude that these lines are not in this file. Where
did they go?
Thanks,
Brian
Brian Cox <brian.cox@ca.com> writes:
> I've already posted the tail of this output previously.
> I conclude that these lines are not in this file. Where
> did they go?
[ scratches head... ] Your example command works as expected for me.
[ rereads thread... ] Oh, you're running 8.1. I think you have to
do the command as a superuser to get that output in 8.1. Later versions
are less picky.
regards, tom lane
> [ scratches head... ] Your example command works as expected for me.
> [ rereads thread... ] Oh, you're running 8.1. I think you have to
> do the command as a superuser to get that output in 8.1. Later versions
> are less picky.
Yes, with the right incantations, the FSM information does appear. The perils
of being a bit behind the times, I guess.
Thanks for your help,
Brian