MariaDB server with 80K-100K DataBases

Question

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.

• jynus: What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
• jkavalik: 85K databases means 85K subdirectories, most filesystems are not optimized for such things.
• Prem: @jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
• jynus: Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
• Prem: @jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!

Answer 1

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.

Answer 2

If i understand you can create up to 85,000 databases? Hmm, I would agree with one of the post, not the best approach. The only issue i can think of if "information_schema" is not able to keep up with the quantity of databases. What's your key_buffer_size? What's your open file limit? Table_cache?

Honestly, you'd be better off approaching this issue differently. Instead of creating that many databases with few tables and few rows, it would be better merge all these database in few databases / tables. If you must separate the schema, (DBs), i would spawn multiple Mysql server and have a round robin system to create the db on each of them.

(MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.)