How much data can MySQL hold before having issues?-CodePudding

This is a bit of a general question but, I recently started paying for a webhost for my website and, once I noticed that the MySQL servers are shared(checked phpmyadmin, it has sent 9GB of data on a total of 15k requests the past hour alone, and has been running for 39 days without a restart) and I only have admin control over my own databases, I started wondering:

How big can a MySQL database become before having issues?(Say, long delays, errors, crashes, etc?) Does anyone have experience with that? Mind you mine runs on MySQL 5.7

CodePudding user response：

The load you describe should be easy for MySQL on a reasonably powerful server. But it depends on what the queries are, and how well you have optimized.

At my last DBA job, we "gently encouraged" the developers (i.e. alerted them with PagerDuty) if the database size grew over 1TB, or if a single table grew over 512GB, or the queries per second rose over 50k. That's when things started to go south for a typical application workload, if the database and queries were not designed by developers who were especially mindful about optimization. Also the servers were pretty powerful hardware, circa 2020 (48 core Xeon, 256GB RAM, 3TB NVMe storage typically with 2 or 3 physical devices in a RAID).

With more care, it's possible to increase the scale of MySQL a lot higher. With variants like PlanetScale, it can support even more. Cf. https://planetscale.com/blog/one-million-queries-per-second-with-mysql

On the other hand, I saw a photo of one of our NVMe drive literally melted from serving 6k writes per second on a database with half a TB of data. It depends on the types of queries (perhaps it was a faulty NVMe drive).

The only answer that could be useful to you is:

You must load-test your queries on your server.

Also, besides query performance (the only thing most developers care about), you also have to consider database operations. I supported some databases that grew without bound, despite my team's urging the developers to split them up. The result was it would take more than 24 hours to make a backup, which is a problem if there's a policy requirement that backups must be made daily. Also if they wanted to alter a table (e.g. add a column), it could take up to 4 weeks to run.