(posted at 09:46PM BST)
It is a generally accepted practice in the hosting world to have websites and databases running on the same servers; back in the day, this was due to the unacceptable latencies of opening TCP connections from the webserver to the database server - the query typically only taking microseconds once the initial connection had been established.
A lot has changed since then.
Databases can now span many gigabytes in size as opposed to megabytes; some poorly designed tables (by those with no proper grounding in Computer Science or understanding of the concept of normalization) with inappropriate joins or lack of suitable indexing can cause a database server much pain.
You can take the easy way out and throw more CPU/RAM at the problem; unfortunately, this does not scale with the larger databases, as a database consisting of 3GB of diskspace will consistently eat 3GB of RAM every time you run a query that needs to 'touch' every single row in the table due to a poorly constructed query or non-existent or insufficient indexing.
Even with properly constructed databases, you still run into the problem of disk caching - the primary purpose of a webserver is to answer the request and stream the response back to the client as quickly as possible; latency due to database issues as described or poorly-constructed queries will kill the responsiveness of your site.
The correct answer is to run a separate database server with RAID 0 on the filesystem that stores the databases.
RAID 0 ?
Is he nuts !?!?!
Not quite... you see, RAID 0 offers the absolute highest disk performance you can get but with the tradeoff that you have no redundancy.
Although, if you introduce DRBD into the equation and replicate the database filesystem to another machine which is using RAID 5, you regain your redundancy there at the cost of introducing slight latency to your disk writes - which you would have experienced anyway if the database server was running RAID 5.
If the RAID 0 array fails, the secondary DRBD instance will transparently answer any read/write requests from the database server; while also acting as a source for the rebuild of the RAID 0 array.
The end results speak for themselves; disk throughput is more than trebled and without a webserver on the same machine screwing with the disk cache, the average SQL query is answered in less than one fifth of the time it usually takes on a shared server - the latency of the TCP connection is less than the time it takes for the browser to initiate a HTTP connection.
Of course, it doesn't matter how well-specified or well-configured your webserver/database server are if your code is unoptimized garbage and your database design has a graduate DBA crying into his soggy cornflakes.
The general rule is that optimization should be used to make fast code even faster... slow code should be re-factored into fast code and then optimized into faster code!
IMHO, trying to optimize slow code is an exercise in futility. |