Thread: Performance Tuning / RAM Usage
I am running postgresql 7.1.2 on redhat 7.2 and having problems with performance... During peak activity, the postgres processes lock up and report "UPDATE waiting" (under ps listing) with no light at the end of the tunnel. There is a script which has to insert up to a couple hundred new records into two large (800,000 record each) tables, and the inserts take well over a minute. I don't mind it taking time, but I'm concerned when the processes start failing all together! I have 512M of RAM on a 1Ghz PIII server (Dell), and all database connections are made through php. They are all persistent connections. I am considering adding 1536M of additional RAM (for a grand total of 2G) and possibly updating to redhat 7.3 (with postgres 7.2) if necessary. I am really looking to find the best way to configure the database to utilize the existing RAM (and any new RAM I may add). Also, to what degree will adding memory help the database performance if the select and insert operations are mostly disk-intensive? Thanks, Garo Hussenjian. =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
On Tue, Aug 27, 2002 at 07:24:31PM -0700, Garo Hussenjian wrote:
> I am running postgresql 7.1.2 on redhat 7.2 and having problems with
> performance... During peak activity, the postgres processes lock up and
> report "UPDATE waiting" (under ps listing) with no light at the end of the
> tunnel.
Are you using referential integrity? Are you getting deadlocks? (I
know that the Debian package does not -- or at least didn't used to
-- check for deadlocks at all, so you might have them and not know
it).
> I am really looking to find the best way to configure the database to
> utilize the existing RAM (and any new RAM I may add). Also, to what degree
> will adding memory help the database performance if the select and insert
> operations are mostly disk-intensive?
In order to give you any help, we need more information about your
configuration.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes:
> I know that the Debian package does not -- or at least didn't used to
> -- check for deadlocks at all,
[blinks] What?
regards, tom lane
On Wed, Aug 28, 2002 at 10:40:46AM -0400, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > I know that the Debian package does not -- or at least didn't used to
> > -- check for deadlocks at all,
>
> [blinks] What?
Hmm. I just checked my home box (where I have the Debian package
installed), and it seems no longer to be the case. There's no entry
for deadlock_timeout, so the default (1000, IIRC) is what gets used,
I guess. I have a dim memory, however, of a comment in the old
config file to the effect that the (Debian) default setting of
deadlock_timeout was infinite, i.e. effectively no deadlock checking.
Hmm. I just checked an ancient box with 6.5 on it, and it's not
there. Maybe I'm on crack.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Wed, 2002-08-28 at 15:55, Andrew Sullivan wrote: > On Wed, Aug 28, 2002 at 10:40:46AM -0400, Tom Lane wrote: > > Andrew Sullivan <andrew@libertyrms.info> writes: > > > I know that the Debian package does not -- or at least didn't used to > > > -- check for deadlocks at all, > > > > [blinks] What? > > Hmm. I just checked my home box (where I have the Debian package > installed), and it seems no longer to be the case. There's no entry > for deadlock_timeout, so the default (1000, IIRC) is what gets used, > I guess. I have a dim memory, however, of a comment in the old > config file to the effect that the (Debian) default setting of > deadlock_timeout was infinite, i.e. effectively no deadlock checking. > > Hmm. I just checked an ancient box with 6.5 on it, and it's not > there. Maybe I'm on crack. I fear so :-) I'm sure I've never made any such change to the package. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Use hospitality one to another without grudging." I Peter 4:9
On Tue, 27 Aug 2002, Garo Hussenjian wrote: > I am running postgresql 7.1.2 on redhat 7.2 and having problems with > performance... During peak activity, the postgres processes lock up and > report "UPDATE waiting" (under ps listing) with no light at the end of the > tunnel. > > There is a script which has to insert up to a couple hundred new records > into two large (800,000 record each) tables, and the inserts take well over > a minute. I don't mind it taking time, but I'm concerned when the processes > start failing all together! Check for the size of both your tables and indexes, you could have a problem with uncontrolled growth in one or the other. you can use reindex to recreate and index and reclaim lost space. also, do all your inserts as a single transaction if you aren't already. > I have 512M of RAM on a 1Ghz PIII server (Dell), and all database > connections are made through php. They are all persistent connections. > > I am considering adding 1536M of additional RAM (for a grand total of 2G) > and possibly updating to redhat 7.3 (with postgres 7.2) if necessary. If you aren't running out of RAM and your CPUs are sitting at 100% during this process, the extra memory probably won't help much. > I am really looking to find the best way to configure the database to > utilize the existing RAM (and any new RAM I may add). Also, to what degree > will adding memory help the database performance if the select and insert > operations are mostly disk-intensive? turn off fsync. It may not be the safest setting but it sure makes postgresql go faster, and if you're on a stable machine with UPS that's backed up daily, the chances of losing data because of it are pretty small.
Oliver Elphick wrote: > On Wed, 2002-08-28 at 15:55, Andrew Sullivan wrote: > > On Wed, Aug 28, 2002 at 10:40:46AM -0400, Tom Lane wrote: > > > Andrew Sullivan <andrew@libertyrms.info> writes: > > > > I know that the Debian package does not -- or at least didn't used to > > > > -- check for deadlocks at all, > > > > > > [blinks] What? > > > > Hmm. I just checked my home box (where I have the Debian package > > installed), and it seems no longer to be the case. There's no entry > > for deadlock_timeout, so the default (1000, IIRC) is what gets used, > > I guess. I have a dim memory, however, of a comment in the old > > config file to the effect that the (Debian) default setting of > > deadlock_timeout was infinite, i.e. effectively no deadlock checking. > > > > Hmm. I just checked an ancient box with 6.5 on it, and it's not > > there. Maybe I'm on crack. > > I fear so :-) > > I'm sure I've never made any such change to the package. Remember Debian had the pg_hba.conf 'peer' patch in their distribution a few years ago, so it is possible they mucked up the deadlock timeout too --- not likely, but possible. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073