Thread: Performance Tuning
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
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 =:->
Attachment
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
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
> 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
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
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.
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.
> 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.
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
>> 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...)
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
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>