X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BB7B29FB32F for ; Mon, 7 Aug 2006 20:29:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 13895-08 for ; Mon, 7 Aug 2006 23:29:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id CBEC29FA21B for ; Mon, 7 Aug 2006 20:29:10 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Performance tuning question X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 7 Aug 2006 17:29:08 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Performance tuning question Thread-Index: Aca6cieG+T4a+KAeScq9l7//DnMfjwAACzgA From: "Benjamin Krajmalnik" To: "Chris Mair" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200608/79 X-Sequence-Number: 22623 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]=20 > Sent: Monday, August 07, 2006 4:38 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] Performance tuning question >=20 >=20 > > isweb01# vmstat 10 > > procs memory page disks faults > > cpu > > r b w avm fre flt re pi po fr sr ad4 ad6 in=20 > sy cs us > > sy id > > 1 0 0 648368 47052 10322 0 0 0 7505 136 0 0 =20 > 839 6241 2114 > > 18 10 71 > > 1 0 0 651392 42464 9823 0 0 0 6624 0 0 0 =20 > 667 5374 1703 > > 16 10 73 > > 0 0 0 648368 42316 9672 0 0 0 6677 0 0 0 =20 > 652 5290 1674 > > 16 10 74 > > 1 0 0 650300 39840 6843 0 0 0 4695 0 0 0 =20 > 866 6123 2217 > > 15 10 76 > > 0 0 0 648388 39540 6913 0 0 0 4808 0 0 0=20 > 1279 9694 3367 > > 18 10 72 > > 1 0 0 649764 36780 10528 0 0 0 7337 0 0 0=20 > 1182 9207 3127 > > 23 11 66 > > 1 0 0 651372 33180 13763 0 0 0 9392 0 0 0=20 > 1129 9458 2950 > > 26 13 61 > > 1 0 0 651452 57444 14711 0 0 0 10087 666 0 0 =20 > 889 8044 2315 > > 23 13 63 > > 1 0 0 650664 55956 12388 0 0 0 8479 0 0 0 =20 > 773 6791 2006 > > 20 11 68 > > 2 0 0 649632 55152 10621 0 0 0 7256 0 0 0 =20 > 805 5811 1985 > > 18 11 71 > >=20 > > I have increased the shared memory by 50%, and temp_buffers=20 > to 5000,=20 > > but no noticeable difference in speed. > > As I mentioned, the system has 2 drives in RAID-1, so pg_xlog is on=20 > > the same disk. > > Would moving pg_xlog to a different disk increase the performance? =20 > > The server I am currently running this on is a temporary=20 > server while=20 > > I rebuild our main data server which is SCSI. > > Right now I am going to test a few things on a secondary=20 > dev server I=20 > > set (old server with IDE). This one has 2 drives, so I=20 > will run some=20 > > tests with pg_xlog on the same drive and on a separate drive. >=20 > Having pg_xlog on another disk than the data itselft helps a=20 > lot for frequent writes/updates. >=20 > Still it's not so clear to me on *where* exactly your=20 > performance problem is. Is it that 40 msec time you=20 > mentioned? On *average* your machine doesn't seem to be=20 > overloaded at all from reading vmstat's output. Do you need=20 > do have this call terminate in less than 40 msec, even though=20 > average load is no problem? Then you have a responsivness=20 > problem, and not an easy one, I'm afraid :/ FreeBSD (or=20 > Linux) are not real time systems that can guarantee they will=20 > complete something within msecs. >=20 > If this is the case (and I'm a bit guessing here), I'm afraid=20 > you need to buffer data in the client. >=20 >=20 > > Also, I > > will load the data on an empty database as well as a=20 > restored database. > >=20 > > I really need to find a way to make this faster :( The monitoring=20 > > agent which we use has a single logging thread, and if the database=20 > > does not keep up with it it will stall. >=20 > Does it buffer at all? >=20 > > Worst case, I will virtualize the monitroing agent, but that will=20 > > require quite a bit of work on our side. >=20 >=20 > Bye, Chris. >=20 > --=20 >=20 > Chris Mair > http://www.1006.org >=20 >=20 >=20