Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

Bigger and Better MySQL | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades


Wipro Technologies
Tata Consultancy Services
Cognizant Technologies

Home » Articles » Bigger and Better MySQL

Bigger and Better MySQL

Article Posted On Date : Wednesday, February 3, 2010

Bigger and Better MySQL

By Sean Hull

Usually an article like this one will start out with a technical word "scaling".  Unfortunately like health care reform, everyone can't always agree on what they mean by it, or even what the goal is.  So, I deliberately chose not to use the word, and use the non-technical words that we can all agree on.  Typically, when our database is slowing down, we want it to be faster, stronger, bigger and better!

With that in mind I'm going to discuss some of the various ways to get there, and hopefully put some of the technology options in perspective.  This will help you survey the landscape, and plan for your future needs.  The first part of the article discussed query tuning and hardware changes, while this installment covers adding additional servers, and application changes to make that work.
Bigger With More Boxes

Adding more MySQL instances to the mix is one way to get a faster overall response to your application.  If you have a server with multiple CPUs a lot of memory and fast disk, chances are good you're not fully exploiting all that processing power.  So in that case, it may be that running multiple MySQL instances can scale on that server itself.  That's because MySQL out of the box is a single process with multiple threads for sessions, so there's a limit to how much hardware it can really make use of.  

If your single server is maxed out, you may well benefit from using multiple servers.  But whether your multiple MySQL instances are on one server with different ports, or multiple servers, you still need a method for the application to decide where to send queries.  Do they make changes?  In that case, they'll need to go to your single master database.  Are they doing selects, then a fleet of read-only slaves will work for those queries.

1. Data Partitioning and Sharding

Since many web applications identify users by session, dividing hits to the different slaves by session could make a lot of sense.  A-G, H-O, P-Z for example might work, or a hash of the username, or the userid might be other methods to distribute users on different servers.  This is called the partition key and is an important decision as it affects how you build out your slaves, and potentially how load is distributed across those servers.  It might also affect outages of data, if one of the slaves goes down.

If you're doing this type of partitioning, you'll need to decide at runtime which database to hit.  This can be done with a middle layer like MySQL Proxy.  Although still in alpha, the concept is good, and some are already using it in production.  It sits on a server responding to requests at port 3306 then forwards those queries on to the appropriate server behind the scenes based on some logic coded in a high-speed language called lua.  

The other option is to make the decision on where to send your query in the application itself.  This is the most flexible method as it provides you with full control of the decision making process.  You can check the slaves to see if they are caught up or lagging and use master_pos_wait as needed.  Your particular language or web framework may provide some support for this kind of logic already, so check your documentation.  You might also look into Continuent Tungsten, DBIx::DBCluster for Perl and SQLRelay, which supports a lot of different languages and databases.  Also, a CMS like Drupal for instance, already has multi-readonly slave support built in, so you just have to enable it.

Another consideration when using this type of architecture is deciding whether to hit the primary or slave and when. The most basic split is based on the query, all INSERT, UPDATE, DELETE go to master, and SELECT to slaves.   If you hit the slave directly after a user submits a comment on a blog, for instance, it may not be on the slave yet, due to the lag inherent in MySQLs replication architecture.  That is what's called an artifact.  

Checking for stale data is a better method.  If you have reporting queries that run at night, this method might work well.  You just need to make sure replication is caught up.  

Another method would be to track database changes by a version number, and verify that you have the latest "version" before reading your data.

Lastly, MySQL provides a function called master_pos_wait, which makes sure the slave is up to a certain point in the binary log before completing.

2. Functional Partitioning

Chances are you're probably already doing a bit of this.  It involves creating a copy of the production database for different functions, such as one for data warehousing and reporting, another for text searching, and so on.
Better MySQL Through Load Balancing

If all of your slaves have the same read-only data, you may want to do some sort of load-balancing to distribute read traffic evenly.  You can choose from random, least connections, fastest response round robin, or some sort of weighted decision.  Although some hardware load balancers may provide you with functionality you need, they tend to be designed for web-traffic, and don't have database specific features.  

Fortunately there are a number of software solutions, which are appealing.  The Linux Virtual Server or LVS project is very mature, and provides something like DNS, but at the IP level, and is very very fast.  A couple of projects have been built on top of LVS too, including wackamole, which is peer-based so you don't have a single point of failure, and ultramonkey.

MySQL provides a lot of sophisticated features but scaling remains a nebulous and exotic technical term thrown about by a lot of different folks in different circumstances to mean possibly different things.  So we've endeavored to cover the topic while using this word less, and talking more about what you care about, namely making MySQL faster, stronger, bigger and better.

Sponsored Ads

Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions


Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions

Copyright © 2001-2023 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2023

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |

Copyright ©2001-2023 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.