From: Gavin Sherry <swm@alcove.com.au>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow concurrent update of same row in a given table
Date: Wed, 28 Sep 2005 22:53:35 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0509282245040.19538@linuxworld.com.au> (raw)
In-Reply-To: <a97c770305092805271ff5e83f@mail.gmail.com>
References: <a97c770305092805271ff5e83f@mail.gmail.com>
On Wed, 28 Sep 2005, Rajesh Kumar Mallah wrote:
> Hi
>
> While doing some stress testing for updates in a small sized table
> we found the following results. We are not too happy about the speed
> of the updates particularly at high concurrency (10 clients).
>
> Initially we get 119 updates / sec but it drops to 10 updates/sec
> as concurrency is increased.
>
> PostgreSQL: 8.0.3
> -------------------------------
> TABLE STRUCTURE: general.stress
> -------------------------------
> | dispatch_id | integer | not null |
> | query_id | integer | |
> | generated | timestamp with time zone | |
> | unsubscribes | integer | |
> | read_count | integer | |
> | status | character varying(10) | |
> | bounce_tracking | boolean | |
> | dispatch_hour | integer | |
> | dispatch_date_id | integer | |
> +------------------+--------------------------+-----------+
> Indexes:
> "stress_pkey" PRIMARY KEY, btree (dispatch_id)
>
> UPDATE STATEMENT:
> update general.stress set read_count=read_count+1 where dispatch_id=114
This means you are updating only one row, correct?
> Number of Copies | Update perl Sec
>
> 1 --> 119
> 2 ---> 59
> 3 ---> 38
> 4 ---> 28
> 5 --> 22
> 6 --> 19
> 7 --> 16
> 8 --> 14
> 9 --> 11
> 10 --> 11
> 11 --> 10
So, 11 instances result in 10 updated rows per second, database wide or
per instance? If it is per instance, then 11 * 10 is close to the
performance for one connection.
That being said, when you've got 10 connections fighting over one row, I
wouldn't be surprised if you had bad performance.
Also, at 119 updates a second, you're more than doubling the table's
initial size (dead tuples) each second. How often are you vacuuming and
are you using vacuum or vacuum full?
Gavin
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: swm@alcove.com.au, mallah.rajesh@gmail.com
Subject: Re: Slow concurrent update of same row in a given table
In-Reply-To: <Pine.LNX.4.58.0509282245040.19538@linuxworld.com.au>
* 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