Thread: PostgreSQL performance question.
Hi all,
I have been using PostgreSQL (currently 7.4.7) for several years now and am very happy with it but I currently run a website that has had a little bit of a boost and I am starting to see some performance problems (Not necessarily PostgreSQL).
The original website server ran on a single machine Dual 1.4 Dell 1650. I moved the database off this machine and onto a Dual Opteron 248 with two SATA hard disks mirrored using software raid. The Apache server remains on the small machine along with a Squid Proxy. I also started to agressively cache most requests to the database and have taken the requests hitting the database down by about %65 using Squid and memcached. I am looking to take this to about %80 over the next few weeks. The problem is that the database has increased in size by over 100% over the same period and looks likely to increase further.
The database has been allocated 2Gb worth of shared buffers and I have tweaked most of the settings in the config recently to see if I could increase the performance any more and have seen very little performance gain for the various types of queries that I am running.
It would appear that the only alternative may be a new machine that has a better disk subsystem or a large disk array then bung more RAM in the Opteron machine (max 16Gb 4Gb fitted) or purchase another machine with built in U320 SCSI ie an HP Proliant DL380 or Dell 2850.
Some indication of current performance is as follows. I know these statements are hardly indicative of a full running application and everything that goes with it but I would be very interested in hearing if anyone has a similar setup and is able to squeeze a lot more out of PostgreSQL. From what I can see here the numbers look OK for the hardware I am running on and that its not PostgreSQL that is the problem.
Inserting 1 million rows into the following table.These are raw insert statements.
Column | Type | Modifiers
--------+------------------------+-----------
--
http://www.hjackson.org
http://www.uklug.co.uk
I have been using PostgreSQL (currently 7.4.7) for several years now and am very happy with it but I currently run a website that has had a little bit of a boost and I am starting to see some performance problems (Not necessarily PostgreSQL).
The original website server ran on a single machine Dual 1.4 Dell 1650. I moved the database off this machine and onto a Dual Opteron 248 with two SATA hard disks mirrored using software raid. The Apache server remains on the small machine along with a Squid Proxy. I also started to agressively cache most requests to the database and have taken the requests hitting the database down by about %65 using Squid and memcached. I am looking to take this to about %80 over the next few weeks. The problem is that the database has increased in size by over 100% over the same period and looks likely to increase further.
The database has been allocated 2Gb worth of shared buffers and I have tweaked most of the settings in the config recently to see if I could increase the performance any more and have seen very little performance gain for the various types of queries that I am running.
It would appear that the only alternative may be a new machine that has a better disk subsystem or a large disk array then bung more RAM in the Opteron machine (max 16Gb 4Gb fitted) or purchase another machine with built in U320 SCSI ie an HP Proliant DL380 or Dell 2850.
Some indication of current performance is as follows. I know these statements are hardly indicative of a full running application and everything that goes with it but I would be very interested in hearing if anyone has a similar setup and is able to squeeze a lot more out of PostgreSQL. From what I can see here the numbers look OK for the hardware I am running on and that its not PostgreSQL that is the problem.
Inserting 1 million rows into the following table.These are raw insert statements.
Column | Type | Modifiers
--------+------------------------+-----------
id | integer |
data | character varying(100) |
where "data" has an average of 95 characters.
23mins 12 seconds.
Wrapping this in a transaction:
1min 47 seconds.
Select from the following table.
Table "public.test"
Column | Type | Modifiers
text | character varying(50) | not null
id | integer | not null
num | integer | default 0
Indexes:
"test_pkey" primary key, btree (text, id)
"test_id_idx" btree (id)
"test_text_idx" btree (text)
select count(*) from test;
count
----------
14289420
(1 row)
# select * from test where text = 'uk' ;
Time: 1680.607 ms
Get it into RAM hence the slight delay here. This delay has a serious impact on the user waiting in the web application.
# select * from test where text = 'uk' ;
Time: 477.739 ms
After it is in RAM.
select count(*) from test where text = 'uk' ;
count
--------
121058
(1 row)
The website has a fairly high volume of inserts and deletes which also means that I use pg_autovacum to keep things reasonably clean. However, I find that every couple of weeks performance degrades so much that I need to do a vacuum full which can take a long time and cripples the database. I have read in the docs that you should only need to vacuum full rarely but I am finding in practice this is not the case which might suggest that I have something set wrong in my config file.
max_fsm_pages = 500000 # I am thinking this might be a bit low.
max_fsm_relations = 1000
Any pointers to better hardware or recommendations on settings gladly recieved.
Regards,
Harry Jackson.
data | character varying(100) |
where "data" has an average of 95 characters.
23mins 12 seconds.
Wrapping this in a transaction:
1min 47 seconds.
Select from the following table.
Table "public.test"
Column | Type | Modifiers
text | character varying(50) | not null
id | integer | not null
num | integer | default 0
Indexes:
"test_pkey" primary key, btree (text, id)
"test_id_idx" btree (id)
"test_text_idx" btree (text)
select count(*) from test;
count
----------
14289420
(1 row)
# select * from test where text = 'uk' ;
Time: 1680.607 ms
Get it into RAM hence the slight delay here. This delay has a serious impact on the user waiting in the web application.
# select * from test where text = 'uk' ;
Time: 477.739 ms
After it is in RAM.
select count(*) from test where text = 'uk' ;
count
--------
121058
(1 row)
The website has a fairly high volume of inserts and deletes which also means that I use pg_autovacum to keep things reasonably clean. However, I find that every couple of weeks performance degrades so much that I need to do a vacuum full which can take a long time and cripples the database. I have read in the docs that you should only need to vacuum full rarely but I am finding in practice this is not the case which might suggest that I have something set wrong in my config file.
max_fsm_pages = 500000 # I am thinking this might be a bit low.
max_fsm_relations = 1000
Any pointers to better hardware or recommendations on settings gladly recieved.
Regards,
Harry Jackson.
--
http://www.hjackson.org
http://www.uklug.co.uk
> I have been using PostgreSQL (currently 7.4.7) for several years now and > am very happy with it but I currently run a website that has had a > little bit of a boost and I am starting to see some performance problems > (Not necessarily PostgreSQL). PostgreSQL 8.1.1 should give you greater performance... > The database has been allocated 2Gb worth of shared buffers and I have > tweaked most of the settings in the config recently to see if I could > increase the performance any more and have seen very little performance > gain for the various types of queries that I am running. That sounds like far too many shared buffers? I wouldn't usually use more than a few tens of thousands, eg. 10k-50k. And that'd only be on 8.1 that has more efficient buffer management. > Get it into RAM hence the slight delay here. This delay has a serious > impact on the user waiting in the web application. > > # select * from test where text = 'uk' ; > Time: 477.739 ms You need to show us the explain analyze plan output for this. But 477ms is far too slow for an index scan on a million row table. > max_fsm_pages = 500000 # I am thinking this might be a bit low. > max_fsm_relations = 1000 Maybe do a once-off vacuum full to make sure all your tables are clean? Chris
On Thu, 15 Dec 2005, Harry Jackson wrote: > Hi all, > I have been using PostgreSQL (currently 7.4.7) for several years now and > am very happy with it but I currently run a website that has had a > little bit of a boost and I am starting to see some performance problems > (Not necessarily PostgreSQL). Definately plan an 8.1 upgrade. [snip] > The database has been allocated 2Gb worth of shared buffers and I have > tweaked most of the settings in the config recently to see if I could > increase the performance any more and have seen very little performance > gain for the various types of queries that I am running. 2 GB is too much for 7.4. I'm not sure about 8.1 because there hasn't been any conclusive testing I think. OSDL is using 200000, which is ~1.5GB. Why not turn on log_min_duration_statement or process the log with PQA (http://pgfoundry.org/projects/pqa/) to look for expensive queries. Also, why kind of IO load are you seeing (iostat will tell you). > It would appear that the only alternative may be a new machine that has > a better disk subsystem or a large disk array then bung more RAM in the > Opteron machine (max 16Gb 4Gb fitted) or purchase another machine with > built in U320 SCSI ie an HP Proliant DL380 or Dell 2850. Have a look at what your IO load is like, first. > Some indication of current performance is as follows. I know these > statements are hardly indicative of a full running application and > everything that goes with it but I would be very interested in hearing > if anyone has a similar setup and is able to squeeze a lot more out of > PostgreSQL. From what I can see here the numbers look OK for the > hardware I am running on and that its not PostgreSQL that is the > problem. > Inserting 1 million rows into the following table.These are raw insert > statements. [snip] Yes, the performance looks a bit poor. I'd say that 8.1 will help address that. Also, don't under estimate the effects of CLUSTER on performance, particularly <8.1. Thanks, Gavin
On 12/15/05, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > PostgreSQL 8.1.1 should give you greater performance... Indeed it has. I am seeing a 25% increase in one particular select statement. This increases to 32% with set enable_bitmapscan to off; I also ran a test script full of common SQL that the application runs. I added some extra SQL to burst the cache a bit and I have managed to get an average 14% increase. I have not started tweaking things that much yet to take advantage of the new parameters so I may yet see more of an increase but initial indications are that the changes from 7.4.7 to 8.1.1 are significant. The one thing that may be skewing these results is that this was compiled and installed from source with ./configure CFLAGS='-O2' --with-openssl --enable-thread-safety I am not sure what the default Debian binary for 7.4.7 is compiled with so this may have had some affect. -- Harry http://www.hjackson.org http://www.uklug.co.uk
On Sun, Dec 18, 2005 at 02:11:16AM +0000, Harry Jackson wrote: > The one thing that may be skewing these results is that this was > compiled and installed from source with > > ./configure CFLAGS='-O2' --with-openssl --enable-thread-safety > > I am not sure what the default Debian binary for 7.4.7 is compiled > with so this may have had some affect. This isn't a performance note, but you might be interested in hearing that there are being maintained official backports of 8.0 and 8.1 for Debian sarge (by Martin Pitt, the same person who maintains both the sarge and sid versions). Take a look at http://people.debian.org/~mpitt/packages/sarge-backports/ It might be more comfortable in the long run than maintaining your own source installation, although YMMV. /* Steinar */ -- Homepage: http://www.sesse.net/