Re: [PERFORM] autocommit (true/false) for more than 1 million records

2014-08-22 Thread Stephen Frost
* Emi Lu (em...@encs.concordia.ca) wrote:
> >*
> >>Trying to insert into one table with 1 million records through java
> >>JDBC into psql8.3. May I know (1) or (2) is better please?
> >>
> >>(1) set autocommit(true)
> >>(2) set autocommit(false)
> >>  commit every n records (e.g., 100, 500, 1000, etc)
> >It depends on what you need.
> >
> >Data will be available to concurrent processes earlier with (1), while
> >(2) will go faster.
> No need to worry about the lock/loosing records because after data
> loading will do a check. For now, I'd like the fastest way. Would
> you suggest commit every 1000 or 3000 records?

The improvement drops off pretty quickly in my experience, but it
depends on the size of the records and other things.

Try it and see..?  It's almost certainly going to depend on your
specific environment.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [PERFORM] autocommit (true/false) for more than 1 million records

2014-08-22 Thread Emi Lu

*

Trying to insert into one table with 1 million records through java
JDBC into psql8.3. May I know (1) or (2) is better please?

(1) set autocommit(true)
(2) set autocommit(false)
  commit every n records (e.g., 100, 500, 1000, etc)

It depends on what you need.

Data will be available to concurrent processes earlier with (1), while
(2) will go faster.
No need to worry about the lock/loosing records because after data 
loading will do a check. For now, I'd like the fastest way. Would you 
suggest commit every 1000 or 3000 records?


Thanks a lot!
Emi


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: [PERFORM] autocommit (true/false) for more than 1 million records

2014-08-22 Thread Stephen Frost
* Emi Lu (em...@encs.concordia.ca) wrote:
> Hello,
> 
> Trying to insert into one table with 1 million records through java
> JDBC into psql8.3. May I know (1) or (2) is better please?
> 
> (1) set autocommit(true)
> (2) set autocommit(false)
>  commit every n records (e.g., 100, 500, 1000, etc)

It depends on what you need.

Data will be available to concurrent processes earlier with (1), while
(2) will go faster.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [PERFORM] autocommit (true/false) for more than 1 million records

2014-08-22 Thread David G Johnston
Emi Lu-2 wrote
> Hello,
> 
> Trying to insert into one table with 1 million records through java JDBC 
> into psql8.3. May I know (1) or (2) is better please?
> 
> (1) set autocommit(true)
> (2) set autocommit(false)
>   commit every n records (e.g., 100, 500, 1000, etc)
> 
> Thanks a lot!
> Emi

Typically the larger the n the better.  Locking and risk of data loss on a
failure are the tradeoffs to consider.  Other factors, like memory, make
choosing too large an n bad so using 500,000 is probably wrong but 500 is
probably overly conservative.  Better advice depends on context and
hardware.

You should also consider upgrading to a newer, supported, version of
PostgreSQL.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/autocommit-true-false-for-more-than-1-million-records-tp5815943p5815946.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[PERFORM] autocommit (true/false) for more than 1 million records

2014-08-22 Thread Emi Lu

Hello,

Trying to insert into one table with 1 million records through java JDBC 
into psql8.3. May I know (1) or (2) is better please?


(1) set autocommit(true)
(2) set autocommit(false)
 commit every n records (e.g., 100, 500, 1000, etc)

Thanks a lot!
Emi




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

2014-08-22 Thread Andres Freund
On 2014-08-21 14:02:26 -0700, Josh Berkus wrote:
> On 08/20/2014 07:40 PM, Bruce Momjian wrote:
> > Not sure how you can make such a blanket statement when so many people
> > have tested and shown the benefits of hyper-threading.  
> 
> Actually, I don't know that anyone has posted the benefits of HT.
> Link?

There's definitely cases where it can help. But it's highly workload
*and* hardware dependent.

> OS is RHEL with 2.6.32-431.3.1.el6.x86_64.
> 
> I've emailed a kernel hacker who works at Intel for comment; for one
> thing, I'm wondering if the older kernel version is a problem for a
> system like this.

I'm not sure if it has been backported by redhat, but there
definitely have been significant improvement in SMT aware scheduling
after vanilla 2.6.32.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

2014-08-22 Thread Shaun Thomas

On 08/22/2014 01:37 AM, Scott Marlowe wrote:


I thought they were fixed in 3.8.something? We're running 3.8 on our
production servers but IO is not an issue for us.


Yeah. 3.8 fixed a ton of issues that were plaguing us. There were still 
a couple patches I wanted that didn't get in until 3.11+, but the worst 
of the behavior was solved before that.


Bugs in kernel cache page aging algorithms are bad, m'kay?

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance