Re: strange performance regression between 7.4 and 8.1

От: Ron
Тема: Re: strange performance regression between 7.4 and 8.1
Дата: ,
Msg-id: E1HMw94-0001rS-OZ@elasmtp-galgo.atl.sa.earthlink.net
(см: обсуждение, исходный текст)
Ответ на: strange performance regression between 7.4 and 8.1  ("Alex Deucher")
Список: pgsql-performance

Скрыть дерево обсуждения

strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
 Re: strange performance regression between 7.4 and 8.1  ("Joshua D. Drake", )
  Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
   Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
    Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
   Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
    Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
     Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
      Re: strange performance regression between 7.4 and 8.1  (Tom Lane, )
       Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
   Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
    Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
     Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
      Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
       Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
        Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
         Re: strange performance regression between 7.4 and 8.1  ("Joshua D. Drake", )
          Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
           Re: strange performance regression between 7.4 and 8.1  ("Joshua D. Drake", )
            Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
         Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
          Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
 Re: strange performance regression between 7.4 and 8.1  (Florian Weimer, )
  Re: strange performance regression between 7.4 and 8.1  (Carlos Moreno, )
   Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
  Re: strange performance regression between 7.4 and 8.1  (Alvaro Herrera, )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
   Re: strange performance regression between 7.4 and 8.1  (Scott Marlowe, )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
 Re: strange performance regression between 7.4 and 8.1  (Anton Rommerskirchen, )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
   Re: strange performance regression between 7.4 and 8.1  (Guido Neitzer, )
    Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
     Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )

At 07:36 PM 3/1/2007, Jeff Frost wrote:
>On Thu, 1 Mar 2007, Alex Deucher wrote:
>
>>> >> Postgresql might be choosing a bad plan because your
>>> effective_cache_size
>>> >> is
>>> >> way off (it's the default now right?).  Also, what was the
>>> block read/write
>>> >
>>> > yes it's set to the default.
>>> >
>>> >> speed of the SAN from your bonnie tests?  Probably want to tune
>>> >> random_page_cost as well if it's also at the default.
>>> >>
>>> >
>>> >                   ------Sequential Output------ --Sequential Input-
>>> > --Random-
>>> >                   -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
>>> > --Seeks--
>>> > Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP
>>> K/sec %CP /sec
>>> > %CP
>>> > luna12-san   16000M 58896  91 62931   9 35870   5 54869  82
>>> 145504 13 397.7
>>> > 0
>>> >
>>>So, you're getting 62MB/s writes and 145MB/s reads.  Just FYI, that write
>>>speed is about the same as my single SATA drive write speed on my
>>>workstation,
>>>so not that great.  The read speed is decent, though and with that sort of
>>>read performance, you might want to lower random_page_cost to something like
>>>2.5 or 2 so the planner will tend to prefer index scans.
>>
>>Right, but the old box was getting ~45MBps on both reads and writes,
>>so it's an improvement for me :)  Thanks for the advice, I'll let you
>>know how it goes.
>
>Do you think that is because you have a different interface between
>you and the SAN?  ~45MBps is pretty slow - your average 7200RPM
>ATA133 drive can do that and costs quite a bit less than a SAN.
>
>Is the SAN being shared between the database servers and other
>servers?  Maybe it was just random timing that gave you the poor
>write performance on the old server which might be also yielding
>occassional poor performance on the new one.

Remember that pg, even pg 8.2.3, has a known history of very poor
insert speed (see comments on this point by Josh Berkus,  Luke Lonergan, etc)

For some reason, the code changes that have resulted in dramatic
improvements in pg's read speed have not had nearly the same efficacy
for writes.

Bottom line: pg presently has a fairly low and fairly harsh upper
bound on write performance.   What exactly that bound is has been the
subject of some discussion, but IIUC the fact of its existence is
well established.

Various proposals for improving the situation exist, I've even made
some of them, but AFAIK this is currently considered one of the
"tough pg problems".

Cheers,
Ron Peacetree



В списке pgsql-performance по дате сообщения:

От: Oleg Bartunov
Дата:
Сообщение: Re: Array indexes, GIN?
От: Adam L Beberg
Дата:
Сообщение: Re: Array indexes, GIN?