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(at)1006(dot)org]
> Sent: Monday, August 07, 2006 2:54 AM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin(at)postgresql(dot)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
>
>
>