MySQL Crash

emiliomedina21

Verified User
Joined
Jul 23, 2019
Messages
18
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:
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?
 
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:
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.
 
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
 
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
 
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.
 
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 :)
 
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.
 
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:
Back
Top