Is there any data corruption/damage to the database if we forcefully
kill autovacuum using cron job (if it is running longer than a
predefined time frame).


Regards
Sachchida


-----Original Message-----
From: Sachchida Ojha
Sent: Tuesday, August 21, 2007 5:46 PM
To: 'Michael Glaesemann'
Cc: 'pgsql-performance@postgresql.org'
Subject: RE: [PERFORM] Autovacuum is running forever

Our model is to provode black box solutions to our customer.

Black box, I mean application system, web sever and database is running
on the same machine.

We are running our sensor on 10 assets (windows machine) and sending
asset data to the server at every 15 minutes. There are some other user
operations going on to those assets at the same time.

On server

Cpu util ranging from 10-75%
Mem util ranging from 15-50%




Regards
Sachchida


-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Tuesday, August 21, 2007 5:36 PM
To: Sachchida Ojha
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Autovacuum is running forever


On Aug 21, 2007, at 16:26 , Sachchida Ojha wrote:
> In my OLTP system, we are inserting, updating and deleting the data
> every second.
>
> Autovacuum started and never finished slowing down the whole system.
There's the possibility that your autovacuum settings aren't aggressive
enough for your system, so it's never able to catch up.
Without knowing details it's hard to say for certain. What are your
autovacuum settings and other details about the load on your system?

Michael Glaesemann
grzm seespotcode net



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

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> Is there any data corruption/damage to the database if we forcefully
> kill autovacuum using cron job (if it is running longer than a
> predefined time frame).

Not really. but vacuum will just have to run that long again plus
some the next time it starts up.

Again, it's better to run vacuum more often not less often, and keep
the cost_delay high enough that it doesn't interfere with your I/O.
however, that will make it run even longer.

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

http://archives.postgresql.org

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> Is there any data corruption/damage to the database if we forcefully
> kill autovacuum using cron job (if it is running longer than a
> predefined time frame).

Oh, and I'd look at your I/O subsystem. You might want to look at
putting $300 hardware RAID cards with battery backed cache and 4 or so
disks in a RAID10 in them. It sounds to me like you could use more
I/O for your vacuuming. Vacuuming isn't CPU intensive, but it can be
I/O intensive.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

We are having only two disk (40GB each). One disk is used for OS, App
Server, and application. Second disk is used for postgresql database.
It's a dual cpu machine having 2 GB of ram.


Regards
Sachchida


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tuesday, August 21, 2007 6:05 PM
To: Sachchida Ojha
Cc: Michael Glaesemann; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Autovacuum is running forever

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> Is there any data corruption/damage to the database if we forcefully
> kill autovacuum using cron job (if it is running longer than a
> predefined time frame).

Oh, and I'd look at your I/O subsystem. You might want to look at
putting $300 hardware RAID cards with battery backed cache and 4 or so
disks in a RAID10 in them. It sounds to me like you could use more I/O
for your vacuuming. Vacuuming isn't CPU intensive, but it can be I/O
intensive.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> We are having only two disk (40GB each). One disk is used for OS, App
> Server, and application. Second disk is used for postgresql database.
> It's a dual cpu machine having 2 GB of ram.

Even a single disk, with a battery backed caching controller will
generally run things like updates and inserts much faster, and is
usually a much better performance under load than a single disk.

I'd at least look at mirroring them for redundancy and better read performance.

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

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> Thanks to all of you. I have changed the settings and reloaded the
> config. Let me run this system overnight. I will update this forum if
> new settings works for me. I am also asking management to upgrade the
> hardware.

You need to run vacuum verbose on the database (not an individual
table) and note the output at the end. It will tell you how bloated
your current db is. If vacuums have been delayed for too long, you
may need to vacuum full and / or reindex the bloated tables and
indexes to reclaim the lost space.

Assuming that there's not too much dead space, or that if there is
you've used vacuum full / reindexdb to reclaim it, then vacuum running
regularly and in the background should fix this issue...

The output of vacuum verbose you're looking for is like this:

DETAIL: A total of 2096 page slots are in use (including overhead).
2096 page slots are required to track all free space.
Current limits are: 20000 page slots, 1000 relations, using 182 KB.

If it comes back with some huge number for page slots (like in the
millions) needed to track all the dead tuples you'll need that vacuum
full / reindex. A certain amount of dead space is ok, even a good
thing, since you don't have to extend your table / index files to
insert. 10-30% dead space is normal. anything around 100% or heading
up from there is bad.

You'll also want to look through the rest of the vacuum verbose output
for things like this:

INFO: vacuuming "abc.zip_test"
INFO: index "zip_test_pkey" now contains 1000000 row versions in 3076 pages
DETAIL: 8589 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.37s/0.23u sec elapsed 28.23 sec.
INFO: "zip_test": removed 8589 row versions in 55 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "zip_test": found 8589 removable, 1000000 nonremovable row
versions in 6425 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.36s/0.34u sec elapsed 100.52 sec.

If the number of rows removed and the pages they held were a large
percentage of the table, then you'll likely need to reindex them to
get the space back. Or cluster on an index.

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

Thanks to all of you. I have changed the settings and reloaded the
config. Let me run this system overnight. I will update this forum if
new settings works for me. I am also asking management to upgrade the
hardware.

Thanks a lot.


Regards
Sachchida


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tuesday, August 21, 2007 6:05 PM
To: Sachchida Ojha
Cc: Michael Glaesemann; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Autovacuum is running forever

On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> Is there any data corruption/damage to the database if we forcefully
> kill autovacuum using cron job (if it is running longer than a
> predefined time frame).

Oh, and I'd look at your I/O subsystem. You might want to look at
putting $300 hardware RAID cards with battery backed cache and 4 or so
disks in a RAID10 in them. It sounds to me like you could use more I/O
for your vacuuming. Vacuuming isn't CPU intensive, but it can be I/O
intensive.

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