Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-31 Thread Andrew Sullivan
On Fri, Mar 25, 2005 at 06:21:24PM -0500, Bruce Momjian wrote:
 
 Can we issue a LOCK TABLE with a statement_timeout, and only do the
 VACUUM FULL if we can get a lock quickly?  That seems like a plan.

I think someone else's remark in this thread is important, though:
autovacuum shouldn't ever block other transactions, and this approach
will definitely run that risk.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-27 Thread Matthew T. O'Connor
Well the simple answer is that pg_autovacuum didn't see 10,000 inserts 
updates or deletes.
pg_autovacuum saw:476095 - 471336 = 4759  U/D's relevant for 
vacuuming and
  634119 - 629121 = 4998  I/U/D's relevant for performing analyze.

The tough question is why is pg_autovacuum not seeing all the updates.  
Since autovacuum depends on the stats system for it's numbers, the most 
likely answer is that the stats system is not able to keep up with the 
workload, and is ignoring some of the updates.  Would you check to see 
what the stats system is reporting for numbers of I/U/D's for the 
file_92 table?  The query pg_autovacuum uses is:

select a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,
  b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
from pg_class a, pg_stat_all_tables b
where a.oid=b.relid and a.relkind = 'r'
Take a look at the n_tup_ins, upd, del numbers before and see if they 
are keeping up with the actual number if I/U/D's that you are 
performing.  If they are, then it's a pg_autovacuum problem that I will 
look into further, if they are not, then it's a stats system problem 
that I can't really help with.

Good luck,
Matthew
Otto Blomqvist wrote:
Hello !
I'm running pg_autovacuum on a 1GHz, 80Gig, 512Mhz machine. The database is
about 30MB tarred. We have about 5 Updates/Inserts/Deletes per day. It
runs beautifully for ~4 days. Then the HDD activity and the Postmaster CPU
usage goes up ALOT. Even though I have plenty (?) of FSM (2 million) pages.
I perform a vacuum and everything is back to normal for another 4 days. I
could schedule a manual vacuum each day but the util is not called
pg_SemiAutoVacuum so I'm hoping this is not necessary. The same user that
ran the manual vacuum is running pg_autovacuum. The normal CPU usage is
about 10% w/ little HD activity.
Im running autovacuum with the following flags -d 3 -v 300 -V 0.1 -s 180 -S
0.1 -a 200 -A 0.1
Below are some snipplets regarding vacuuming from the busiest table
This is the last VACUUM ANALYZE performed by pg_autovacuum before I ran the
manual vacuum
[2005-03-24 02:05:43 EST] DEBUG:Performing: VACUUM ANALYZE
public.file_92
[2005-03-24 02:05:52 EST] INFO: table name: secom.public.file_92
[2005-03-24 02:05:52 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-24 02:05:52 EST] INFO: reltuples: 106228.00;  relpages:
9131
[2005-03-24 02:05:52 EST] INFO: curr_analyze_count: 629121;
curr_vacuum_count: 471336
[2005-03-24 02:05:52 EST] INFO: last_analyze_count: 629121;
last_vacuum_count: 471336
[2005-03-24 02:05:52 EST] INFO: analyze_threshold: 10822;
vacuum_threshold: 10922
This is the last pg_autovacuum debug output before I ran the manual vacuum
[2005-03-24 09:18:44 EST] INFO: table name: secom.public.file_92
[2005-03-24 09:18:44 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-24 09:18:44 EST] INFO: reltuples: 106228.00;  relpages:
9131
[2005-03-24 09:18:44 EST] INFO: curr_analyze_count: 634119;
curr_vacuum_count: 476095
[2005-03-24 09:18:44 EST] INFO: last_analyze_count: 629121;
last_vacuum_count: 471336
[2005-03-24 09:18:44 EST] INFO: analyze_threshold: 10822;
vacuum_threshold: 10922
file_92 had about 1 Inserts/Deletes between 02:05  and 9:20
Then i Ran a vacuum verbose
23 Mar 05 - 9:20 AM
INFO:  vacuuming public.file_92
INFO:  index file_92_record_number_key now contains 94 row versions in
2720 pages
DETAIL:  107860 index row versions were removed.
2712 index pages have been deleted, 2060 are currently reusable.
CPU 0.22s/0.64u sec elapsed 8.45 sec.
INFO:  file_92: removed 107860 row versions in 9131 pages
DETAIL:  CPU 1.13s/4.27u sec elapsed 11.75 sec.
INFO:  file_92: found 107860 removable, 92 nonremovable row versions in
9131 pages
DETAIL:  91 dead row versions cannot be removed yet.
There were 303086 unused item pointers.
0 pages are entirely empty.
CPU 1.55s/5.00u sec elapsed 20.86 sec.
INFO:  file_92: truncated 9131 to 8423 pages
DETAIL:  CPU 0.65s/0.03u sec elapsed 5.80 sec.
INFO:  free space map: 57 relations, 34892 pages stored; 34464 total pages
needed
DETAIL:  Allocated FSM size: 1000 relations + 200 pages = 11784 kB
shared memory.
Also, file_92 is just a temporary storage area, for records waiting to be
processed. Records are in there typically ~10 sec.
Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could
autovacuum let this happen ? I would estimate the table had about 1
inserts/deletes between the last pg_autovacuum Vacuum analyze and my
manual vacuum verbose.
It is like the suction is not strong enough ;)
Any ideas ? It would be greatly appreciated as this is taking me one step
closer to the looney bin.
Thanks
/Otto Blomqvist

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
 

