pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedchoosing the right platform
14+ messages / 6 participants
[nested] [flat]
* choosing the right platform
@ 2003-04-09 03:38 Matthew Nuzum <cobalt@bearfruit.org>
0 siblings, 2 replies; 14+ messages in thread
From: Matthew Nuzum @ 2003-04-09 03:38 UTC (permalink / raw)
To: pgsql-performance
Hello all,
I've been the lead developer of a successful (so-far) web application.
Currently we run the database and the web application on the same server,
but it will soon be necessary to split them and add some more web servers.
I'm not counting on using replication any time soon, so I want to choose the
hardware platform that will meet my needs for some time. Maybe you can give
some suggestions...
My application is written in PHP and relies on the apache web server. We
use persistent db connections, so it appears (correct me if I'm wrong) that
every apache child process gets one connection to the database. If my
MaxClients is 150, each web server will be able to make up to 150 db
connections. I'd like to play with that number a little bit once I get the
webserver off of the db server. I feel that I could handle a greater number
of Clients, so let us say that I have up to 200 connections per server.
I'd like to have room to grow, so let's also say that I go to 20 web servers
for a total of 4000 connections. (I'd probably like to go even higher, so
consider this our starting point)
With a couple dozen active accounts and a lot of test data, my current
database is equiv to about 100 active accounts. Its current disk space
consumption is:
data # du --max-depth=2
3656 ./base/1
3656 ./base/16975
4292 ./base/95378
177824 ./base/200371
189432 ./base
144 ./global
82024 ./pg_xlog
2192 ./pg_clog
273836 .
This is not optimized and there is a lot of old data, but to be safe, maybe
we should assume that each account uses 4 MB of disk space in the db,
counting indexes, tables and etc. I'd like to scale to 15,000 - 25,000
accounts, but I doubt that will be feasible at my budget level. (Also,
there is a lot of optimizing to do, so it won't surprise me if this 4MB
number is more like 2MB or even less)
I'm not as concerned with disk subsystem or layout at the moment. I've seen
a lot of good documentation (especially from Bruce Momjian, thanks!) on this
subject. I'm mostly concerned with choosing the platform that's going to
allow the scalability I need.
Currently I'm most experienced in Linux, especially RedHat. I'm "certified"
on SCO Openserver (5.x) and I've played with Irix, OSF/1 (I don't think it's
called that anymore), Free BSD (3.x) and Solaris (2.x). I'm most
comfortable with Linux, but I'm willing to use a different platform if it
will be beneficial. I've heard that Solaris on the Sparc platform is
capable of addressing larger amounts of RAM than Linux on Intel does. I
don't know if that's true or if that has bearing, but I'd like to hear your
opinions.
My budget is going to be between (US) $5,000 and $10,000 and I'd like to
stay under $7,000. I'm a major bargain hunter, so I shop e-bay a lot and
here are some samplings that I think may be relevant for discussion:
SUN (I'm not an expert in this, advice is requested)
----------------------------------------------------
SUN ENTERPRISE 4500 8x400 Mhz 4MB Cache CPUs 8GB RAM no hard drives ~$6,000
Sun E3500 - 8 x 336MHz 4MB Cache CPUs 4GB RAM 8 x 9.1GB FC disks ~$600.00
Any other suggestions?
INTEL (I'm much more familiar with this area)
----------------------------------------------------
Compaq DL580 4x700 MHz 2MB Cache CPUs 4GB RAM (16GB Max) HW Raid w/ 64MB
Cache ~$6000
IBM Netfinity 7100 4x500 MHz 1MB Cache CPUs up to (16GB Max) HW Raid
Dell PowerEdge 8450 8x550 2M Cache CPUS 4GB (32GB Max) HS RAID w/ 16MB Cache
~$4,500
Any other suggestions?
Any other hardware platforms I should consider?
Finally, and I know this sounds silly, but I don't have my own data center,
so size is something I need to take into consideration. I pay for data
center space by the physical size of my servers. My priorities are
Performance, Reasonable amount of scalability (as outlined above) and
finally physical size.
Thanks for taking the time to read this and for any assistance you can give,
Matthew Nuzum
www.bearfruit.org
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 16:28 Josh Berkus <josh@agliodbs.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
1 sibling, 1 reply; 14+ messages in thread
From: Josh Berkus @ 2003-04-09 16:28 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-performance
Matthew,
> Currently I'm most experienced in Linux, especially RedHat. I'm
> "certified" on SCO Openserver (5.x) and I've played with Irix, OSF/1 (I
> don't think it's called that anymore), Free BSD (3.x) and Solaris (2.x).
> I'm most
> comfortable with Linux, but I'm willing to use a different platform if it
> will be beneficial. I've heard that Solaris on the Sparc platform is
> capable of addressing larger amounts of RAM than Linux on Intel does. I
> don't know if that's true or if that has bearing, but I'd like to hear your
> opinions.
Please browse through the list archives. We have numerous posts on the
platform subject. In fact, several of us are trying to put together a
PostgreSQL performance test package to answer this question difinitively
rather than anecdotally.
Anecdotal responses are:
Solaris is *bad* for PostgreSQL, due to a high per-process overhead.
Universal opinion on this list has been that Solaris is optimized for
multi-threaded applications, not multi-process applications, and postgres is
the latter.
*BSD has a *lot* of fans on the PGSQL lists, many of whom claim significantly
better performance than Linux, mostly due to better filesystem I/O.
Linux is used heavily by a lot of PostgreSQL users. I have yet to see anyone
provide actual Linux vs. BSD statistics, though ... something we hope to do.
Nobody has come forward and reported on PostgreSQL on SCO Unix.
Irix is widely regarded as a "dead" platform, though PostgreSQL does run on it
...
Good luck, and keep watching this space!
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 16:51 scott.marlowe <scott.marlowe@ihs.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
1 sibling, 1 reply; 14+ messages in thread
From: scott.marlowe @ 2003-04-09 16:51 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: pgsql-performance
I would say up front that both Linux and BSD are probably your two best
choices. If you're familiar with one more than the other, that
familiarity may be more important than the underlying differences in the
two OSes, as they are both good platforms to run postgresql on top of.
Secondly, look carefully at using persistant connections in large numbers.
While persistant connections DO represent a big savings in connect time,
the savings are lost in the noise of many PHP applications.
i.e. my dual PIII swiss army knife server can initiate single persistant
connections at 1,000,000 a second (reusing the old ones of course).
non-persistant connects happen at 1,000 times a second. Most of my
scripts run in 1/10th of a second or so, so the 1/1000th used to connect
is noise to me.
If you are going to use persistant connections, it might work better to
let apache have only 20 or 40 children, which will force the apache
children to "round robin" serve the requests coming in.
This will usually work fine, since keeping the number of apache children
down keeps the number of postgresql backends down, which keeps the system
faster in terms of response time. Turn keep alive down to something short
like 10 seconds, or just turn it off, as keep alive doesn't really save
all that much time in apache.
Note that machine testing with 100 simo connections doesn't translate
directly to 100 users. Generally, x simos usually represents about 10 to
20 x users, since users don't click buttons all that fast. so an apache
configured by 40 max children should handle 100 to 200 users with no
problem.
On Tue, 8 Apr 2003, Matthew Nuzum wrote:
> Hello all,
>
> I've been the lead developer of a successful (so-far) web application.
> Currently we run the database and the web application on the same server,
> but it will soon be necessary to split them and add some more web servers.
>
> I'm not counting on using replication any time soon, so I want to choose the
> hardware platform that will meet my needs for some time. Maybe you can give
> some suggestions...
>
> My application is written in PHP and relies on the apache web server. We
> use persistent db connections, so it appears (correct me if I'm wrong) that
> every apache child process gets one connection to the database. If my
> MaxClients is 150, each web server will be able to make up to 150 db
> connections. I'd like to play with that number a little bit once I get the
> webserver off of the db server. I feel that I could handle a greater number
> of Clients, so let us say that I have up to 200 connections per server.
>
> I'd like to have room to grow, so let's also say that I go to 20 web servers
> for a total of 4000 connections. (I'd probably like to go even higher, so
> consider this our starting point)
>
> With a couple dozen active accounts and a lot of test data, my current
> database is equiv to about 100 active accounts. Its current disk space
> consumption is:
> data # du --max-depth=2
> 3656 ./base/1
> 3656 ./base/16975
> 4292 ./base/95378
> 177824 ./base/200371
> 189432 ./base
> 144 ./global
> 82024 ./pg_xlog
> 2192 ./pg_clog
> 273836 .
>
> This is not optimized and there is a lot of old data, but to be safe, maybe
> we should assume that each account uses 4 MB of disk space in the db,
> counting indexes, tables and etc. I'd like to scale to 15,000 - 25,000
> accounts, but I doubt that will be feasible at my budget level. (Also,
> there is a lot of optimizing to do, so it won't surprise me if this 4MB
> number is more like 2MB or even less)
>
> I'm not as concerned with disk subsystem or layout at the moment. I've seen
> a lot of good documentation (especially from Bruce Momjian, thanks!) on this
> subject. I'm mostly concerned with choosing the platform that's going to
> allow the scalability I need.
>
> Currently I'm most experienced in Linux, especially RedHat. I'm "certified"
> on SCO Openserver (5.x) and I've played with Irix, OSF/1 (I don't think it's
> called that anymore), Free BSD (3.x) and Solaris (2.x). I'm most
> comfortable with Linux, but I'm willing to use a different platform if it
> will be beneficial. I've heard that Solaris on the Sparc platform is
> capable of addressing larger amounts of RAM than Linux on Intel does. I
> don't know if that's true or if that has bearing, but I'd like to hear your
> opinions.
>
> My budget is going to be between (US) $5,000 and $10,000 and I'd like to
> stay under $7,000. I'm a major bargain hunter, so I shop e-bay a lot and
> here are some samplings that I think may be relevant for discussion:
>
> SUN (I'm not an expert in this, advice is requested)
> ----------------------------------------------------
> SUN ENTERPRISE 4500 8x400 Mhz 4MB Cache CPUs 8GB RAM no hard drives ~$6,000
> Sun E3500 - 8 x 336MHz 4MB Cache CPUs 4GB RAM 8 x 9.1GB FC disks ~$600.00
> Any other suggestions?
>
> INTEL (I'm much more familiar with this area)
> ----------------------------------------------------
> Compaq DL580 4x700 MHz 2MB Cache CPUs 4GB RAM (16GB Max) HW Raid w/ 64MB
> Cache ~$6000
> IBM Netfinity 7100 4x500 MHz 1MB Cache CPUs up to (16GB Max) HW Raid
> Dell PowerEdge 8450 8x550 2M Cache CPUS 4GB (32GB Max) HS RAID w/ 16MB Cache
> ~$4,500
> Any other suggestions?
>
> Any other hardware platforms I should consider?
>
> Finally, and I know this sounds silly, but I don't have my own data center,
> so size is something I need to take into consideration. I pay for data
> center space by the physical size of my servers. My priorities are
> Performance, Reasonable amount of scalability (as outlined above) and
> finally physical size.
>
> Thanks for taking the time to read this and for any assistance you can give,
>
> Matthew Nuzum
> www.bearfruit.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 17:13 Matthew Nuzum <cobalt@bearfruit.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 4 replies; 14+ messages in thread
From: Matthew Nuzum @ 2003-04-09 17:13 UTC (permalink / raw)
To: 'Josh Berkus' <josh@agliodbs.com>; pgsql-performance
> Anecdotal responses are:
>
> Solaris is *bad* for PostgreSQL, due to a high per-process overhead.
> Universal opinion on this list has been that Solaris is optimized for
> multi-threaded applications, not multi-process applications, and postgres
> is
> the latter.
>
> *BSD has a *lot* of fans on the PGSQL lists, many of whom claim
> significantly
> better performance than Linux, mostly due to better filesystem I/O.
>
> Linux is used heavily by a lot of PostgreSQL users. I have yet to see
> anyone
> provide actual Linux vs. BSD statistics, though ... something we hope to
> do.
...
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
Thanks for the reply. Three things come to mind:
About the list archives...
I read through the entire archive at
http://archives.postgresql.org/pgsql-performance/ and didn't see much talk
on the subject. It only goes back 8 months though, so I don't know if there
is another archive that is more comprehensive...
Also,
I'm glad to hear your comments about Solaris, I'm really most comfortable
with Linux and I think I can pick up BSD pretty easily.
About the Intel platform though,
It's only been pretty recently (relatively speaking) that servers based on
IA32 architecture have had support for greater than 2GB of RAM. I've heard
talk about problems with applications that require more than 2GB. I do
believe that my tables will become larger than this, and the way I
understand it, sort mem works best when the tables can be loaded completely
in RAM.
I don't suspect that individual tables will be 2GB, but that the size of all
tables combined will be. If there is a limitation on the largest chunk of
RAM allocated to a program, will I have problems?
Finally, can someone suggest a *BSD to evaluate? FreeBSD 4.8? 5.0? Is Apple
a good choice? (I've heard it's based on BSD Unix)
Thanks,
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 17:20 eric soroos <eric-psql@soroos.net>
parent: Matthew Nuzum <cobalt@bearfruit.org>
3 siblings, 0 replies; 14+ messages in thread
From: eric soroos @ 2003-04-09 17:20 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; Josh Berkus <josh@agliodbs.com>; pgsql-performance
> Finally, can someone suggest a *BSD to evaluate? FreeBSD 4.8? 5.0? Is Apple
> a good choice? (I've heard it's based on BSD Unix)
I wouldn't recommend OSX for deployment if you're worried about performance. The hardware availiable and the settings to take advantage of it just aren't there yet, compared to the more established FreeBSD and Linux offerings.
Development on a tibook is another matter, I'd recommend it to anyone with an attraction to shiny things that do real work.
eric
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 17:55 scott.marlowe <scott.marlowe@ihs.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
3 siblings, 1 reply; 14+ messages in thread
From: scott.marlowe @ 2003-04-09 17:55 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: 'Josh Berkus' <josh@agliodbs.com>; pgsql-performance
On Wed, 9 Apr 2003, Matthew Nuzum wrote:
> I'm glad to hear your comments about Solaris, I'm really most comfortable
> with Linux and I think I can pick up BSD pretty easily.
>
> About the Intel platform though,
>
> It's only been pretty recently (relatively speaking) that servers based on
> IA32 architecture have had support for greater than 2GB of RAM. I've heard
> talk about problems with applications that require more than 2GB. I do
> believe that my tables will become larger than this, and the way I
> understand it, sort mem works best when the tables can be loaded completely
> in RAM.
>
> I don't suspect that individual tables will be 2GB, but that the size of all
> tables combined will be. If there is a limitation on the largest chunk of
> RAM allocated to a program, will I have problems?
A couple more suggestions. One is to never allocate more than 50% of your
memory to a database's shared buffers, i.e. let the OS buffer the disks en
masse, while the database should have a smaller buffer for the most recent
accesses. This is because kernel caching is usually faster and more
efficient than the database doing it, and this becomes more an issue with
large chunks of memory, which both Linux and BSD are quite good at
caching, and postgresql, not so good.
The other is to look at Linux or BSD on 64 bit hardware (Sparc, IBM
Zseries mainframes, SGI Altix, etc...) where the one thing that's worth
being on the bleeding edge for is databases and their memory hungry ways.
:-)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 18:03 Josh Berkus <josh@agliodbs.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
3 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2003-04-09 18:03 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-performance
Matthew,
> I read through the entire archive at
> http://archives.postgresql.org/pgsql-performance/ and didn't see much talk
> on the subject. It only goes back 8 months though, so I don't know if there
> is another archive that is more comprehensive...
Really? There was a long-running Mac OS X vs. Solaris thread that touched on
most major platforms, about 2-3 months ago.
> I don't suspect that individual tables will be 2GB, but that the size of all
> tables combined will be. If there is a limitation on the largest chunk of
> RAM allocated to a program, will I have problems?
No. Since PostgreSQL is a multi-process architecture, not a multi-threaded,
you only need enough RAM per process to load the current largest query.
Plus, in my experience, Disk I/O issues are vastly more important than RAM in
database performance. You're better off spending money on really fast disks
in Linux RAID or really good hardware RAID 1+0 ....
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 23:45 Jim C. Nasby <jim@nasby.net>
parent: Matthew Nuzum <cobalt@bearfruit.org>
3 siblings, 0 replies; 14+ messages in thread
From: Jim C. Nasby @ 2003-04-09 23:45 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Wed, Apr 09, 2003 at 01:13:46PM -0400, Matthew Nuzum wrote:
> Finally, can someone suggest a *BSD to evaluate? FreeBSD 4.8? 5.0? Is Apple
> a good choice? (I've heard it's based on BSD Unix)
FreeBSD has 3 different branches:
-current:
This is bleeding edge. Definitely need to be careful with this one, and
it's not recommended for production.
-stable:
This is still a 'live' branch that any FBSD coder can (generally) commit
to, but they are far more careful about breaking this branch. Not as
stable as a release branch, but it's probably suitable for production so
long as you're careful to test things.
release branches:
Every time an official release is done (ie: 4.8), a branch is created.
The only code committed to these branches are security patches and fixes
for very serious bugs. These branches are extremely stable.
5.0 is the first release after several years of development in -current.
It incorporates some major changes designed to allow the kernel to run
multi-threaded. However, unlike what usually happens, 5.0 is not
considered to be -stable yet. First, this is still very new code;
second, I believe there's some performance issues that are still being
addressed. The intention is that 5.1 will be the first -stable release
of the 5.x code.
Because you're looking for something that's production ready, you
probably want 4.8 (cvs tag RELENG_4_8). However, if you don't plan to
hit production until late this year (when 5.1 should be out), you might
want to try 5.0.
Far more info is available at http://www.freebsd.org/releng/index.html
BTW, I've heard of many, many companies moving their Oracle installs
from Sun to RS/6000 because RS/6000's typically need 1/2 the processors
that Sun does for a given load. If you're going to look at big-iron,
RS/6000 is definitely worth a look if you see anything.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 23:47 Jim C. Nasby <jim@nasby.net>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 14+ messages in thread
From: Jim C. Nasby @ 2003-04-09 23:47 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Matthew Nuzum <cobalt@bearfruit.org>; Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Wed, Apr 09, 2003 at 11:55:56AM -0600, scott.marlowe wrote:
> A couple more suggestions. One is to never allocate more than 50% of your
> memory to a database's shared buffers, i.e. let the OS buffer the disks en
> masse, while the database should have a smaller buffer for the most recent
> accesses. This is because kernel caching is usually faster and more
> efficient than the database doing it, and this becomes more an issue with
> large chunks of memory, which both Linux and BSD are quite good at
> caching, and postgresql, not so good.
That seems odd... shouldn't pgsql be able to cache information better
since it would be cached in whatever format is best for it, rather than
the raw page format (or maybe that is the best format). There's also the
issue of having to go through more layers of software if you're relying
on the OS caching. All the tuning info I've seen for every other
database I've worked with specifically recommends giving the database as
much memory as you possibly can, the theory being that it will do a much
better job of caching than the OS will.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-09 23:58 Jim C. Nasby <jim@nasby.net>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 14+ messages in thread
From: Jim C. Nasby @ 2003-04-09 23:58 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-performance
On Wed, Apr 09, 2003 at 10:51:34AM -0600, scott.marlowe wrote:
> Secondly, look carefully at using persistant connections in large numbers.
>
> While persistant connections DO represent a big savings in connect time,
> the savings are lost in the noise of many PHP applications.
>
> i.e. my dual PIII swiss army knife server can initiate single persistant
> connections at 1,000,000 a second (reusing the old ones of course).
> non-persistant connects happen at 1,000 times a second. Most of my
> scripts run in 1/10th of a second or so, so the 1/1000th used to connect
> is noise to me.
My $0.02 from my experience with Sybase and DB2:
It's not the connection *time* that's an issue, it's the amount of
resources (mostly memory) used by each database connection. Each db2
connection to a database uses 4-8 meg of memory; on my pgsql system,
each connection appears to be using about 4M. This is the resident set,
which I believe indicates memory that basically can't be shared. All
this memory is memory that can't be used for buffering/caching; on a
system with a hundred connections, it can really start to add up.
If your PHP is written in such a way that it does all the database work
in one section of code, and only holds a connection to the database in
that one section, then you can potentially have a lot of apache
processes for each database connection.
Of course, all this holds true wether you're using pooling or not. How
much pooling will help depends on how expensive it is for the *database*
to handle each new connection request, and how your code is written.
Since it's often not possible to put all you database code in one place
like I mentioned above, an alternative is to connect right before you do
an operation, and disconnect as soon as you're done. This doesn't add
much (if any) expense if you're using pooling, but it's a very different
story if you're not using pooling.
> If you are going to use persistant connections, it might work better to
> let apache have only 20 or 40 children, which will force the apache
> children to "round robin" serve the requests coming in.
>
> This will usually work fine, since keeping the number of apache children
> down keeps the number of postgresql backends down, which keeps the system
> faster in terms of response time. Turn keep alive down to something short
> like 10 seconds, or just turn it off, as keep alive doesn't really save
> all that much time in apache.
Very important advice. Generally, once you push a database past a
certain point, your performance degrades severely as the database
thrashes about trying to answer all the pending queries.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-10 16:42 scott.marlowe <scott.marlowe@ihs.com>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 14+ messages in thread
From: scott.marlowe @ 2003-04-10 16:42 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-performance
On Wed, 9 Apr 2003, Jim C. Nasby wrote:
> On Wed, Apr 09, 2003 at 10:51:34AM -0600, scott.marlowe wrote:
> > Secondly, look carefully at using persistant connections in large numbers.
> >
> > While persistant connections DO represent a big savings in connect time,
> > the savings are lost in the noise of many PHP applications.
> >
> > i.e. my dual PIII swiss army knife server can initiate single persistant
> > connections at 1,000,000 a second (reusing the old ones of course).
> > non-persistant connects happen at 1,000 times a second. Most of my
> > scripts run in 1/10th of a second or so, so the 1/1000th used to connect
> > is noise to me.
>
> My $0.02 from my experience with Sybase and DB2:
> It's not the connection *time* that's an issue, it's the amount of
> resources (mostly memory) used by each database connection. Each db2
> connection to a database uses 4-8 meg of memory;
Agreed.
> on my pgsql system,
> each connection appears to be using about 4M. This is the resident set,
> which I believe indicates memory that basically can't be shared. All
> this memory is memory that can't be used for buffering/caching; on a
> system with a hundred connections, it can really start to add up.
If I run "select * from logs" from two different psql sessions on my
backup box hitting my main box (psql would hold the result set and throw
the results off if I ran it on the main box) I get this output from top:
No (pgsql) load:
8:58am up 9 days, 22:43, 4 users, load average: 0.65, 0.54, 0.35
169 processes: 168 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.1% user, 0.1% system, 0.0% nice, 99.1% idle
CPU1 states: 32.1% user, 3.2% system, 0.0% nice, 64.0% idle
Mem: 1543980K av, 1049864K used, 494116K free, 265928K shrd, 31404K buff
Swap: 2048208K av, 0K used, 2048208K free 568600K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10241 postgres 9 0 4216 4216 4136 S 0.0 0.2 0:05 postmaster
10242 postgres 9 0 4444 4444 4156 S 0.0 0.2 0:00 postmaster
10243 postgres 9 0 4812 4812 4148 S 0.0 0.3 0:00 postmaster
1 psql select *:
9:03am up 9 days, 22:48, 2 users, load average: 0.71, 0.71, 0.46
166 processes: 165 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
CPU1 states: 0.1% user, 2.0% system, 0.0% nice, 97.3% idle
Mem: 1543980K av, 1052188K used, 491792K free, 265928K shrd, 32036K buff
Swap: 2048208K av, 0K used, 2048208K free 570656K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10241 postgres 10 0 4216 4216 4136 S 0.0 0.2 0:05 postmaster
10242 postgres 9 0 4448 4448 4156 S 0.0 0.2 0:00 postmaster
10243 postgres 9 0 4812 4812 4148 S 0.0 0.3 0:00 postmaster
18026 postgres 9 0 236M 236M 235M S 0.0 15.6 0:12 postmaster
18035 postgres 10 0 5832 5732 5096 S 0.0 0.3 0:00 postmaster
2 psql select *:
9:03am up 9 days, 22:49, 2 users, load average: 0.58, 0.66, 0.45
166 processes: 165 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 2.2% system, 0.0% nice, 97.2% idle
CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
Mem: 1543980K av, 1053152K used, 490828K free, 265928K shrd, 32112K buff
Swap: 2048208K av, 0K used, 2048208K free 570684K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10241 postgres 8 0 4216 4216 4136 S 0.0 0.2 0:05 postmaster
10242 postgres 9 0 4448 4448 4156 S 0.0 0.2 0:00 postmaster
10243 postgres 9 0 4812 4812 4148 S 0.0 0.3 0:00 postmaster
18026 postgres 9 0 236M 236M 235M S 0.0 15.6 0:12 postmaster
18035 postgres 9 0 236M 236M 235M S 0.0 15.6 0:12 postmaster
The difference between SIZE and SHARE is the delta, which is only
something like 3 or 4 megs for the initial select * from logs, but the
second one is only 1 meg. On average, the actual increase in memory usage
for postgresql isn't that great, usually about 1 meg.
Running out of memory isn't really a problem with connections<=200 and 1
gig of ram, as long as sort_mem isn't too high. I/O contention is the
killer at that point, as is CPU load.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-10 16:51 scott.marlowe <scott.marlowe@ihs.com>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 14+ messages in thread
From: scott.marlowe @ 2003-04-10 16:51 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Matthew Nuzum <cobalt@bearfruit.org>; 'Josh Berkus' <josh@agliodbs.com>; pgsql-performance
On Wed, 9 Apr 2003, Jim C. Nasby wrote:
> On Wed, Apr 09, 2003 at 11:55:56AM -0600, scott.marlowe wrote:
> > A couple more suggestions. One is to never allocate more than 50% of your
> > memory to a database's shared buffers, i.e. let the OS buffer the disks en
> > masse, while the database should have a smaller buffer for the most recent
> > accesses. This is because kernel caching is usually faster and more
> > efficient than the database doing it, and this becomes more an issue with
> > large chunks of memory, which both Linux and BSD are quite good at
> > caching, and postgresql, not so good.
>
> That seems odd... shouldn't pgsql be able to cache information better
> since it would be cached in whatever format is best for it, rather than
> the raw page format (or maybe that is the best format).
Yes and no. The problem isn't that the data is closer to postgresql in
it's buffers versus further away in kernel buffers, it's that postgresql's
caching algorhythm isn't performance tweaked for very large settings, it's
performance tweaked to provide good performance on smaller machines, with
say 4 or 16 Megs of shared buffers. Handling large buffers requires a
different approach to handling small ones, and the kernel is optimized in
that direction.
Also, the kernel in most Oses, i.e. Linux and BSD tends to use "spare ram"
with abandon as cache memory, so if you've got 4 gigs of ram, with 200
Megs set aside for postgresql, it's quite likely that the kernel cache can
hold ALL your dataset for you once it's been read in once. So, the data
is already cached once. Caching it again in Postgresql only gains a
little, since the speed difference of postgresql shared buffer / cache and
kernel caches is very small. However, the speed going to the hard drive
is much slower.
What you don't want is a postgresql cache that's bigger (on average) than
the kernel cache, since the kernel cache will then be "thrashing" when you
access information not currently in either cache. I.e. postgresql becomes
your only cache, and kernel caching stops working for you and becomes just
overhead, since you never get anything from it if it's too small to cache
something long enough to be used again.
> There's also the
> issue of having to go through more layers of software if you're relying
> on the OS caching. All the tuning info I've seen for every other
> database I've worked with specifically recommends giving the database as
> much memory as you possibly can, the theory being that it will do a much
> better job of caching than the OS will.
That's old school thinking. There was a day when kernel caching was much
slower, and writing directly to your devices in a raw mode was the only
way to ensure good performance. Nowadays, most modern Unix kernels and
their file systems are a match for most database needs. heck, with some
storage systems, the performance of the file system is just not really an
issue, it's the bandwidth of the connector you use.
Note that this is a good thing (TM) since it frees the postgresql
development team to do other things than worry about caching 1 gig of
data.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-10 17:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2003-04-10 17:17 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Matthew Nuzum <cobalt@bearfruit.org>; 'Josh Berkus' <josh@agliodbs.com>; pgsql-performance
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Note that this is a good thing (TM) since it frees the postgresql
> development team to do other things than worry about caching 1 gig of
> data.
Yeah. I think this is one very fundamental difference of design
philosophy between Postgres and more-traditional databases such as
Oracle. We prefer to let the kernel and filesystem do their jobs,
and we assume they will do them well; whereas Oracle wants to bypass
if not replace the kernel and filesystem. Partly this is a matter of
the PG project not having the manpower to replace those layers. But
I believe the world has changed in the last twenty years, and the Oracle
approach is now obsolete: it's now costing them design and maintenance
effort that isn't providing much return. Modern kernels and filesystems
are *good*, and it's not easy to do better. We should focus our efforts
on functionality that doesn't just duplicate what the OS can do.
This design approach shows up in other areas too. For instance, in
another thread I was just pointing out that there is no need for our
frontend/backend protocol to solve communication problems like dropped
or duplicated packets; TCP does that perfectly well already.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: choosing the right platform
@ 2003-04-22 08:30 Jim C. Nasby <jim@nasby.net>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Jim C. Nasby @ 2003-04-22 08:30 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-performance
On Thu, Apr 10, 2003 at 10:42:35AM -0600, scott.marlowe wrote:
> The difference between SIZE and SHARE is the delta, which is only
> something like 3 or 4 megs for the initial select * from logs, but the
> second one is only 1 meg. On average, the actual increase in memory usage
> for postgresql isn't that great, usually about 1 meg.
>
> Running out of memory isn't really a problem with connections<=200 and 1
> gig of ram, as long as sort_mem isn't too high. I/O contention is the
> killer at that point, as is CPU load.
Except you should consider what you could be doing with that 200M, ie:
caching data. Even something as small as 1M per connection starts to add
up.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-04-22 08:30 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-09 03:38 choosing the right platform Matthew Nuzum <cobalt@bearfruit.org>
2003-04-09 16:28 ` Josh Berkus <josh@agliodbs.com>
2003-04-09 17:13 ` Matthew Nuzum <cobalt@bearfruit.org>
2003-04-09 17:20 ` eric soroos <eric-psql@soroos.net>
2003-04-09 17:55 ` scott.marlowe <scott.marlowe@ihs.com>
2003-04-09 23:47 ` Jim C. Nasby <jim@nasby.net>
2003-04-10 16:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-04-10 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-09 18:03 ` Josh Berkus <josh@agliodbs.com>
2003-04-09 23:45 ` Jim C. Nasby <jim@nasby.net>
2003-04-09 16:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-04-09 23:58 ` Jim C. Nasby <jim@nasby.net>
2003-04-10 16:42 ` scott.marlowe <scott.marlowe@ihs.com>
2003-04-22 08:30 ` Jim C. Nasby <jim@nasby.net>
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