Results 1 to 18 of 18

Thread: MySQL Crash

  1. #1
    Join Date
    Jul 2019
    Posts
    18

    Question MySQL Crash

    Hi everyone,
    In last days mysqld crashes all nights, in error log i found these errors:

    - Warning: a long semaphore wait
    - Error in accept: Too many open files

    The open_files_limit var is set to 50000 in /etc/my.cnf. Anyone knows how to solve this?
    Thank you.
    Last edited by emiliomedina21; 09-25-2019 at 07:32 AM.

  2. #2
    Join Date
    May 2014
    Location
    Netherlands Germany
    Posts
    583
    Quote Originally Posted by emiliomedina21 View Post
    Hi everyone,
    In last days mysqld crashes all nights, in error log i found these errors:

    - Warning: a long semaphore wait
    - Error in accept: Too many open files

    The open_files_limit var is set to 50000 in /etc/my.cnf. Anyone knows how to solve this?
    Thank you.

    Which OS which versions DA, maridab, mysql and so more things are needed for getting support here i guess.

    Before / after update?
    DUTCH GERMAN, GERMAN DUTCH

  3. #3
    Join Date
    Jul 2019
    Posts
    18
    Hi, thank you for your response. Versions are:

    Centos 7
    MariaDB 10.3.16
    DirectAdmin 1.59.0

    I think the service are not taking the value set in /etc/my.cnf because in the proc limits i got this:

    cat /proc/76931/limits
    Limit Soft Limit Hard Limit Units
    Max cpu time unlimited unlimited seconds
    Max file size unlimited unlimited bytes
    Max data size unlimited unlimited bytes
    Max stack size 8388608 unlimited bytes
    Max core file size 0 unlimited bytes
    Max resident set unlimited unlimited bytes
    Max processes 256185 256185 processes
    Max open files 16364 16364 files
    Max locked memory 65536 65536 bytes
    Max address space unlimited unlimited bytes
    Max file locks unlimited unlimited locks
    Max pending signals 256185 256185 signals
    Max msgqueue size 819200 819200 bytes
    Max nice priority 0 0
    Max realtime priority 0 0
    Max realtime timeout unlimited unlimited us
    Last edited by emiliomedina21; 09-25-2019 at 08:56 AM.

  4. #4
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    13,800
    Hello,

    Did you add open_files_limit under [mysqld] section?
    Regards, Alex G.

    - Get the best commercial DirectAdmin support and hire me on poralix.com
    - Follow and like @Poralix on Facebook

  5. #5
    Join Date
    Jul 2019
    Posts
    18
    Quote Originally Posted by zEitEr View Post
    Hello,

    Did you add open_files_limit under [mysqld] section?
    Yes, it is added.

  6. #6
    Join Date
    May 2008
    Location
    Bulgaria
    Posts
    981
    It is not only the MariaDB/MySQL config which limits the number of opened files - it is also the OS potentially limiting them and its limit is stronger (the my.cnf can eventually further limit the OS restriction but not loosen it). So if you have for example 5000 OS limit and 200000 my.cnf limit, the effectual limit for MariaDB/MySQL will be 5000.

    In FreeBSD the max number of opened files is controlled via /etc/sysctl.conf:

    Code:
    # sysctl kern.maxfiles
    kern.maxfiles: 65536
    I am unsure which will be the one for your OS.

  7. #7
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    13,800
    How does your /etc/my.cnf look like?
    Regards, Alex G.

    - Get the best commercial DirectAdmin support and hire me on poralix.com
    - Follow and like @Poralix on Facebook

  8. #8
    Join Date
    Jul 2019
    Posts
    18
    Quote Originally Posted by zEitEr View Post
    How does your /etc/my.cnf look like?
    [mysqld]
    local-infile=0
    innodb_file_per_table=1
    performance-schema=0
    max_connections=500
    max_allowed_packet=128M
    tmp_table_size=32M
    max_heap_table_size=32M
    open_files_limit=50000
    innodb_log_buffer_size=32M

  9. #9
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    13,800
    and file /etc/my.cnf is readable for mysql server?
    Regards, Alex G.

    - Get the best commercial DirectAdmin support and hire me on poralix.com
    - Follow and like @Poralix on Facebook

  10. #10
    Join Date
    Jul 2019
    Posts
    18
    Quote Originally Posted by zEitEr View Post
    and file /etc/my.cnf is readable for mysql server?
    Oh! It does not:
    -rw-r--r-- 1 root root 207 Sep 18 21:29 my.cnf

  11. #11
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    13,800
    The permissions -rw-r--r-- 1 root root are sufficient.
    Regards, Alex G.

    - Get the best commercial DirectAdmin support and hire me on poralix.com
    - Follow and like @Poralix on Facebook

  12. #12
    Join Date
    Jul 2019
    Posts
    18
    Should I have to run chown root:mysql /etc/my.cnf ?
    Thank you.

  13. #13
    Join Date
    Jul 2019
    Posts
    18
    Quote Originally Posted by zEitEr View Post
    The permissions -rw-r--r-- 1 root root are sufficient.
    Is there any way to fix this?

  14. #14
    Join Date
    Apr 2009
    Posts
    2,439
    MariaDB does not make use of open_files_limit in my.cnf. Please post the output from these commands:

    Code:
    ulimit -a
    
    ulimit -Hn
    
    ulimit -Sn

  15. #15
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    13,800
    OK, I see. Make sure you have

    Code:
    # Number of files limit. previously [mysqld_safe] open-file-limit
    LimitNOFILE=655350
    in /etc/systemd/system/mysqld.service or /etc/systemd/system/mysql.service then.

    Restart SQL server after it.
    Regards, Alex G.

    - Get the best commercial DirectAdmin support and hire me on poralix.com
    - Follow and like @Poralix on Facebook

  16. #16
    Join Date
    Nov 2015
    Posts
    26
    In my config file I have dashes (-) between the words instead of underscores.. It wouldn't work otherwise. So maybe you can try open-files-limit ? :P Restart MariaDB afterwards of course

  17. #17
    Join Date
    Jul 2019
    Posts
    18
    Quote Originally Posted by zEitEr View Post
    OK, I see. Make sure you have

    Code:
    # Number of files limit. previously [mysqld_safe] open-file-limit
    LimitNOFILE=655350
    in /etc/systemd/system/mysqld.service or /etc/systemd/system/mysql.service then.

    Restart SQL server after it.
    I did it and its working now. Thank you very much to everyone.

  18. #18
    Join Date
    Feb 2005
    Location
    Hong Kong
    Posts
    137
    One of our server encountered the same after updating CentOS from 7.6 to 7.7, and MariaDB from 10.1.37 to 10.1.41

    I checked /etc/systemd/system/mysqld.service , it already contains "LimitNOFILE=655350" under [Service] section out of the box.

    However, sometimes the MariaDB server crashed (especially high I/O load, e.g. backup period)

    We are running database on SSD partition already. The I/O wait shall be small.

    Code:
    Oct 24 22:36:10 xxxx mysqld: InnoDB: Warning: a long semaphore wait:
    Oct 24 22:36:10 xxxx mysqld: --Thread 139658478462720 has waited at dict0dict.cc line 984 for 241.00 seconds the semaphore:
    Oct 24 22:36:10 xxxx mysqld: Mutex at 0x7f05b0c112e8 '&dict_sys->mutex', lock var 1
    Oct 24 22:36:10 xxxx mysqld: Last time reserved by thread 139658495248128 in file not yet reserved line 0, waiters flag 1
    Oct 24 22:36:10 xxxx mysqld: InnoDB: Warning: a long semaphore wait:
    Oct 24 22:36:10 xxxx mysqld: --Thread 139662426380032 has waited at dict0dict.cc line 1138 for 241.00 seconds the semaphore:
    Oct 24 22:36:10 xxxx mysqld: Mutex at 0x7f05b0c112e8 '&dict_sys->mutex', lock var 1
    Oct 24 22:36:10 xxxx mysqld: Last time reserved by thread 139658495248128 in file not yet reserved line 0, waiters flag 1
    Then, the MariaDB database crashed and caused a few MyISAM tables corrupted (need mysqlcheck --autorepair)

    ----

    When the server running normally today (not crashed one <-- I cannot get the crashed one, it is auto-restarted)
    I tried to use lsof, and found -

    Code:
    1101473 total opened files, whereas
    868028 is httpd
    209664 is mysqld
    remaining are others - dovecot, ...
    ---

    In the past (2 years ago), we already set the following inside /etc/sysctl.d/xxx.conf , and reboot, to increase semaphore limit.
    Code:
    kernel.sem=250 256000 32 1024
    ----

    ulimit:
    Code:
    [root@xxxx ~]# ulimit -a
    core file size          (blocks, -c) 0
    data seg size           (kbytes, -d) unlimited
    scheduling priority             (-e) 0
    file size               (blocks, -f) unlimited
    pending signals                 (-i) 127697
    max locked memory       (kbytes, -l) 64
    max memory size         (kbytes, -m) unlimited
    open files                      (-n) 1024
    pipe size            (512 bytes, -p) 8
    POSIX message queues     (bytes, -q) 819200
    real-time priority              (-r) 0
    stack size              (kbytes, -s) 8192
    cpu time               (seconds, -t) unlimited
    max user processes              (-u) 127697
    virtual memory          (kbytes, -v) unlimited
    file locks                      (-x) unlimited
    [root@xxxx ~]#
    [root@xxxx ~]#
    [root@xxxx ~]# ulimit -Hn
    4096
    [root@xxxx ~]#
    [root@xxxx ~]#
    [root@xxxx ~]# ulimit -Sn
    1024
    [root@xxxx ~]#
    [root@xxxx ~]#
    [root@xxxx ~]#
    ----

    Also, for unknown reason, after MariaDB update to 10.1.41 , for any mysqld systemctl operation (start/stop/restart/status), it showed -

    Code:
    Warning: mysqld.service changed on disk. Run 'systemctl daemon-reload' to reload units.
    Even I reboot the server, but the same.

    These 2 problems occurred on 1 server. They do not occur in other servers (with the same build, same OS, same MariaDB version)

    Anything I can do? Please kindly help/advise.

    Thank you very much.
    Last edited by ccto; 10-25-2019 at 07:27 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •