COPY and text encodings

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

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

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

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073