Solved

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

Posted on 2013-06-12
11
164 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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