Re: Finding bottleneck - Mailing list pgsql-performance

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.39rows=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=1loops=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.866rows=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 - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
       ""

On Mon, 8 Aug 2005, Merlin Moncure wrote:

>> Kari Lavikka <tuner@bdb.fi> 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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>