Thread: "Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
"Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
From
antthelimey
Date:
On machine 1 - a table that contains between 12 and 18 million rows On machine 2 - a Java app that calls Select * on the table, and writes it into a Lucene index Originally had a fetchSize of 10,000 and would take around 38 minutes for 12 million, 50 minutes for 16ish million to read it all & write it all back out as the lucene index One day it started taking 4 hours. If something changed, we dont know what it was We tracked it down to, after 10 million or so rows, the Fetch to get the next 10,000 rows from the DB goes from like 1 second to 30 seconds, and stays there After spending a week of two devs & DBA trying to solve this, we eventually "solved" it by upping the FetchRowSize in the JDBC call to 50,000 It was performing well enough again for a few weeks then...one day... it started taking 4 hours again we tried upping the shared_buffer from 16GB to 20GB And last night... it took 7 hours does anyone have ANY ideas?! we are on postgres 9.1 the Server merely shows an idle transaction, waiting to be asked for the next bunch of rows - not IO or CPU bound thanks much -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-on-12-18M-row-table-from-remote-machine-thru-JDBC-Performance-nose-dives-after-10M-ish-records-tp5725854.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
Re: "Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
From
Luis Flores
Date:
Try to run the software with -XX:+PrintGCDetails probably the Java app is memory starved. You can also try to run the java program with the Netbeans Profiler or with VisualVM and check what's blocking the process. Luis Flores On Fri, Sep 28, 2012 at 3:05 PM, antthelimey <antthelimey@gmail.com> wrote: > On machine 1 - a table that contains between 12 and 18 million rows > On machine 2 - a Java app that calls Select * on the table, and writes it > into a Lucene index > > Originally had a fetchSize of 10,000 and would take around 38 minutes for 12 > million, 50 minutes for 16ish million to read it all & write it all back out > as the lucene index > > One day it started taking 4 hours. If something changed, we dont know what > it was > > We tracked it down to, after 10 million or so rows, the Fetch to get the > next 10,000 rows from the DB goes from like 1 second to 30 seconds, and > stays there > > After spending a week of two devs & DBA trying to solve this, we eventually > "solved" it by upping the FetchRowSize in the JDBC call to 50,000 > > It was performing well enough again for a few weeks > > then...one day... it started taking 4 hours again > > we tried upping the shared_buffer from 16GB to 20GB > > And last night... it took 7 hours > > does anyone have ANY ideas?! > > we are on postgres 9.1 > > the Server merely shows an idle transaction, waiting to be asked for the > next bunch of rows - not IO or CPU bound > > thanks much > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Select-on-12-18M-row-table-from-remote-machine-thru-JDBC-Performance-nose-dives-after-10M-ish-records-tp5725854.html > Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com. > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc