Обсуждение: Performance tuning question
I just migrated from PG 8.1.4 Windows to 8.1.4 FreeBSD/i386. 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. 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 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. 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 Any assistance will be deeply appreciated.
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
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 > > >
> 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. Having pg_xlog on another disk than the data itselft helps a lot for frequent writes/updates. Still it's not so clear to me on *where* exactly your performance problem is. Is it that 40 msec time you mentioned? On *average* your machine doesn't seem to be overloaded at all from reading vmstat's output. Do you need do have this call terminate in less than 40 msec, even though average load is no problem? Then you have a responsivness problem, and not an easy one, I'm afraid :/ FreeBSD (or Linux) are not real time systems that can guarantee they will complete something within msecs. If this is the case (and I'm a bit guessing here), I'm afraid you need to buffer data in the client. > 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. Does it buffer at all? > Worst case, I will virtualize the monitroing agent, but that will > require quite a bit of work on our side. Bye, Chris. -- Chris Mair http://www.1006.org
Chris, I just finished running some benchmarks on an underpowered server compared to the one I am running in production. My initial tests were run on an ampty database, pg_xlog on the same spindle. Stored procedure execution speed was ~15 ms. I then restored the production database so I would have ~3GB database. Execution time for the stored procedure went up to about 40 ms average (with a miuch higher variance), but with a processing speed of 18 stored procedure calls per second. I them moved pg_xlog to a separate spindle. Execution time went down to about 17 ms Beyond moving pg_xlog to a separate spindle, are there any other things you can think of which may improve the performance? > -----Original Message----- > From: Chris Mair [mailto:chrisnospam@1006.org] > Sent: Monday, August 07, 2006 4:38 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] Performance tuning question > > > > 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. > > Having pg_xlog on another disk than the data itselft helps a > lot for frequent writes/updates. > > Still it's not so clear to me on *where* exactly your > performance problem is. Is it that 40 msec time you > mentioned? On *average* your machine doesn't seem to be > overloaded at all from reading vmstat's output. Do you need > do have this call terminate in less than 40 msec, even though > average load is no problem? Then you have a responsivness > problem, and not an easy one, I'm afraid :/ FreeBSD (or > Linux) are not real time systems that can guarantee they will > complete something within msecs. > > If this is the case (and I'm a bit guessing here), I'm afraid > you need to buffer data in the client. > > > > 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. > > Does it buffer at all? > > > Worst case, I will virtualize the monitroing agent, but that will > > require quite a bit of work on our side. > > > Bye, Chris. > > -- > > Chris Mair > http://www.1006.org > > >
> I just finished running some benchmarks on an underpowered server > compared to the one I am running in production. > My initial tests were run on an ampty database, pg_xlog on the same > spindle. > Stored procedure execution speed was ~15 ms. > > I then restored the production database so I would have ~3GB database. > Execution time for the stored procedure went up to about 40 ms average > (with a miuch higher variance), but with a processing speed of 18 stored > procedure calls per second. > > I them moved pg_xlog to a separate spindle. > Execution time went down to about 17 ms Good! > Beyond moving pg_xlog to a separate spindle, are there any other things > you can think of which may improve the performance? You could bundle more work into a single transaction. I don't know what your call is doing, but 1 call that does 20 inserts in a transaction is certainly more efficient than 2 calls doing two transactions with 10 inserts each. If you can use bigger bundles of work, do that. Maybe reasoning in "MByte (or whatever) per second" rather than "time to do a small piece of the work" helps. Otherwise you're into real time stuff and RDBMS' and real time stuff don't mix well. Bye :) Chris. -- Chris Mair http://www.1006.org
A vacuum full analyze might help.
On 8/8/06, Benjamin Krajmalnik <kraj@illumen.com> wrote:
Chris,
I just finished running some benchmarks on an underpowered server
compared to the one I am running in production.
My initial tests were run on an ampty database, pg_xlog on the same
spindle.
Stored procedure execution speed was ~15 ms.
I then restored the production database so I would have ~3GB database.
Execution time for the stored procedure went up to about 40 ms average
(with a miuch higher variance), but with a processing speed of 18 stored
procedure calls per second.
I them moved pg_xlog to a separate spindle.
Execution time went down to about 17 ms
Beyond moving pg_xlog to a separate spindle, are there any other things
you can think of which may improve the performance?
> -----Original Message-----
> From: Chris Mair [mailto:chrisnospam@1006.org]
> Sent: Monday, August 07, 2006 4:38 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Performance tuning question
>
>
> > 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.
>
> Having pg_xlog on another disk than the data itselft helps a
> lot for frequent writes/updates.
>
> Still it's not so clear to me on *where* exactly your
> performance problem is. Is it that 40 msec time you
> mentioned? On *average* your machine doesn't seem to be
> overloaded at all from reading vmstat's output. Do you need
> do have this call terminate in less than 40 msec, even though
> average load is no problem? Then you have a responsivness
> problem, and not an easy one, I'm afraid :/ FreeBSD (or
> Linux) are not real time systems that can guarantee they will
> complete something within msecs.
>
> If this is the case (and I'm a bit guessing here), I'm afraid
> you need to buffer data in the client.
>
>
> > 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.
>
> Does it buffer at all?
>
> > Worst case, I will virtualize the monitroing agent, but that will
> > require quite a bit of work on our side.
>
>
> Bye, Chris.
>
> --
>
> Chris Mair
> http://www.1006.org
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org