Thread: Why pgAdmin III guru suggests VACUUM in 8.1
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.
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.