2 tier

Manat

Hello all,

I have a general infrastructure question. Although I already know the answer, I don't know it specific to PHP and mySQL.

How important is it to have a 2-tier architecture with PHP and MySQL? I mean a Web Server and a Database Server.

I know the answer but not with mysql. Because with SQL Server and Oracle you can have stored procedures and triggers, user defined functions and this all happens on the DB server.
So you can seperate alot of the RAM and CPU power.

However mysql doesn't support stored procs or triggers and much less user defined functions. So really the only advantage would lie in putting table scans on another box??? Is that it?

Thanks
I apreciate your input

Manat

anyone?

Weedpacket

You sound confused. MySQL is a database server. You need a web server (e.g., Apache) if you want to provide a web site, and if you want it to have a database-driven back end, you need a database server.

Or are you talking about machines? The vast majority of sites run on a single machine that have both web- and database server installed.

If you want a database server with stored procedures, triggers, and user-defined functions, consider PostgreSQL.

Manat

Hi,

No i'm not confused at all. MySQL is not a database server, it's a DBMS, just as SQL Server is an RDBMS. When I say Database server, as my the subject line of my post would infer I mean seperating the webserver and the DBMS onto two seperate machines(2-tier).

My question is mostly based on hardware. I don't see the ambiguity in my post. When I mentioned stored procs and triggers, I was saying that is a big reason why SQL Server and Oracle always have their own dedicated box.

So my question is, should MySQL go into it's own server(machine, e.g. compaq proliant). Most sites have a 3 tier architecture, with the web server, code logic on one box and the DB on it's own box. If you have a site that gets around 3,000 unique hits a day, should a 2-tier architecture be the way to go?

Thanks
MK

drawmack

There is another benefit to two machines that you're missing, security.

If you have the web server on one machine and the database on another you can have the web server in the DMZ and the database server in the MZ

Manat

very true!

But as far as CPU and RAM goes, specific to MySQL, is a table scan the only benefit of a two tier architecture?

Thx
MK

drawmack

that depends how complex are your queries. Even without triggers and user defined procs the cpu load can get pretty big if you're doing a lot of math and string functions on the data right in the sql.

Manat

True, but math and string functions are done by PHP.
Since it's done by PHP, it should be the web server doing the processing, no?

For example, I have a search box, that takes the description content of a product and truncates it to 180characters and stores that in a variable and and echos it in a while loop, for each search result. Is truncation done on the DB or in Web Server RAM?

Thanks
MK

Weedpacket

Originally posted by Manat
True, but math and string functions are done by PHP.
Since it's done by PHP, it should be the web server doing the processing, no?

If you're not using any of the inbuilt maths/string functions the DBMS comes with. Or writing stored procedures.

For example, I have a search box, that takes the description content of a product and truncates it to 180characters and stores that in a variable and and echos it in a while loop, for each search result. Is truncation done on the DB or in Web Server RAM?

If the DBMS did it, it would reduce the connection load between the database and the web application, because you would no longer be transferring a load of information from the database to the web application that the latter just throws away.

I like drawmack's security consideration, as the best reason for splitting the job across two machines. As far as CPU and RAM goes - if you can afford it (and can justify the expense on the basis of expected traffic), go for it; if you can't, the problem's moot. Personally, I don't see why having triggers or stored procedures suddenly makes a DBMS so big that it needs its own machine. Sure, if you're buying a comprehensive-with-a-kitchen-sink system like Oracle or SQL Server, then you probably would have to get a second machine, or skimp elsewhere (I can easily imagine SQL Server making things a tight fit with only 1GB RAM to play with). But there shouldn't be any problems fitting both a web server and a database server into one machine's core memory.