Re: Some frustrations with admin tasks on PGSQL database

On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote:
> However, I am a little frustrated by the amount of time PGSQL takes to
> complete tasks. Just to accommodate these tasks, my conf file has the
> following:
>
> autovacuum = off
> wal_buffers=64
> checkpoint_segments=1000
> checkpoint_timeout=900
> fsync = off
> maintenance_work_mem = 128MB

I note you did not give either the shared_buffers setting or the
work_mem setting, both of which would have a major impact on your
problem.

> I have dropped all indexes/indicises on my table, except for the
> primary key. Still, when I run the query:
>
> UPDATE mytable SET mycolumn = lower(mycolumn);
>
> This is, at the time of this writing, has taken well over 35 minutes!
> On a table of a "mere" 6 million rows (quoted from one discussion on
> this mailing list).

How big is the actual table itself (in bytes).

> Suspecting that locking may be the cause of this, I read up on
> http://www.postgresql.org/docs/8.2/static/explicit-locking.html and
> found nothing specific that would help a person starting out on the DB
> to actually do meaningful explicit locking that the UPDATE command
> does not already do.

I doubt it's locking.

> What else can I do to make this go fast enough to be normal!? Penny
> for any thoughts and tips.

Check your I/O throughput. You should be maxing out your disk...
Although, with 6million rows, it might even fit in memory. Can you see
(in ps) what it's actually doing?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.