Very high effective_cache_size == worse performance?

От: David Kerr
Тема: Very high effective_cache_size == worse performance?
Дата: ,
Msg-id: 20100420173936.GA50886@mr-paradox.net
(см: обсуждение, исходный текст)
Ответы: Re: Very high effective_cache_size == worse performance?  ("Joshua D. Drake")
Re: Very high effective_cache_size == worse performance?  (Robert Haas)
Re: Very high effective_cache_size == worse performance?  (Scott Marlowe)
Re: Very high effective_cache_size == worse performance?  ("Joshua D. Drake")
Список: pgsql-performance

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

Very high effective_cache_size == worse performance?  (David Kerr, )
 Re: Very high effective_cache_size == worse performance?  ("Joshua D. Drake", )
 Re: Very high effective_cache_size == worse performance?  (Robert Haas, )
  Re: Very high effective_cache_size == worse performance?  (David Kerr, )
   Re: Very high effective_cache_size == worse performance?  (Nikolas Everett, )
    Re: Very high effective_cache_size == worse performance?  (Kris Jurka, )
    Re: Very high effective_cache_size == worse performance?  (David Kerr, )
     Re: Very high effective_cache_size == worse performance?  (Scott Marlowe, )
      Re: Very high effective_cache_size == worse performance?  (David Kerr, )
       Re: Very high effective_cache_size == worse performance?  (Scott Marlowe, )
        Re: Very high effective_cache_size == worse performance?  (David Kerr, )
       Re: Very high effective_cache_size == worse performance?  (Scott Marlowe, )
       Re: Very high effective_cache_size == worse performance?  (Greg Smith, )
        Re: Very high effective_cache_size == worse performance?  (David Kerr, )
         Re: Very high effective_cache_size == worse performance?  (Greg Smith, )
   Re: Very high effective_cache_size == worse performance?  (Robert Haas, )
    Re: Very high effective_cache_size == worse performance?  (David Kerr, )
     Re: Very high effective_cache_size == worse performance?  (Scott Marlowe, )
      Re: Very high effective_cache_size == worse performance?  (Scott Carey, )
   Re: Very high effective_cache_size == worse performance?  ("Kevin Grittner", )
    Re: Very high effective_cache_size == worse performance?  (David Kerr, )
 Re: Very high effective_cache_size == worse performance?  (Scott Marlowe, )
  Re: Very high effective_cache_size == worse performance?  (Scott Marlowe, )
 Re: Very high effective_cache_size == worse performance?  ("Joshua D. Drake", )

Howdy all,

I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
64bit OS. No users currently.

I've got a J2EE app that loads data into the DB, it's got logic behind it so it's not a simple bulk load, so
i don't think we can use copy.

Based on the tuning guides, it set my effective_cache_size to 128GB (1/2 the available memory) on the box.

When I ran my load, it took aproximately 15 hours to do load 20 million records. I thought this was odd because
on a much smaller machine I was able to do that same amount of records in 6 hours.

My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give
any indication that we had resource issues.

So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size
from 128GB to 2GB).

Now the large box performs the same as the smaller box. (which is fine).

incidentally, both tests were starting from a blank database.

Is this expected?

Thanks!

Dave


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

От: "Kevin Grittner"
Дата:
Сообщение: Re: significant slow down with various LIMIT
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Very high effective_cache_size == worse performance?