--

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
It looks like the reltuples-values are screwed up. Even though rows are
constantly being removed from the table the reltuples keep going up. If I
understand correctly that also makes the Vacuum threshold go up and we end
up in a vicious circle. Right after pg_autovacuum performed a vacuum analyze
on the table it actually had 31000 records, but reltuples reports over 100k.
I'm not sure if this means anything But i thought i would pass it along.

PG version 8.0.0, 31MB tarred DB.

[2005-03-25 09:16:14 EST] INFO:dbname: testing
[2005-03-25 09:16:14 EST] INFO:  oid: 9383816
[2005-03-25 09:16:14 EST] INFO:  username: (null)
[2005-03-25 09:16:14 EST] INFO:  password: (null)
[2005-03-25 09:16:14 EST] INFO:  conn is null, (not connected)
[2005-03-25 09:16:14 EST] INFO:  default_analyze_threshold: 1000
[2005-03-25 09:16:14 EST] INFO:  default_vacuum_threshold: 500


[2005-03-25 09:05:12 EST] INFO:  table name: secom.public.file_92
[2005-03-25 09:05:12 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 09:05:12 EST] INFO: reltuples: 49185.00;  relpages:
8423
[2005-03-25 09:05:12 EST] INFO: curr_analyze_count: 919274;
curr_vacuum_count: 658176
[2005-03-25 09:05:12 EST] INFO: last_analyze_count: 899272;
last_vacuum_count: 560541
[2005-03-25 09:05:12 EST] INFO: analyze_threshold: 49685;
vacuum_threshold: 100674


[2005-03-25 09:10:12 EST] DEBUG:   Performing: VACUUM ANALYZE
public.file_92
[2005-03-25 09:10:33 EST] INFO:  table name: secom.public.file_92
[2005-03-25 09:10:33 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 09:10:33 EST] INFO: reltuples: 113082.00;  relpages:
6624
[2005-03-25 09:10:33 EST] INFO: curr_analyze_count: 923820;
curr_vacuum_count: 662699
[2005-03-25 09:10:33 EST] INFO: last_analyze_count: 923820;
last_vacuum_count: 662699
[2005-03-25 09:10:33 EST] INFO: analyze_threshold: 113582;
vacuum_threshold: 227164


[2005-03-25 09:16:14 EST] INFO:  table name: secom.public.file_92
[2005-03-25 09:16:14 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 09:16:14 EST] INFO: reltuples: 113082.00;  relpages:
6624  -- Actually has 31k rows
[2005-03-25 09:16:14 EST] INFO: curr_analyze_count: 923820;
curr_vacuum_count: 662699
[2005-03-25 09:16:14 EST] INFO: last_analyze_count: 923820;
last_vacuum_count: 662699
[2005-03-25 09:16:14 EST] INFO: analyze_threshold: 113582;
vacuum_threshold: 227164

DETAIL:  Allocated FSM size: 1000 relations + 200 pages = 11784 kB
shared memory.




- Original Message -
From: Matthew T. O'Connor matthew@zeut.net
To: Otto Blomqvist [EMAIL PROTECTED];
pgsql-performance@postgresql.org
Sent: Thursday, March 24, 2005 3:58 PM
Subject: Re: [PERFORM] pg_autovacuum not having enough suction ?


 I would rather keep this on list since other people can chime in.

 Otto Blomqvist wrote:

 It does not seem to be a Stats collector problem.
 
oid   | relname | relnamespace | relpages | relisshared | reltuples |
 schemaname | n_tup_ins | n_tup_upd | n_tup_del

-+-+--+--+-+---+---
-
 +---+---+---
  9384219 | file_92 | 2200 | 8423 | f   | 49837 |
 public |158176 |318527 |158176
 (1 row)
 
 I insert 5 records
 
 secom=# select createfile_92records(1, 5);--- this is a pg
script
 that inserts records  1 threw 5.
  createfile_92records
 --
 0
 
 
oid   | relname | relnamespace | relpages | relisshared | reltuples |
 schemaname | n_tup_ins | n_tup_upd | n_tup_del

-+-+--+--+-+---+---
-
 +---+---+---
  9384219 | file_92 | 2200 | 8423 | f   | 49837 |
 public |208179 |318932 |158377
 (1 row)
 
 reltuples does not change ? Hmm. n_tup_ins looks fine.
 
 

 That is expected, reltuples only gets updated by a vacuum or an analyze.

 This table is basically a queue full of records waiting to get transfered
 over from our 68030 system to the PG database. The records are then moved
 into folders (using a trigger) like file_92_myy depending on what month
the
 record was created on the 68030. During normal operations there should
not
 be more than 10 records at a time in the table, although during the
course
 of a day a normal system will get about 50k records. I create 5
records
 to simulate incoming traffic, since we don't have much traffic in the
test
 lab.
 
 After a few hours we have
 
 secom=# select count(*) from file_92;
  count
 ---
  42072
 
 So we have sent over approx 8000 Records.
 
