Thread: Performance Tuning / RAM Usage

Performance Tuning / RAM Usage

From
Garo Hussenjian
Date:
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


Re: Performance Tuning / RAM Usage

From
Andrew Sullivan
Date:
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


Re: Performance Tuning / RAM Usage

From
Tom Lane
Date:
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

Re: Performance Tuning / RAM Usage

From
Andrew Sullivan
Date:
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


Re: Performance Tuning / RAM Usage

From
Oliver Elphick
Date:
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


Re: Performance Tuning / RAM Usage

From
"scott.marlowe"
Date:
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.


Re: Performance Tuning / RAM Usage

From
Bruce Momjian
Date:
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