Thread: Performance

Performance

From
Bryan Irvine
Date:
I've read up some on performance tuning now that I have postgresql
running, and I'm wondering how I can make it run the fastest it possibly
can.  It's getting logged to by snort from 2 different firewalls, and is
getting approximately 200,000 inserts per day (this seems like a lot to
me but maybe not). and I'm wondering how I can tweak the postgresql.conf
(or anything else) to make the most of every cycle.

I've read that disabling fsync, setting buffer size, crontabbing vacuum,
and vacuum analyze, as well as raising the amount of time between
checkpoint runs helps.  I haven't read what I should set some of these
values to.  How high should buffer and cache size be? how high should I
raise the checkpoint?  Any other tips?

The machine is a Duron 933Mhz, with 256M RAM, with 2 40gb IDE drives on
a striped array running on OpenBSD.  The only other software running is
nagios, mrtg, and a couple of leetle perl scripts.

--Bryan


Re: Performance

From
Josh Berkus
Date:
Bryan,

> I've read that disabling fsync, setting buffer size, crontabbing vacuum,
> and vacuum analyze, as well as raising the amount of time between
> checkpoint runs helps.  I haven't read what I should set some of these
> values to.  How high should buffer and cache size be? how high should I
> raise the checkpoint?  Any other tips?

Two:
Join the PGSQL-PERFORMANCE mailing list for more help.
Read http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
... if you have not already

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


COPY and text encodings

From
"M. Bastin"
Date:
Hi all,

When doing a COPY FROM STDIN, while having set the client encoding to
some encoding, is the encoding of all data being uploaded
automatically being converted to the server's encoding, or does COPY
do a "blind", bulk upload?

Thanks,

Marc

Re: Performance

From
János Löbb
Date:
> The machine is a Duron 933Mhz, with 256M RAM, with 2 40gb IDE drives on
> a striped array running on OpenBSD.  The only other software running is
> nagios, mrtg, and a couple of leetle perl scripts.
>
> --Bryan
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

My guess is that by tripling the RAM will do better than any tweaking
on the database side.
János
----------------------------------------------
Trying to argue with a politician is like lifting up the head of a
corpse.
(S. Lem: His Master Voice)


Re: COPY and text encodings

From
Bruce Momjian
Date:
Yes, I think it is being converted automatically.

---------------------------------------------------------------------------

M. Bastin wrote:
> Hi all,
>
> When doing a COPY FROM STDIN, while having set the client encoding to
> some encoding, is the encoding of all data being uploaded
> automatically being converted to the server's encoding, or does COPY
> do a "blind", bulk upload?
>
> Thanks,
>
> Marc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  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