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@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.