If you don't need access to the old data constantly:
- copy the live data to a new table
- TRUNCATE the old table (which needs an exclusive lock but is very fast)
- insert the data back in
- for an event log I would imagine this could work
Obtaining exclusive locks on this table is very dif
The above shows that the indexes contained 10M rows and 160M of dead
space each. That means you weren't vacuuming nearly enough.
How is it that a row in the table can grow to a size far exceeding the sum
of the maximum sized of the fields it consists of?
13M dead rows, and the table is 1.4M
> INFO: index "job_log_id_pkey" now contains 10496152 row versions in
> 59665 pages
See the 10496152 above? That means you have 10496152 rows of data in your
table. If those, only 365000 are alive. That means you have basically
never vacuumed this table before, correct?
Almost correct :| I
9 6:25.10 postmaster
--top output end--
I know my database needs a major redesign. But I'm having a hard time
explaining the poor performance nevertheless.
Regards,
Bealach
From: Andreas Kretschmer <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org
Sub
Hi folks,
Sorry to be bringing this up again, but I'm stumped by this problem
and hope you can shed some light on it.
I'm running postgresql 8.0 on a RLE4 server with 1.5 GB of RAM and a
Xenon 2 GHz CPU. The OS is bog standard and I've not done any kernel
tuning on it. The file system is also bo
Thanks very much - there are a lot of good articles there... Reading as
fast as I can :)
Best,
Bealach
From: "Thomas F. O'Connell" <[EMAIL PROTECTED]>
To: Bealach-na Bo <[EMAIL PROTECTED]>
CC: PgSQL - Performance
Subject: Re: [PERFORM] Very slow queries - please
A quick note to say that I'm very grateful for Tom Lane's input also.
Tom, I did put you on the list of recipients for my last posting to
pgsql-performance, but got:
cut here
This is an automatically generated Delivery Status Notification.
Delivery to the
OK.
The consensus seems to be that I need more indexes and I also need to
look into the NOT IN statement as a possible bottleneck. I've
introduced the indexes which has led to a DRAMATIC change in response
time. Now I have to experiment with INNER JOIN -> OUTER JOIN
variations, SET ENABLE_SEQSCAN
ithout time zone) AND ((job_stop <=
'2005-11-14 05:00:00'::timestamp without time zone) OR (job_stop IS NULL)))
-> Index Scan using node_id_pkey on node n (cost=0.00..5.99
rows=1 width=4)
Index Cond: ("outer".node_id = n.node_id)
Hi Folks,
I'm new to Postgresql.
I'm having great difficulties getting the performance I had hoped for
from Postgresql 8.0. The typical query below takes ~20 minutes !!
I hope an expert out there will tell me what I'm doing wrong - I hope
*I* am doing something wrong.
Hardware
Single
10 matches
Mail list logo
