From: | "Paragon" <lr(at)pcorp(dot)us> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | VACUUM hanging on PostgreSQL 8.3.1 for larger tables |
Date: | 2008-04-13 04:34:01 |
Message-ID: | 000501c89d1f$98c03720$4c812e40@q |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am running into problems vacuuming my larger tables. It seems for tables
greater than 1 million rows, Vacuum just hangs. I could leave it running
for hours and it never comes to completion.
Things like copying the whole table to a temp table with bulk insert such as
(SELECT * INTO temp FROM sometable) takes about 60-80 secs for a 1.5
miliion table
creating an index about 30 secs,
ANALYZE VERBOSE about 109 secs (haven't changed the default % scan) - below
is a sample of that
INFO: analyzing "ky.ky_edges"
INFO: "ky_edges": scanned 3000 of 115299 pages, containing 39113 live rows
and 5216 dead rows; 3000 rows in sample, 1503230 estimated total rows
My fillfactors are set to about 90%.
I should add I have POSTGIS geometry fields in these tables (2 of them). I
did a test creating a temp table of the same records, but leaving out the
geometry fields with same results. So I ruled out the geometry fields as
the issue.
Below are my general specs
"PostgreSQL 8.3.1 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070626 (Red Hat 4.1.2-14)"
shared_buffers = 1536MB
temp_buffers = 128MB
maintenance_work_mem = 512MB
work_mem = 256MB
max_fsm_relations = 1000
max_fsm_pages = 204800
max_fsm_relations = 1000 (never quite understood how these fsm things work)
wal_buffers = 10MB
vmstat shows
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id
wa st
0 0 280 123328 4320 2762896 0 0 31 74 62 33 1 0 98
1 0
cat /proc/cpuinfo key elements show 8 of these
model name : Intel(R) Xeon(R) CPU E5410 @ 2.33GHz
stepping : 6
cpu MHz : 2333.644
cache size : 6144 KB
uname -a shows: 2.6.18-53.1.4.el5 #1 SMP Wed Nov 14 10:37:33 EST 2007 i686
i686 i386 GNU/Linux
Thanks,
Regina
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-04-13 04:43:52 | Re: VACUUM hanging on PostgreSQL 8.3.1 for larger tables |
Previous Message | Craig Ringer | 2008-04-13 02:03:48 | Re: SQL injection, php and queueing multiple statement |