From: David Kerr <dmk@mr-paradox.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Nikolas Everett <nik9000@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very high effective_cache_size == worse performance?
Date: Tue, 20 Apr 2010 11:28:32 -0700
Message-ID: <20100420182832.GB53489@mr-paradox.net> (raw)
In-Reply-To: <j2qdcc563d11004201123u21219445v4a9129ee7d7b520b@mail.gmail.com>
References: <20100420173936.GA50886@mr-paradox.net>
<h2w603c8f071004201044tbc8ce8fenb846b7643a2b9376@mail.gmail.com>
<20100420180351.GA51940@mr-paradox.net>
<w2ld4e11e981004201112v3571f350zaaa0112d1e035097@mail.gmail.com>
<20100420182030.GA53489@mr-paradox.net>
<j2qdcc563d11004201123u21219445v4a9129ee7d7b520b@mail.gmail.com>
On Tue, Apr 20, 2010 at 12:23:51PM -0600, Scott Marlowe wrote:
- On Tue, Apr 20, 2010 at 12:20 PM, David Kerr <dmk@mr-paradox.net> 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 <dmk@mr-paradox.net> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmk@mr-paradox.net, scott.marlowe@gmail.com, nik9000@gmail.com, robertmhaas@gmail.com
Subject: Re: Very high effective_cache_size == worse performance?
In-Reply-To: <20100420182832.GB53489@mr-paradox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox