Re: strange performance regression between 7.4 and 8.1

От: Ron
Тема: Re: strange performance regression between 7.4 and 8.1
Дата: ,
Msg-id: E1HN9zd-0001tV-Gi@elasmtp-galgo.atl.sa.earthlink.net
(см: обсуждение, исходный текст)
Ответ на: strange performance regression between 7.4 and 8.1  ("Alex Deucher")
Ответы: Re: 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 10:16 AM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Florian Weimer <> wrote:
>>* Alex Deucher:
>>
>> > I have noticed a strange performance regression and I'm at a loss as
>> > to what's happening.  We have a fairly large database (~16 GB).
>>
>>Sorry for asking, but is this a typo?  Do you mean 16 *TB* instead of
>>16 *GB*?
>>
>>If it's really 16 GB, you should check if it's cheaper to buy more RAM
>>than to fiddle with the existing infrastructure.
>
>Yes, 16 GB.  I'd rather not shell out for more ram, if I'm not even
>sure that will help.  The new system should be faster, or at least as
>fast, so I'd like to sort out what's going on before I buy more ram.
>
OK.  You
a= went from pg 7.4.x to 8.1.4 AND

b= you changed from 4 SPARC CPUs (how many cores?  If this is > 4...)
to 2 2C Opterons AND
(SPEC and TPC bench differences between these CPUs?)

c= you went from a Sun box to a "white box" AND
(memory subsystem differences?  other differences?)

d=  you went from local HD IO to a SAN
(many differences hidden in that one line...  ...and is the physical
layout of tables and things like pg_xlog sane on the SAN?)


...and you did this by just pulling over the old DB onto the new HW?

May I suggest that it is possible that your schema, queries, etc were
all optimized for pg 7.x running on the old HW?
(explain analyze shows the old system taking ~1/10 the time per row
as well as estimating the number of rows more accurately)

RAM is =cheap=.  Much cheaper than the cost of a detective hunt
followed by rework to queries, schema, etc.
Fitting the entire DB into RAM is guaranteed to help unless this is
an OLTP like application where HD IO is  required to be synchronous..
If you can fit the entire DB comfortably into RAM, do it and buy
yourself the time to figure out the rest of the story w/o impacting
on production performance.

Cheers,
Ron Peacetree



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

От: Guido Neitzer
Дата:
Сообщение: Re: strange performance regression between 7.4 and 8.1
От: Jeff Frost
Дата:
Сообщение: Re: strange performance regression between 7.4 and 8.1