• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

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

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
luigidenaro
Asked:
luigidenaro
  • 5
  • 2
  • 2
5 Solutions
 
dpearsonCommented:
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
 
dpearsonCommented:
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
 
Aaron TomoskySD-WAN SimplifiedCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
luigidenaroAuthor Commented:
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
 
dpearsonCommented:
I'm not personally aware of anyone doing automatic MySQL sharding outside of commercial solutions.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
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
 
dpearsonCommented:
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
 
luigidenaroAuthor Commented:
And do you know any recommended commercial solutions I can use?

Thx and Regards,
Luis Rinaldi.
0
 
dpearsonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now