Well to start i thought i was a genius for setting up the MYSQL replication server. Well im not….. Anyways i found it really helped but it wasnt a complete solution the load is still intense. After much research i found that what i did would be completly awesome in a read only enviroment, but there is writing involved. Because of this the replication still bottlenecks at certain places. What do i do?!?!?! Well after a weekend of research i have another solution im going to try. Caching. I noticed that we get roughly 60k page views a day. Thats a crap load of queries given that every page has some sort of database connection. Ive decided that for this im going to use Memcached. “Memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.”–Taken right off there site. Well so we have database connections cached, what about static html and images? For that im going to implement Squid and set it up as a reverse proxy server.

The Rundown of Memcached

Memchached can cache database queries, flat out bad ass. However all these queries are being put into memory, and whats cool about memcached is that you can have it hosted on another server. Basically you run a query on a page, the same query gets hit by users say every 4 seconds. Well cache the results of the query and keep them cached for 5 seconds. This will add about a 5 second delay to when items get updated, but will alleviate all those queries to huge monolithic poorly designed databases!

Sharding the database

Sharding, try searching it. Took me forever to find some descent sources to learn what the heck this is. As far as i knew before this, it was when you died in EQ2 and needed to find your shard so you don’t have an exp penalty :). Any who. Sharding. What it means is to break your database/database server into many. Only storing a certain region of the data on each shard. Now each shard can be replicated and clustered all you want, but the overall concept of separating the data across several servers has to remain. Whats the point you ask? Why would you waste time and effort? But managing it would suck, right? Ill tell you the point. When you do a write to a table in a database it locks it temporarily to change the data. Say you have a very intensive back end that needs updating a lot…well thats a lot of locking and will cause the reading to get backed up. So what you do is split the data across several servers, so that the weight of writing to one server is now spread across many causing less backups. The downfall however is you’ll have to teach your program to know what server to connect to for its information.

Author: Jason Rogers