limit of open files: Debian Jessies´ MySQL vs Community Oracle MySQL

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
2
down vote

favorite












I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6.



I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables.



Whilst for two or three years, I had never noticeable problems with the Debian version, the services provided by this server failed a few hours after this upgrade.



Debugging the problem, I found out it was due to the limit of the open files per process.



I solved the problem raising the limits of the open files per process. I added to /etc/sysctl.conf:



fs.file-max=100000


and to /etc/security/limits.conf



* - nofile 100000


Afterwards I restarted MySQL, and that alone restored the normality of the services.
For a document talking about this. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit



From my own server:



mysql> show global variables like 'open%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| open_files_limit | 100000 |
+------------------+--------+
1 row in set (0.00 sec)

mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
| 15000 |
+--------------------+
1 row in set (0.00 sec)


After solving the problem, I set out to find if there is any difference in the scripts or code between Debian´s and Oracle´s versions.



In my short analysis, I learnt they both have the open_files_limitin my.cnf and the option "--open-files-limit" in the script /usr/bin/mysqld_safe, which actually led me to find out the document/linked I mentioned previously.



However, I did not find out anything more in the code or scripts that led me to justify the difference in behaviour concerning open files limits.



I can see also that in another server with MySQL 5.5 from Jessie the table_open_cache is actually lower.



mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
| 512 |
+--------------------+
1 row in set (0.00 sec)


Whilst the default table_open_cache increasing could partly explain the problem, I am not sure if it fully justifies it.



Could someone shed some light on this?










