From: antthelimey <antthelimey@gmail.com>
To: pgsql-performance@postgresql.org
Subject: "Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
Date: Fri, 28 Sep 2012 06:52:30 -0700 (PDT)
Message-ID: <1348840349990-5725853.post@n5.nabble.com> (raw)
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
we are using PGSQL 9.1
does anyone have ANY ideas?!
thanks much
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Select-on-12-18M-row-table-from-remote-machine-thru-JDBC-Per...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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: antthelimey@gmail.com
Subject: Re: "Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
In-Reply-To: <1348840349990-5725853.post@n5.nabble.com>
* 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