pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSome help on buffers and other performance tricks
11+ messages / 7 participants
[nested] [flat]
* Some help on buffers and other performance tricks
@ 2005-11-09 20:11 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 11+ messages in thread
From: Yves Vindevogel @ 2005-11-09 20:11 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I've got PG 8.0 on Debian sarge set up ...
I want to speed up performance on the system.
The system will run PG, Apache front-end on port 80 and Tomcat / Cocoon
for the webapp.
The webapp is not so heavily used, so we can give the max performance
to the database.
The database has a lot of work to do, we upload files every day.
The current server has 8 databases of around 1 million records. This
will be more in the future.
There's only one main table, with some smaller tables. 95% of the
records are in that one table.
A lot of updates are done on that table, affecting 10-20% of the
records.
The system has 1 gig of ram. I could give 512Mb to PG.
Filesystem is ext2, with the -noatime parameter in fstab
Could I get some suggestions in how to configure my buffers, wals, ....
?
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Some help on buffers and other performance tricks
@ 2005-11-09 21:24 Ron Peacetree <rjpeace@earthlink.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Peacetree @ 2005-11-09 21:24 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
0= Optimize your schema to be a tight as possible. Your goal is to give yourself the maximum chance that everything you want to work on is in RAM when you need it.
1= Upgrade your RAM to as much as you can possibly strain to afford. 4GB at least. It's that important.
2= If the _entire_ DB does not fit in RAM after upgrading your RAM, the next step is making sure your HD IO subsystem is adequate to your needs.
3= Read the various pg tuning docs that are available and Do The Right Thing.
4= If performance is still not acceptable, then it's time to drill down into what specific actions/queries are problems.
If you get to here and the entire DBMS is still not close to acceptable, your fundamental assumptions have to be re-examined.
Ron
-----Original Message-----
From: Yves Vindevogel <yves.vindevogel@implements.be>
Sent: Nov 9, 2005 3:11 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Some help on buffers and other performance tricks
Hi all,
I've got PG 8.0 on Debian sarge set up ...
I want to speed up performance on the system.
The system will run PG, Apache front-end on port 80 and Tomcat / Cocoon
for the webapp.
The webapp is not so heavily used, so we can give the max performance
to the database.
The database has a lot of work to do, we upload files every day.
The current server has 8 databases of around 1 million records. This
will be more in the future.
There's only one main table, with some smaller tables. 95% of the
records are in that one table.
A lot of updates are done on that table, affecting 10-20% of the
records.
The system has 1 gig of ram. I could give 512Mb to PG.
Filesystem is ext2, with the -noatime parameter in fstab
Could I get some suggestions in how to configure my buffers, wals, ....
?
Met vriendelijke groeten,
Bien � vous,
Kind regards,
Yves Vindevogel
Implements
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-09 23:07 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ron Peacetree <rjpeace@earthlink.net>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2005-11-09 23:07 UTC (permalink / raw)
To: Ron Peacetree <rjpeace@earthlink.net>; +Cc: Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
Ron Peacetree wrote:
> 0= Optimize your schema to be a tight as possible. Your goal is to give yourself the maximum chance that everything you want to work on is in RAM when you need it.
> 1= Upgrade your RAM to as much as you can possibly strain to afford. 4GB at least. It's that important.
> 2= If the _entire_ DB does not fit in RAM after upgrading your RAM, the next step is making sure your HD IO subsystem is adequate to your needs.
> 3= Read the various pg tuning docs that are available and Do The Right Thing.
> 4= If performance is still not acceptable, then it's time to drill down into what specific actions/queries are problems.
> If you get to here and the entire DBMS is still not close to acceptable, your fundamental assumptions have to be re-examined.
IMHO you should really be examining your queries _before_ you do any
investment in hardware, because later those may prove unnecessary.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-09 23:53 Frank Wiles <frank@wiles.org>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Frank Wiles @ 2005-11-09 23:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: rjpeace@earthlink.net; yves.vindevogel@implements.be; pgsql-performance
On Wed, 9 Nov 2005 20:07:52 -0300
Alvaro Herrera <alvherre@commandprompt.com> wrote:
> IMHO you should really be examining your queries _before_ you do any
> investment in hardware, because later those may prove unnecessary.
It all really depends on what you're doing. For some of the systems
I run, 4 GBs of RAM is *WAY* overkill, RAID 1+0 is overkill, etc.
In general I would slightly change the "order of operations" from:
1) Buy tons of RAM
2) Buy lots of disk I/O
3) Tune your conf
4) Examine your queries
to
1) Tune your conf
2) Spend a few minutes examining your queries
3) Buy as much RAM as you can afford
4) Buy as much disk I/O as you can
5) Do in depth tuning of your queries/conf
Personally I avoid planning my schema around my performance at
the start. I just try to represent the data in a sensible,
normalized way. While I'm sure I sub-consciously make decisions
based on performance considerations early on, I don't do any major
schema overhauls until I find I can't get the performance I need
via tuning.
Obviously there are systems/datasets/quantities where this won't
always work out best, but for the majority of systems out there
complicating your schema, maxing your hardware, and THEN tuning
is IMHO the wrong approach.
---------------------------------
Frank Wiles <frank@wiles.org>
http://www.wiles.org
---------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 00:43 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Frank Wiles <frank@wiles.org>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2005-11-10 00:43 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: rjpeace@earthlink.net; yves.vindevogel@implements.be; pgsql-performance
Frank Wiles wrote:
> Obviously there are systems/datasets/quantities where this won't
> always work out best, but for the majority of systems out there
> complicating your schema, maxing your hardware, and THEN tuning
> is IMHO the wrong approach.
I wasn't suggesting to complicate the schema -- I was actually thinking
in systems where some queries are not using indexes, some queries are
plain wrong, etc. Buying a very expensive RAID and then noticing that
you just needed to create an index, is going to make somebody feel at
least somewhat stupid.
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 17.7", W 73º 14' 26.8"
Y una voz del caos me habló y me dijo
"Sonríe y sé feliz, podría ser peor".
Y sonreí. Y fui feliz.
Y fue peor.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 00:54 Frank Wiles <frank@wiles.org>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 11+ messages in thread
From: Frank Wiles @ 2005-11-10 00:54 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: rjpeace@earthlink.net; yves.vindevogel@implements.be; pgsql-performance
On Wed, 9 Nov 2005 21:43:33 -0300
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Frank Wiles wrote:
>
> > Obviously there are systems/datasets/quantities where this won't
> > always work out best, but for the majority of systems out there
> > complicating your schema, maxing your hardware, and THEN tuning
> > is IMHO the wrong approach.
>
> I wasn't suggesting to complicate the schema -- I was actually
> thinking in systems where some queries are not using indexes, some
> queries are plain wrong, etc. Buying a very expensive RAID and then
> noticing that you just needed to create an index, is going to make
> somebody feel at least somewhat stupid.
Sorry I was referring to Ron statement that the first step should
be to "Optimize your schema to be as tight as possible."
But I agree, finding out you need an index after spending $$$ on
extra hardware would be bad. Especially if you have to explain it
to the person forking over the $$$! :)
---------------------------------
Frank Wiles <frank@wiles.org>
http://www.wiles.org
---------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 04:20 Ron Peacetree <rjpeace@earthlink.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Peacetree @ 2005-11-10 04:20 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; pgsql-performance
The point Gentlemen, was that Good Architecture is King. That's what I was trying to emphasize by calling proper DB architecture step 0. All other things being equal (and they usually aren't, this sort of stuff is _very_ context dependent), the more of your critical schema that you can fit into RAM during normal operation the better.
...and it all starts with proper DB design. Otherwise, you are quite right in stating that you risk wasting time, effort, and HW.
Ron
-----Original Message-----
From: Frank Wiles <frank@wiles.org>
Sent: Nov 9, 2005 6:53 PM
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: rjpeace@earthlink.net, yves.vindevogel@implements.be, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Some help on buffers and other performance tricks
On Wed, 9 Nov 2005 20:07:52 -0300
Alvaro Herrera <alvherre@commandprompt.com> wrote:
> IMHO you should really be examining your queries _before_ you do any
> investment in hardware, because later those may prove unnecessary.
It all really depends on what you're doing. For some of the systems
I run, 4 GBs of RAM is *WAY* overkill, RAID 1+0 is overkill, etc.
In general I would slightly change the "order of operations" from:
1) Buy tons of RAM
2) Buy lots of disk I/O
3) Tune your conf
4) Examine your queries
to
1) Tune your conf
2) Spend a few minutes examining your queries
3) Buy as much RAM as you can afford
4) Buy as much disk I/O as you can
5) Do in depth tuning of your queries/conf
Personally I avoid planning my schema around my performance at
the start. I just try to represent the data in a sensible,
normalized way. While I'm sure I sub-consciously make decisions
based on performance considerations early on, I don't do any major
schema overhauls until I find I can't get the performance I need
via tuning.
Obviously there are systems/datasets/quantities where this won't
always work out best, but for the majority of systems out there
complicating your schema, maxing your hardware, and THEN tuning
is IMHO the wrong approach.
---------------------------------
Frank Wiles <frank@wiles.org>
http://www.wiles.org
---------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 15:16 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Ron Peacetree <rjpeace@earthlink.net>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2005-11-10 15:16 UTC (permalink / raw)
To: Ron Peacetree <rjpeace@earthlink.net>; +Cc: Frank Wiles <frank@wiles.org>; pgsql-performance
On Wed, 2005-11-09 at 22:20, Ron Peacetree wrote:
> The point Gentlemen, was that Good Architecture is King. That's what I was trying to emphasize by calling proper DB architecture step 0. All other things being equal (and they usually aren't, this sort of stuff is _very_ context dependent), the more of your critical schema that you can fit into RAM during normal operation the better.
>
> ...and it all starts with proper DB design. Otherwise, you are quite right in stating that you risk wasting time, effort, and HW.
Very valid point. It's the reason, in my last job, we had a mainline
server with dual 2800MHz CPUs and a big RAID array.
And our development, build and test system was a Dual Pentium Pro 200
with 256 Meg of ram. You notice slow queries real fast on such a box.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 15:25 Frank Wiles <frank@wiles.org>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 11+ messages in thread
From: Frank Wiles @ 2005-11-10 15:25 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
On Thu, 10 Nov 2005 09:16:10 -0600
Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> Very valid point. It's the reason, in my last job, we had a mainline
> server with dual 2800MHz CPUs and a big RAID array.
>
> And our development, build and test system was a Dual Pentium Pro 200
> with 256 Meg of ram. You notice slow queries real fast on such a box.
I know several people who use this development method. It can
sometimes lead to premature optimizations, but overall I think it is
a great way to work.
---------------------------------
Frank Wiles <frank@wiles.org>
http://www.wiles.org
---------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 15:43 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 11+ messages in thread
From: Merlin Moncure @ 2005-11-10 15:43 UTC (permalink / raw)
To: Ron Peacetree <rjpeace@earthlink.net>; +Cc: pgsql-performance
> The point Gentlemen, was that Good Architecture is King. That's what
I
> was trying to emphasize by calling proper DB architecture step 0. All
> other things being equal (and they usually aren't, this sort of stuff
is
> _very_ context dependent), the more of your critical schema that you
can
> fit into RAM during normal operation the better.
>
> ...and it all starts with proper DB design. Otherwise, you are quite
> right in stating that you risk wasting time, effort, and HW.
>
> Ron
+1!
I answer lots of question on this list that are in the form of 'query x
is running to slow'. Often, the first thing that pops in my mind is
'why are you running query x in the first place?'
The #1 indicator that something is not right is 'distinct' clause.
Distinct (and its evil cousin, union) are often brought in to address
problems.
The human brain is the best optimizer. Even on old hardware the server
can handle a *lot* of data. It's just about where we add
inefficiency...lousy database designs lead to lousy queries or (even
worse) extra application code.
Merlin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Some help on buffers and other performance tricks
@ 2005-11-10 15:51 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Frank Wiles <frank@wiles.org>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2005-11-10 15:51 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
On Thu, 2005-11-10 at 09:25, Frank Wiles wrote:
> On Thu, 10 Nov 2005 09:16:10 -0600
> Scott Marlowe <smarlowe@g2switchworks.com> wrote:
>
> > Very valid point. It's the reason, in my last job, we had a mainline
> > server with dual 2800MHz CPUs and a big RAID array.
> >
> > And our development, build and test system was a Dual Pentium Pro 200
> > with 256 Meg of ram. You notice slow queries real fast on such a box.
>
> I know several people who use this development method. It can
> sometimes lead to premature optimizations, but overall I think it is
> a great way to work.
Hehe. Yeah, you get used to things running a bit slower pretty
quickly. Keep in mind though, that the test box is likely only
supporting one single application at a time, whereas the production
server may be running dozens or even hundreds of apps, so it's important
to catch performance issues before they get to production.
Plus, the Dual PPRo 200 WAS running a decent RAID array, even if it was
a linux kernel software RAID and not hardware. But it was on 8 9
gigabyte SCSI drives, so it was quite fast for reads.
In actuality, a lot of the folks developed their code on their own
workstations (generally 1+GHz machines with 1G or more of ram) then had
to move them over to the ppro 200 for testing and acceptance. So that
kind of helps stop the premature optimizations. We were mainly looking
to catch stupidity before it got to production.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-11-10 15:51 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-09 20:11 Some help on buffers and other performance tricks Yves Vindevogel <yves.vindevogel@implements.be>
2005-11-09 21:24 Re: Some help on buffers and other performance tricks Ron Peacetree <rjpeace@earthlink.net>
2005-11-09 23:07 ` Alvaro Herrera <alvherre@commandprompt.com>
2005-11-09 23:53 ` Frank Wiles <frank@wiles.org>
2005-11-10 00:43 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-11-10 00:54 ` Frank Wiles <frank@wiles.org>
2005-11-10 04:20 Re: Some help on buffers and other performance tricks Ron Peacetree <rjpeace@earthlink.net>
2005-11-10 15:16 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-10 15:25 ` Frank Wiles <frank@wiles.org>
2005-11-10 15:51 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-10 15:43 Re: Some help on buffers and other performance tricks Merlin Moncure <merlin.moncure@rcsonline.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