oid   | relname | relnamespace | relpages | relisshared | reltuples |
 schemaname | n_tup_ins | n_tup_upd | n_tup_del

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Matthew T. O'Connor
hmm the value in reltuples should be accurate after a vacuum (or 
vacuum analyze) if it's not it's a vacuum bug or something is going on 
that isn't understood.  If you or pg_autovacuum are running plain 
analyze commands, that could explain the invalid reltules numbers.

Was reltuples = 113082 correct right after the vacuum? 

Matthew
Otto Blomqvist wrote:
It looks like the reltuples-values are screwed up. Even though rows are
constantly being removed from the table the reltuples keep going up. If I
understand correctly that also makes the Vacuum threshold go up and we end
up in a vicious circle. Right after pg_autovacuum performed a vacuum analyze
on the table it actually had 31000 records, but reltuples reports over 100k.
I'm not sure if this means anything But i thought i would pass it along.
PG version 8.0.0, 31MB tarred DB.
[2005-03-25 09:16:14 EST] INFO:dbname: testing
[2005-03-25 09:16:14 EST] INFO:  oid: 9383816
[2005-03-25 09:16:14 EST] INFO:  username: (null)
[2005-03-25 09:16:14 EST] INFO:  password: (null)
[2005-03-25 09:16:14 EST] INFO:  conn is null, (not connected)
[2005-03-25 09:16:14 EST] INFO:  default_analyze_threshold: 1000
[2005-03-25 09:16:14 EST] INFO:  default_vacuum_threshold: 500
[2005-03-25 09:05:12 EST] INFO:  table name: secom.public.file_92
[2005-03-25 09:05:12 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 09:05:12 EST] INFO: reltuples: 49185.00;  relpages:
8423
[2005-03-25 09:05:12 EST] INFO: curr_analyze_count: 919274;
curr_vacuum_count: 658176
[2005-03-25 09:05:12 EST] INFO: last_analyze_count: 899272;
last_vacuum_count: 560541
[2005-03-25 09:05:12 EST] INFO: analyze_threshold: 49685;
vacuum_threshold: 100674
[2005-03-25 09:10:12 EST] DEBUG:   Performing: VACUUM ANALYZE
public.file_92
[2005-03-25 09:10:33 EST] INFO:  table name: secom.public.file_92
[2005-03-25 09:10:33 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 09:10:33 EST] INFO: reltuples: 113082.00;  relpages:
6624
[2005-03-25 09:10:33 EST] INFO: curr_analyze_count: 923820;
curr_vacuum_count: 662699
[2005-03-25 09:10:33 EST] INFO: last_analyze_count: 923820;
last_vacuum_count: 662699
[2005-03-25 09:10:33 EST] INFO: analyze_threshold: 113582;
vacuum_threshold: 227164
[2005-03-25 09:16:14 EST] INFO:  table name: secom.public.file_92
[2005-03-25 09:16:14 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 09:16:14 EST] INFO: reltuples: 113082.00;  relpages:
6624  -- Actually has 31k rows
[2005-03-25 09:16:14 EST] INFO: curr_analyze_count: 923820;
curr_vacuum_count: 662699
[2005-03-25 09:16:14 EST] INFO: last_analyze_count: 923820;
last_vacuum_count: 662699
[2005-03-25 09:16:14 EST] INFO: analyze_threshold: 113582;
vacuum_threshold: 227164
DETAIL:  Allocated FSM size: 1000 relations + 200 pages = 11784 kB
shared memory.

- Original Message -
From: Matthew T. O'Connor matthew@zeut.net
To: Otto Blomqvist [EMAIL PROTECTED];
pgsql-performance@postgresql.org
Sent: Thursday, March 24, 2005 3:58 PM
Subject: Re: [PERFORM] pg_autovacuum not having enough suction ?
 

I would rather keep this on list since other people can chime in.
Otto Blomqvist wrote:
   

It does not seem to be a Stats collector problem.
 oid   | relname | relnamespace | relpages | relisshared | reltuples |
schemaname | n_tup_ins | n_tup_upd | n_tup_del
 

-+-+--+--+-+---+---
   

-
 

+---+---+---
9384219 | file_92 | 2200 | 8423 | f   | 49837 |
public |158176 |318527 |158176
(1 row)
I insert 5 records
secom=# select createfile_92records(1, 5);--- this is a pg
 

script
 

that inserts records  1 threw 5.
createfile_92records
--
  0
 oid   | relname | relnamespace | relpages | relisshared | reltuples |
schemaname | n_tup_ins | n_tup_upd | n_tup_del
 

-+-+--+--+-+---+---
   

-
 

+---+---+---
9384219 | file_92 | 2200 | 8423 | f   | 49837 |
public |208179 |318932 |158377
(1 row)
reltuples does not change ? Hmm. n_tup_ins looks fine.
 

That is expected, reltuples only gets updated by a vacuum or an analyze.
   

This table is basically a queue full of records waiting to get transfered
over from our 68030 system to the PG database. The records are then moved
into folders (using a trigger) like file_92_myy depending on what month
 

the
 

record was created on the 68030. During normal operations there should
 

not
 

be more than 10 records at a time in the table, although during the
 

course
 

of a day a normal system will get about 50k records. I create 5
 

