Solved

Should I use Mysql Cluster for an application with High mysql concurrent connections?

Posted on 2013-06-12
11
160 Views
Last Modified: 2015-04-27
Dear Experts,

Actually I have a mysql database where I record user website visits statistics (like google analytics).
This database is experiencing a high volume of concurrent connections and it's killing my Dedicated Server, I also have a java chat application on the same server which is affected for the CPU consummed for this Statistics Database.

Im planning to move this Database to a separate Dedicated Server but I want to think ahead on growing and I thought on create a mysql cluster for this database.

You should know I have a diarly cron process that totalizes all tables into smaller ones and my clients consult this information in real time.
Also my java chat application is the one that writes this database then I need high availability and low resources consumption.
 
I need your advice on this, Im doing right thinking on mysql cluster? There is another better alternative for the solution Im looking for?

Thx and Regards,
Luis Rinaldi.
0
Comment
Question by:luigidenaro
  • 5
  • 2
  • 2
11 Comments
 
LVL 26

Accepted Solution

by:
dpearson earned 300 total points
ID: 39243540
A MySQL cluster is certainly one approach to solving this, but you should carefully read and consider all of the limitations of the MySQL cluster implementation:
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-limitations.html

There are some pretty major limitations.

Another approach is to implement sharding of the database, where you essentially implement 2 (and later more) instances of MySQL each with the same set of tables and write half of the data to each database instance.  You can read more about this here: http://en.wikipedia.org/wiki/Shard_(database_architecture)

Sharding has none of the limitations of a cluster and is very popular in large scale applications, but the biggest downside is that it requires changes to the database layer in the application.  If your current application isn't very complex, these changes may only take a few days to implement and then you can scale up easily in the future.  However, with a complex application the changes may be more significant.

Hope that helps,

Doug
0
 
LVL 26

Expert Comment

by:dpearson
ID: 39243542
I should also have mentioned that today there are companies offering products that will support automatic sharding of MySQL.  But I'm assuming you're looking for non-commercial solutions?
0
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 200 total points
ID: 39243676
Cluster is generally to improve reading times, sharding is much better for your needs of writing log data.

You could also send all this data to a web service that could scale up or handle the sharding or whatever you do just so its abstracted away from your application.
0
 

Author Comment

by:luigidenaro
ID: 39246514
Thx For ur comments, Im not looking For a not comercial solution, but I preffer to don't change The dB engine to avoid changing my app programming, what product do You know with automatic sharding support?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Expert Comment

by:dpearson
ID: 39246520
I'm not personally aware of anyone doing automatic MySQL sharding outside of commercial solutions.
0
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 200 total points
ID: 39246710
What abou making some timy changes to your spp to rotate multiple connections? So you can manually connect to another database and rotate your writes by some arbitrary method.

the cleaner way would be to change your direct db connection to a web server or some other middleman that will handle the splitting for you. Even if you bought a commercial sharding solution, it would probably require code changes to support it. You don't need to actually setup sharding since its all going to be aggregated and summarized later, you just want to split your writes to load balance. So you could manually choose that these three websites write to logserver1 and these go to logserver2, etc...
0
 
LVL 26

Assisted Solution

by:dpearson
dpearson earned 300 total points
ID: 39248125
There are commercial solutions that don't require changes at the application level - they essentially handle all of the sharding within a cluster style environment, linking multiple machines together into a single logical database.

But as I say I'm not aware of anyone doing that in the open source world today.

When we sharded our MySQL database, we did it ourselves and changed the application level.  It didn't take us long to make the code changes to support it.
0
 

Author Comment

by:luigidenaro
ID: 39250151
And do you know any recommended commercial solutions I can use?

Thx and Regards,
Luis Rinaldi.
0
 
LVL 26

Assisted Solution

by:dpearson
dpearson earned 300 total points
ID: 39250772
Here's a couple to checkout:
http://www.clustrix.com/
and
http://www.scalebase.com/

They both claim to support SQL scaling w/o sharding changes in your code.  I've been pitched by both companies but since we have done our own sharding solution we didn't go with either.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now