Re: Performance tuning question

Поиск
Список
Период
Сортировка
От Benjamin Krajmalnik
Тема Re: Performance tuning question
Дата
Msg-id BF337097BDD9D849A2F4B818DDB279872525A6@stash.stackdump.local
обсуждение исходный текст
Ответ на Performance tuning question  ("Benjamin Krajmalnik" <kraj@illumen.com>)
Ответы Re: Performance tuning question  (Chris Mair <chrisnospam@1006.org>)
Re: Performance tuning question  (adey <adey11@gmail.com>)
Список pgsql-admin
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
>
>
>

В списке pgsql-admin по дате отправления:

Предыдущее
От: Chris Mair
Дата:
Сообщение: Re: Performance tuning question
Следующее
От: Geoff Parker
Дата:
Сообщение: disk usage spike