Our Blog

MySQL Database Sizes

August 20th, 2009

closeThis post was published 10 years 1 month 27 days ago which means the content may no longer be applicable or relevant to the service we offer today. If in doubt, please contact us.

One of the things not very many people consider when using shared hosting is the size of their MySQL databases. Very few hosts that I’m aware of routinely monitor and tweak their MySQL configurations in order to provide the best possible performance, and resultingly MySQL performance can often be quite slow. At this*, we monitor many things that typical hosts don’t, and one of these things is our MySQL configurations – along with the sizes of users databases and the number of tables within. This process is always ongoing, and as user data changes within the database, so do the settings and modifications we make to compensate. And it works. We’ve had some positive feedback from people who have moved from other hosting providers and have noticed that our MySQL performance is significantly better. But, it only works if we keep things under control….

Within our AUP (Acceptable Usage Policy) we make a point of mentioning that whilst there’s no hard limit in place for MySQL database sizes, we would consider the 256MB-512MB range as “excessive” for shared hosting. 256MB may not seem like a lot to you, but for a database that’s often a heck of a lot of data and records.  To give you some idea for comparison, the average size of a database on our servers is currently at around 20-35MB, so our AUP is aimed at just over 10 times the average size. To give you further insight on the scope or severity of this issue, of the many hundreds of databases on our systems, currently only approximately 6 appear to be over this recommended AUP limit.

Why is database size a problem?

This is an issue that really needs to be looked at from several angles.

Memory

From a technical perspective and explained simply, the bigger the database – the more memory that MySQL consumes in order to read and store the contents. When there’s no more available memory, MySQL will read from the hard drive instead – much like the “Swap” or “Page” file within Windows. This is obviously much slower than reading from the memory and results in some performance degradation. Now, obviously on a shared hosting platform, unless you have very strict policies in place, it’s going to be almost inevitable that customer databases will (combined) weigh in at perhaps several gigabytes. This is certainly not uncommon and is to be expected. Left unmonitored, though, databases could grow to degrade performance is a very noticeable way.

Performance

One of the most common issues with MySQL performance is the often lack of good indexing and optimisation. I won’t go into the deep technical details here, but essentially the larger the database, the more records you can expect there to be inside. The more records there are, the longer it takes to search through these records to find the one you’re looking for. Indexing allows you to search through these records faster, but again this consumes memory and can only do so much for performance.

Backing up large databases from within MySQL, especially on a shared hosting platform, can also increase load due to locking tables and other issues.

Evaluation

As a shared hosting provider we need to evaluate and often be strict within our policies as to what is deemed acceptable or not on shared hosting. In my many years experience of server management, it seems quite evident that users simply do not look after their own MySQL databases to the point where they will let them grow and grow and grow until their hosting provider steps in and informs them that there’s an issue. Unfortunately, at the point where it begins to become an issue, it’s often a little too late and is more than likely affecting performance in a way where the database needs to be disabled or otherwise heavily pruned. Some users aren’t even aware their databases are growing. Installing plugins that log user activity is one of the common causes of huge database growth.

To try and prevent issues like this from happening, we’ve decided to potentially implement a solution that will limit the size of MySQL databases. The way this would work, effectively, is that if your database exceeds a certain threshold size outlined within our AUP (we were thinking 512MB), your “INSERT”,”CREATE” and “UPDATE” permissions would be revoked for that database. Your database would be left active, and will function to some degree, but no new data could be added until it is brought down to below this threshold. The obvious drawback to this solution would be that if you are using scripts to update tables that contain, for example, statistics – then this would perhaps display errors on your website.

So why the change?

Overall it’s to really maintain the performance of our shared hosting servers and to further draw the line between what should be considered a suitable site for shared hosting, and one that is not. For the legitimate databases that are genuinely this large, it allows us to confirm that indeed this site requires a different solution to the ones that we can offer.

Unfortunately it’s not a case of just including the MySQL database usage into your disk quota. There’s a world of difference between storing 512MB of static files and having a 512MB database, and this is fundamentally the issue.

Don’t Panic

At this point we’re really trying to gauge your feedback on this idea before implementation. As I stated before, currently as it stands only 6 or so databases would even be affected by such a change, so this is certainly not an implementation that is going to have any noticeable impact on the service of almost all of our customers. For those who are curious, your database sizes can be viewed directly from within cPanel, so you can see just how likely this is to be a problem for you.

We welcome your feedback on this issue, so please let us know what you think!

Two Comments

  1. Darfuria

    August 21, 2009

    I think it’s a great idea – although I can’t see the limitations ever affecting me.

    I would be interested to see some differences in performance between two databases that are exactly the same – one that has been optimised by you, and one that hasn’t.

  2. Jules

    August 21, 2009

    @Darfuria – That’s (hopefully) the point 🙂 The limitations should affect only a very very small number of people, but serve to ensure that the others get the best possible performance they can. We don’t want anyone to be negatively impacted by this, but rather cause them to become proactive in ensuring that their databases are pruned of old and possibly obsolete data regularly. Most of the time it’s silly things like a forum that can contain many tens of thousands of “Invalid login” entries that simply need to be emptied.

    In regards to the opimisations, it’s really hard to say what the difference would be in terms of performance. On the individual databases, we don’t do much optimisation at all. Weekly we optimise all tables on every database to reduce slack space (this helps to keep your database sizes down and improves performance slightly) and we do have checks looking for poorly utilised indexes and will change them to be more efficient if needs be.

    The real performance gains come from the MySQL configuration itself, and unfortunately it’s something not many people know how to (or bother to) optimise correctly. Maybe I’ll try and get together some benchmarks for comparison 😉 In the meantime, here’s what someone said when migrating from an expensive VPS:

    http://www.thiswebhost.com/forums/index.php/topic,8.msg57.html#msg57

What do you think?