From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Slowly Queries
Date: Thu, 14 Dec 2006 07:32:39 +0530
Message-ID: <a97c77030612131802g7698376ep7573c68935a52a5e@mail.gmail.com> (raw)
In-Reply-To: <20061214010656.GA18647@localhost>
References: <20061214010656.GA18647@localhost>
On 12/14/06, Anton P. Linevich <lists-pgsql-admin@chexov.kiev.ua> wrote:
> Hi guys. Can you give me a describe what should i do when get a lot of
> slowly queries?
> Last week i saw such INFO messages in postgres log:
we too faced the same problem wherin a table was updated very frequently
and with time it used to loose performance. vaccuuming the table
was not very effective.
we changed the application to insert to a seperate table whenever a banner
was viewed instead of updating view count in the banner master table. this
was very fast. but this adds to complexity.
some expert will hopefully tell the proper solution to it.
regds
mallah.
>
> LOG: duration: 8144.361 ms statement: update videos set
> watched=watched+1, last_view_date='1166057431' where id='106980'
> LOG: duration: 7825.637 ms statement: update videos set
> watched=watched+1, last_view_date='1166057431' where id='99337'
> LOG: duration: 7023.592 ms statement: insert into
> statistics_search(date,search,type,id_member) VALUES
> ('1166057432','sexy','V','0')
> LOG: duration: 6603.307 ms statement: insert into
> statistics_search(date,search,type,id_member) VALUES ('1166057432','Fun
> Day','V','0')
> LOG: duration: 6093.724 ms statement: insert into
> statistics_search(date,search,type,id_member) VALUES
> ('1166057433','party','V','0')
> LOG: duration: 5855.535 ms statement: update videos set
> watched=watched+1, last_view_date='1166057433' where id='102462'
> LOG: duration: 5464.827 ms statement: update videos set
> watched=watched+1, last_view_date='1166057433' where id='103901'
> LOG: duration: 8917.981 ms statement: update users_auth set
> date_last='1166057430' where id='384891'
> LOG: duration: 7584.912 ms statement: update videos set
> watched=watched+1, last_view_date='1166057431' where id='103164'
> LOG: duration: 5181.864 ms statement: update videos set
> watched=watched+1, last_view_date='1166057434' where id='112060'
> LOG: duration: 38394.879 ms statement: update upload_synchronizer set
> lock_status='N' where sess_id='103503'
>
> How i can tune database for quick updates?
> I have SATA RAID1.
>
> Thank you all for your answers.
>
> --
> Anton P. Linevich
>
> ---------------------------(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
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mallah.rajesh@gmail.com
Subject: Re: Slowly Queries
In-Reply-To: <a97c77030612131802g7698376ep7573c68935a52a5e@mail.gmail.com>
References: <20061214010656.GA18647@localhost> <a97c77030612131802g7698376ep7573c68935a52a5e@mail.gmail.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