Welcome to WebmasterWorld Guest from 54.147.16.12

Forum Moderators: bakedjake

Message Too Old, No Replies

Two MySQL + HTTP servers or 1 HTTP + 1 MySQL server

     
1:28 pm on Apr 15, 2004 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 3, 2002
posts:482
votes: 0


Which setup would usually scale better:

- having one http only server which connects via TCP/IP to a MySQL server on a 100Mbit/s network

- two servers each running an http server and MySQL with replication, assuming the client code is aware and sends write queries only to the master via TCP/IP or Unix sockets and read queries via Unix socket locally. Also some kind of a reverse http proxy will be used in this case.

The applications will be using the database heavily, unfortunately no previous info on how it behaves with replication or via TCP/IP is available to take into account.

If I take the second path, would an Apache reverse proxy server better than something more specialized like Eddie (http://eddie.sourceforge.net/gateway.html) or a squid reverse proxy.

Another option is to serve static files off a 3rd server which will run the reverse proxy.

If the servers are different I guess using the one with most RAM for db would be best.

Any recommendations/past experience?

2:46 am on Apr 16, 2004 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 26, 2003
posts:1347
votes: 17


In your case, i'd say just pony up the money and buy oracle 9i RAC and live alot more comfortable.

RAC is easy to maintain, setup and you can cluster/load balance without replication. RAC for linux has all the features you need and over the run is probably cheaper then replication and proxying nightmares you would/could experience otherwise.

BUT, without knowing what your app does, we can't really help you. Is this alot of queries? updates? inserts?

if its queries and they're common you could setup squid to do caching and make sure your result pages are cachable by x amount of time and that will offload some of your db server.

Otherwise, get an opteron machine, kernel 2.6 and run mysql on some fast scsi drives that are stripped for performance and you will have a killer backend capable of handling a huge load.

8:49 am on Apr 16, 2004 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 3, 2002
posts:482
votes: 0


>In your case, i'd say just pony up the money and buy oracle 9i RAC and live alot more comfortable.

I was specifically asking for MySQL because the app uses MySQL, we can't just change it to use Oracle as a backend like that.

>BUT, without knowing what your app does, we can't really help you. Is this alot of queries? updates? inserts?

It's an online store, most of the queries are SELECTs.

>if its queries and they're common you could setup squid to do caching and make sure your result pages are cachable by x amount of time and that will offload some of your db server.

We can setup http 304 caching from the config, without a reverse proxy.

>Otherwise, get an opteron machine, kernel 2.6 and run mysql on some fast scsi drives that are stripped for performance and you will have a killer backend capable of handling a huge load.

Actually I'm a bit of an Intel fan when it comes to hardware, it will run on FreeBSD too not Linux. It won't have hardware RAID, is md comparable or it's no good?

3:30 pm on Apr 16, 2004 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 26, 2003
posts:1347
votes: 17


Martin,

Just offering my advice but it sounds like you already decided on your platform.

The purpose of using squid or any other caching process is to offload your webservers completely or to be able to add/remove webservers to handle the load.

HOWEVER it sounds like your aiming to run a huge e-commerce system that will be expected to be busy and in my opinion mysql won't cut it. Someone else can chime in, but it sounds like if your App is written for mysql then you should contact the programmer/vendor and have them build a scalable solution for you as no matter what you do the issue with availability and performance relies in the design of the app and not just the hardware thrown at it.

I'm responsible for some LARGE database (terabytes in size) and i use the tools that are right for the job. I wouldn't trust my ecommerce enterprise to mysql unless i had a vendor to support it and integrate replication & load balancing for me.

Is it more cost effective for you to come up with free ways to try and tweak the performance or to pay for an application that is designed for commerce & availability from the get go and focus on your business needs and requirements?

5:47 pm on Apr 22, 2004 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 20, 2004
posts:1477
votes: 0


MySQL is an excellent solution, and is extremely competitive with Oracle, SQL Server, etc. It incorporates load balancing in a manner which performs exceptionally well with Linux, which also has excellent load balancing and clustering capabilities. MySQL thoroughly supports db replication. The user/developer/support community is unlike any other outside of the open source bubble.

As a database developer who has used Oracle8i in addition to most other db apps, I can say that working with MySQL in a robust e-commerce environment is sweet. It's fast, capable, and scalable without the huge investments required by Larry Ellison, Bill Gates, and the rest.

I can say that Oracle is truly excellent, and should be considered when there is an idiosyncratic feature that your application simply must use, but for 99% of the web db apps out there, it's simply overkill.

To pull a brief testimonial from MySQL.org:
"We chose to use the MySQL database server because of its ease of installation, maintainability, configuration and speed. MySQL has also provided us with huge cost savings, which we have been able to funnel into other resources." óRachael LaPorte Taylor, senior technology architect at the U.S. Census Bureau

That's the U.S. Census Bureau. Wanna guess how many terrabytes of data they maintain? :)

7:38 pm on Apr 22, 2004 (gmt 0)

New User

10+ Year Member

joined:Apr 22, 2004
posts:3
votes: 0


The setup depends on your application, the expected expansion, failover requirements etc.

Some things to consider:

Are most database access reads or writes?

What precentage of db queries can be cached?

Are DB operations RAM or CPU intensive?

What is the resource utilization (hits, cpu, bandwidth) distribution on your web site between images, static html, and dynamic scripts?

What scripting lanaguage are you using?

How does apache caching impact your web site?

All of these will help form an answer. If I had two servers, I would rather put the database on one and apache on the other. This allows you to fine tune the system from the ground up for the specific application. For example, on the DB server you may configure dedicated drives for logging, the database files, and the core operating system while spreading swap partitions across all but the non database drives. Block size, partitioning, etc. could all be tuned for mysql performance.

On the apache box, you can isolate the logs and web site files on different disks. Dual procs using the apache worker mpm can scale very well.

You could offload image calls to another low-end system. I've done this on some sites by callling images via image.domain.com. This gets sent to a server with minimal apache build.

3:29 pm on Apr 23, 2004 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 26, 2003
posts:1347
votes: 17


8i is a far cry from 9i and 10g. If you want an enterprise mysql installation it will cost as much if not more then an oracle installation when you figure in software and support costs.

I can buy oracle 9i for 3995, pay for metalink support and have a high availability database that scales very well and has vastly superior recovery features.

Have you ever tried maintaining a replicated mysql instance and then recovering corrupted data while keeping the performance and availability of your ecommerce site alive?

I've had mysql corruption on busy sites hundreds of times. I have yet to see an oracle instance that corrupted on me once.

Mysql has its place, no denying that. But use what your business demands, and not just something because its "Free" as your cost isn't just the cost of the software but the cost per transaction, the cost for support and the cost for downtime as well.

11:37 pm on Apr 24, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:July 28, 2003
posts:188
votes: 0


If you think you can pay $3995 for Oracle 9i RAC to use in an internet connected application, then Oracle licensing's lawyers would like to discuss your license violation with you.

With a volume discount, a 2 cpu license of Oracle database + RAC and first year's support comes to only $146K. :(

You can purchase a LOT of professional support from MySQL for that much money. Enough to have them come and set everything up for you and babysit it for you completely if you like and still have cash left over.

With the added bonus that the person they send will have actually used their product on your platform before, unlike the consultant Oracle sent us for our 9i RAC install, who hadn't ever installed RAC (or even basic 9i) on our platform before. Sure, RAC was still pretty new a couple years ago when this happened, but we could have sat there and followed the installation instructions step-by-step just as easily as he did.

Oh yeah, and 9i RAC is slower than MySQL and has a tendancy to take out all the other cluster nodes if one goes down because of the way they share transactions in a failure.

There are a few features that Oracle will get you that MySQL doesn't have, but cost, speed, reliability and stuff you need for a basic web/ecommerce application aren't among them.

No, I don't work for MySQL, but I do take care of several 9i RAC clusters. We posted Oracle's "Unbreakable" line of advertisements on the department wall as a joke when they came out. The latest versions out are getting decent, but they still have a tendency to not last longer than a couple of months and aren't nearly as reliable as a fully patched up 8i system.

6:02 pm on Apr 26, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 26, 2004
posts:173
votes: 0


HyperThreading does increase the performance of particular database queries, but many queries are most I/O intensive and therefore do not take advantage of HT in a measurable way. One query where I've noticed a performance improvement is in calculating the distance between lat/lon points using the great circle formula to sort thousands of records. HT does come into play in this scenario for my web site because of the millions of calculations that must be made to return a result set.

Oracle, Mysql, Postgresql et al will each perform well for eCommerce. With that said, each will require that a knowledgeable database administrator help manage the system, or bottlenecks can and will crop up. For example, a finely tuned database server that runs fine now may bottleneck once tables begin gathering a six months' worth of data. You need someone to refine your indexes and perhaps re-think query methods if that happens.

As for Mysql "not cutting it", I'd beg to differ on that point. A popular example of Mysql-powered ecommerce is [finance.yahoo.com....] Then there is the U.S. Census web site [fedstats.gov...] which runs many Mysql-based tools like Quick Facts, Fed Stats and so on....

I have my own anecdotal evidence regarding Mysql performance. My dual 2.2ghz Xeon rackmount (2gb ram, 2xUltra 160s) serves up about 25 web sites, many of which are Mysql-driven. The most popular site snipped during the business day averages about 400 visitors per hour. During the business day my server is generating about 3 database-driven pages per second, and the server load averages "0.20" during this period. Mysql reports an average of approx. 30 queries per second during the business day. So, in my situation, Mysql performs more than just fine. It screams!

Sean.

[edited by: DaveAtIFG at 6:11 pm (utc) on April 26, 2004]
[edit reason] Removed URL [/edit]

6:06 pm on Apr 26, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 26, 2004
posts:173
votes: 0


To the original question.... On only a 100Mbit connection between machines I would go for replication.... 8-10 megabytes/second peak of 100Mbit is not fast enough for data transfers and you'll bottleneck badly. If you could manage 1Gbit then that's a horse of a different color!

Why not a single quad-processor server with two large raid arrays? One for web server data, the other for database server data?

Sean.

9:07 am on Apr 27, 2004 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 3, 2002
posts:482
votes: 0


Actually I already have it on 3 servers, 2 Apache + 1 MySQL. The DB is doing an average of 270 queries/s with load of 0.8.
12:24 pm on Apr 27, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 26, 2004
posts:173
votes: 0


Martin,

Nice hardware! Have you tested complex queries via SSH/mysql cli on the Mysql machine and also via SSH/mysql cli on the Apache machine to gauge the efficiency lost through the 8MB/second interface (as opposed to the 80MB/sec interface of a raid array for example). I'm curious about the results.

I've learned that software can come into play too. I was able to reduce my queries per second 400% by developing a cacheing module in PHP. I wrote a "put_object" function and a "get_object" function that would work in tandem. Before rendering a particular database-intensive page section, I would first check to see (via get_object) if that section had already been rendered and stored inside my object_cache table (via put_object). Each "object" was stored with some context information and a "timetolive" variable (which varies depending on the section of the page). It's worked tremendously well for me. Particularly because particular sections of a page may be different for every visitor, while other sections are universal. A traditional whole-page cache system wouldn't work for me, but this works great.

Sean.

5:03 pm on Apr 27, 2004 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 26, 2003
posts:1347
votes: 17


You don't need Rac for high availability. You can use Datagaurd or even "ship your archive logs to a standby" and you can buy Oracle for 3995 for use in a server environment.

I'm not sure about your Rac install however i have a cluster of 3 v880's running an install of 11i with everythingf from ERP to CRM to sales online to a custom scheduling application and up to 7,000 users online tied into our portal servers and we have 0 downtime.

Anyhow, your cost does vary on your sales person. However for any business going into etailling even 146k is cheap for the security and reliability benifits you take. If you do 1-2 million a day in etailing and your mysql server crashes for 2-3 hours while you restore or fail over you have already lost the cost in savings you supposedly save. Also for the 146k you bet you can get oracle to throw in free training and free access to metalink and gold level support for atleast the first year if not the full 3 years.

Just use the tool thats right for the job. If you don't want opinions don't ask for them :)