pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
time to stop tuning?
5+ messages / 3 participants
[nested] [flat]

* time to stop tuning?
@ 2004-11-26 17:13  David Parker <dparker@tazznetworks.com>
  0 siblings, 2 replies; 5+ messages in thread

From: David Parker @ 2004-11-26 17:13 UTC (permalink / raw)
  To: pgsql-performance

We have a network application in which many clients will be executing a mix of select/insert/update/deletes on a central postgres 7.4.5 database, running on Solaris 9 running on dual 2.3 ghz Xeons, with 2 gig of RAM and a RAID 10 disk. The test database is about 400 meg in size.

We have tuned the postgresql.conf parameters to the point where we are confident we have enough memory for shared buffers and for sorting. We are still tuning SQL statements, but we're pretty sure the big wins have been achieved.

We are maxing out on the backend with 30 postmaster processes, each taking up about 2.5-3% of the CPU. We have tested mounting the whole database in /tmp, hence in memory, and it has made no difference in performance, so it seems we are purely CPU bound at this point.

About 70% of our time is spent in selects, and another 25% spent in inserts/updates of a single table (about 10% out of the selects % is against this table).

Now, our application client is not doing nearly enough of it's own caching, so a lot the work the database is doing currently is redundant, and we are working on the client, but in the meantime we have to squeeze as much as we can from the backend.

After that long intro, I have a couple of questions:

1) Given that the data is all cached, what can we do to make sure that postgres is generating
the most efficient plans in this case? We have bumped up effective_cache_size, but it had no
effect. Also, what would the most efficient plan for in-memory data look like? I mean, does one
still look for the normal stuff - index usage, etc., or are seqscans what we should be looking for?
I've seen some stuff about updating statistics targets for specific tables, but I'm not sure I 
understand it, and don't know if something like that applies in this case. I can supply some specific plans, if that would help (this email is already too long...).

2) We have SQL test environment where we just run the SQL statements executed by the clients (culled from the log file) in psql. In our test environment, the same set of SQL statements runs 4X faster that the times achieved in the test that generated our source log file. Obviously there was a bigger load on the machine in the full test, but I'm wondering if there are any particular diagnostics that I should be looking at to ferret out contention. I haven't seen anything that looked suspicious in pg_locks, but it's difficult to interpret that data when the database is under load (at least for someone of my limited experience).

I suspect the ultimate answer to our problem will be:

   1) aggressive client-side caching
   2) SQL tuning
   3) more backend hardware

But I would grateful to hear any tips/anecdotes/experiences that others might have from tuning similar applications.

Thanks!

- DAP
----------------------------------------------------------------------------------
David Parker    Tazz Networks    (401) 709-5130
 



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: time to stop tuning?
@ 2004-11-26 18:29  Rod Taylor <rbt@sitesell.com>
  parent: David Parker <dparker@tazznetworks.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Rod Taylor @ 2004-11-26 18:29 UTC (permalink / raw)
  To: David Parker <dparker@tazznetworks.com>; +Cc: pgsql-performance

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@sitesell.com>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: time to stop tuning?
@ 2004-11-26 19:04  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David Parker <dparker@tazznetworks.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2004-11-26 19:04 UTC (permalink / raw)
  To: David Parker <dparker@tazznetworks.com>; +Cc: pgsql-performance

"David Parker" <dparker@tazznetworks.com> writes:
> 1) Given that the data is all cached, what can we do to make sure that
> postgres is generating the most efficient plans in this case? We have
> bumped up effective_cache_size, but it had no effect.

If you're willing to bet on everything being in RAM all the time,
dropping random_page_cost to 1 would be a theoretically sound thing
to do.  In any case you should look at reducing it considerably from
the default setting of 4.

Something that might also be interesting is to try increasing all the
cpu_xxx cost factors, on the theory that since the unit of measurement
(1 sequential page fetch) relates to an action involving no actual I/O,
the relative costs of invoking an operator, etc, should be rated higher
than when you expect actual I/O.  I'm not real confident that this would
make things better --- you might find that any improvement would be
swamped by the low accuracy with which we model CPU costs (such as the
assumption that every operator costs the same to evaluate).  But it's
worth some experimentation.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: time to stop tuning?
@ 2004-11-26 19:16  David Parker <dparker@tazznetworks.com>
  0 siblings, 1 reply; 5+ messages in thread

From: David Parker @ 2004-11-26 19:16 UTC (permalink / raw)
  To: Rod Taylor <rbt@sitesell.com>; +Cc: pgsql-performance

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@sitesell.com] 
>Sent: Friday, November 26, 2004 1:29 PM
>To: David Parker
>Cc: Postgresql Performance
>Subject: Re: [PERFORM] 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@sitesell.com>
>
>

Attachments:

plan.txt (text/plain, 4.5K) download 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)
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: time to stop tuning?
@ 2004-11-26 21:48  Rod Taylor <rbt@sitesell.com>
  parent: David Parker <dparker@tazznetworks.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Rod Taylor @ 2004-11-26 21:48 UTC (permalink / raw)
  To: David Parker <dparker@tazznetworks.com>; +Cc: pgsql-performance

> 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.

Very well.. Sometimes more simultaneous workers helps, other times it
hinders.

> 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

Those aren't likely from your production system as there isn't any data
in those tables and the queries took less than 1ms.

-- 
Rod Taylor <rbt@sitesell.com>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-11-26 21:48 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-26 17:13 time to stop tuning? David Parker <dparker@tazznetworks.com>
2004-11-26 18:29 ` Rod Taylor <rbt@sitesell.com>
2004-11-26 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-26 19:16 Re: time to stop tuning? David Parker <dparker@tazznetworks.com>
2004-11-26 21:48 ` Rod Taylor <rbt@sitesell.com>

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