DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=muStpgAY7LP5y7gdsXiW85b0hd4crws4cIub5kDrH8jY4C49fgozJtG/FZNyKaqIxEn0mJHI8422Dbty5awA7DPjLPbOhV6FjqirVFldk6z8XQEKwYdYLjB4eksGBanS7wP76xpk6ptWAjPph+m49/po5VwK+lis55DMxh0DSgY=
Message-ID: <a97c77030604031136m4579129dw26b5f032c5b2b42a@mail.gmail.com>
Date: Tue, 4 Apr 2006 00:06:45 +0530
From: "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>
To: "Kenji Morishige" <kenjim@juniper.net>
Subject: Re: optimizing db for small table with tons of updates
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060403182403.GA12257@juniper.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20060403182403.GA12257@juniper.net>

Dear Kenji,

we had similar issuse with a banner impression update system,
that had high concurrency. we modfied the system to use insert
instead of update of the same row. performance wise things are
much better , but you have to keep deleting old data.

hope you extrapolate what i mean if its applicable to your case.

Regds
Rajesh Kumar Mallah

On 4/3/06, Kenji Morishige <kenjim@juniper.net> wrote:
> I am using postgresql to be the central database for a variety of tools f=
or
> our testing infrastructure. We have web tools and CLI tools that require =
access
> to machine configuration and other states for automation.  We have one to=
ol that
> uses a table that looks like this:
>
> systest_live=3D# \d cuty
>                 Table "public.cuty"
>    Column    |           Type           | Modifiers
> -------------+--------------------------+-----------
>  resource_id | integer                  | not null
>  lock_start  | timestamp with time zone |
>  lock_by     | character varying(12)    |
>  frozen      | timestamp with time zone |
>  freeze_end  | timestamp with time zone |
>  freeze_by   | character varying(12)    |
>  state       | character varying(15)    |
> Indexes:
>     "cuty_pkey" PRIMARY KEY, btree (resource_id)
>     "cuty_main_idx" btree (resource_id, lock_start)
> Foreign-key constraints:
>     "cuty_resource_id_fkey" FOREIGN KEY (resource_id) REFERENCES resource=
(resource_id) ON UPDATE CASCADE ON DELETE CASCADE
>
> Various users run a tool that updates this table to determine if the part=
icular
> resource is available or not.  Within a course of a few days, this table =
can
> be updated up to 200,000 times.  There are only about 3500 records in thi=
s
> table, but the update and select queries against this table start to slow
> down considerablly after a few days.  Ideally, this table doesn't even ne=
ed
> to be stored and written to the filesystem.  After I run a vacuum against=
 this
> table, the overall database performance seems to rise again.  When databa=
se
> is running with recent vacuum the average server load is about .40, but a=
fter
> this table is updated 200,000+ times, the server load can go up to 5.0.
>
> here is a typical update query:
> 2006-04-03 10:53:39 PDT testtool systest_live kyoto.englab.juniper.net(48=
88) LOG:  duration: 2263.741 ms  statement: UPDATE cuty SET
>          lock_start =3D NOW(),
>          lock_by =3D 'tlim'
>         WHERE resource_id=3D'2262' and (lock_start IS NULL OR lock_start =
< (NOW() - interval '3600 second'))
>
> We used to use MySQL for these tools and we never had any issues, but I b=
elieve
> it is due to the transactional nature of Postgres that is adding an overh=
ead
> to this problem.  Are there any table options that enables the table cont=
ents
> to be maintained in ram only or have delayed writes for this particular t=
able?
>
> Thanks in advance,
> Kenji
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>