Re: How to increase shared mem for PostgreSQL on FreeBSD

On Fri, 14 Dec 2001, Joe Koenig wrote:

> concerned with. The whole script takes about 27 minutes to run and
> inserts somewhere around 700,000 rows.
......
> When the script is solely doing inserts, it is able
> to insert around 200 rows per second. The inserts are wrapped in
> transactions in groups of 5000. It seems that I should be able to insert
> faster than that with my system (1GHz, 1GB RAM, RAID 5 w/10K 18GB
> drives). Are there other things I need to be concerned with that will
> help the speed? my shared_buffers is 15200 and sort_mem is at 8096
> currently. Also, the script doing the inserts is written in PHP 4.1.0 -
> could that be slowing me, as well? Thanks,

I think if you were going to be doing such large updates that you would be
better off using the copy command. As a reference I insert about 2.5
Million records in the neighborhood of 40 minutes or about 1050 inserts
per second. The hardware is a 500Mhz pentinum III with 512MB ram, 8000
buffers on postgresql.conf. The drives are 2 IDE 7,2000 RPM drives on Raid
1 configuration.

How many drives do you have?
If you have 4 drives I would recommend you used Raid 1+0 or if your
controller can't do it, then use two separate Raid 1 configurations.

Also you could put the logs directory, pg_xlog I believe, in one
raid set and the rest of the data on the other one. That may help on your
inserts.

I don't know anything about your data structures, but I think doing part
of your problem may be that regular inserts update the indexes and I have
been told copy doesn't. That may be a big part of your overhead. I just do
a vacuum analyze when I am done copying all the data.

Two quick suggestions:
-Try dropping your indexes before you start the inserts
-Try increasing the number of transanctions to a group of 10000.

What is the lenght of your rows been inserted? The rows from the example
above are about 60 bytes long. Doing a little math: 60 bytes * 1050
transactions per second = 61K/sec. The problem on my case is definitely
not bandwith, but random access. I just got a 15K rpm drive today and will be
putting that into a 1Gz machine with 1GB of RAM.