Why pgAdmin III guru suggests VACUUM in 8.1

Keep in mind that if analyze has never been run on a table the database
will assume 1000 rows, which is definately off from 122 rows.

You might want to ask on the pgAdmin list. Though I'd recommend against
calling the guru 'stupid' over there. :)

On Sun, Nov 20, 2005 at 09:13:36PM +0200, Andrus Moor wrote:

I'm using Postgres 8.1 in Windows XP

Sometimes when using pgAdmin the following Guru hint appears suddenly:

"Running VACUUM recommended
The estimated rowcount on the table "firma1.algsa" deviates significantly
from the actual rowcount. You should run VACUUM ANALYZE on this table.
Instead of issuing a manual VACUUM ANALYZE command on this table (you can
use the pgAdmin III maintenance menu for this), running VACUUM ANALYZE on a
regular or automated basis should be considered. This can be achieved using
a scheduler. PostgreSQL also supplies the pg_autovacuum daemon, which will
track changes made to the database and issue vacuum commands as required
automatically. In most cases, pg_autovacuum will be the best choice. "

I have noticed in Postgres log, that autovacuum processes my cluster
regulary.
So in my knowledge, this hint is wrong.
Please confirm that guru is stupid.

Also I followed this hint and got the results:

INFO: vacuuming "firma1.algsa"
INFO: index "algsa_pkey" now contains 122 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO: "algsa": found 0 removable, 122 nonremovable row versions in 4 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.06 sec.
INFO: analyzing "firma1.algsa"
INFO: "algsa": scanned 4 of 4 pages, containing 122 live rows and 0 dead
rows; 122 rows in sample, 122 estimated total rows

So it seems that vacuum did make anything.

Andrus.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim,

Keep in mind that if analyze has never been run on a table the database
will assume 1000 rows, which is definately off from 122 rows.

autovacuum processes this tabele regularly.
I believed that autovacuum can update the row count to be real.

You might want to ask on the pgAdmin list. Though I'd recommend against
calling the guru 'stupid' over there. :)

I'm creating application which upsizes existing data to new postgres 8.1
database.

Will I need to run VACUUM ANALYZE after upsizing ?

Must I enable statitics collection for this database ?

Andrus.

On Tue, Nov 22, 2005 at 09:01:25PM +0200, Andrus wrote:

Jim,

Keep in mind that if analyze has never been run on a table the database
will assume 1000 rows, which is definately off from 122 rows.

autovacuum processes this tabele regularly.
I believed that autovacuum can update the row count to be real.

It can, but without looking at logs of what autovacuum has actually done
there's no way to know if it actually has analyzed that table or not.

You might want to ask on the pgAdmin list. Though I'd recommend against
calling the guru 'stupid' over there. :)

I'm creating application which upsizes existing data to new postgres 8.1
database.

Will I need to run VACUUM ANALYZE after upsizing ?

Upsizes? Are you adding more data? If so then yes, analyze would be
good, though autovacuum should handle it for you.

Must I enable statitics collection for this database ?

Well, autovacuum depends on it; see
http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Jim,

Upsizes? Are you adding more data? If so then yes, analyze would be
good, though autovacuum should handle it for you.

I create new Postgres database, upsize a lot of data into it. After that
this database goes online and will receive a lot of transactions daily.
I'm using PG 8.1 default postgres.conf file.

I read than autovacuum may skip some tables which can cause wrong guru
hints.

So it seems that I should run manually VACUUM ANALYZE for the whole database
once for initial statistics colection of those tables, isn't it?

Andrus.

On Tue, Nov 22, 2005 at 09:33:34PM +0200, Andrus wrote:

Jim,

Upsizes? Are you adding more data? If so then yes, analyze would be
good, though autovacuum should handle it for you.

I create new Postgres database, upsize a lot of data into it. After that

FWIW, people generally refer to that as 'loading data'; I've never heard
of 'upsizing' before, which is why I was somewhat confused.

this database goes online and will receive a lot of transactions daily.
I'm using PG 8.1 default postgres.conf file.

Well, by default autovacuum is turned off. Aside from that there's other
things you'll want to tune. Take a look at the annotated postgresql.conf
that's in techdocs.

I read than autovacuum may skip some tables which can cause wrong guru
hints.

So it seems that I should run manually VACUUM ANALYZE for the whole database
once for initial statistics colection of those tables, isn't it?

No real need to vacuum, just running analyze on the entire database
would suffice.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Andrus wrote:

Jim,

Keep in mind that if analyze has never been run on a table the database
will assume 1000 rows, which is definately off from 122 rows.

autovacuum processes this tabele regularly.
I believed that autovacuum can update the row count to be real.

I think this is a poor choice of wording. Autovacuum frequently creates
log entries that read something like:

LOG: autovacuum: processing database "foo"

This implies that autovacuum has done something to that database, which
may or may not be the case. All this message really means is that
autovacuum took at look at this database to see if there was any VACUUM
or ANALYZE operations that should be performed. I think saying
"processing" implies that some action is being taken.

Also this creates a lot of noise in the log files. I think it would be
better to downgrade this message to a NOTICE or even a DEBUG, and
replace it with a LOG level message that states when action has taken
place against the table.

Matt

"Matthew T. O'Connor" <matthew@zeut.net> writes:

LOG: autovacuum: processing database "foo"

