From: Tom Lane <tgl@sss.pgh.pa.us>
To: Shivakumar Ramannavar <shivasr@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Fwd: My Query to insert and retrieve takes time
Date: Fri, 04 Nov 2011 10:08:06 -0400
Message-ID: <6405.1320415686@sss.pgh.pa.us> (raw)
In-Reply-To: <CACdOLXae4kZcKBHpe4vyhSy589qV06FUtoEoD=33yZLGBDVNgw@mail.gmail.com>
References: <CACdOLXa12h6oN406mqZAZaCJG3G6hK=eTx_YhthJZU6vomPkAA@mail.gmail.com>
<CACdOLXae4kZcKBHpe4vyhSy589qV06FUtoEoD=33yZLGBDVNgw@mail.gmail.com>
Shivakumar Ramannavar <shivasr@gmail.com> writes:
> Here is some observation drawn from PostgreSQL performance testing:
> There are around 310,000 records, it takes 8.313 milliseconds to
> retrieval/search a record, and it takes 8.321 milliseconds more to update a
> record.
It was already pointed out to you that these numbers are suspiciously
close together, and also suspiciously close to the rotation time of a
7200RPM disk. It seems very likely that you have configured things so
that each operation is done in a transaction that has to be explicitly
committed to disk, thus requiring a write to WAL, which would limit the
transaction rate to one per rotation. However, since you haven't shown
us anything about how the queries are being issued, we can't confirm
or disprove that theory.
Consider batching multiple operations into a single transaction.
Turning off synchronous_commit is another possibility, depending on
what your crash-safety requirements are.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, shivasr@gmail.com
Subject: Re: Fwd: My Query to insert and retrieve takes time
In-Reply-To: <6405.1320415686@sss.pgh.pa.us>
* 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