Hi. Thanks for responding.
As it happens, the client-side already has a connection pool.
We need statistics enabled so that autovacuum can run (without
autovacuum running our updates begin to kill us pretty quickly).
Moving off of Solaris 9 isn't an option, even for the purposes of
comparison, unfortunately.
On limiting the client side connections: we've been gradually pushing up
the client-side connection pool and threads, and have seen steady
improvement in our throughput up to the current barrier we have reached.
I guess the idea would be that backing off on the connections would
allow each operation to finish faster, but that hasn't been the observed
behavior so far.
I've attached the plans for the 4 queries that represent ~35% of our
load. These are run against the same dataset, but without any other
load. Another big query basically requires a test to be runnning because
the data is transient, and I can't run that at the moment. The times for
the individual queries is really fine - it's just they are called 3
times for every logical "unit of work" on the client side, so they are
called thousands of times in a given test (hence the need for client
caching).
Thanks.
- DAP
>-----Original Message-----
>From: Rod Taylor [mailto:rbt@xxxxxxxxxxxx]
>Sent: Friday, November 26, 2004 1:29 PM
>To: David Parker
>Cc: Postgresql Performance
>Subject: Re: time to stop tuning?
>
>On Fri, 2004-11-26 at 12:13 -0500, David Parker wrote:
>>
>> I suspect the ultimate answer to our problem will be:
>>
>> 1) aggressive client-side caching
>> 2) SQL tuning
>> 3) more backend hardware
>
>#0 might actually be using connection pooling and using cached
>query plans (PREPARE), disabling the statistics daemon, etc.
>
>For the plans, send us EXPLAIN ANALYZE output for each of the
>common queries.
>
>If you can try it, I'd give a try at FreeBSD or a newer Linux
>on your system instead of Solaris. Older versions of Solaris
>had not received the same amount of attention for Intel
>hardware as the BSDs and Linux have and I would imagine
>(having not tested it recently) that this is still true for
>32bit Intel.
>
>Another interesting test might be to limit the number of
>simultaneous connections to 8 instead of 30 (client side
>connection retry) after client side connection pooling via
>pgpool or similar has been installed.
>
>Please report back with your findings.
>--
>Rod Taylor <rbt@xxxxxxxxxxxx>
>
>
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.38..10.93 rows=1 width=93) (actual time=0.501..0.522 rows=1 loops=1)
Join Filter: (subplan)
-> Hash Join (cost=1.06..2.15 rows=1 width=36) (actual time=0.097..0.109 rows=1 loops=1)
Hash Cond: ("outer".id = "inner".devicetype)
-> Seq Scan on devicetype dt (cost=0.00..1.05 rows=5 width=36) (actual time=0.003..0.009 rows=5 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=36) (actual time=0.037..0.037 rows=0 loops=1)
-> Seq Scan on device d (cost=0.00..1.06 rows=1 width=36) (actual time=0.018..0.021 rows=1 loops=1)
Filter: ((name)::text = 'perftoolBAS'::text)
-> Hash Join (cost=1.31..6.11 rows=2 width=129) (actual time=0.172..0.253 rows=2 loops=1)
Hash Cond: ("outer".devicerule = "inner".id)
-> Seq Scan on devicescript ds (cost=0.00..4.55 rows=44 width=165) (actual time=0.007..0.074 rows=44 loops=1)
Filter: ((scripttype)::text = 'CLI'::text)
-> Hash (cost=1.31..1.31 rows=1 width=36) (actual time=0.065..0.065 rows=0 loops=1)
-> Seq Scan on devicerule dr (cost=0.00..1.31 rows=1 width=36) (actual time=0.008..0.021 rows=1 loops=1)
Filter: ((name)::text = '256k_downstream'::text)
SubPlan
-> Seq Scan on devicescript_devicetype dsdt (cost=0.00..2.62 rows=10 width=36) (actual time=0.005..0.053 rows=18 loops=2)
Filter: (possession = $0)
Total runtime: 0.647 ms
(19 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2.16..3.25 rows=1 width=334) (actual time=0.084..0.096 rows=1 loops=1)
Hash Cond: ("outer".id = "inner".devicetype)
-> Seq Scan on devicetype dt (cost=0.00..1.05 rows=5 width=67) (actual time=0.002..0.007 rows=5 loops=1)
-> Hash (cost=2.16..2.16 rows=1 width=339) (actual time=0.066..0.066 rows=0 loops=1)
-> Hash Join (cost=1.06..2.16 rows=1 width=339) (actual time=0.047..0.062 rows=1 loops=1)
Hash Cond: ("outer".device = "inner".id)
-> Seq Scan on mgmtprotocol mp (cost=0.00..1.06 rows=5 width=327) (actual time=0.004..0.010 rows=5 loops=1)
Filter: (("type")::text = 'CLI'::text)
-> Hash (cost=1.06..1.06 rows=1 width=84) (actual time=0.024..0.024 rows=0 loops=1)
-> Seq Scan on device d (cost=0.00..1.06 rows=1 width=84) (actual time=0.017..0.019 rows=1 loops=1)
Filter: ((ipaddress)::text = '10.1.10.60'::text)
Total runtime: 0.176 ms
(12 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.31..3.77 rows=2 width=16) (actual time=0.117..0.174 rows=1 loops=1)
Hash Cond: ("outer".devicerule = "inner".id)
-> Seq Scan on devruleparameter drp (cost=0.00..2.29 rows=29 width=52) (actual time=0.004..0.034 rows=29 loops=1)
-> Hash (cost=1.31..1.31 rows=1 width=36) (actual time=0.035..0.035 rows=0 loops=1)
-> Seq Scan on devicerule dr (cost=0.00..1.31 rows=1 width=36) (actual time=0.016..0.030 rows=1 loops=1)
Filter: ((name)::text = '256k_downstream'::text)
Total runtime: 0.221 ms
(7 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.06..2.15 rows=1 width=14) (actual time=0.053..0.053 rows=0 loops=1)
Hash Cond: ("outer".devicetype = "inner".id)
-> Seq Scan on device d (cost=0.00..1.05 rows=5 width=50) (actual time=0.002..0.008 rows=5 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=36) (actual time=0.017..0.017 rows=0 loops=1)
-> Seq Scan on devicetype dt (cost=0.00..1.06 rows=1 width=36) (actual time=0.015..0.015 rows=0 loops=1)
Filter: ((vendor)::text = 'P-Cube'::text)
Total runtime: 0.102 ms
(7 rows)
