From: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
---|---|
To: | Otto Blomqvist <o(dot)blomqvist(at)secomintl(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: pg_autovacuum not having enough suction ? |
Date: | 2005-03-24 23:58:00 |
Message-ID: | 42435408.30303@zeut.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
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 50000 records
>
>secom=# select createfile_92records(1, 50000); <--- this is a pg script
>that inserts records 1 threw 50000.
> 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 50000 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 + (50000-42072)*2 = 334788 pretty close. (Each record
>gets updated twice, then moved)
>n_tup_del: 158377 + (50000-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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-25 00:01:54 | Re: Upcoming 8.0.2 Release |
Previous Message | Matthew T. O'Connor | 2005-03-24 23:40:44 | Re: pg_autovacuum not having enough suction ? |
From | Date | Subject | |
---|---|---|---|
Next Message | Karim Nassar | 2005-03-25 00:10:57 | Delete query takes exorbitant amount of time |
Previous Message | Matthew T. O'Connor | 2005-03-24 23:40:44 | Re: pg_autovacuum not having enough suction ? |