records
 

to simulate incoming traffic, since we

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Tom Lane
Matthew T. O'Connor matthew@zeut.net writes:
 hmm the value in reltuples should be accurate after a vacuum (or 
 vacuum analyze) if it's not it's a vacuum bug or something is going on 
 that isn't understood.  If you or pg_autovacuum are running plain 
 analyze commands, that could explain the invalid reltules numbers.

 Was reltuples = 113082 correct right after the vacuum? 

Another thing to check is whether the reltuples (and relpages!) that
autovacuum is reporting are the same as what's actually in the pg_class
row for the relation.  I'm wondering if this could be a similar issue
to the old autovac bug where it wasn't reading the value correctly.

If they are the same then it seems like it must be a backend issue.

One thing that is possibly relevant here is that in 8.0 a plain VACUUM
doesn't set reltuples to the exactly correct number, but to an
interpolated value that reflects our estimate of the steady state
average between vacuums.  I wonder if that code is wrong, or if it's
operating as designed but is confusing autovac.

Can autovac be told to run the vacuums in VERBOSE mode?  It would be
useful to compare what VERBOSE has to say to the changes in
reltuples/relpages.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
 Was reltuples = 113082 correct right after the vacuum?

No, There where about 31000 rows after the vacuum. I'm no expert but tuples
= rows, right ?

This is not a normal table though, in the sence that it is only a
temporary holding ground as I explained earlier. I create 5 records and
these get sent over from our custom 68030 system, to tables like
file_92_myy, depending on the date of the record.  A pl/pgsql script is used
as a trigger to move the records after they get data from the 68030. Don't
know if that is of interest or not. I could post the trigger if you'd like.


Matthew T. O'Connor matthew@zeut.net wrote in message
news:[EMAIL PROTECTED]
 hmm the value in reltuples should be accurate after a vacuum (or
 vacuum analyze) if it's not it's a vacuum bug or something is going on
 that isn't understood.  If you or pg_autovacuum are running plain
 analyze commands, that could explain the invalid reltules numbers.

 Was reltuples = 113082 correct right after the vacuum?

 Matthew


 Otto Blomqvist wrote:

 It looks like the reltuples-values are screwed up. Even though rows are
 constantly being removed from the table the reltuples keep going up. If I
 understand correctly that also makes the Vacuum threshold go up and we
end
 up in a vicious circle. Right after pg_autovacuum performed a vacuum
analyze
 on the table it actually had 31000 records, but reltuples reports over
100k.
 I'm not sure if this means anything But i thought i would pass it along.
 
 PG version 8.0.0, 31MB tarred DB.
 
 [2005-03-25 09:16:14 EST] INFO:dbname: testing
 [2005-03-25 09:16:14 EST] INFO:  oid: 9383816
 [2005-03-25 09:16:14 EST] INFO:  username: (null)
 [2005-03-25 09:16:14 EST] INFO:  password: (null)
 [2005-03-25 09:16:14 EST] INFO:  conn is null, (not connected)
 [2005-03-25 09:16:14 EST] INFO:  default_analyze_threshold: 1000
 [2005-03-25 09:16:14 EST] INFO:  default_vacuum_threshold: 500
 
 
 [2005-03-25 09:05:12 EST] INFO:  table name: secom.public.file_92
 [2005-03-25 09:05:12 EST] INFO: relid: 9384219;   relisshared: 0
 [2005-03-25 09:05:12 EST] INFO: reltuples: 49185.00;
relpages:
 8423
 [2005-03-25 09:05:12 EST] INFO: curr_analyze_count: 919274;
 curr_vacuum_count: 658176
 [2005-03-25 09:05:12 EST] INFO: last_analyze_count: 899272;
 last_vacuum_count: 560541
 [2005-03-25 09:05:12 EST] INFO: analyze_threshold: 49685;
 vacuum_threshold: 100674
 
 
 [2005-03-25 09:10:12 EST] DEBUG:   Performing: VACUUM ANALYZE
 public.file_92
 [2005-03-25 09:10:33 EST] INFO:  table name: secom.public.file_92
 [2005-03-25 09:10:33 EST] INFO: relid: 9384219;   relisshared: 0
 [2005-03-25 09:10:33 EST] INFO: reltuples: 113082.00;
relpages:
 6624
 [2005-03-25 09:10:33 EST] INFO: curr_analyze_count: 923820;
 curr_vacuum_count: 662699
 [2005-03-25 09:10:33 EST] INFO: last_analyze_count: 923820;
 last_vacuum_count: 662699
 [2005-03-25 09:10:33 EST] INFO: analyze_threshold: 113582;
 vacuum_threshold: 227164
 
 
 [2005-03-25 09:16:14 EST] INFO:  table name: secom.public.file_92
 [2005-03-25 09:16:14 EST] INFO: relid: 9384219;   relisshared: 0
 [2005-03-25 09:16:14 EST] INFO: reltuples: 113082.00;
relpages:
 6624  -- Actually has 31k rows
 [2005-03-25 09:16:14 EST] INFO: curr_analyze_count: 923820;
 curr_vacuum_count: 662699
 [2005-03-25 09:16:14 EST] INFO: last_analyze_count: 923820;
 last_vacuum_count: 662699
 [2005-03-25 09:16:14 EST] INFO: analyze_threshold: 113582;
 vacuum_threshold: 227164
 
 DETAIL:  Allocated FSM size: 1000 relations + 200 pages = 11784 kB
 shared memory.
 
 
 
 
 - Original Message -
 From: Matthew T. O'Connor matthew@zeut.net
 To: Otto Blomqvist [EMAIL PROTECTED];
 pgsql-performance@postgresql.org
 Sent: Thursday, March 24, 2005 3:58 PM
 Subject: Re: [PERFORM] pg_autovacuum not having enough suction ?
 
 
 
 
 I would rather keep this on list since other people can chime in.
 
 Otto Blomqvist wrote:
 
 
 
 It does not seem to be a Stats collector problem.
 
   oid   | relname | relnamespace | relpages | relisshared | reltuples |
 schemaname | n_tup_ins | n_tup_upd | n_tup_del
 
 

-+-+--+--+-+---+--
-
 
 
 -
 
 
 +---+---+---
 9384219 | file_92 | 2200 | 8423 | f   | 49837 |
 public |158176 |318527 |158176
 (1 row)
 
 I insert 5 records
 
 secom=# select createfile_92records(1, 5);--- this is a pg
 
 
 script
 
 
 that inserts records  1 threw 5.
 createfile_92records
 --
0
 
 
   oid   | relname | relnamespace | relpages | relisshared | reltuples |
 schemaname | n_tup_ins | n_tup_upd | n_tup_del
 
 

-+-+--+--+-+---+--
-
 
 
 -
 
 
 +---+---+---
 9384219

lazy_update_relstats considered harmful (was Re: [PERFORM] pg_autovacuum not having enough suction ?)

2005-03-25 Thread Tom Lane
I wrote:
 One thing that is possibly relevant here is that in 8.0 a plain VACUUM
 doesn't set reltuples to the exactly correct number, but to an
 interpolated value that reflects our estimate of the steady state
 average between vacuums.  I wonder if that code is wrong, or if it's
 operating as designed but is confusing autovac.

Now that I think it over, I'm thinking that I must have been suffering
severe brain fade the day I wrote lazy_update_relstats() (see
vacuumlazy.c).  The numbers that that routine is averaging are the pre-
and post-vacuum physical tuple counts.  But the difference between them
consists of known-dead tuples, and we shouldn't be factoring dead tuples
into reltuples.  The planner has always considered reltuples to count
only live tuples, and I think this is correct on two grounds:

1. The numbers of tuples estimated to be returned by scans certainly
shouldn't count dead ones.

2. Dead tuples don't have that much influence on scan costs either, at
least not once they are marked as known-dead.  Certainly they shouldn't
be charged at full freight.

It's possible that there'd be some value in adding a column to pg_class
to record dead tuple count, but given what we have now, the calculation
in lazy_update_relstats is totally wrong.

The idea I was trying to capture is that the tuple density is at a
minimum right after VACUUM, and will increase as free space is filled
in until the next VACUUM, so that recording the exact tuple count
underestimates the number of tuples that will be seen on-the-average.
But I'm not sure that idea really holds water.  The only way that a
table can be at steady state over a long period is if the number of
live tuples remains roughly constant (ie, inserts balance deletes).
What actually increases and decreases over a VACUUM cycle is the density
of *dead* tuples ... but per the above arguments this isn't something
we should adjust reltuples for.

So I'm thinking lazy_update_relstats should be ripped out and we should
go back to recording just the actual stats.

Sound reasonable?  Or was I right the first time and suffering brain
fade today?

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist

 Another thing to check is whether the reltuples (and relpages!) that
 autovacuum is reporting are the same as what's actually in the pg_class
 row for the relation.  I'm wondering if this could be a similar issue
 to the old autovac bug where it wasn't reading the value correctly.

These values where extracted at roughly the same time.

 relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
-+--+-+--+---+-+
---+--+---+---+---+-
+-+-+--+---+-+--+---
---+-+++-++-
---
 file_92 | 2200 | 9384220 |  100 | 0 | 9384219 |
0 | 6624 |113082 | 0 | 0 | t   | f
| r   |   23 | 0 |   1 |0 |0 |
0 | t  | f  | f   | f  |
(1 row)

secom=# select count(*) from file_92;
 count
---
 17579
(1 row)

[2005-03-25 12:16:32 EST] INFO:  table name: secom.public.file_92
[2005-03-25 12:16:32 EST] INFO: relid: 9384219;   relisshared: 0
[2005-03-25 12:16:32 EST] INFO: reltuples: 113082.00;  relpages:
6624
[2005-03-25 12:16:32 EST] INFO: curr_analyze_count: 993780;
curr_vacuum_count: 732470
[2005-03-25 12:16:32 EST] INFO: last_analyze_count: 923820;
last_vacuum_count: 662699
[2005-03-25 12:16:32 EST] INFO: analyze_threshold: 113582;
vacuum_threshold: 227164


Hope this helps, if there is anything else I can do please let me know.


 If they are the same then it seems like it must be a backend issue.

 One thing that is possibly relevant here is that in 8.0 a plain VACUUM
 doesn't set reltuples to the exactly correct number, but to an
 interpolated value that reflects our estimate of the steady state
 average between vacuums.  I wonder if that code is wrong, or if it's
 operating as designed but is confusing autovac.


This average steady state value might be hard to interpolete in this case
since this is only a temporary holding place for the records ..? Normaly the
table has  10 records in it at the same time. In the lab we create a
lump-traffic by sending over 5 Records. It takes about 20 hours to
transfer over all of the 50k records.





---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Matthew T. O'Connor
Tom Lane wrote:
Matthew T. O'Connor matthew@zeut.net writes:
 

hmm the value in reltuples should be accurate after a vacuum (or 
vacuum analyze) if it's not it's a vacuum bug or something is going on 
that isn't understood.  If you or pg_autovacuum are running plain 
analyze commands, that could explain the invalid reltules numbers.
   

Was reltuples = 113082 correct right after the vacuum? 
   

Another thing to check is whether the reltuples (and relpages!) that
autovacuum is reporting are the same as what's actually in the pg_class
row for the relation.  I'm wondering if this could be a similar issue
to the old autovac bug where it wasn't reading the value correctly.
 

I don't think so, as he did some manual selects from pg_class and 
pg_stat_all in one of the emails he sent that were showing similar 
numbers to what autovac was reporting.

If they are the same then it seems like it must be a backend issue.
One thing that is possibly relevant here is that in 8.0 a plain VACUUM
doesn't set reltuples to the exactly correct number, but to an
interpolated value that reflects our estimate of the steady state
average between vacuums.  I wonder if that code is wrong, or if it's
operating as designed but is confusing autovac.
 

Ahh  Now that you mention it, I do remember the discussion during 
8.0 development.  This sounds very much like the cause of the problem.  
Autovac is not vacuuming often enough for this table because reltuples 
is telling autovac that there are alot more tuples in this table than 
there really are. 

Really this is just another case of the more general problem with 
autovac as it stands now.  That is, you can't set vacuum thresholds on a 
per table basis, and databases like this can't survive with a one size 
fits all threshold.  I would suggest that Otto perform regular cron 
based vacuums of this one table in addition to autovac, that is what 
several people I have heard from in the field are doing.

Come hell or high water I'm gonna get autovac integrated into 8.1, at 
which point per table thresholds would be easy todo.

Can autovac be told to run the vacuums in VERBOSE mode?  It would be
useful to compare what VERBOSE has to say to the changes in
reltuples/relpages.
Not as it stands now.  That would be an interesting feature for 
debugging purposes though.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: lazy_update_relstats considered harmful (was Re: [PERFORM] pg_autovacuum not having enough suction ?)

2005-03-25 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
 On Fri, 2005-03-25 at 15:22 -0500, Tom Lane wrote:
 2. Dead tuples don't have that much influence on scan costs either, at
 least not once they are marked as known-dead.  Certainly they shouldn't
 be charged at full freight.

 Yes, minor additional CPU time, but the main issue is when the dead
 tuples force additional I/O.

I/O costs are mostly estimated off relpages, though, not reltuples.
The only time you really pay through the nose for a dead tuple is when
an indexscan visits it, but with the known-dead marking we now do in
btree indexes, I'm pretty sure that path is seldom taken.

 It's possible that there'd be some value in adding a column to pg_class
 to record dead tuple count, but given what we have now, the calculation
 in lazy_update_relstats is totally wrong.

 Yes, thats the way. We can record the (averaged?) dead tuple count, but
 also record the actual row count in reltuples.

What I'd be inclined to record is the actual number of dead rows removed
by the most recent VACUUM.  Any math on that is best done in the
planner, since we can change the logic more easily than the database
contents.  It'd probably be reasonable to take half of that number as
the estimate of the average number of dead tuples.

But in any case, that's for the future; we can't have it in 8.0.*, and
right at the moment I'm focusing on what to push out for 8.0.2.

 We definitely need to record the physical and logical tuple counts,
 since each of them have different contributions to run-times.

There isn't any difference, if you are talking about fully dead tuples.
It would be possible for VACUUM to also count the number of
not-committed-but-not-removable tuples (ie, new from still-open
transactions, plus dead-but-still-visible-to-somebody), but I'm not sure
that it would be useful to do so, because that sort of count is hugely
transient.  The stat would be irrelevant moments after it was taken.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Tom Lane
 Otto Blomqvist wrote:
 This table is basically a queue full of records waiting to get transfered
 over from our 68030 system to the PG database. The records are then moved
 into folders (using a trigger) like file_92_myy depending on what month the
 record was created on the 68030. During normal operations there should not
 be more than 10 records at a time in the table, although during the course
 of a day a normal system will get about 50k records. I create 5 records
 to simulate incoming traffic, since we don't have much traffic in the test
 lab.