Also this creates a lot of noise in the log files. I think it would be
better to downgrade this message to a NOTICE or even a DEBUG, and
replace it with a LOG level message that states when action has taken
place against the table.

I agree that the "processing database" message isn't too exciting, but
it seems that forcing per-table messages up to LOG level would create
even more log clutter. I could support "processing table" at level
DEBUG1 and "processing database" at DEBUG2. Or maybe we should think
harder about the idea recently mentioned of letting the autovacuum
process have its own log-level setting, separate from ordinary backends'
setting.

regards, tom lane

FWIW, people generally refer to that as 'loading data'; I've never heard
of 'upsizing' before, which is why I was somewhat confused.

I'm sorry.
I defined upsizing as creating new postgres database from some other
database data.

Google search for upsize returns the titles :

Upsize your Access database with SQL Server Data Transformation ...
Microsoft Office Assistance: Upsize your Microsoft Access database
Prestwood MS SQL 2000 Upsize Kit, buy, purchase

It seems that this term is used in Microsoft desktop databases (FoxPro,
Access) for moving them to the SQL server. Both of them have upsizing
wizards.
So it is term created by Microsoft only.

I'm using PG 8.1 default postgres.conf file.

Well, by default autovacuum is turned off. Aside from that there's other
things you'll want to tune. Take a look at the annotated postgresql.conf
that's in techdocs.

No. autovacuum is turned ON by default in 8.1 XP

I read from the docs you mentioned that Postgres has low maintenance needs
compared to other databases. So I'm expecting that there is no need to tune
something.

I read than autovacuum may skip some tables which can cause wrong guru
hints.

So it seems that I should run manually VACUUM ANALYZE for the whole
database
once for initial statistics colection of those tables, isn't it?

No real need to vacuum, just running analyze on the entire database
would suffice.

Thank you. I now send ANALYZE command to Postgres after data loading is
completed.

Andrus.

I agree that the "processing database" message isn't too exciting, but
it seems that forcing per-table messages up to LOG level would create
even more log clutter. I could support "processing table" at level
DEBUG1 and "processing database" at DEBUG2. Or maybe we should think
harder about the idea recently mentioned of letting the autovacuum
process have its own log-level setting, separate from ordinary backends'
setting.

I would be helpful that the message gives the result also, like

23 tables in database 'foo' processed, 45 tables skipped, 12345 dead tuples
removed in 45 seconds

Andrus.

On Wed, Nov 23, 2005 at 04:56:58PM +0200, Andrus wrote:

No. autovacuum is turned ON by default in 8.1 XP

Hrm, interesting that it's different than on Unix.

I read from the docs you mentioned that Postgres has low maintenance needs
compared to other databases. So I'm expecting that there is no need to tune
something.

Initial tuning != maintenance. Many of PostgreSQL's default settings are
extremely conservative and will benefit from being increased on almost
any hardware. There's extensive discussion of this to be found in the
pgsql-performance archives, but take a look at shared_buffers and
work_memory at a minimum.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

No. autovacuum is turned ON by default in 8.1 XP

Hrm, interesting that it's different than on Unix.

Why major functionality is configured differently in different platforms ?
This increases the cost of initial tuning when mixed platforms are used.

Initial tuning != maintenance. Many of PostgreSQL's default settings are
extremely conservative and will benefit from being increased on almost
any hardware. There's extensive discussion of this to be found in the
pgsql-performance archives, but take a look at shared_buffers and
work_memory at a minimum.

I need to create installation for dumb users to ship DBMS with my
application.
So manual initial tuning is not possible.

How to force Postgres to use reliable settings by default?

How to use

shared_buffers=auto
work_memory =auto

in conf file ?

Andrus.

On Wednesday 30 November 2005 12:12, Andrus wrote:

No. autovacuum is turned ON by default in 8.1 XP

Hrm, interesting that it's different than on Unix.

Why major functionality is configured differently in different platforms ?
This increases the cost of initial tuning when mixed platforms are used.

Yeah, that's odd. It's really more of a packaging decision though.

Initial tuning != maintenance. Many of PostgreSQL's default settings are
extremely conservative and will benefit from being increased on almost
any hardware. There's extensive discussion of this to be found in the
pgsql-performance archives, but take a look at shared_buffers and
work_memory at a minimum.

I need to create installation for dumb users to ship DBMS with my
application.
So manual initial tuning is not possible.

Some tuning is... for instance during installation you should run vacuum
analyze after loading your data in.

How to force Postgres to use reliable settings by default?

How to use

shared_buffers=auto
work_memory =auto

It will dynamically allocate shared_buffers to a point, but you'll need to
figure out a way to auto tune these features yourself. It isn't an impossible
task in a controlled environment (use the annotated pg conf to find an
algorithm and then build a new conf file and copy it over before start up) so
you should have a chance, but it is complicated to do in an uncontrolled
environment, which is why it doesn't work that way as of yet.

BTW, if you have a good idea of the size of your data set, you might be able
to hardcode things. Ie. a desktop app is probably not going to need multi
gb / 100 user settings. HTH

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

I need to create installation for dumb users to ship DBMS with my
application.
So manual initial tuning is not possible.

Some tuning is... for instance during installation you should run vacuum
analyze after loading your data in.

My application runs ANALYZE command programmatically after data loading.
So end user does not need to run it.

Autovacuum is turned ON by default.
Why I should to run VACUUM ANALYZE in this case ?
ANALYZE must be sufficient.

Andrus.