MariaDB server with 80K-100K DataBases












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.










share|improve this question
















bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 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
    Sep 4 '15 at 10:31






  • 2





    85K databases means 85K subdirectories, most filesystems are not optimized for such things.

    – jkavalik
    Sep 4 '15 at 10:50






  • 1





    @jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.

    – Prem
    Sep 4 '15 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
    Sep 4 '15 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
    Sep 4 '15 at 11:19
















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.










share|improve this question
















bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 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
    Sep 4 '15 at 10:31






  • 2





    85K databases means 85K subdirectories, most filesystems are not optimized for such things.

    – jkavalik
    Sep 4 '15 at 10:50






  • 1





    @jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.

    – Prem
    Sep 4 '15 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
    Sep 4 '15 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
    Sep 4 '15 at 11:19














2












2








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.










share|improve this 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.







mysql performance optimization mariadb virtualisation






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 4 '15 at 11:05







Prem

















asked Sep 4 '15 at 10:23









PremPrem

1186




1186





bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.










  • 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
    Sep 4 '15 at 10:31






  • 2





    85K databases means 85K subdirectories, most filesystems are not optimized for such things.

    – jkavalik
    Sep 4 '15 at 10:50






  • 1





    @jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.

    – Prem
    Sep 4 '15 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
    Sep 4 '15 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
    Sep 4 '15 at 11:19














  • 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
    Sep 4 '15 at 10:31






  • 2





    85K databases means 85K subdirectories, most filesystems are not optimized for such things.

    – jkavalik
    Sep 4 '15 at 10:50






  • 1





    @jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.

    – Prem
    Sep 4 '15 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
    Sep 4 '15 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
    Sep 4 '15 at 11:19








2




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
Sep 4 '15 at 10:31





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
Sep 4 '15 at 10:31




2




2





85K databases means 85K subdirectories, most filesystems are not optimized for such things.

– jkavalik
Sep 4 '15 at 10:50





85K databases means 85K subdirectories, most filesystems are not optimized for such things.

– jkavalik
Sep 4 '15 at 10:50




1




1





@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.

– Prem
Sep 4 '15 at 10:57





@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.

– Prem
Sep 4 '15 at 10:57




1




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
Sep 4 '15 at 11:13





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
Sep 4 '15 at 11:13




1




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
Sep 4 '15 at 11:19





@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
Sep 4 '15 at 11:19










2 Answers
2






active

oldest

votes


















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






share|improve this answer































    0














    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, the 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.






    share|improve this answer























      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "182"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f114195%2fmariadb-server-with-80k-100k-databases%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









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






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






        share|improve this answer


























          0












          0








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






          share|improve this answer













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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Sep 4 '15 at 16:24









          greenlitmysqlgreenlitmysql

          32613




          32613

























              0














              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, the 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.






              share|improve this answer




























                0














                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, the 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.






                share|improve this answer


























                  0












                  0








                  0







                  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, the 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.






                  share|improve this 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, the 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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Sep 4 '15 at 16:43









                  Rick JamesRick James

                  42.2k22258




                  42.2k22258






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f114195%2fmariadb-server-with-80k-100k-databases%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh