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
