How large can a MySQL database get prior to efficiency begins to weaken
At what factor does a MySQL database start to shed efficiency?
- Does physical database dimension issue?
- Do variety of documents issue?
- Is any kind of efficiency destruction straight or rapid?
I have what I think to be a huge database, with about 15M documents which occupy virtually 2GB. Based upon these numbers, exists any kind of motivation for me to cleanse the information out, or am I secure to permit it to proceed scaling for a couple of even more years?
I would certainly concentrate first on your indexes, than have a web server admin consider your OS, and also if all that does not aid it could be time for a master/slave arrangement.
That holds true. An additional point that generally functions is to simply lower the amount of information that is repetitively collaborated with. If you have "old information" and also "new information" and also 99% of your questions collaborate with new information, simply relocate all the old information to an additional table - and also do not consider it ;)
- > Have an appearance at partitioning.
I as soon as was contacted to consider a mysql that had "quit working". I uncovered that the DB documents were staying on a Network Appliance filer placed with NFS2 and also with a maximum documents dimension of 2GB. And also indeed, the table that had actually quit approving purchases was specifically 2GB on disk. Yet when it come to the efficiency contour I'm informed that it was functioning like a champ right up till it really did not operate at all! This experience constantly offers for me as a wonderful suggestion that there're constantly measurements over and also listed below the one you normally believe.
It's sort of meaningless to speak about "database performance", "query performance" is a far better term below. And also the solution is : it relies on the question, information that it operates, indexes, equipment, etc You can get a suggestion of the amount of rows are mosting likely to be checked and also what indexes are mosting likely to be made use of with EXPLAIN syntax.
2GB does not actually count as a "huge" database - it's even more of a tool dimension.
In basic this is a really refined concern and also not unimportant whatsoever. I urge you to read mysqlperformanceblog.com and also High Performance MySQL. I actually assume there is no basic solution for this.
I'm working with a task which has a MySQL database with virtually 1TB of information. One of the most vital scalability variable is RAM. If the indexes of your tables match memory and also your questions are very maximized, you can offer a practical quantity of demands with an ordinary equipment.
The variety of documents do issue, depending of just how your tables resemble. It's a distinction to have a great deal of varchar areas or a number of ints or longs.
The physical dimension of the database matters too : consider back-ups, as an example. Relying on your engine, your physical db documents on expand, yet do not reduce, as an example with innodb. So removing a great deal of rows, does not aid to reduce your physical documents.
There's a whole lot to this concerns and also as in a great deal of instances the evil one remains in the information.
The physical database dimension does not matter. The variety of documents do not matter.
In my experience the largest trouble that you are mosting likely to run in to is not dimension, yet the variety of questions you can take care of at once. More than likely you are mosting likely to need to relocate to a master/slave arrangement to make sure that the read questions can run versus the servants and also the write questions run versus the master. Nonetheless if you are not all set for this yet, you can constantly fine-tune your indexes for the questions you are going to quicken the feedback times. Additionally there is a great deal of tweaking you can do to the network pile and also bit in Linux that will certainly aid.
I have actually had mine rise to 10GB, with just a modest variety of links and also it took care of the demands simply great.
I would certainly concentrate first on your indexes, after that have a web server admin consider your OS, and also if all that does not aid it could be time to implement a master/slave arrangement.