Mysql – MariaDB server with 80K-100K DataBases

mariadbMySQLoptimizationperformancevirtualisation

BACKGROUND :

We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.

We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.

Problem :

When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.

Questions :

Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?

Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.

Best Answer

As already stated, the filesystem is the problem. However, there may be some tuning that would help.

If innodb_file_per_table has been ON, then there are 2*12 files in each database directory. Turning that OFF would lead to fewer files (but not fewer directories). This might help some.

SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';

There is some info that can be gathered from those outputs.

Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;. I will run a number of checks to see if the table_open_cache is too small, and other things.

Related Question