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












2















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





























    2















    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



























      2












      2








      2








      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 '18 at 12:03







      Rui F Ribeiro

















      asked Jan 28 '16 at 15:00









      Rui F RibeiroRui F Ribeiro

      41.4k1481140




      41.4k1481140






















          1 Answer
          1






          active

          oldest

          votes


















          2














          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 files in 5.5, in 5.6 seems to be at least 2048 files.



          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',
            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%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















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            2














            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 files in 5.5, in 5.6 seems to be at least 2048 files.



            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






























              2














              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 files in 5.5, in 5.6 seems to be at least 2048 files.



              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




























                2












                2








                2







                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 files in 5.5, in 5.6 seems to be at least 2048 files.



                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 files in 5.5, in 5.6 seems to be at least 2048 files.



                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 22 at 17:25

























                answered Feb 10 '16 at 8:39









                Rui F RibeiroRui F Ribeiro

                41.4k1481140




                41.4k1481140






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Unix & Linux 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%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















                    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

                    How to make a Squid Proxy server?

                    Is this a new Fibonacci Identity?

                    19世紀