Configuration of MySQL for Shared Hosting

If you ask around about shared hosting setups with MySQL everybody is frightened. In fact it looks like shared hosting is one of the most difficult setups you can get.

The number of users is big, the number of tables huge and the load pattern is completely unpredictable and the queries often very, let us say: non-optimal.

Here one of the DBA wisdoms come into play: Controlling developers is like herding cats.

If you talk to the Shared MySQL Hoster they confirm that this setups are very demanding!

Why is shared hosting of MySQL databases so difficult to operate?

Several different problems come into play:

• The number of database users are typically hundreds or even thousands.
• There are many databases (schemata). Typically 1 to 10 per user.
• There is a huge number of tables (a mixture of InnoDB and MyISAM).
• The application and user behavior is more or less unpredictable and completely non-controllable.

The number of tables and users is a problem because it is out of the range of typical administrators experience and out of the range where typical MySQL configurations recommendations are valid. Further Shared Hoster are possibly not the main target group of Oracle so their problems will not get the highest priority on the task list of the MySQL developers.

The user behavior is a problem, because we have to fight with a big number of standard applications (my hoster for example provides up to 60 different applications) and a countless number of self written code and software. Mainly the latter ones can contain very badly written queries, because shared hosting customers are not always very skilled. This causes a high CPU load to the system and possibly massive I/O load as well.

The usage of RAM and the network throughput in such setups is typically NOT a problem. So one should better invest in more cores and a better I/O systems than in more RAM.

The MySQL Configuration for Shared Hoster

When it comes to the configuration of MySQL especially the following parameters should be considered:

• table_cache (up to MySQL v5.0)
• table_open_cache / table_definition_cache (from MySQL v5.1)
• innodb_open_files (with innodb_file_per_table = 1)
• max_open_files (open_files_limit, ulimit -n)

The default values of these variables are much to small for Shared MySQL Hoster setups.

With these parameters applies the rule: As big as possible (in a reasonable range). I have not heard anything negative about setting those values too big, except they allocate more memory, what in this case should not be a problem. But you should not overdo (avoid swapping). I would personally try to increase those values up to the number of your tables and measure how the system behaves. Values from 50k to 100k are not uncommon.

The MySQL Status Variables:

mysql> SHOW GLOBAL STATUS LIKE 'Open%table%';

provides you the information about the impact of the MySQL Variables mentioned above.

Literature

1. Server Status Variables

The following command gives you a rough idea about the number of used InnoDB tables:

shell> lsof -p  | grep -c '\.ibd'

Other MySQL configuration variables should be considered and sized accordingly as well but for those just the normal rules apply.

If you want to get some more information about your MySQL database configuration our MySQL Database Health Check bot is happy to help you out.

You can typically gather those information from your shared hoster as well and see if he has configured your MySQL instance correctly! :)

Linux tweaks

If you are asking around it is common sense that Linux should not have a problem with up to 1M file handles.

For the file system the general opinion is that XFS (and on Solaris ZFS) running on a RAID-10 disk gives the best results.

Versions, Architecture and Data Distribution

Versions

From the database point of view MySQL v5.0 does not scale very well on many cores (up to 8?). If you have servers with more than 8 cores you have 2 possibilities: Upgrade to MySQL 5.1 or even 5.5 or you setup several MySQL instances per server.

With MySQL 5.5 Oracle has decided to make InnoDB the default Storage Engine. This especially interesting for the Shared Hoster because InnoDB has different behavior than MyISAM and thus they should gain experience with the new release before switching all there thousands of users to the new version.

InnoDB as the Default MySQL Storage Engine

I got some information from somebody who was testing MySQL extensively in the range of 10k - 100k InnoDB tables. This person told me, that InnoDB is fine up to about 10k tables and above it becomes slow.

If Oracle works on this specific problem is not known yet but at least it looks like they are aware: InnoDB now limits the memory used to hold table information when many tables are opened.

Literature

1. What Is New in MySQL 5.6, MySQL 5.6: Data dictionary LRU

Architecture

Virtualization in shared hosting setups is typically not used. The general opinion is, that virtualization has more disadvantages than advantages (I/O, Overhead, etc.).

Further it looks like MySQL Proxy is used to redirect the traffic of the applications to their specific back-end.

Up to version v0.7 MySQL Proxy is single-threaded. What in certain scenarios was a missive bottleneck. From version v0.8 MySQL Proxy is multi-threaded and should NOT be a bottleneck anymore.

Literature

1. MySQL Proxy: 0.8.0 released

Data Distribution

The following data distribution for MySQL is feasible:

• 1000 to >5000 Schemata per MySQL instance (mysqld). More than 5000 Schemata was considered to be too many because the systems became sluggish.
• 100k to 500k tables per instance (between 1% and 10% InnoDB tables)
• 15 to 25 instances per server (mostly 5.0)

An other discussion we had was, if it makes sense to consolidate everything to one big machine (HA setup) or to several different severs.

The advantage of having one big machine is to have less maintenance work and more resources free for other task.

The advantage of many MySQL installations/servers is, that in case of a problem only ¹/_n^th of your customers experiences a downtime and if one customer causes some troubles it affects only his instance but not all others. You can relatively easy move heavy users from a busy machine to a less busy machine and you can try out new releases first on their impacts just with few customers and not with all of them...

I am very interested to hear about your experience with MySQL shared hosting and your findings and opinions.

Thanks a lot to all people who gave me their advices and information for this blog article: our customers and friends from the MySQL community.

Literature

Other articles to the same subject:

1. The problems with multi-tenant MySQL
2. Multi-tenancy Drizzle