share|improve this question



























    up vote
    2
    down vote

    favorite












    I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6.



    I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables.



    Whilst for two or three years, I had never noticeable problems with the Debian version, the services provided by this server failed a few hours after this upgrade.



    Debugging the problem, I found out it was due to the limit of the open files per process.



    I solved the problem raising the limits of the open files per process. I added to /etc/sysctl.conf:



    fs.file-max=100000


    and to /etc/security/limits.conf



    * - nofile 100000


    Afterwards I restarted MySQL, and that alone restored the normality of the services.
    For a document talking about this. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit



    From my own server:



    mysql> show global variables like 'open%';
    +------------------+--------+
    | Variable_name | Value |
    +------------------+--------+
    | open_files_limit | 100000 |
    +------------------+--------+
    1 row in set (0.00 sec)

    mysql> select @@table_open_cache;
    +--------------------+
    | @@table_open_cache |
    +--------------------+
    | 15000 |
    +--------------------+
    1 row in set (0.00 sec)


    After solving the problem, I set out to find if there is any difference in the scripts or code between Debian´s and Oracle´s versions.



    In my short analysis, I learnt they both have the open_files_limitin my.cnf and the option "--open-files-limit" in the script /usr/bin/mysqld_safe, which actually led me to find out the document/linked I mentioned previously.



    However, I did not find out anything more in the code or scripts that led me to justify the difference in behaviour concerning open files limits.



    I can see also that in another server with MySQL 5.5 from Jessie the table_open_cache is actually lower.



    mysql> select @@table_open_cache;
    +--------------------+
    | @@table_open_cache |
    +--------------------+
    | 512 |
    +--------------------+
    1 row in set (0.00 sec)


    Whilst the default table_open_cache increasing could partly explain the problem, I am not sure if it fully justifies it.



    Could someone shed some light on this?










    share|improve this question

























      up vote
      2
      down vote

      favorite









      up vote
      2
      down vote

      favorite











      I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6.



      I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables.



      Whilst for two or three years, I had never noticeable problems with the Debian version, the services provided by this server failed a few hours after this upgrade.



      Debugging the problem, I found out it was due to the limit of the open files per process.



      I solved the problem raising the limits of the open files per process. I added to /etc/sysctl.conf:



      fs.file-max=100000


      and to /etc/security/limits.conf



      * - nofile 100000


      Afterwards I restarted MySQL, and that alone restored the normality of the services.
      For a document talking about this. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit



      From my own server:



      mysql> show global variables like 'open%';
      +------------------+--------+
      | Variable_name | Value |
      +------------------+--------+
      | open_files_limit | 100000 |
      +------------------+--------+
      1 row in set (0.00 sec)

      mysql> select @@table_open_cache;
      +--------------------+
      | @@table_open_cache |
      +--------------------+
      | 15000 |
      +--------------------+
      1 row in set (0.00 sec)


      After solving the problem, I set out to find if there is any difference in the scripts or code between Debian´s and Oracle´s versions.



      In my short analysis, I learnt they both have the open_files_limitin my.cnf and the option "--open-files-limit" in the script /usr/bin/mysqld_safe, which actually led me to find out the document/linked I mentioned previously.



      However, I did not find out anything more in the code or scripts that led me to justify the difference in behaviour concerning open files limits.



      I can see also that in another server with MySQL 5.5 from Jessie the table_open_cache is actually lower.



      mysql> select @@table_open_cache;
      +--------------------+
      | @@table_open_cache |
      +--------------------+
      | 512 |
      +--------------------+
      1 row in set (0.00 sec)


      Whilst the default table_open_cache increasing could partly explain the problem, I am not sure if it fully justifies it.



      Could someone shed some light on this?










      share|improve this question















      I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6.



      I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables.



      Whilst for two or three years, I had never noticeable problems with the Debian version, the services provided by this server failed a few hours after this upgrade.



      Debugging the problem, I found out it was due to the limit of the open files per process.



      I solved the problem raising the limits of the open files per process. I added to /etc/sysctl.conf:



      fs.file-max=100000


      and to /etc/security/limits.conf



      * - nofile 100000


      Afterwards I restarted MySQL, and that alone restored the normality of the services.
      For a document talking about this. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit



      From my own server:



      mysql> show global variables like 'open%';
      +------------------+--------+
      | Variable_name | Value |
      +------------------+--------+
      | open_files_limit | 100000 |
      +------------------+--------+
      1 row in set (0.00 sec)

      mysql> select @@table_open_cache;
      +--------------------+
      | @@table_open_cache |
      +--------------------+
      | 15000 |
      +--------------------+
      1 row in set (0.00 sec)


      After solving the problem, I set out to find if there is any difference in the scripts or code between Debian´s and Oracle´s versions.



      In my short analysis, I learnt they both have the open_files_limitin my.cnf and the option "--open-files-limit" in the script /usr/bin/mysqld_safe, which actually led me to find out the document/linked I mentioned previously.



      However, I did not find out anything more in the code or scripts that led me to justify the difference in behaviour concerning open files limits.



      I can see also that in another server with MySQL 5.5 from Jessie the table_open_cache is actually lower.



      mysql> select @@table_open_cache;
      +--------------------+
      | @@table_open_cache |
      +--------------------+
      | 512 |
      +--------------------+
      1 row in set (0.00 sec)


      Whilst the default table_open_cache increasing could partly explain the problem, I am not sure if it fully justifies it.



      Could someone shed some light on this?







      debian files mysql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Sep 13 at 12:03

























      asked Jan 28 '16 at 15:00









      Rui F Ribeiro

      36.8k1273117




      36.8k1273117




















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          2
          down vote



          accepted










          This server is a legacy server with light use, hence so many DBs/tables.



          In short, what it happened is that the upgrade from the 5.5 MySQL Debian Jessie official package to the 5.6 MySQL Oracle´s version raises the limit of the open files for table caching.



          Whilst the open table cache is by default 512 in 5.5, in 5.6 seems to be at least 2048.



          Evidently, the problem only manifests itself when you have more than 1000 and so tables already used/opened as the limit per process of files open by non-root users is 1024 by default.



          The cache seems to be adjusting itself to around 10% of the open files allowed for the user after raising the limits, will investigate it further.



          As final comments, either rising the system limits or adjusting the variable open_files_limit in my.cnf or as a parameter for MySQL solves the problem.



          An alternative to raising the system limits in /etc/security/limits.conf, would be then adding in the mysqld section of /etc/mysql/my.cnf



          open_files_limit = 100000


          It should be duly noted that raising the open file limits is recommended for database, web or email servers that have a lot of tables/vhosts/email users.



          The DB responsiveness also seems faster, however I have not conducted any comparative performance tests between the two versions in my particular case.






          share|improve this answer






















            Your Answer







            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "106"
            ;
            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',
            convertImagesToLinks: false,
            noModals: false,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













             

            draft saved


            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f258286%2flimit-of-open-files-debian-jessies%25c2%25b4-mysql-vs-community-oracle-mysql%23new-answer', 'question_page');

            );

            Post as a guest






























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            2
            down vote



            accepted










            This server is a legacy server with light use, hence so many DBs/tables.



            In short, what it happened is that the upgrade from the 5.5 MySQL Debian Jessie official package to the 5.6 MySQL Oracle´s version raises the limit of the open files for table caching.



            Whilst the open table cache is by default 512 in 5.5, in 5.6 seems to be at least 2048.



            Evidently, the problem only manifests itself when you have more than 1000 and so tables already used/opened as the limit per process of files open by non-root users is 1024 by default.



            The cache seems to be adjusting itself to around 10% of the open files allowed for the user after raising the limits, will investigate it further.



            As final comments, either rising the system limits or adjusting the variable open_files_limit in my.cnf or as a parameter for MySQL solves the problem.



            An alternative to raising the system limits in /etc/security/limits.conf, would be then adding in the mysqld section of /etc/mysql/my.cnf



            open_files_limit = 100000


            It should be duly noted that raising the open file limits is recommended for database, web or email servers that have a lot of tables/vhosts/email users.



            The DB responsiveness also seems faster, however I have not conducted any comparative performance tests between the two versions in my particular case.






            share|improve this answer


























              up vote
              2
              down vote



              accepted










              This server is a legacy server with light use, hence so many DBs/tables.



              In short, what it happened is that the upgrade from the 5.5 MySQL Debian Jessie official package to the 5.6 MySQL Oracle´s version raises the limit of the open files for table caching.



              Whilst the open table cache is by default 512 in 5.5, in 5.6 seems to be at least 2048.



              Evidently, the problem only manifests itself when you have more than 1000 and so tables already used/opened as the limit per process of files open by non-root users is 1024 by default.



              The cache seems to be adjusting itself to around 10% of the open files allowed for the user after raising the limits, will investigate it further.



              As final comments, either rising the system limits or adjusting the variable open_files_limit in my.cnf or as a parameter for MySQL solves the problem.



              An alternative to raising the system limits in /etc/security/limits.conf, would be then adding in the mysqld section of /etc/mysql/my.cnf



              open_files_limit = 100000


              It should be duly noted that raising the open file limits is recommended for database, web or email servers that have a lot of tables/vhosts/email users.



              The DB responsiveness also seems faster, however I have not conducted any comparative performance tests between the two versions in my particular case.






              share|improve this answer
























                up vote
                2
                down vote



                accepted







                up vote
                2
                down vote



                accepted






                This server is a legacy server with light use, hence so many DBs/tables.



                In short, what it happened is that the upgrade from the 5.5 MySQL Debian Jessie official package to the 5.6 MySQL Oracle´s version raises the limit of the open files for table caching.



                Whilst the open table cache is by default 512 in 5.5, in 5.6 seems to be at least 2048.



                Evidently, the problem only manifests itself when you have more than 1000 and so tables already used/opened as the limit per process of files open by non-root users is 1024 by default.



                The cache seems to be adjusting itself to around 10% of the open files allowed for the user after raising the limits, will investigate it further.



                As final comments, either rising the system limits or adjusting the variable open_files_limit in my.cnf or as a parameter for MySQL solves the problem.



                An alternative to raising the system limits in /etc/security/limits.conf, would be then adding in the mysqld section of /etc/mysql/my.cnf



                open_files_limit = 100000


                It should be duly noted that raising the open file limits is recommended for database, web or email servers that have a lot of tables/vhosts/email users.



                The DB responsiveness also seems faster, however I have not conducted any comparative performance tests between the two versions in my particular case.






                share|improve this answer














                This server is a legacy server with light use, hence so many DBs/tables.



                In short, what it happened is that the upgrade from the 5.5 MySQL Debian Jessie official package to the 5.6 MySQL Oracle´s version raises the limit of the open files for table caching.



                Whilst the open table cache is by default 512 in 5.5, in 5.6 seems to be at least 2048.



                Evidently, the problem only manifests itself when you have more than 1000 and so tables already used/opened as the limit per process of files open by non-root users is 1024 by default.



                The cache seems to be adjusting itself to around 10% of the open files allowed for the user after raising the limits, will investigate it further.



                As final comments, either rising the system limits or adjusting the variable open_files_limit in my.cnf or as a parameter for MySQL solves the problem.



                An alternative to raising the system limits in /etc/security/limits.conf, would be then adding in the mysqld section of /etc/mysql/my.cnf



                open_files_limit = 100000


                It should be duly noted that raising the open file limits is recommended for database, web or email servers that have a lot of tables/vhosts/email users.



                The DB responsiveness also seems faster, however I have not conducted any comparative performance tests between the two versions in my particular case.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Feb 5 at 22:57

























                answered Feb 10 '16 at 8:39









                Rui F Ribeiro

                36.8k1273117




                36.8k1273117



























                     

                    draft saved


                    draft discarded















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f258286%2flimit-of-open-files-debian-jessies%25c2%25b4-mysql-vs-community-oracle-mysql%23new-answer', 'question_page');

                    );

                    Post as a guest













































































                    Popular posts from this blog

                    How to check contact read email or not when send email to Individual?

                    Displaying single band from multi-band raster using QGIS

                    How many registers does an x86_64 CPU actually have?