pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance Tuning
15+ messages / 10 participants
[nested] [flat]
* Performance Tuning
@ 2005-02-09 20:01 Chris Kratz <chris.kratz@vistashare.com>
0 siblings, 3 replies; 15+ messages in thread
From: Chris Kratz @ 2005-02-09 20:01 UTC (permalink / raw)
To: pgsql-performance
Hello All,
In contrast to what we hear from most others on this list, we find our
database servers are mostly CPU bound. We are wondering if this is because
we have postgres configured incorrectly in some way, or if we really need
more powerfull processor(s) to gain more performance from postgres.
We continue to tune our individual queries where we can, but it seems we still
are waiting on the db a lot in our app. When we run most queries, top shows
the postmaster running at 90%+ constantly during the duration of the request.
The disks get touched occasionally, but not often. Our database on disk is
around 2.6G and most of the working set remains cached in memory, hence the
few disk accesses. All this seems to point to the need for faster
processors.
Our question is simply this, is it better to invest in a faster processor at
this point, or are there configuration changes to make it faster? I've done
some testing with with 4x SCSI 10k and the performance didn't improve, in
fact it actually was slower the the sata drives marginally. One of our
developers is suggesting we should compile postgres from scratch for this
particular processor, and we may try that. Any other ideas?
-Chris
On this particular development server, we have:
Athlon XP,3000
1.5G Mem
4x Sata drives in Raid 0
Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1
Items changed in the postgresql.conf:
tcpip_socket = true
max_connections = 32
port = 5432
shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each
sort_mem=16384
vacuum_mem = 32768 # min 1024, size in KB
max_fsm_pages = 60000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
effective_cache_size = 115200 # typically 8KB each
random_page_cost = 1 # units are one sequential page fetch cost
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 20:27 Rod Taylor <rbt@sitesell.com>
parent: Chris Kratz <chris.kratz@vistashare.com>
2 siblings, 1 reply; 15+ messages in thread
From: Rod Taylor @ 2005-02-09 20:27 UTC (permalink / raw)
To: Chris Kratz <chris.kratz@vistashare.com>; +Cc: pgsql-performance
On Wed, 2005-02-09 at 15:01 -0500, Chris Kratz wrote:
> Hello All,
>
> In contrast to what we hear from most others on this list, we find our
> database servers are mostly CPU bound. We are wondering if this is because
> we have postgres configured incorrectly in some way, or if we really need
> more powerfull processor(s) to gain more performance from postgres.
Not necessarily. I had a very disk bound system, bought a bunch of
higher end equipment (which focuses on IO) and now have a (faster) but
CPU bound system.
It's just the way the cookie crumbles.
Some things to watch for are large calculations which are easy to move
client side, such as queries that sort for display purposes. Or data
types which aren't really required (using numeric where an integer would
do).
> We continue to tune our individual queries where we can, but it seems we still
> are waiting on the db a lot in our app. When we run most queries, top shows
> the postmaster running at 90%+ constantly during the duration of the request.
Is this for the duration of a single request or 90% constantly?
If it's a single request, odds are you're going through much more
information than you need to. Lots of aggregate work (max / min) perhaps
or count(*)'s where an approximation would do?
> Our question is simply this, is it better to invest in a faster processor at
> this point, or are there configuration changes to make it faster? I've done
If it's for a single request, you cannot get single processors which are
much faster than what you describe as having.
Want to send us a few EXPLAIN ANALYZE's of your longer running queries?
--
Rod Taylor <rbt@sitesell.com>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 20:38 John Arbash Meinel <john@arbash-meinel.com>
parent: Chris Kratz <chris.kratz@vistashare.com>
2 siblings, 1 reply; 15+ messages in thread
From: John Arbash Meinel @ 2005-02-09 20:38 UTC (permalink / raw)
To: Chris Kratz <chris.kratz@vistashare.com>; +Cc: pgsql-performance
Chris Kratz wrote:
>Hello All,
>
>In contrast to what we hear from most others on this list, we find our
>database servers are mostly CPU bound. We are wondering if this is because
>we have postgres configured incorrectly in some way, or if we really need
>more powerfull processor(s) to gain more performance from postgres.
>
>
>
If everything is cached in ram, it's pretty easy to be CPU bound. You
very easily could be at this point if your database is only 2.6G and you
don't touch all the tables often.
I do believe that when CPU bound, the best thing to do is get faster CPUs.
...
>Our question is simply this, is it better to invest in a faster processor at
>this point, or are there configuration changes to make it faster? I've done
>some testing with with 4x SCSI 10k and the performance didn't improve, in
>fact it actually was slower the the sata drives marginally. One of our
>developers is suggesting we should compile postgres from scratch for this
>particular processor, and we may try that. Any other ideas?
>
>-Chris
>
>On this particular development server, we have:
>
>Athlon XP,3000
>1.5G Mem
>4x Sata drives in Raid 0
>
>
>
I'm very surprised you are doing RAID 0. You realize that if 1 drive
goes out, your entire array is toast, right? I would recommend doing
either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes.
Probably most important, though is to look at the individual queries and
see what they are doing.
>Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1
>
>Items changed in the postgresql.conf:
>
>tcpip_socket = true
>max_connections = 32
>port = 5432
>shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each
>sort_mem=16384
>vacuum_mem = 32768 # min 1024, size in KB
>max_fsm_pages = 60000 # min max_fsm_relations*16, 6 bytes each
>max_fsm_relations = 1000 # min 100, ~50 bytes each
>effective_cache_size = 115200 # typically 8KB each
>random_page_cost = 1 # units are one sequential page fetch cost
>
>
Most of these seem okay to me, but random page cost is *way* too low.
This should never be tuned below 2. I think this says "an index scan of
*all* rows is as cheap as a sequential scan of all rows." and that
should never be true.
What could actually be happening is that you are getting index scans
when a sequential scan would be faster.
I don't know what you would see, but what does "explain analyze select
count(*) from blah;" say. If it is an index scan, you have your machine
mistuned. select count(*) always grabs every row, and this is always
cheaper with a sequential scan.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 20:59 Greg Stark <gsstark@mit.edu>
parent: Chris Kratz <chris.kratz@vistashare.com>
2 siblings, 1 reply; 15+ messages in thread
From: Greg Stark @ 2005-02-09 20:59 UTC (permalink / raw)
To: Chris Kratz <chris.kratz@vistashare.com>; +Cc: pgsql-performance
Chris Kratz <chris.kratz@vistashare.com> writes:
> We continue to tune our individual queries where we can, but it seems we still
> are waiting on the db a lot in our app. When we run most queries, top shows
> the postmaster running at 90%+ constantly during the duration of the request.
> The disks get touched occasionally, but not often. Our database on disk is
> around 2.6G and most of the working set remains cached in memory, hence the
> few disk accesses. All this seems to point to the need for faster
> processors.
I would suggest looking at the top few queries that are taking the most
cumulative time on the processor. It sounds like the queries are doing a ton
of logical i/o on data that's cached in RAM. A few indexes might cut down on
the memory bandwidth needed to churn through all that data.
> Items changed in the postgresql.conf:
> ...
> random_page_cost = 1 # units are one sequential page fetch cost
This makes it nigh impossible for the server from ever making a sequential
scan when an index would suffice. What query made you do this? What plan did
it fix?
--
greg
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 21:25 Chris Kratz <chris.kratz@vistashare.com>
parent: John Arbash Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 15+ messages in thread
From: Chris Kratz @ 2005-02-09 21:25 UTC (permalink / raw)
To: John Arbash Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
On Wednesday 09 February 2005 03:38 pm, John Arbash Meinel wrote:
>...
> I'm very surprised you are doing RAID 0. You realize that if 1 drive
> goes out, your entire array is toast, right? I would recommend doing
> either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes.
<grin> Yeah, we know. This is a development server and we drop and reload
databases regularly (sometimes several times a day). In this case we don't
really care about the integrity of the data since it's for our developers to
test code against. Also, the system is on a mirrored set of drives. On our
live servers we have hardware raid 1 at this point for the data drives. When
I/O becomes a bottleneck, we are planning on moving to Raid 10 for the data
and Raid 1 for the transaction log with as many drives as I can twist arms
for. Up to this point it has been easier just to stuff the servers full of
memory and let the OS cache the db in memory. We know that at some point
this will no longer work, but for now it is.
As a side note, I learned something very interesting for our developers here.
We had been doing a drop database and then a reload off a db dump from our
live server for test data. This takes 8-15 minutes depending on the server
(the one above takes about 8 minutes). I learned through testing that I can
use create database template some_other_database and make a duplicate in
about 2.5 minutes. which is a huge gain for us. We can load a pristine copy,
make a duplicate, do our testing on the duplicate, drop the duplicate and
create a new duplicate in less then five mintes.
Cool.
> Probably most important, though is to look at the individual queries and
> see what they are doing.
>
> >Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1
> >
> >Items changed in the postgresql.conf:
> >
> >tcpip_socket = true
> >max_connections = 32
> >port = 5432
> >shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each
> >sort_mem=16384
> >vacuum_mem = 32768 # min 1024, size in KB
> >max_fsm_pages = 60000 # min max_fsm_relations*16, 6 bytes each
> >max_fsm_relations = 1000 # min 100, ~50 bytes each
> >effective_cache_size = 115200 # typically 8KB each
> >random_page_cost = 1 # units are one sequential page fetch cost
>
> Most of these seem okay to me, but random page cost is *way* too low.
> This should never be tuned below 2. I think this says "an index scan of
> *all* rows is as cheap as a sequential scan of all rows." and that
> should never be true.
You caught me. I actually tweaked that today after finding a page that
suggested doing that if the data was mostly in memory. I have been running
it at 2, and since we didn't notice any improvement, it will be going back to
2.
> What could actually be happening is that you are getting index scans
> when a sequential scan would be faster.
>
> I don't know what you would see, but what does "explain analyze select
> count(*) from blah;" say. If it is an index scan, you have your machine
> mistuned. select count(*) always grabs every row, and this is always
> cheaper with a sequential scan.
>
> John
> =:->
With a random_page_cost set to 1, on a larger table a select count(*) nets
this...
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9848.12..9848.12 rows=1 width=0) (actual
time=4916.869..4916.872 rows=1 loops=1)
-> Seq Scan on answer (cost=0.00..8561.29 rows=514729 width=0) (actual
time=0.011..2624.202 rows=514729 loops=1)
Total runtime: 4916.942 ms
(3 rows)
Now here is a very curious thing. If I turn on timing and run the count
without explain analyze, I get...
count
--------
514729
(1 row)
Time: 441.539 ms
How odd. Running the explain adds 4.5s to it. Running the explain again goes
back to almost 5s. Now I wonder why that would be different.
Changing random cpu cost back to 2 nets little difference (4991.940ms for
explain and 496ms) But we will leave it at that for now.
--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 22:08 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 15+ messages in thread
From: Merlin Moncure @ 2005-02-09 22:08 UTC (permalink / raw)
To: Chris Kratz <chris.kratz@vistashare.com>; +Cc: pgsql-performance
> Hello All,
>
> In contrast to what we hear from most others on this list, we find our
> database servers are mostly CPU bound. We are wondering if this is
> because
> we have postgres configured incorrectly in some way, or if we really
need
> more powerfull processor(s) to gain more performance from postgres.
Yes, many apps are not I/O bound (mine isn't). Here are factors that
are likely to make your app CPU bound:
1. Your cache hit ratio is very high
2. You have a lot of concurrency.
3. Your queries are complex, for example, doing sorting or statistics
analysis
4. Your queries are simple, but the server has to process a lot of them
(transaction overhead becomes significant) sequentially.
5. You have context switching problems, etc.
On the query side, you can tune things down considerably...try and keep
sorting down to a minimum (order on keys, avoid distinct where possible,
use 'union all', not 'union'). Basically, reduce individual query time.
Other stuff:
For complex queries, use views to cut out plan generation.
For simple but frequently run queries (select a,b,c from t where k), use
parameterized prepared statements for a 50% cpu savings, this may not be
an option in some client interfaces.
On the hardware side, you will get improvements by moving to Opteron,
etc.
Merlin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 22:15 Chris Kratz <chris.kratz@vistashare.com>
parent: Rod Taylor <rbt@sitesell.com>
0 siblings, 0 replies; 15+ messages in thread
From: Chris Kratz @ 2005-02-09 22:15 UTC (permalink / raw)
To: Rod Taylor <rbt@sitesell.com>; +Cc: pgsql-performance
On Wednesday 09 February 2005 03:27 pm, you wrote:
---snip---
> > We continue to tune our individual queries where we can, but it seems we
> > still are waiting on the db a lot in our app. When we run most queries,
> > top shows the postmaster running at 90%+ constantly during the duration
> > of the request.
>
> Is this for the duration of a single request or 90% constantly?
No, this is during the processing of a request. The rest of the time, it sits
idle.
We thought we would post our config and see if there was something obvious we
were missing. I expect the only real answer is to continue to optimise the
sql our app generates since compexity seems to be the issue.
> If it's a single request, odds are you're going through much more
> information than you need to. Lots of aggregate work (max / min) perhaps
> or count(*)'s where an approximation would do?
Yes, many of our queries heavily use common aggregates and grouping. And the
explains bears out that we spend most of our time in sorts related to the
grouping, aggregating, etc. The problem we often need to get multiple
records per person, but then summarize that data per person. Our users want
Accurate, Fast and Complex. It's hard to convince them they can only have 2
of the 3. :-)
> > Our question is simply this, is it better to invest in a faster processor
> > at this point, or are there configuration changes to make it faster?
> > I've done
>
> If it's for a single request, you cannot get single processors which are
> much faster than what you describe as having.
>
> Want to send us a few EXPLAIN ANALYZE's of your longer running queries?
Many (most) of our queries are dynamic based on what the user needs.
Searches, statistics gathering, etc are all common tasks our users do.
Here is an explain from a common search giving a list of people. This runs in
about 4.2s (4.5s with web page generation) which is actually pretty amazing
when you think about what it does. It's just that we are always looking for
speed in the web environment since concurrent usage can be high at times
making the server feel less responsive. I'm looking at possibly moving this
into lazy materialized views at some point since I can't seem to make the sql
go much faster.
Sort (cost=8165.28..8198.09 rows=13125 width=324) (actual
time=4116.714..4167.915 rows=13124 loops=1)
Sort Key: system_name_id, fullname_lfm_sort
-> GroupAggregate (cost=6840.96..7267.53 rows=13125 width=324) (actual
time=2547.928..4043.255 rows=13124 loops=1)
-> Sort (cost=6840.96..6873.78 rows=13125 width=324) (actual
time=2547.876..2603.938 rows=14115 loops=1)
Sort Key: system_name_id, fullname_last_first_mdl, phone,
daytime_phone, email_address, fullname_lfm_sort, firstname, is_business, ssn,
inactive
-> Subquery Scan foo (cost=5779.15..5943.21 rows=13125
width=324) (actual time=2229.877..2459.003 rows=14115 loops=1)
-> Sort (cost=5779.15..5811.96 rows=13125 width=194)
(actual time=2229.856..2288.350 rows=14115 loops=1)
Sort Key: dem.nameid, dem.name_float_lfm_sort
-> Hash Left Join (cost=2354.58..4881.40
rows=13125 width=194) (actual time=1280.523..2139.423 rows=14115 loops=1)
Hash Cond: ("outer".relatednameid =
"inner".nameid)
-> Hash Left Join (cost=66.03..1889.92
rows=13125 width=178) (actual time=576.228..1245.760 rows=14115 loops=1)
Hash Cond: ("outer".nameid =
"inner".nameid)
-> Merge Left Join
(cost=0.00..1758.20 rows=13125 width=174) (actual time=543.056..1015.657
rows=13124 loops=1)
Merge Cond: ("outer".inactive =
"inner".validanswerid)
-> Index Scan using
namemaster_inactive_idx on namemaster dem (cost=0.00..3714.19 rows=13125
width=163) (actual time=0.594..188.219 rows=13124 loops=1)
Filter: (programid = 55)
-> Index Scan using
validanswerid_pk on validanswer ina (cost=0.00..1103.61 rows=46367 width=19)
(actual time=0.009..360.218 rows=26005 loops=1)
-> Hash (cost=65.96..65.96 rows=31
width=8) (actual time=33.053..33.053 rows=0 loops=1)
-> Nested Loop
(cost=0.00..65.96 rows=31 width=8) (actual time=0.078..25.047 rows=1874
loops=1)
-> Index Scan using
relationship_programid on relationship s (cost=0.00..3.83 rows=1 width=4)
(actual time=0.041..0.047 rows=1 loops=1)
Index Cond:
(programid = 55)
Filter:
(inter_agency_id = 15530)
-> Index Scan using
"relationshipdetail_relatio-4" on relationshipdetail r (cost=0.00..61.17
rows=77 width=12) (actual time=0.017..9.888 rows=1874 loops=1)
Index Cond:
(r.relationshipid = "outer".relationshipid)
-> Hash (cost=2142.84..2142.84 rows=58284
width=24) (actual time=704.197..704.197 rows=0 loops=1)
-> Seq Scan on namemaster rln155301
(cost=0.00..2142.84 rows=58284 width=24) (actual time=0.015..402.784
rows=58284 loops=1)
Total runtime: 4228.945 ms
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 22:17 Chris Kratz <chris.kratz@vistashare.com>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 15+ messages in thread
From: Chris Kratz @ 2005-02-09 22:17 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-performance
On Wednesday 09 February 2005 03:59 pm, Greg Stark wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > We continue to tune our individual queries where we can, but it seems we
> > still are waiting on the db a lot in our app. When we run most queries,
> > top shows the postmaster running at 90%+ constantly during the duration
> > of the request. The disks get touched occasionally, but not often. Our
> > database on disk is around 2.6G and most of the working set remains
> > cached in memory, hence the few disk accesses. All this seems to point
> > to the need for faster processors.
>
> I would suggest looking at the top few queries that are taking the most
> cumulative time on the processor. It sounds like the queries are doing a
> ton of logical i/o on data that's cached in RAM. A few indexes might cut
> down on the memory bandwidth needed to churn through all that data.
Hmmm, yes we continue to use indexes judiciously. I actually think we've
overdone it in some cases since inserts are starting to slow in some critical
areas.
> > Items changed in the postgresql.conf:
> > ...
> > random_page_cost = 1 # units are one sequential page fetch cost
>
> This makes it nigh impossible for the server from ever making a sequential
> scan when an index would suffice. What query made you do this? What plan
> did it fix?
Yes, it got set back to 2. I was testing various settings suggested by a
posting in the archives and that one didn't get reset.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 22:30 Chris Kratz <chris.kratz@vistashare.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 15+ messages in thread
From: Chris Kratz @ 2005-02-09 22:30 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-performance
On Wednesday 09 February 2005 05:08 pm, Merlin Moncure wrote:
> > Hello All,
> >
> > In contrast to what we hear from most others on this list, we find our
> > database servers are mostly CPU bound. We are wondering if this is
> > because
> > we have postgres configured incorrectly in some way, or if we really
>
> need
>
> > more powerfull processor(s) to gain more performance from postgres.
>
> Yes, many apps are not I/O bound (mine isn't). Here are factors that
> are likely to make your app CPU bound:
>
> 1. Your cache hit ratio is very high
> 2. You have a lot of concurrency.
> 3. Your queries are complex, for example, doing sorting or statistics
> analysis
For now, it's number 3. Relatively low usage, but very complex sql.
> 4. Your queries are simple, but the server has to process a lot of them
> (transaction overhead becomes significant) sequentially.
> 5. You have context switching problems, etc.
>
> On the query side, you can tune things down considerably...try and keep
> sorting down to a minimum (order on keys, avoid distinct where possible,
> use 'union all', not 'union'). Basically, reduce individual query time.
>
> Other stuff:
> For complex queries, use views to cut out plan generation.
> For simple but frequently run queries (select a,b,c from t where k), use
> parameterized prepared statements for a 50% cpu savings, this may not be
> an option in some client interfaces.
Prepared statements are not something we've tried yet. Perhaps we should look
into that in cases where it makes sense.
>
> On the hardware side, you will get improvements by moving to Opteron,
> etc.
>
> Merlin
Well, that's what we were looking for.
---
It sounds like our configuration as it stands is probably about as good as we
are going to get with the hardware we have at this point.
We are cpu bound reflecting the fact that we tend to have complex statements
doing aggregates, sorts and group bys.
The solutions appear to primarily be:
1. Going to faster hardware of which probably Opterons would be about the only
choice. And even that probably won't be a huge difference.
2. Moving to more materialized views and prepared statements where we can.
3. Continue to tweak the sql behind our app.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-09 23:58 PFC <lists@boutiquenumerique.com>
parent: Chris Kratz <chris.kratz@vistashare.com>
0 siblings, 1 reply; 15+ messages in thread
From: PFC @ 2005-02-09 23:58 UTC (permalink / raw)
To: Chris Kratz <chris.kratz@vistashare.com>; John Arbash Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
> As a side note, I learned something very interesting for our developers
> here.
> We had been doing a drop database and then a reload off a db dump from
> our
> live server for test data. This takes 8-15 minutes depending on the
> server
> (the one above takes about 8 minutes). I learned through testing that I
> can
> use create database template some_other_database and make a duplicate in
> about 2.5 minutes. which is a huge gain for us. We can load a pristine
> copy,
> make a duplicate, do our testing on the duplicate, drop the duplicate and
> create a new duplicate in less then five mintes.
I think thats because postgres just makes a file copy from the template.
Thus you could make it 2x faster if you put the template in another
tablespace on another drive.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-10 01:27 Mike Rylander <mrylander@gmail.com>
parent: Chris Kratz <chris.kratz@vistashare.com>
0 siblings, 1 reply; 15+ messages in thread
From: Mike Rylander @ 2005-02-10 01:27 UTC (permalink / raw)
To: Chris Kratz <chris.kratz@vistashare.com>; +Cc: Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-performance
On Wed, 9 Feb 2005 17:30:41 -0500, Chris Kratz
<chris.kratz@vistashare.com> wrote:
> The solutions appear to primarily be:
> 1. Going to faster hardware of which probably Opterons would be about the only
> choice. And even that probably won't be a huge difference.
I'd beg to differ on that last part. The difference between a 3.6GHz
Xeon and a 2.8GHz Opteron is ~150% speed increase on the Opteron on my
CPU bound app. This is because the memory bandwidth on the Opteron is
ENORMOUS compared to on the Xeon. Add to that the fact that you
actually get to use more than about 2G of RAM directly and you've got
the perfect platform for a high speed database on a budget.
> 2. Moving to more materialized views and prepared statements where we can.
Definitely worth investigating. I wish I could, but I can't get my
customers to even consider slightly out of date stats.... :(
> 3. Continue to tweak the sql behind our app.
Short of an Opteron based system, this is by far your best bet.
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-10 03:09 Christopher Browne <cbbrowne@acm.org>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 15+ messages in thread
From: Christopher Browne @ 2005-02-10 03:09 UTC (permalink / raw)
To: pgsql-performance
The world rejoiced as lists@boutiquenumerique.com (PFC) wrote:
>> As a side note, I learned something very interesting for our
>> developers here.
>> We had been doing a drop database and then a reload off a db dump
>> from our
>> live server for test data. This takes 8-15 minutes depending on the
>> server
>> (the one above takes about 8 minutes). I learned through testing
>> that I can
>> use create database template some_other_database and make a duplicate in
>> about 2.5 minutes. which is a huge gain for us. We can load a
>> pristine copy,
>> make a duplicate, do our testing on the duplicate, drop the duplicate and
>> create a new duplicate in less then five mintes.
>
> I think thats because postgres just makes a file copy from the
> template. Thus you could make it 2x faster if you put the template
> in another tablespace on another drive.
I had some small amusement today trying this feature out in one of our
environments today...
We needed to make a copy of one of the databases we're replicating for
the sysadmins to use for some testing.
I figured using the "template" capability was:
a) Usefully educational to one of the other DBAs, and
b) Probably a quick way to copy the data over.
We shortly discovered that we had to shut off the Slony-I daemon in
order to get exclusive access to the database; no _big_ deal.
At that point, he hit ENTER, and rather quickly saw...
CREATE DATABASE.
We then discovered that the sysadmins wanted the test DB to be on one
of the other servers. Oops. Oh, well, we'll have to do this on the
other server; no big deal.
Entertainment ensued... "My, that's taking a while..." At about the
point that we started thinking there might be a problem...
CREATE DATABASE
The entertainment was that the first box is one of those spiffy new
4-way Opteron boxes, whilst the "slow" one was a 4-way Xeon... Boy,
those Opterons are faster...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://cbbrowne.com/info/rdbms.html
"No matter how far you have gone on the wrong road, turn back."
-- Turkish proverb
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning
@ 2005-02-10 03:38 PFC <lists@boutiquenumerique.com>
parent: Mike Rylander <mrylander@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: PFC @ 2005-02-10 03:38 UTC (permalink / raw)
To: Mike Rylander <mrylander@gmail.com>; Chris Kratz <chris.kratz@vistashare.com>; +Cc: Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-performance
>> 2. Moving to more materialized views and prepared statements where we
>> can.
>
> Definitely worth investigating. I wish I could, but I can't get my
> customers to even consider slightly out of date stats.... :(
Put a button 'Stats updated every hour', which gives the results in 0.1
seconds, and a button 'stats in real time' which crunches 10 seconds
before displaying the page... if 90% of the people click on the first one
you save a lot of CPU.
Seems like people who hit Refresh every 10 seconds to see an earnings
graph creep up by half a pixel every time... but it seems it's moving !
More seriously, you can update your stats in near real time with a
materialized view, there are two ways :
- ON INSERT / ON UPDATE triggers which update the stats in real time
based on each modification
- Have statistics computed for everything until some point in time (like
an hour ago) and only compute and add stats on the records added or
modified since (but it does not work very well for deleted records...)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Performance tuning
@ 2005-03-11 15:40 Jacques Caron <jc@directinfos.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jacques Caron @ 2005-03-11 15:40 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I'm preparing a set of servers which will eventually need to handle a high
volume of queries (both reads and writes, but most reads are very simple
index-based queries returning a limited set of rows, when not just one),
and I would like to optimize things as much as possible, so I have a few
questions on the exact way PostgreSQL's MVCC works, and how transactions,
updates and vacuuming interact. I hope someone will be able to point me in
the right direction (feel free to give pointers if I missed the places
where this is described).
From what I understand (and testing confirms it), bundling many queries in
one single transaction is more efficient than having each query be a
separate transaction (like with autocommit on). However, I wonder about the
limits of this:
- are there any drawbacks to grouping hundreds or thousands of queries
(inserts/updates) over several minutes in one single transaction? Other
than the fact that the inserts/updates will not be visible until committed,
of course. Essentially turning autocommit off, and doing a commit once in a
while.
- does this apply only to inserts/selects/updates or also for selects?
Another way to put this is: does a transaction with only one select
actually have much transaction-related work to do? Or, does a transaction
with only selects actually have any impact anywhere? Does it really leave a
trace anywhere? Again, I understand that selects grouped in a transaction
will not see updates done after the start of the transaction (unless done
by the same process).
- if during a single transaction several UPDATEs affect the same row, will
MVCC generate as many row versions as there are updates (like would be the
case with autocommit) or will they be grouped into one single row version?
Another related issue is that many of the tables are indexed on a date
field, and one process does a lot of updates on "recent" rows (which lead
to many dead tuples), but after that "older" rows tend to remain pretty
much unchanged for quite a while. Other than splitting the tables into
"old" and "recent" tables, is there any way to make vacuum more efficient?
Scanning the whole table for dead tuples when only a small portion of the
table actually has any does not feel like being very efficient in this
situation.
Other issue: every five minutes or so, I see a noticeable performance drop
as PostgreSQL checkpoints. This is 7.4.3 with pretty lousy hardware, I know
8.0 with decent hardware and separate disk(s) for pg_xlog will definitely
help, but I really wonder if there is any way to reduce the amount of work
that needs to be done at that point (I'm a strong believer of fixing
software before hardware). I have already bumped checkpoint_segments to 8,
but I'm not quite sure I understand how this helps (or doesn't help)
things. Logs show 3 to 6 "recycled transaction log file" lines at that
time, that seems quite a lot of work for a load that's still pretty low.
Does grouping of more queries in transactions help with this? Are there
other parameters that can affect things, or is just a matter of how much
inserts/updates/deletes are done, and the amount of data that was changed?
Last point: some of the servers have expandable data (and will be
replicated with slony-I) and will run with fsync off. I have read
conflicting statements as to what exactly this does: some sources indicate
that setting fsync off actually switches off WAL/checkpointing, others that
it just prevents the fsync (or equivalent) system calls. Since I still see
checkpointing in that case, I guess it's not exactly the former, but I
would love to understand more about it. Really, I would love to be able to
set some tables or databases to "go as fast as you can and don't worry
about transactions, MVCC or anything like that", but I'm not sure that
option exists...
Thanks,
Jacques.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance tuning
@ 2005-03-14 09:41 Richard Huxton <dev@archonet.com>
parent: Jacques Caron <jc@directinfos.com>
0 siblings, 0 replies; 15+ messages in thread
From: Richard Huxton @ 2005-03-14 09:41 UTC (permalink / raw)
To: Jacques Caron <jc@directinfos.com>; +Cc: pgsql-performance
Jacques Caron wrote:
> I'm preparing a set of servers which will eventually need to handle a
> high volume of queries (both reads and writes, but most reads are very
> simple index-based queries returning a limited set of rows, when not
> just one), and I would like to optimize things as much as possible, so I
> have a few questions on the exact way PostgreSQL's MVCC works, and how
> transactions, updates and vacuuming interact. I hope someone will be
> able to point me in the right direction (feel free to give pointers if I
> missed the places where this is described).
>
> From what I understand (and testing confirms it), bundling many queries
> in one single transaction is more efficient than having each query be a
> separate transaction (like with autocommit on). However, I wonder about
> the limits of this:
>
> - are there any drawbacks to grouping hundreds or thousands of queries
> (inserts/updates) over several minutes in one single transaction? Other
> than the fact that the inserts/updates will not be visible until
> committed, of course. Essentially turning autocommit off, and doing a
> commit once in a while.
1. If any locks are held then they will be held for much longer, causing
other processes to block.
2. PG needs to be able to roll back the changes - thousands of simple
inserts are fine, millions will probably not be.
> - does this apply only to inserts/selects/updates or also for selects?
> Another way to put this is: does a transaction with only one select
> actually have much transaction-related work to do? Or, does a
> transaction with only selects actually have any impact anywhere? Does it
> really leave a trace anywhere? Again, I understand that selects grouped
> in a transaction will not see updates done after the start of the
> transaction (unless done by the same process).
There are implications if a SELECT has side-effects (I can call a
function in a select - that might do anything).
> - if during a single transaction several UPDATEs affect the same row,
> will MVCC generate as many row versions as there are updates (like would
> be the case with autocommit) or will they be grouped into one single row
> version?
I believe there will be many versions. Certainly for 8.0 that must be
the case to support savepoints within a transaction.
> Another related issue is that many of the tables are indexed on a date
> field, and one process does a lot of updates on "recent" rows (which
> lead to many dead tuples), but after that "older" rows tend to remain
> pretty much unchanged for quite a while. Other than splitting the tables
> into "old" and "recent" tables, is there any way to make vacuum more
> efficient? Scanning the whole table for dead tuples when only a small
> portion of the table actually has any does not feel like being very
> efficient in this situation.
Not really.
> Other issue: every five minutes or so, I see a noticeable performance
> drop as PostgreSQL checkpoints. This is 7.4.3 with pretty lousy
> hardware, I know 8.0 with decent hardware and separate disk(s) for
> pg_xlog will definitely help, but I really wonder if there is any way to
> reduce the amount of work that needs to be done at that point (I'm a
> strong believer of fixing software before hardware). I have already
> bumped checkpoint_segments to 8, but I'm not quite sure I understand how
> this helps (or doesn't help) things. Logs show 3 to 6 "recycled
> transaction log file" lines at that time, that seems quite a lot of work
> for a load that's still pretty low. Does grouping of more queries in
> transactions help with this? Are there other parameters that can affect
> things, or is just a matter of how much inserts/updates/deletes are
> done, and the amount of data that was changed?
You might be better off reducing the number of checkpoint segments, and
decreasing the timeout. There is a balance between doing a lot of work
in one go, and the overhead of many smaller bursts of activity.
> Last point: some of the servers have expandable data (and will be
> replicated with slony-I) and will run with fsync off. I have read
> conflicting statements as to what exactly this does: some sources
> indicate that setting fsync off actually switches off WAL/checkpointing,
> others that it just prevents the fsync (or equivalent) system calls.
> Since I still see checkpointing in that case, I guess it's not exactly
> the former, but I would love to understand more about it. Really, I
> would love to be able to set some tables or databases to "go as fast as
> you can and don't worry about transactions, MVCC or anything like that",
> but I'm not sure that option exists...
Setting fsync=false means the sync isn't done, so data might still be
cached below PG's level. I'm not sure it's ever going to be possible to
mark a table as "ignore transactions" - it would be a lot of work, and
means you couldn't guarantee transactions that included that table in
any way.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-03-14 09:41 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-09 20:01 Performance Tuning Chris Kratz <chris.kratz@vistashare.com>
2005-02-09 20:27 ` Rod Taylor <rbt@sitesell.com>
2005-02-09 22:15 ` Chris Kratz <chris.kratz@vistashare.com>
2005-02-09 20:38 ` John Arbash Meinel <john@arbash-meinel.com>
2005-02-09 21:25 ` Chris Kratz <chris.kratz@vistashare.com>
2005-02-09 23:58 ` PFC <lists@boutiquenumerique.com>
2005-02-10 03:09 ` Christopher Browne <cbbrowne@acm.org>
2005-02-09 20:59 ` Greg Stark <gsstark@mit.edu>
2005-02-09 22:17 ` Chris Kratz <chris.kratz@vistashare.com>
2005-02-09 22:08 Re: Performance Tuning Merlin Moncure <merlin.moncure@rcsonline.com>
2005-02-09 22:30 ` Chris Kratz <chris.kratz@vistashare.com>
2005-02-10 01:27 ` Mike Rylander <mrylander@gmail.com>
2005-02-10 03:38 ` PFC <lists@boutiquenumerique.com>
2005-03-11 15:40 Performance tuning Jacques Caron <jc@directinfos.com>
2005-03-14 09:41 ` Re: Performance tuning Richard Huxton <dev@archonet.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