You are here

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 1/nth 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

Comments

One of challenges with shared hosting is monitoring and controlling single user/db usage. For this I think is useful Percona Xtra DB with detailed per-user/index/host/table statistics and counters. P.
Paolo Lunazzicomment

Percona Server has an option to limit amount of memory consumed by dictionary entries. This might be similar to what is in 5.6 from Oracle; I have not looked at the 5.6 code yet. We created this feature specifically for hosting providers :-) There are a number of other improvements for shared hosting providers, too, such as "don't crash the whole server when a single table gets a corruption" or "allow import/export of individual tables to another server".
Baron Schwartzcomment

MySQL Proxy is not full multithreaded, the network layer is multithreaded, but not the LUA layer, what does it mean? As soon as you use LUA, you are again using just a single thread. We are planning on converting the LUA layer to multithread, but it is not there yet. Regards, Diego
Diegocomment

I had read your article and it does lighten my mind up. However, all my efforts on surfing for information on how to set up the shared server wasn't enough. I can't find a clear picture on how to set it up, even i just want to connect my virtual machine with database in my own pc. Hope can get some help.
BBluecomment

Hello BBlue, Can you please elaborate a bit more what the problem is? It is not clear to me where you stuck. Regards, Oli
olicomment

MySQL was connected with localhost by default, am I right? I would like to make it not only localhost, so that my other computer can get access or connected to the database on my PC. I read through some website and they suggest to change the bind address in my.cnf. Unfortunately, I can't locate the file and failed to find the line to change.
BBluecomment

This depends a bit on version and distribution. Debian/Ubuntu for example sets bind-address to localhost/127.0.0.1 afaik. Look for my.cnf in /etc/ or /etc/mysqld. Other locations you can find with:
mysqld --help --verbose | grep my.cnf
olicomment

We have seen a customer running a MySQL instance with about 1 Mio InnoDB tables. He had some problems (hick-ups) but basically it worked...
olicomment

In MySQL 8 the MySQL Data Dictionary was placed inside the InnoDB Storage Engine. This has some impact on multi-tenant applications:

Operation MySQL 8.0 MariaDB 10.5
Create 10 schemata x 200 tables 140s 48s
Drop 10 schemata 27s 7.5s
mysqldump --databases of 10 schemata 17s 2.5s
Restore of dump of 10 schemata 210s 96s

Parameters used:

Parameter MySQL 8.0 MariaDB 10.5
sync_frm n.a. ON
Binary Log ON ON
sync_binlog 0 0
innodb_flush_log_at_trx_commit 0 2
table_definition_cache 2000 1400
table_open_cache 4000 2000
table_open_cache_instances 16 16
tablespace_definition_cache 256 n.a.

Shinguzcomment