Scaling WordPress Part 1: Using MySQL Replication and HyperDB

Comments

1. Lester Chan (32 comments.) says:

   3/27/2010 at 2:43 am

   It is good to use it in conjunction with Ryan’s Memcached Object Cache and Andy’s batcache.
2. scribu (42 comments.) says:

   3/27/2010 at 4:16 am

   Nice write-up. I hope I get a chance to try it out soon.
3. Richard Hay (32 comments.) says:

   3/27/2010 at 7:57 am

   Keith – great story and I just moved to VPS so I hope to try this out in the future.

   BTW – can you contact me via email?

   Thanks.
4. Alex (1 comments.) says:

   3/27/2010 at 8:08 am

   Just a question about your afterthought. What if the boxes are outside the network, wouldn’t that way add bandwith? and plus the advantage to have multiple-location backups.

   Your article made me want to try that. I’m currently serving from my home computer-connection, and I would like to scale using my family’s computers-connections… wish me good luck
5. Jake (1 comments.) says:

   3/27/2010 at 10:27 am

   Hi Keith, thanks for this post. I am a web designer and not an IT person. But there have been situations where we were suggested Joomla instead of WP saying it breaks after a certain point is passed in size. They probably need to read this.
6. Joseph Scott (5 comments.) says:

   3/27/2010 at 5:34 pm

   As Lester mentioned, most scaling discussions should include a shared cache system. WP Super Cache is nice, but only if you have one web server. Once you even consider more than one web server you need to look at a shared cache system like memcached. Ryan’s memcached object cache plugin and batcache (from Andy) are a great combination that leverage memcached to provide a network available cache system for all of your web servers.
   • Lester Chan (32 comments.) says:

     3/29/2010 at 3:25 am

     Yeap, a central memcached cluster is a must for scaling
     • Keith Dsouza (82 comments.) says:

       3/29/2010 at 11:40 am

       @Joseph and @Lester – I agree that memcache is essential and will covering it in future parts in the series
7. Dustin (4 comments.) says:

   3/27/2010 at 9:22 pm

   Very cool. Thanks Keith. Now I just have to find a new host so that I can go back to using a VPS. Media Temple was a nightmare.

   Tough part about maximizing load time when you have traffic with a WordPress install is having 3rd party plugins play nice with eachother, the theme you’re using, and any customization you make.

   I have enjoyed WP-Minify, WP-Super Cache, and XCache as my opcode cache with Mystique as my theme of choice to customize, but then one of the three developers makes an upgrade and there goes compatibility.

   Open source software like the WordPress community is a good reminder that to really be into managing your own site you really need to learn just about everything from client-side to server-side unless you want to suffer a heart attack.
8. Ghislain D (1 comments.) says:

   3/27/2010 at 9:54 pm

   I have setup a VPS from scratch and I realized That I should have used a 64 bit version.(my mistake)
   If you need a setup like THIS don’t forget mysql limitation of 2gig per table on a 32 bit linux. So If you expect a lot of visitors, comments then you may choose the 64 version. So you can go above 2 gig limit.
   Then Sky is the limit, actually 4 terabytes for 1 table not the whole database, Just your comment table!
   By the the time you fill up this…

   Source: http://dev.mysql.com/doc/refma.....table.html

   enjoy !
   • Rob Scott (8 comments.) says:

     4/1/2010 at 8:08 am

     Yes, but doesn’t replication in this manner allow you to add in a 64 bit MySQL database for the reads – assuming you NEED more than 2GB ram for your table.
9. Andrew@BloggingGuide (63 comments.) says:

   3/29/2010 at 4:14 am

   Thanks for this very useful post. Will be bookmarking this, will definitely use it before i ran out of tweaks
10. Rob Scott (8 comments.) says:

    4/6/2010 at 9:19 am

    Oh here is a question I have been toying with – would it speed things up to replicate databases in the same box? Having one dbase for writes and one (or more) for reads – particularly on a blog that has a number of authors – this might make things quicker if the database is bottlenecking?
11. Jerome says:

    12/13/2011 at 7:14 am

    Hi there,
    Really good info and it clarified a lot regarding the master/slave usage.

    Does all my content will be going into on massive database.
    is it the best way, or will it be better to seperate content int many databases.
    For example: one site – one DB.

    I am a bit confuse as i dont think wordpress.com is using one massive database with all 60 million people.

    Thanks for any help and hope i make sense.

    Regards,
    Jerome
12. Mummy Ninja (3 comments.) says:

    1/29/2012 at 1:47 am

    So this is what HyperDB does. At first I really can’t understand what’s the use of it, but now I know that it’s very useful.
13. Jim says:

    5/27/2012 at 10:39 pm

    Part 2?
14. Yorkim (1 comments.) says:

    6/27/2012 at 1:52 am

    Hi

    Thanks for this post. I’m going to implement it on my own site. I’m going to try to provide high availability over multiple geographical sites.

    Right now my “single point of faillure” is at the internet connection at site A. In the next couple of month’s, I’m going to create site B and setup the replication. (Both sites will be connected with an site2site vpn.)

    Do you know how the MySQL Master/Slave setup reacts on connection issue between the 2 databases? I mean, what if the link between the master and the slave is down. I do a create and an update in the master database, the master will try to notify the slave but it will fail since the link is down. 10 min later the slave is back up, will the master retry? Will it push the previous updates when a new insert/update is done?

    Kr.

Trackbacks/Pingbacks

1. Scaling WordPress Part 1: Using MySQL Replication and HyperDB 3 responses says:

   3/27/2010 at 11:19 am

   […] Scaling WordPress Part 1: Using MySQL Replication and HyperDB […]
2. Weekend Roundup #76 says:

   3/29/2010 at 2:43 pm

   […] for MobilesConceptual SEO Mistakes You Must AvoidWhy You’ll Never Get Rich BloggingWordPressScaling WordPress Part 1: Using MySQL Replication and HyperDBList Posts by Category In NavigationWordPress hack: Show your top contributors without a […]
3. WordPress Picks for the Week [03/31] | Techtites says:

   3/31/2010 at 7:15 am

   […] Scaling WordPress Part 1: Using MySQL Replication and HyperDB […]
4. ??? WordPress ?? Part 1??? MySQL ????? HyperDB | Digital Cote says:

   3/31/2010 at 11:57 pm

   […] Scaling WordPress Part 1: Using MySQL Replication and HyperDB How To Set Up Database Replication In MySQL This entry was posted in System and tagged HyperDB, […]