From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kari Lavikka <tuner@bdb.fi>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Finding bottleneck
Date: Mon, 08 Aug 2005 12:56:40 -0400
Message-ID: <29384.1123520200@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.HPX.4.62.0508081839420.3361@purple.bdb.fi>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD026@Herge.rcsinc.local>
<Pine.HPX.4.62.0508081839420.3361@purple.bdb.fi>
Kari Lavikka <tuner@bdb.fi> writes:
> 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.
What that sounds like to me is a machine with inadequate disk I/O bandwidth.
Your earlier comment that checkpoint drives the machine into the ground
fits right into that theory, too. You said there is "almost no IO-wait"
but are you sure you are measuring that correctly?
Something else just struck me from your first post:
> Queries accumulate and when checkpointing is over, there can be
> something like 400 queries running but over 50% of cpu is just idling.
400 queries? Are you launching 400 separate backends to do that?
Some sort of connection pooling seems like a good idea, if you don't
have it in place already. If the system's effective behavior in the
face of heavy load is to start even more concurrent backends, that
could easily drive things into the ground.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tuner@bdb.fi, merlin.moncure@rcsonline.com
Subject: Re: Finding bottleneck
In-Reply-To: <29384.1123520200@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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