Message-ID: <6df55552430e183a729aa039ff729f06.squirrel@sq.gransy.com>
In-Reply-To: <006c01cd08dd$205af940$6110ebc0$@com>
References: <006301cd07fd$358ebe40$a0ac3ac0$@com>
	<4F6AFB5902000025000465C7@gw.wicourts.gov>
	<CAHyXU0xyVixmLdiMaD2wUU6DgN2AiXi1_Avm+k7vB49BFYEPPA@mail.gmail.com>
	<006c01cd08dd$205af940$6110ebc0$@com>
Date: Fri, 23 Mar 2012 11:24:12 +0100
Subject: Re: Write workload is causing severe slowdown in
 Production
From: "Tomas Vondra" <tv@fuzzy.cz>
To: gnanam@zoniac.com
Cc: "'Merlin Moncure'" <mmoncure@gmail.com>,
	"'Kevin Grittner'" <kevin.grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.22
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Importance: Normal
Content-Transfer-Encoding: quoted-printable

On 23 B=C5=99ezen 2012, 11:10, Gnanakumar wrote:
> First off, thank you *so much* for that detailed explanation comparing
> with
> a real-time application performance benchmark, which was really
> enlightening
> for me.
>
>> How are you handling concurrency?  (Are you using FOR SHARE on your
>> SELECT statements?  Are you explicitly acquiring table locks before
>> modifying data?  Etc.)  You might be introducing blocking somehow.
>
> No, actually am not explicitly locking any tables -- all are *simple*
> select, update, insert statements only.

Are those wrapped in a transaction or not? Each transaction forces a fsyn=
c
when committing, and if each of those INSERT/UPDATE statements stands on
it's own it may cause of lot of I/O.

>> Besides the outdated PostgreSQL release and possible blocking, I
>> would be concerned if you are using any sort of ORM for the update
>> application.  You want to watch that very closely because the
>> default behavior of many of them does not scale well.  There's
>> usually a way to get better performance through configuration and/or
>> bypassing automatic query generation for complex data requests.
>
> Am not able to understand above statements (...any sort of ORM for the
> update application ...) clearly.  Can you help me in understanding this=
?

There are tools that claim to remove the object vs. relational discrepanc=
y
when accessing the database. They often generate queries on the fly, and
some of the queries are pretty awful (depends on how well the ORM model i=
s
defined). There are various reasons why this may suck - loading too much
data, using lazy fetch everywhere etc.

Are you using something like Hibernate, JPA, ... to handle persistence?

Tomas