Re: Very high effective_cache_size == worse performance?

От: David Kerr
Тема: Re: Very high effective_cache_size == worse performance?
Дата: ,
Msg-id: 20100420180351.GA51940@mr-paradox.net
(см: обсуждение, исходный текст)
Ответ на: Re: Very high effective_cache_size == worse performance?  (Robert Haas)
Ответы: Re: Very high effective_cache_size == worse performance?  (Nikolas Everett)
Re: Very high effective_cache_size == worse performance?  (Robert Haas)
Re: Very high effective_cache_size == worse performance?  ("Kevin Grittner")
Список: 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", )

On Tue, Apr 20, 2010 at 01:44:18PM -0400, Robert Haas wrote:
- On Tue, Apr 20, 2010 at 1:39 PM, David Kerr <> wrote:
- > 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?
-
- Lowering effective_cache_size tends to discourage the planner from
- using a nested-loop-with-inner-indexscan plan - that's it.
-
- What may be happening is that you may be loading data into some tables
- and then running a query against those tables before the autovacuum
- daemon has a chance to analyze them.  I suspect that if you enable
- some logging you'll find that one of those queries is really, really
- slow, and that (by happy coincidence) discouraging it from using the
- index it thinks it should use happens to produce a better plan.  What
- you should probably do is, for each table that you bulk load and then
- query, insert a manual ANALYZE between the two.
-
- ...Robert
-

that thought occured to me while I was testing this. I ran a vacuumdb -z
on my database during the load and it didn't impact performance at all.

Incidentally the code is written to work like this :

while (read X lines in file){
Process those lines.
write lines to DB.
}

So i would generally expect to get the benefits of the updated staticis
once the loop ended. no?  (would prepared statements affect that possibly?)

Also, while I was debugging the problem, I did load a 2nd file into the DB
ontop of one that had been loaded. So the statistics almost certinaly should
have been decent at that point.

I did turn on log_min_duration_statement but that caused performance to be unbearable,
but i could turn it on again if it would help.

Dave


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

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