[oclug]PostgreSQL Hardware

[Brad Barnett]

On Sun, 27 Oct 2002 13:27:23 -0500
Rod Giffin <rod at giffinscientific.com> wrote:

> On Sunday 27 October 2002 07:23, Brad Barnett wrote:
> > On Sun, 27 Oct 2002 01:48:35 -0400
> >
> > Rod Giffin <rod at giffinscientific.com> wrote:
> > "Did that."  "What came first."  I'm glad you agree with me, because
> > these days the database server can often hit the wall first.  Of
> > course, as I've said all along, this depends on the circumstances.  It
> > is quite possible to write code on a web server that will hit that
> > bottleneck first.
> 
> The thing is, I don't agree with you.  It's more likely that the web 
> application server will experience loading before the database.  Yes
> it's possible that it happens in reverse, but the fact of the matter is

Then you do agree with me Rod.  You just aren't paying attention, and
that's the problem.  In each and every message in this thread, I've been
consistent in saying that "Since we don't know what this database will be
used for, we can't determine the machine requirements"

By typing the words "more likely" and "Yes it's possible that it happens
in reverse", you do, in fact, agree with me.  

You seem to think that this has to do with the average use, or the common
use.  It doesn't.  This has to do with a specific use, and until you get
that through your head, and continue to talk about averages and
probabilities, you're responses are going to be incorrect.

> that is usually a symptom of poor application design, and/or poor
> database design, and/or perhaps underpowered database hardware.
>  

Again, you're not really paying attention here.  Yes, sure, there are
cases where if your database is hitting the wall before your client
application, you've probably got poorly written queries, or the data is
organized incorrectly in the database.  However, there are also many cases
where the client end of things is relatively simple, even with minor PHP
and HTML pages compared to the queries that get thrown at the web server.

That's all that counts.  It doesn't matter how often either occurrence
happens.  It just matters that it does.

> Since I'm not assuming that the system architect is an idiot, I'm
> assuming that the design of both the database and the application are
> appropriate for the requirement, and that the tables are properly
> indexed.  The original question also assumed this, in that the original
> question was essentially "Is this hardware configuration enough to
> handle this load"
> 
> The answer is, on the database: Given the data we currently have,
> chances are you're in overkill mode there, but don't change anything at
> the moment because it's certainly better to have extra horsepower than
> not enough.

Again, here you are, backing up what I have been saying all along.  You've
used the word "chances" to modify your reasoning above.

> 
> The answer for the web application server: We don't know enough about
> the application.  However, typically you can use several web application
> servers to connect to one database, the technology is designed this way.
>  Also, 
> typically, the load on the web server is higher than the load on the 
> database, simply by virtue of the fact that the application server is
> doing more work, to wit:
> 
> The database server maintains part of the data layer.  
> The application server on the other hand is maintaining the rest of the
> data layer as well as all of the business logic, the application logic,
> and the presentation layer, as well as maintaining a pool of  dynamic
> connections to the database, multiple connections to the up to 700
> clients that are simultaneously connected, and all of their session
> management information.  While the database may implement a security
> layer, it's only user is the web server.  The application server on the
> other hand may be implementing a security layer for 700 simultaneous
> users - in this case.
> 
> Every time the users so something in the application, the application
> server has to respond to it.  It has some work to do in all of it's
> layers.  The database, on the other hand only has to respond when a user
> wants to read or write data from or to the database.  This might happen
> on every page but, don't forget that the same data that leaves the
> database has to be processed by the application server as well.  The
> data has to be parsed, formatted, and validated before it can be sent on
> to the client, or to the database.  In any case, this happens inside of
> the application server, not the client, and not in the database.

Rod, some of what you say above is correct, and some of it isn't.  You did
get the "typically" part correct, however.  Mostly, you've left out many
things that the database may do.  Your view of a database seems to be one
of a program that just spits out data, without any computational effort at
all.  You seem to think that a database just "reads and writes" data.  It
does far, far more than that, if used correctly.  

Databases are designed to _compare_ data and make responses based on that
comparison as well.  They don't just throw out data without even thinking
about it, although they certainly can.  With the flexibility of SQL, it is
disturbing to think that people today believe that all a database does is
spit out raw data.

For example, the database, if the queries are complex enough, can have a
significant amount of CPU overhead per query.  You seem to think that any
query that takes CPU load is a bad query, since you seem to think that all
good queries have no CPU or memory load.  You haven't said so, of course,
but you've completely ignored the possibility.

A complex and cpu intensive query is _not_ a bad thing, if it is doing a
more efficient job of handling the data comparisons than the application
server can.  Databases are designed to do just this, and there are several
projects I have worked on that ended up on this boat.  These weren't cases
where the queries were poorly written, or the tables or indexes were
poorly designed.  These were simply the sort of queries that were very
intensive, and were better handled by a database engine than PHP or Perl. 


Part of the reason for that is bandwidth requirements between the server
and client alone.  

It makes sense to throw more money at the database hardware _if_ the
database is more efficient at using that hardware for its task than
anything else, and it needs the hardware.

Until you wake up to the FACT that the database can be used for more than
simple SELECT statements, you'll continue to keep this flawed design
methodology. 



> 
> > > Question for you... what the hell is the "raw data stage" in
> > > software design?
> >
> > I don't know.  I was referring to taking a chunk of raw data, and then
> > building the database for it, not about software design.  You've
> > mentioned in the past that you need to design a database _for_ the
> > data, so you obviously what I'm talking about in that respect.
> 
> Well, you've got the software design process backwards... well maybe not
> quite backwards but you've got the cart before the horse so to speak. 
> Other than reference data which you might already have (although it's
> not a prerequisite for database application design) the purpose of
> building the application is to gather, store, retrieve and process data.
>  You don't normally start out 
> with data, you end up with data.  

What is your point here Rod?  Whether or not you normally have data to
start out with, the fact is you NEED to design the software and the
database layout to take that data into account.  Anything else is foolish.
 It is this design process I was referring to.

This of course doesn't change the fact that you can have data before a
database.  I know you find it hard and incredibly difficult to believe
though, from the way you worded your comments above.

> The reason you might need a database
> for the data is so you have some place convenient to put it once you
> get it - and allow you to access that data again.
> 

You don't say.  Wow. ;)

> It's also entirely possible that you can use flat files in your
> application - which would almost negate your requirement for a
> database... although the indexing capabilities of the database software
> can come awfully handy there too.  

Yes, you can use flat files.  Many applications don't need the overhead of
the database layer.   

> 
> You know I've never met Milan in person that I know of, but I have seen
> some of the results of his work.  I can tell you that he knows something
> of what he does for a living.  You'd do well to sit back and learn
> something from people who do this sort of stuff for a living, then maybe
> someday you can sit back and say, "I'm not an authority, I just do what
> I do."
>