01-04-2007, 02:17 PM #1Junior Guru
- Join Date
- Jun 2004
[hardware question] why is a seperate server for mysql recommended?
I'm just curious what the logic behind this is, exactly.
a good deal of the price in buying servers frequently comes from a nice motherboard, chassis, etc. having to purchase those items twice is just taking away from other components you could be adding to one server-- more ram, more/faster cpu's, etc. not to mention the wasted overhead in just running the operating system twice.
considering all of those factors, wouldn't it be logical to build a nicer single server rather than two "okay" servers?
the only real advantages I see are
1) potentially httpd could be reachable when mysql isn't... but for something like vb, that's almost a moot point
2) at some point you're going to hit a point where buying a nicer CPU isn't really worth the money; spending twice the money for a 10% performance increase, etc. but it seems like multiple cpu's could still get around that.
01-04-2007, 03:54 PM #2WHT Addict
- Join Date
- Jun 2004
- San Diego, CA
I pack my MySQL boxes with small but fast SCSI disks (36gb 15krpm), 2 on a RAID1 and 4 on a RAID 1/0. Not much storage, but really fast and reliable.
To do this on a web server would be wasteful. The web server doesn't need the kind of IO that a MySQL box does so it is usually fine to leave a plain SATA in the web server with a ton of storage.
Also, having run a hungry MySQL server on the same box as a web server before, I've seen MySQL use up most/all of the disk IO and leave apache children in wait for a *long* time which ends up using up all the available connection slots and essentially downs the server. Of course, since it was likely apache causing the load for MySQL, the situation resolves itself, but it is still an embarassing situation.Matt Bloom
AngryHosting - Load balanced/redundant shared hosting solutions
01-04-2007, 03:56 PM #3Web Hosting Master
- Join Date
- Jan 2004
Separating SQL from a shared LAMP setup onto a dedicated server is so you can maximize your investment and resources so they're fine tuned for MySQL.
Why would you want to waste a full Raid 10 SCSI array on a Apache/PHP server? Or other expensive options and upgrades related to increased system performance, management, and redundancy?
It really just depends on your overal goal and strategy for your website.
I personally would rather buy a number of basic 1U servers for $1-2k with acceptable resources to host Apache, then load balance those, and purchase a single high end MySQL server for say $3-4k.
You're really just arguing the old debate of Scaling Out vs Scaling Up.
Your point about buying a higher end CPU paying twice as much for a 10% increase is true. Just as much as buying a Quad CPU system for 4 times as much for only a real world difference of say 30%.
If your forums become popular I will guarantee you that you will need to split mySQL off to another server, optimize your forums, and then possibly look into load balancing Apache, and possibly MySQL at a later date.
01-04-2007, 04:12 PM #4Web Hosting Evangelist
- Join Date
- Nov 2004
- FreeBSD 6.2
I think this only applies where you have your mysql server being heavily used, in which case you want to spread the load so it doesn't steal resources from apache and other import services.
my customers don't use mysql that heavily so i can get away with hosting it on the same machine.George Donnelly / Systems Administrator
High Speed Rails inc. / FOSS Hosting
"Linux is Luke. FreeBSD is Yoda."
01-04-2007, 04:24 PM #5Web Hosting Master
- Join Date
- Apr 2004
It used to be highly recommended in the windows enviroment, because MS SQL is so highly priced. We would break out a SQL box to save on lincencing costs. Most "enterprise" hosts do this, but there is absoultely nothing wrong with hosting services on a single box.
01-04-2007, 07:35 PM #6Junior Guru Wannabe
- Join Date
- Dec 2006
Here are some reasons why...
Relating to MySQL performance, although it is true that the overall CPU performance isn't exponential with an increase in CPUs on certain CPU performance related benchmarks , relating to how MySQL handles single connections/threads and doesn't parallel them across multiple CPUs, if you are blocked on CPU waits, having multi procs is a supreme advantage. You can see this with a 2 proc system as you may have some queries/threads coming in on different procs.
The questions is really overall MySQL server performance for a number of queries in a single connection/thread. It's true that since you only get 1 CPU at a time, the max performance per CPU is not that great on multiproc systems compared to single proc, because they are similar in performance. The real issue in wanting multiple procs is that you can handle N threads simultaneously, which removes a significant performance bottleneck of CPU waits.
I've got 2 Quad Proc Dual Cores, for example. This gives me a total of 8 procs per machine that MySQL can utilize on a per thread/connection basis.
Remember, there are some quirky things with MySQL relating to connections, threads and queries. Typically, a new connection will get spun up as a new thread and all corresponding queries from that connection will only use 1 CPU, namely the one that got chosen when the connection/thread creation happened.
Now, this isn't technically true if your new connection hits a cached thread. If you have a sufficiently large enough pool of thread cache (MySQL can create this pool on startup or on the fly). This can help you to not only use your CPUs more evenly, as when the cached threads get created, they get created across multiple CPUs, not just one. Remember though, if 1 connection = 1 thread, regardless if that thread is cached or not, and you have any high number of SQL queries from that single connection, they will not be able to make usage of multiple CPUs. What matters is if you have 1 query = 1 connection = 1 thread and if the loss of performance of connection creation along with thread creation is tolerable when compared to being able to utilize N CPUs for a large number of sequential queries.
Also, increasing hardware specs will be the only true way to increase write performance of MySQL, as parallel writes of the same data just don't happen with MySQL, so you can't split them over multiple machines like you can reads using replication.
You can see that in a typical web hosting configuration with multiple databases and multiple connections/threads per second, having multi CPUs helps clear CPU wait states. This is only one part of the puzzle for maxing out performance of MySQL, but it's an important one.
The above related to CPU waits, the next situation relates to disk I/O and blocking. If you have a busy web server, your apache logs will be written to disk quite often. You will also have PHP/HTML/CSS and graphics files using disk I/O for their various purposes.
By far, with MySQL, the biggest bottleneck on single server configurations with multiple databases is blocking on disk. This happens because your disk needs to read/write with *every* MySQL Query. Even if you run HEAP/MEMORY Tables, for every query you *must* hit the disk because of how MySQL stores index data. If you have some small number of queries per page, like 10, you can easily compound that over a small number of concurrent visitors, say 10. This would mean MySQL would be handling some 100 odd queries at any given time. If every one of those queries needed the disk and at the same time you have apache/php scripts, cron jobs, backups and whatever else going on, you can see where the performance decrease can happen.
What does having the second machine do for you? Well, again, it will dedicate both disk and CPU to MySQL, which, when not having to compete for those resources from other processes will perform much better. Also, the web server performance will also increase as it is not competing with MySQL for CPU and disk.SupportLayer - Enterprise Linux Server Management
01-05-2007, 07:29 AM #7Retired Moderator
- Join Date
- Oct 2004
- Southwest UK
there's only 1 more thing to add to the discussion on performance - security. Every really secure site will expose as little to the outside as possible, so putting your database on an internal server that is only contactable by your web servers adds another layer of security.
But, I'd agree with everyone above - you generally split them for performance reasons.Do not meddle in the affairs of Dragons, for you are crunchy and taste good.