Actually I modified postgresql.conf a bit and there isn't commit delay any more. That didn't make noticeable difference though..


Workload is generated by a website with about 1000 dynamic page views a second. Finland's biggest site among youths btw.

Anyway, there are about 70 tables and here's some of the most important:
             relname              |  reltuples
----------------------------------+-------------
 comment                          | 1.00723e+08
 comment_archive                  | 9.12764e+07
 channel_comment                  | 6.93912e+06
 image                            | 5.80314e+06
 admin_event                      |  5.1936e+06
 user_channel                     | 3.36877e+06
 users                            |      325929
 channel                          |      252267

Queries to "comment" table are mostly IO-bound but are performing quite well. Here's an example:

(SELECT u.nick, c.comment, c.private, c.admin, c.visible, c.parsable, c.uid_sender, to_char(c.stamp, 'DD.MM.YY HH24:MI') AS stamp, c.comment_id FROM comment c INNER JOIN users u ON u.uid = c.uid_sender WHERE u.status = 'a' AND c.image_id = 15500900 AND c.uid_target = 780345 ORDER BY uid_target DESC, image_id DESC, c.comment_id DESC) LIMIT 36

And explain analyze:
 Limit  (cost=0.00..6.81 rows=1 width=103) (actual time=0.263..17.522 rows=12 
loops=1)
   ->  Nested Loop  (cost=0.00..6.81 rows=1 width=103) (actual 
time=0.261..17.509 rows=12 loops=1)
         ->  Index Scan Backward using comment_uid_target_image_id_comment_id_20050527 on 
"comment" c  (cost=0.00..3.39 rows=1 width=92) (actual time=0.129..16.213 rows=12 
loops=1)
               Index Cond: ((uid_target = 780345) AND (image_id = 15500900))
         ->  Index Scan using users_pkey on users u  (cost=0.00..3.40 rows=1 
width=15) (actual time=0.084..0.085 rows=1 loops=12)
               Index Cond: (u.uid = "outer".uid_sender)
               Filter: (status = 'a'::bpchar)
 Total runtime: 17.653 ms


We are having performance problems with some smaller tables and very simple queries. For example:

SELECT u.uid, u.nick, extract(epoch from uc.stamp) AS stamp FROM user_channel uc INNER JOIN users u USING (uid) WHERE channel_id = 281321 AND u.status = 'a' ORDER BY uc.channel_id, upper(uc.nick)

And explain analyze:
 Nested Loop  (cost=0.00..200.85 rows=35 width=48) (actual time=0.414..38.128 
rows=656 loops=1)
   ->  Index Scan using user_channel_channel_id_nick on user_channel uc  
(cost=0.00..40.18 rows=47 width=27) (actual time=0.090..0.866 rows=667 loops=1)
         Index Cond: (channel_id = 281321)
   ->  Index Scan using users_pkey on users u  (cost=0.00..3.40 rows=1 
width=25) (actual time=0.048..0.051 rows=1 loops=667)
         Index Cond: ("outer".uid = u.uid)
         Filter: (status = 'a'::bpchar)
 Total runtime: 38.753 ms

Under heavy load these queries tend to take several minutes to execute although there's plenty of free cpu available. There aren't any blocking locks in pg_locks.

    |\__/|
    ( oo )    Kari Lavikka - [EMAIL PROTECTED] - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
      ""

On Mon, 8 Aug 2005, Merlin Moncure wrote:

Kari Lavikka <[EMAIL PROTECTED]> writes:
samples  %        symbol name
13513390 16.0074  AtEOXact_CatCache

That seems quite odd --- I'm not used to seeing that function at the
top
of a profile.  What is the workload being profiled, exactly?

He is running a commit_delay of 80000.  Could that be playing a role?

Merlin

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly


---------------------------(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 via email to