SEO

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Paragon" <lr@pcorp.us> writes:
> 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.

Is it actually *doing* anything, like consuming CPU or I/O -- and if so
which? How much does VACUUM VERBOSE print before getting stuck?

> vmstat shows

vmstat without any arguments is very nearly useless, because what it
shows you is averages since system boot. Watch "vmstat 1" for awhile
and you will get an actual picture of what's happening.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

>"Paragon" <lr@pcorp.us> writes:
>> 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.

>Is it actually *doing* anything, like consuming CPU or I/O -- and if so
which? How much does VACUUM VERBOSE print before getting stuck?

It just shows -
Vacuuming "ky.ky_edges" and sits there forever



> vmstat without any arguments is very nearly useless, because what it shows
you is averages since system boot. Watch "vmstat 1" for awhile and you will
> > get an actual picture of what's happening.

With no queries running vmstat 1 looks like this
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 400 178832 5336 2821712 0 0 130 193 22 10 18 0 79
3 0
0 0 400 178708 5336 2822100 0 0 0 0 1024 191 0 0 100
0 0
0 0 400 178460 5348 2822116 0 0 0 1328 1168 182 0 0 96
4 0
1 0 400 178212 5348 2822492 0 0 0 0 1022 201 0 0 100
0 0
0 0 400 178088 5348 2822568 0 0 0 0 1031 196 0 0 100
0 0
0 0 400 177840 5348 2822860 0 0 0 0 1023 194 0 0 100
0 0
0 0 400 177716 5348 2822936 0 0 0 48 1009 164 0 0 100
0 0
0 0 400 177468 5364 2823216 0 0 4 1272 1177 228 0 0 97
3 0
0 0 400 177468 5368 2823236 0 0 4 0 1006 158 0 0 100
0 0
0 0 400 177468 5368 2823440 0 0 0 0 1022 177 0 0 100
0 0

--If I start the
vacuum verbose ky.ky_edges;
and monitor vmstat 1 looks like this


procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id
wa st
1 0 400 169408 5932 2831468 0 0 0 24 1006 184 0 0 100
0 0
0 0 400 169160 5956 2831616 0 0 0 1132 1108 215 0 0 100
0 0
0 0 400 168792 5968 2831920 0 0 128 676 1055 216 0 0 100
0 0
0 0 400 168544 5968 2832108 0 0 0 24 1024 193 0 0 100
0 0
0 0 400 168300 5992 2832212 0 0 128 1120 1098 219 0 0 100
0 0
0 0 400 168176 6004 2832628 0 0 0 668 1075 198 0 0 100
0 0
0 0 400 168052 6004 2832640 0 0 0 24 1005 195 0 0 99
1 0
0 0 400 167684 6028 2832844 0 0 128 1116 1118 224 0 0 100
0 0
0 0 400 167560 6040 2833084 0 0 0 664 1056 216 0 0 99
1 0


Thanks,
Regina



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Paragon" <lr@pcorp.us> writes:
>> Is it actually *doing* anything, like consuming CPU or I/O -- and if so
>> which? How much does VACUUM VERBOSE print before getting stuck?

> --If I start the
> vacuum verbose ky.ky_edges;
> and monitor vmstat 1 looks like this

Sure looks like a near-idle machine to me :-(.

I think that the vacuum must be stuck on a lock. What other Postgres
processes have you got, and what are they doing?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

> Sure looks like a near-idle machine to me :-(.

> I think that the vacuum must be stuck on a lock. What other Postgres
processes have you got, and what are they doing?

> regards, tom lane


Originally I thought it was the autovacuuming getting in the way since I
noticed sometimes it was trying to vacuum the tables I was trying to vacuum
and autovacuuming was taking a long time, but I had turned autovacuuming off
during bulk load process period to prevent this. When I showed you the
stats on only the vacuuming, I had already stopped all bulk load processes
such that

Running
SELECT * FROM pg_stat_activity

Was showing nothing but my single vacuuming process.

If I vacuum say a 365,0000 record table of comparable structure to my 1
million someodd, it takes about 10 minutes to run and runs thru. 10 minutes
for 365,000 records seemed a little long to me from memory on 8.2, but at
least it ran thru okay.

Next I was going to try to do was load the same dataset on my old 8.2 box to
rule out 8.3.1 as the culprit.

Thanks,
Regina





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Paragon" <lr@pcorp.us> writes:
>> Sure looks like a near-idle machine to me :-(.

>> I think that the vacuum must be stuck on a lock. What other Postgres
>> processes have you got, and what are they doing?

> Originally I thought it was the autovacuuming getting in the way since I
> noticed sometimes it was trying to vacuum the tables I was trying to vacuum
> and autovacuuming was taking a long time, but I had turned autovacuuming off
> during bulk load process period to prevent this.

Hmm ... another reason for vacuum running really slowly would be poor
choices of vacuum_cost parameters --- have you got those set to
nondefault values?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Paragon" <lr@pcorp.us> writes:
> Right now I have
> vacuum_cost_delay = 600

Yikes. That's *way* too high. If you're trying to get the vacuum to
complete quickly, it really should be zero anyway. Nonzero is for when
you don't care how long vacuum takes as long as it's not sucking too much
I/O from your real work.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

> Yikes. That's *way* too high. If you're trying to get the vacuum to
complete quickly, it really should be zero anyway. Nonzero is for when you
don't care how >> long vacuum takes as long as it's not sucking too much
I/O from your real work.

Thanks Tom, yap you were right now it runs instantaneously by resetting
vacuum back to defaults. I feel kind of dumb at this point
I guess misunderstood the docs.

Thanks again,
Regina




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello

If Your 'vacuum verbose analyze table' ends pretty fast,and Your 'vacuum
full verbose analyze table' never ends,watch for 'select count (*) from
pg_locks',might be that You have some heavy load transaction processing
on that table,so 'vacuum full....' wait for transactions to end. If
possible kill all processes that access that table and try 'vacuum full
verbose analyze table'. Anyway,if processor is mostly idle,always check
'select count (*) from pg_locks' , at least in my experience.

Sincerely

Dragan Zubac

Tom Lane wrote:
> "Paragon" <lr@pcorp.us> writes:
>
>> Right now I have
>> vacuum_cost_delay = 600
>>
>
> Yikes. That's *way* too high. If you're trying to get the vacuum to
> complete quickly, it really should be zero anyway. Nonzero is for when
> you don't care how long vacuum takes as long as it's not sucking too much
> I/O from your real work.
>
> regards, tom lane
>
>


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general