Re: Performance tuning question

On Mon, 2006-08-07 at 02:18 -0600, Benjamin Krajmalnik wrote:

> I just migrated from PG 8.1.4 Windows to 8.1.4 FreeBSD/i386.

Good move :)

> All of the data insertion to the database is done via a stored procedure
> call.
> I did some benchmarking, and on an empty database the execution time of
> the stored procedure was about 5 ms on average.
> This was done running via EMS SQL Manager.
>
> Now that the database is populated (and it has about 3GB of data, and
> having the data inserted directly by the monitoring application via
> ODBC) the execution speed of the stored procedure has gone to above 40
> ms. These are the values as reported by logging the data.

A 5->40 ms bump might be completely normal if you go from an empty table
to one holding many records. I take it your table has some indexes,
probably a primary key. Inserting in such a table is not a constant
time operation - I guess it's O(log(n)), meaning it increases like a
logarithmic function.

> I assume that the pg_log log is showing the actual execution speed at
> the server, and it is not including the ODBC overhead.

I would suppose so too. That'd rule out the ODBC overhead.

> I need some
> guidance on which parameters to tune.
>
> There are 2 tables constantly being updated, and one constantly being
> inserted to. The 2 being updated are about 170MB, while the one bing
> inserted to is aout 2 GB maximum.

You should find out, whether you're CPU-bound or disk-bound (likely
the latter) - can you send 1 minues worth of output of "vmstat 10"?

> The data server is a 3Gz P4 with 1 GB RAM and SATA RAID-1.
>
> Right now it is configured as follows:
>
> On a 1GB box, I have shared meory at 256M, 25000 shared buffers, 2000
> temp buffers, and work_mem/maintenance_work_mem both set to 128000.
> I have checkpoint_segments set to 30, wal_buffers=16
>
>
>
> An analysis via top shows:
>
>
>
> last pid: 57423; load averages: 0.59, 0.66, 0.63
> up 0+11:22:44 01:42:39
> 62 processes: 1 running, 61 sleeping
> CPU states: 22.9% user, 0.0% nice, 7.3% system, 5.4% interrupt, 64.4%
> idle
> Mem: 140M Active, 480M Inact, 132M Wired, 31M Cache, 110M Buf, 4608K
> Free
> Swap: 2005M Total, 188K Used, 2004M Free

Looks like you're not using the box 100%. Probably your client cannot
keep up with the server. Are you sure you do have a performance problem
at all?

Bye, Chris.

--

Chris Mair
http://www.1006.org