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
