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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
