Re: Very high effective_cache_size == worse performance?

От: David Kerr
Тема: Re: Very high effective_cache_size == worse performance?
Дата: ,
Msg-id: 20100420182832.GB53489@mr-paradox.net
(см: обсуждение, исходный текст)
Ответ на: 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?  (Scott Marlowe)
Re: Very high effective_cache_size == worse performance?  (Greg Smith)
Список: 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 12:23:51PM -0600, Scott Marlowe wrote:
- On Tue, Apr 20, 2010 at 12:20 PM, David Kerr <> wrote:
- > On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote:
- > - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <> wrote:
- > -
- > - You can absolutely use copy if you like but you need to use a non-standard
- > - jdbc driver:  kato.iki.fi/sw/db/postgresql/jdbc/copy/.  I've used it in the
- > - past and it worked.
- > -
- > - Is the whole thing going in in one transaction?  I'm reasonably sure
- > - statistics aren't kept for uncommited transactions.
- > -
- > - For inserts the prepared statements can only help.  For selects they can
- > - hurt because eventually the JDBC driver will turn them into back end
- > - prepared statements that are only planned once.  The price here is that that
- > - plan may not be the best plan for the data that you throw at it.
- > -
- > - What was log_min_duration_statement logging that it killed performance?
- > -
- > - --Nik
- >
- > Good to know about the jdbc-copy. but this is a huge project and the load is
- > just one very very tiny component, I don't think we could introduce anything
- > new to assist that.
- >
- > It's not all in one tx. I don't have visibility to the code to determine how
- > it's broken down, but most likely each while loop is a tx.
- >
- > I set it to log all statements (i.e., = 0.). that doubled the load time from
- > ~15 to ~30 hours. I could, of course, be more granular if it would be helpful.
-
- So are you logging to the same drive that has pg_xlog and your
- data/base directory on this machine?
-

the db, xlog and logs are all on separate areas of the SAN.

separate I/O controllers, etc on the SAN. it's setup well, I wouldn't expect
contention there.

I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng
on my dev environments that mostly resoved the probelm for me. but these machines
still have vanilla syslog.

Dave


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

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