pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slowly Queries
3+ messages / 2 participants
[nested] [flat]

* Slowly Queries
@ 2006-12-14 01:06  Anton P. Linevich <lists-pgsql-admin@chexov.kiev.ua>
  0 siblings, 2 replies; 3+ messages in thread

From: Anton P. Linevich @ 2006-12-14 01:06 UTC (permalink / raw)
  To: pgsql-admin

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:

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Slowly Queries
@ 2006-12-14 02:02  Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  parent: Anton P. Linevich <lists-pgsql-admin@chexov.kiev.ua>
  1 sibling, 0 replies; 3+ messages in thread

From: Rajesh Kumar Mallah @ 2006-12-14 02:02 UTC (permalink / raw)
  To: pgsql-admin

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
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Slowly Queries
@ 2006-12-14 02:03  Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
  parent: Anton P. Linevich <lists-pgsql-admin@chexov.kiev.ua>
  1 sibling, 0 replies; 3+ messages in thread

From: Rajesh Kumar Mallah @ 2006-12-14 02:03 UTC (permalink / raw)
  To: pgsql-admin

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?
btw such questions should go to performance list.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-12-14 02:03 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-14 01:06 Slowly Queries Anton P. Linevich <lists-pgsql-admin@chexov.kiev.ua>
2006-12-14 02:02 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-14 02:03 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>

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