Re: Performance tuning question

Chris,

Thanks for your assistance.

isweb01# vmstat 10
 procs      memory      page                    disks     faults
cpu
 r b w     avm    fre  flt  re  pi  po  fr  sr ad4 ad6   in   sy  cs us
sy id
 1 0 0  648368  47052 10322   0   0   0 7505 136   0   0  839 6241 2114
18 10 71
 1 0 0  651392  42464 9823   0   0   0 6624   0   0   0  667 5374 1703
16 10 73
 0 0 0  648368  42316 9672   0   0   0 6677   0   0   0  652 5290 1674
16 10 74
 1 0 0  650300  39840 6843   0   0   0 4695   0   0   0  866 6123 2217
15 10 76
 0 0 0  648388  39540 6913   0   0   0 4808   0   0   0 1279 9694 3367
18 10 72
 1 0 0  649764  36780 10528   0   0   0 7337   0   0   0 1182 9207 3127
23 11 66
 1 0 0  651372  33180 13763   0   0   0 9392   0   0   0 1129 9458 2950
26 13 61
 1 0 0  651452  57444 14711   0   0   0 10087 666   0   0  889 8044 2315
23 13 63
 1 0 0  650664  55956 12388   0   0   0 8479   0   0   0  773 6791 2006
20 11 68
 2 0 0  649632  55152 10621   0   0   0 7256   0   0   0  805 5811 1985
18 11 71

I have increased the shared memory by 50%, and temp_buffers to 5000, but
no noticeable difference in speed.
As I mentioned, the system has 2 drives in RAID-1, so pg_xlog is on the
same disk.
Would moving pg_xlog to a different disk increase the performance?
The server I am currently running this on is a temporary server while I
rebuild our main data server which is SCSI.
Right now I am going to test a few things on a secondary dev server I
set (old server with IDE).  This one has 2 drives, so I will run some
tests with pg_xlog on the same drive and on a separate drive.  Also, I
will load the data on an empty database as well as a restored database.

I really need to find a way to make this faster :(  The monitoring agent
which we use has a single logging thread, and if the database does not
keep up with it it will stall.
Worst case, I will virtualize the monitroing agent, but that will
require quite a bit of work on our side.



> -----Original Message-----
> From: Chris Mair [mailto:chrisnospam@1006.org]
> Sent: Monday, August 07, 2006 2:54 AM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] 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
>
>
>