?
Solved

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

Posted on 2013-06-12
11
Medium Priority
?
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
11 Comments
 
LVL 28

Accepted Solution

by:
dpearson earned 1200 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 28

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 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 800 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
LVL 28

Expert Comment

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

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 800 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 28

Assisted Solution

by:dpearson
dpearson earned 1200 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 28

Assisted Solution

by:dpearson
dpearson earned 1200 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

800 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