Home » Questions » Computers [ Ask a new question ]

How big can a MySQL database get before performance starts to degrade

How big can a MySQL database get before performance starts to degrade

"At what point does a MySQL database start to lose performance?

Does physical database size matter?
Do number of records matter?
Is any performance degradation linear or exponential?

I have what I believe to be a large database, with roughly 15M records which take up almost 2GB. Based on these numbers, is there any incentive for me to clean the data out, or am I safe to allow it to continue scaling for a few more years?"

Asked by: Guest | Views: 121
Total answers/comments: 4
Guest [Entry]

"The physical database size doesn't matter. The number of records don't matter.

In my experience the biggest problem that you are going to run in to is not size, but the number of queries you can handle at a time. Most likely you are going to have to move to a master/slave configuration so that the read queries can run against the slaves and the write queries run against the master. However if you are not ready for this yet, you can always tweak your indexes for the queries you are running to speed up the response times. Also there is a lot of tweaking you can do to the network stack and kernel in Linux that will help.

I have had mine get up to 10GB, with only a moderate number of connections and it handled the requests just fine.

I would focus first on your indexes, then have a server admin look at your OS, and if all that doesn't help it might be time to implement a master/slave configuration."
Guest [Entry]

"In general this is a very subtle issue and not trivial whatsoever. I encourage you to read mysqlperformanceblog.com and High Performance MySQL. I really think there is no general answer for this.

I'm working on a project which has a MySQL database with almost 1TB of data. The most important scalability factor is RAM. If the indexes of your tables fit into memory and your queries are highly optimized, you can serve a reasonable amount of requests with a average machine.

The number of records do matter, depending of how your tables look like. It's a difference to have a lot of varchar fields or only a couple of ints or longs.

The physical size of the database matters as well: think of backups, for instance. Depending on your engine, your physical db files on grow, but don't shrink, for instance with innodb. So deleting a lot of rows, doesn't help to shrink your physical files.

There's a lot to this issues and as in a lot of cases the devil is in the details."
Guest [Entry]

The database size does matter. If you have more than one table with more than a million records, then performance starts indeed to degrade. The number of records does of course affect the performance: MySQL can be slow with large tables. If you hit one million records you will get performance problems if the indices are not set right (for example no indices for fields in "WHERE statements" or "ON conditions" in joins). If you hit 10 million records, you will start to get performance problems even if you have all your indices right. Hardware upgrades - adding more memory and more processor power, especially memory - often help to reduce the most severe problems by increasing the performance again, at least to a certain degree. For example 37 signals went from 32 GB RAM to 128GB of RAM for the Basecamp database server.
Guest [Entry]

"I would focus first on your indexes, than have a server admin look at your OS, and if all that doesn't help it might be time for a master/slave configuration.

That's true. Another thing that usually works is to just reduce the quantity of data that's repeatedly worked with. If you have ""old data"" and ""new data"" and 99% of your queries work with new data, just move all the old data to another table - and don't look at it ;)

-> Have a look at partitioning."