Re: Performance tuning question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux