2

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.

Improve this question
8
  • 2
    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)?
    – jynus
    Commented Sep 4, 2015 at 10:31
  • 2
    85K databases means 85K subdirectories, most filesystems are not optimized for such things.
    – jkavalik
    Commented Sep 4, 2015 at 10:50
  • 1
    @jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
    – Prem
    Commented Sep 4, 2015 at 10:57
  • 1
    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.
    – jynus
    Commented Sep 4, 2015 at 11:13
  • 1
    @jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
    – Prem
    Commented Sep 4, 2015 at 11:19

2 Answers 2

Reset to default
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.

Improve this answer
0

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.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.