query performance

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