Really the right way to do housekeeping for a table like that is to
VACUUM FULL (or better yet, TRUNCATE, if possible) immediately after
discarding a batch of records.  The VACUUM FULL will take very little
time if it only has to repack 10 records.  Plain VACUUM is likely to
leave the table nearly empty but physically sizable, which is bad news
from a statistical point of view: as the table fills up again, it won't
get physically larger, thereby giving the planner no clue that it
doesn't still have 10 records.  This means the queries that process
the 50K-record patch are going to get horrible plans :-(

I'm not sure if autovacuum could be taught to do that --- it could
perhaps launch a vacuum as soon as it notices a large fraction of the
table got deleted, but do we really want to authorize it to launch
VACUUM FULL?  It'd be better to issue the vacuum synchronously
as part of the batch updating script, I feel.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Joshua D. Drake

  I'm not sure if autovacuum could be taught to do that --- it could
  perhaps launch a vacuum as soon as it notices a large fraction of the
  table got deleted, but do we really want to authorize it to launch
  VACUUM FULL?  It'd be better to issue the vacuum synchronously
  as part of the batch updating script, I feel.
 
 I added this to the TODO section for autovacuum:
 
 o Do VACUUM FULL if table is nearly empty?

We should never automatically launch a vacuum full. That seems like a
really bad idea.

Sincerely,

Joshua D. Drake


 
 I don't think autovacuum is every going to be smart enough to recycle
 during the delete, especially since the rows can't be reused until the
 transaction completes.
 
 One problem with VACUUM FULL would be autovacuum waiting for an
 exclusive lock on the table.  Anyway, it is documented now as a possible
 issue.
 
-- 
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/


---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Tom Lane
Bruce Momjian pgman@candle.pha.pa.us writes:
 Tom Lane wrote:
 I'm not sure if autovacuum could be taught to do that --- it could
 perhaps launch a vacuum as soon as it notices a large fraction of the
 table got deleted, but do we really want to authorize it to launch
 VACUUM FULL?

 One problem with VACUUM FULL would be autovacuum waiting for an
 exclusive lock on the table.  Anyway, it is documented now as a possible
 issue.

I don't care too much about autovacuum waiting awhile to get a lock.
I do care about other processes getting queued up behind it, though.

Perhaps it would be possible to alter the normal lock queuing semantics
for this case, so that autovacuum's request doesn't block later
arrivals, and it can only get the lock when no one is interested in the
table.  Of course, that might never happen, or by the time it does
there's no point in VACUUM FULL anymore :-(

regards, tom lane

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


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Bruce Momjian
Tom Lane wrote:
  Otto Blomqvist wrote:
  This table is basically a queue full of records waiting to get transfered
  over from our 68030 system to the PG database. The records are then moved
  into folders (using a trigger) like file_92_myy depending on what month the
  record was created on the 68030. During normal operations there should not
  be more than 10 records at a time in the table, although during the course
  of a day a normal system will get about 50k records. I create 5 records
  to simulate incoming traffic, since we don't have much traffic in the test
  lab.
 
 Really the right way to do housekeeping for a table like that is to
 VACUUM FULL (or better yet, TRUNCATE, if possible) immediately after
 discarding a batch of records.  The VACUUM FULL will take very little
 time if it only has to repack 10 records.  Plain VACUUM is likely to
 leave the table nearly empty but physically sizable, which is bad news
 from a statistical point of view: as the table fills up again, it won't
 get physically larger, thereby giving the planner no clue that it
 doesn't still have 10 records.  This means the queries that process
 the 50K-record patch are going to get horrible plans :-(
 
 I'm not sure if autovacuum could be taught to do that --- it could
 perhaps launch a vacuum as soon as it notices a large fraction of the
 table got deleted, but do we really want to authorize it to launch
 VACUUM FULL?  It'd be better to issue the vacuum synchronously
 as part of the batch updating script, I feel.

I added this to the TODO section for autovacuum:

o Do VACUUM FULL if table is nearly empty?

I don't think autovacuum is every going to be smart enough to recycle
during the delete, especially since the rows can't be reused until the
transaction completes.

One problem with VACUUM FULL would be autovacuum waiting for an
exclusive lock on the table.  Anyway, it is documented now as a possible
issue.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  Tom Lane wrote:
  I'm not sure if autovacuum could be taught to do that --- it could
  perhaps launch a vacuum as soon as it notices a large fraction of the
  table got deleted, but do we really want to authorize it to launch
  VACUUM FULL?
 
  One problem with VACUUM FULL would be autovacuum waiting for an
  exclusive lock on the table.  Anyway, it is documented now as a possible
  issue.
 
 I don't care too much about autovacuum waiting awhile to get a lock.
 I do care about other processes getting queued up behind it, though.
 
 Perhaps it would be possible to alter the normal lock queuing semantics
 for this case, so that autovacuum's request doesn't block later
 arrivals, and it can only get the lock when no one is interested in the
 table.  Of course, that might never happen, or by the time it does
 there's no point in VACUUM FULL anymore :-(

Can we issue a LOCK TABLE with a statement_timeout, and only do the
VACUUM FULL if we can get a lock quickly?  That seems like a plan.

The only problem is that you can't VACUUM FULL in a transaction:

test= create table test (x int);
CREATE TABLE
test= insert into test values (1);
INSERT 0 1
test= begin;
BEGIN
test= lock table test;
LOCK TABLE
test= vacuum full;
ERROR:  VACUUM cannot run inside a transaction block

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
ok, Thanks a lot for your time guys  ! I guess my table is pretty unusual
and thats why this problem has not surfaced until now. Better late then
never ;) I'll cron a manual vacuum full on the table.



Tom Lane [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
  Otto Blomqvist wrote:
  This table is basically a queue full of records waiting to get
transfered
  over from our 68030 system to the PG database. The records are then
moved
  into folders (using a trigger) like file_92_myy depending on what month
the
  record was created on the 68030. During normal operations there should
not
  be more than 10 records at a time in the table, although during the
course
  of a day a normal system will get about 50k records. I create 5
records
  to simulate incoming traffic, since we don't have much traffic in the
test
  lab.

 Really the right way to do housekeeping for a table like that is to
 VACUUM FULL (or better yet, TRUNCATE, if possible) immediately after
 discarding a batch of records.  The VACUUM FULL will take very little
 time if it only has to repack 10 records.  Plain VACUUM is likely to
 leave the table nearly empty but physically sizable, which is bad news
 from a statistical point of view: as the table fills up again, it won't
 get physically larger, thereby giving the planner no clue that it
 doesn't still have 10 records.  This means the queries that process
 the 50K-record patch are going to get horrible plans :-(

 I'm not sure if autovacuum could be taught to do that --- it could
 perhaps launch a vacuum as soon as it notices a large fraction of the
 table got deleted, but do we really want to authorize it to launch
 VACUUM FULL?  It'd be better to issue the vacuum synchronously
 as part of the batch updating script, I feel.

 regards, tom lane

 ---(end of broadcast)---
 TIP 2: you can get off all lists at once with the unregister command
 (send unregister YourEmailAddressHere to [EMAIL PROTECTED])




---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Tom Lane
Otto Blomqvist [EMAIL PROTECTED] writes:
 Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could
 autovacuum let this happen ?

What PG version is this?

(The earlier autovacuum releases had some bugs with large tables, thus
the question...)

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Otto Blomqvist
Sorry about that. I'm Running 8.0.0 on Linux Redhat 8.0


Tom Lane [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 Otto Blomqvist [EMAIL PROTECTED] writes:
  Over 100'000 Index Rows removed, 300'000 unused item pointers ? How
could
  autovacuum let this happen ?

 What PG version is this?

 (The earlier autovacuum releases had some bugs with large tables, thus
 the question...)

 regards, tom lane

 ---(end of broadcast)---
 TIP 6: Have you searched our list archives?

http://archives.postgresql.org




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Matthew T. O'Connor
The version that shipped with 8.0 should be fine.  The only version that 
had the problem Tom referred to are in the early 7.4.x releases. 

Did you get my other message about information from the stats system 
(I'm not sure why my other post has yet to show up on the performance 
list).

Matthew
Otto Blomqvist wrote:
Sorry about that. I'm Running 8.0.0 on Linux Redhat 8.0
Tom Lane [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 

Otto Blomqvist [EMAIL PROTECTED] writes:
   

Over 100'000 Index Rows removed, 300'000 unused item pointers ? How
 

could
 

autovacuum let this happen ?
 

What PG version is this?
(The earlier autovacuum releases had some bugs with large tables, thus
the question...)
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
   


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
 

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Matthew T. O'Connor
I would rather keep this on list since other people can chime in.
Otto Blomqvist wrote:
It does not seem to be a Stats collector problem.
  oid   | relname | relnamespace | relpages | relisshared | reltuples |
schemaname | n_tup_ins | n_tup_upd | n_tup_del
-+-+--+--+-+---+
+---+---+---
9384219 | file_92 | 2200 | 8423 | f   | 49837 |
public |158176 |318527 |158176
(1 row)
I insert 5 records
secom=# select createfile_92records(1, 5);--- this is a pg script
that inserts records  1 threw 5.
createfile_92records
--
   0
  oid   | relname | relnamespace | relpages | relisshared | reltuples |
schemaname | n_tup_ins | n_tup_upd | n_tup_del
-+-+--+--+-+---+
+---+---+---
9384219 | file_92 | 2200 | 8423 | f   | 49837 |
public |208179 |318932 |158377
(1 row)
reltuples does not change ? Hmm. n_tup_ins looks fine.
 

That is expected, reltuples only gets updated by a vacuum or an analyze.
This table is basically a queue full of records waiting to get transfered
over from our 68030 system to the PG database. The records are then moved
into folders (using a trigger) like file_92_myy depending on what month the
record was created on the 68030. During normal operations there should not
be more than 10 records at a time in the table, although during the course
of a day a normal system will get about 50k records. I create 5 records
to simulate incoming traffic, since we don't have much traffic in the test
lab.
After a few hours we have
secom=# select count(*) from file_92;
count
---
42072
So we have sent over approx 8000 Records.
  oid   | relname | relnamespace | relpages | relisshared | reltuples |
schemaname | n_tup_ins | n_tup_upd | n_tup_del
-+-+--+--+-+---+
+---+---+---
9384219 | file_92 | 2200 | 8423 | f   | 49837 |
public |208218 |334521 |166152
(1 row)
n_tup_upd: 318932 + (5-42072)*2 = 334788 pretty close.   (Each record
gets updated twice, then moved)
n_tup_del: 158377 + (5-42072) = 166305 pretty close. (there are also
minor background traffic going on)
I could send over the full vacuum verbose capture as well as the autovacuum
capture if that is of interest.
That might be helpful.  I don't see a stats system problem here, but I 
also haven't heard of any autovac problems recently, so this might be 
something new.

Thanks,
Matthew O'Connor

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org