Skip site navigation (1) Skip section navigation (2)

Re: update 600000 rows

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: okparanoid(at)free(dot)fr
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: update 600000 rows
Date: 2007-12-15 15:47:02
Message-ID: Pine.GSO.4.64.0712151041120.1456@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, 15 Dec 2007, okparanoid(at)free(dot)fr wrote:

> First when i run htop i see that the memory used is never more than 150 MB.
> I don't understand in this case why setting shmall and shmmax kernel's
> parameters to 16 GB of memory (the server has 32 GB) increase the rapidity of
> the transaction a lot compared to a shmall and shmax in (only) 2 GB ?!

The kernel parameters provide an upper limit for how much memory 
PostgreSQL can allocate, but by themselves they don't actually request 
more memory.  There is a configuration parameters called shared_buffers 
that is the main thing to adjust.  Since you say you're new to this, see 
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm for the 
first set of things you should be adjusting.

If you're doing lots of updates, you'll need to increase 
checkpoint_segments as well.  Once you get the memory allocated and 
checkpoint parameters in the right range, at that point you'll be prepared 
to look into transaction grouping and application issues in that area.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

pgsql-performance by date

Next:From: andrewDate: 2007-12-16 05:21:47
Subject: Re: update 600000 rows
Previous:From: Loïc MarteauDate: 2007-12-15 11:43:10
Subject: Re: update 600000 rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group