Обсуждение: 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