pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL performance question.
4+ messages / 3 participants
[nested] [flat]
* PostgreSQL performance question.
@ 2005-12-15 01:51 Harry Jackson <harryjackson@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Harry Jackson @ 2005-12-15 01:51 UTC (permalink / raw)
To: pgsql-performance
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
--------+------------------------+-----------
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.
--
http://www.hjackson.org
http://www.uklug.co.uk
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL performance question.
@ 2005-12-15 02:02 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Harry Jackson <harryjackson@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Christopher Kings-Lynne @ 2005-12-15 02:02 UTC (permalink / raw)
To: Harry Jackson <harryjackson@gmail.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL performance question.
@ 2005-12-15 02:29 Gavin Sherry <swm@alcove.com.au>
parent: Harry Jackson <harryjackson@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Gavin Sherry @ 2005-12-15 02:29 UTC (permalink / raw)
To: Harry Jackson <harryjackson@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL performance question.
@ 2005-12-18 02:11 Harry Jackson <harryjackson@gmail.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Harry Jackson @ 2005-12-18 02:11 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-18 02:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-15 01:51 PostgreSQL performance question. Harry Jackson <harryjackson@gmail.com>
2005-12-15 02:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-12-18 02:11 ` Harry Jackson <harryjackson@gmail.com>
2005-12-15 02:29 ` Gavin Sherry <swm@alcove.com.au>
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