Re: Finding bottleneck

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 - tuner(at)bdb(dot)fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""

On Mon, 8 Aug 2005, Merlin Moncure wrote:

>> Kari Lavikka <tuner(at)bdb(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>