pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Strong slowdown on huge tables
2+ messages / 2 participants
[nested] [flat]

* Strong slowdown on huge tables
@ 2012-05-29 14:54  Job <Job@colliniconsulting.it>
  0 siblings, 1 reply; 2+ messages in thread

From: Job @ 2012-05-29 14:54 UTC (permalink / raw)
  To: pgsql-performance

Hello,

i use PostgreSQL 8.4.8 on Centos 5.x and i have some table where i load with pg_bulkload webtraffic logs, every day.
After loading new data, i delete with a query 30-days older logs.
Autovacuum is on.

In some systems, i experience a very huge slowdown of this table in queries, expecially update and delete; the database size grows up a lots.
I tried to rebuild indexes and make a manual vacuum; database size reduces, but slowdown problems remains.

Are there particular things to know after using pg_bulkload?
In this case, is preferred to set autovacuum off and launch one time a week, as example, the autovacuum full manually?

Thank you!
Francesco

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Strong slowdown on huge tables
@ 2012-05-29 17:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Job <Job@colliniconsulting.it>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2012-05-29 17:42 UTC (permalink / raw)
  To: Job <Job@colliniconsulting.it>; +Cc: pgsql-performance

Job <Job@colliniconsulting.it> writes:
> i use PostgreSQL 8.4.8 on Centos 5.x and i have some table where i load with pg_bulkload webtraffic logs, every day.
> After loading new data, i delete with a query 30-days older logs.

Since the deletion pattern is so predictable, you should consider
setting this table up as a partitioned table, so that you can just drop
the oldest partition instead of having to rely on DELETE+VACUUM to
reclaim space.  See
http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html

Alternatively, look into whether including a manual VACUUM in your daily
update script helps any.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-05-29 17:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-29 14:54 Strong slowdown on huge tables Job <Job@colliniconsulting.it>
2012-05-29 17:42 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox