FreeBSD 14.1 & MySQL 8.0.39 Max Open Files Limit Warning Error

rotaecho

rotaecho

Hello, I'm more familiar with Solaris (2.51-11) & Linux (Redhat since 95) web systems. Last time I touched FreeBSD was over a decade ago.

I have a FreeBSD 14.1 box running MySQL 8.0.39 it's on a small VPS (2GB) for testing. I'm migrating a Rocky 8 Linux box to FreeBSD 14.1.

For the system values I get:

Code: 
# sysctl kern.maxfiles kern.maxfilesperproc kern.openfiles
kern.maxfiles: 64175
kern.maxfilesperproc: 57753
kern.openfiles: 141

Which look good. However, when I start the MySQL service I get the following Warnings with values requested above

Code: 
2024-10-30T14:27:59.667683Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 32768 (request: 32786)

2024-10-30T14:27:59.667687Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 16375 (requested 16384)

I thought it interesting that MySQL is requesting 32,786 whereas 32,768 is a known common numerical value.

I know it's only a warning, but I'd like to know what's going on here and why it can't request the full amount even at the requested limit.

My MySQL my.cnf could use some tweaking still.

This setup though is running on a similar 2gb Linux system without any file limit errors.

Thanks for any insight I maybe missing here!

-William

Code: 
[mysqld]
performance_schema    = 0
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
replica-load-tmpdir             = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
open_files_limit                = 32768
table_open_cache                = 16384
log-bin                         = mysql-bin
log-output                      = TABLE
relay-log-recovery              = 1
slow_query_log                  = OFF
long_query_time    = 0
log_slow_admin_statements    = OFF
log_slow_replica_statements    = OFF
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
max_binlog_size    = 100M
binlog_expire_logs_seconds      = 2592000
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 32M
max_connections    = 8
long_query_time                 = 0.5
innodb_buffer_pool_size         = 50M
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
#innodb_redo_log_capacity        = 512M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
 
I can't answer your question directly but:

(a) there's a mysql user account - would the per-user limits of that account come into play?
(b) I've been caught out before by MySQL slurping in multiple my.cnf files, so it is worth double-checking you've only got one my.cnf being pulled in
 
It's using the proper my.cnf and I can't find any other's it'd call.

It does run as a user, with nologin as I'd expect. I'll dig more into the issue this week.

Code: 
root@www:~ # find / -name my.cnf -print
/usr/local/etc/mysql/my.cnf
root@www:~ # ps -aux|grep mysql
mysql  908   0.0  0.1   13376   2276  -  Is   07:08      0:00.06 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/usr/local/etc/mysql/my.cnf --b
mysql 2111   0.0 20.8 1196792 427412  -  I    07:08      7:48.84 /usr/local/libexec/mysqld --defaults-extra-file=/usr/local/etc/mysql/my.cnf --basedir=/u
root  6021   0.0  0.0     432    264  0  R+   05:03      0:00.00 grep mysql
root@www:~ # id mysql
uid=88(mysql) gid=88(mysql) groups=88(mysql)
root@www:~ # finger mysql
Login: mysql              Name: MySQL Daemon
Directory: /var/db/mysql                Shell: /usr/sbin/nologin
No Mail.
No Plan.
 
cracauer@ that won't work, MySQL installs as /usr/sbin/nologin

I need to dig a bit into what FreeBSD commands give me more insight. Off to the docs for some research.
 
I'm pretty sure I'm getting the proper settings from the OS and the FreeBSD manual pages for limit shows proper too. Kinda stumped now why MySQL is reporting that warning.

Code: 
# sysctl -a | grep maxfiles

kern.maxfiles: 64175
kern.maxfilesperproc: 57753

The limits for mysql user are adequate

Code: 
# limits -U mysql

Resource limits for class default:
  cputime              infinity secs
  filesize             infinity kB
  datasize             infinity kB
  stacksize            infinity kB
  coredumpsize         infinity kB
  memoryuse            infinity kB
  memorylocked               64 kB
  maxprocesses         infinity
  openfiles            infinity
  sbsize               infinity bytes
  vmemoryuse           infinity kB
  pseudo-terminals     infinity
  swapuse              infinity kB
  kqueues              infinity
  umtxp                infinity

Yet, MySQL doesn't appear to think so:

Code: 
2024-11-01T01:56:38.912099Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 32768 (request: 32786)
2024-11-01T01:56:38.912103Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 16375 (requested 16384)

And there's only one MySQL config being called

Code: 
root@/var/db/mysql # find / -name my.cnf -print
/usr/local/etc/mysql/my.cnf

 # ps -aux|grep mysql
mysql 36739   0.0 12.6 890756 259612  -  S    18:56      0:39.61 /usr/local/libexec/mysqld --defaults-extra-file=/usr/local/etc/mysql/my.cnf . . .

The my.cnf values are proper:

Code: 
# cd /usr/local/etc/mysql/
# ls
keyring		my.cnf		my.cnf.sample

# grep open my.cnf
open_files_limit                = 32768
table_open_cache           = 16384

Kinda stumped. Going to take a few days and then revisit with a fresh perspective.
 
So, when I remove the my.cnf entries for the open_files the DB gets the proper number from the OS. It's not a big DB, but it's working without errors now. I can test more if / when I ever need to increase past those values.
 
I got the same issue, but we need to increase that value (having 90000 tables)...

Bash: 
# sysctl kern.maxfilesperproc
kern.maxfilesperproc: 1885545
# limits -U mysql
Resource limits for class default:
  cputime              infinity secs
  filesize             infinity kB
  datasize             infinity kB
  stacksize            infinity kB
  coredumpsize         infinity kB
  memoryuse            infinity kB
  memorylocked               64 kB
  maxprocesses         infinity
  openfiles            infinity
  sbsize               infinity bytes
  vmemoryuse           infinity kB
  pseudo-terminals     infinity
  swapuse              infinity kB
  kqueues              infinity
  umtxp                infinity

So mySQL should be able to set open_files_limit but it does not. This is probably FreeBSD specific and related to an invalid mySQL system limit detection.

Code: 
2025-04-18T09:17:59.329754Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 32768 (request: 66046)

I have open a bug report: https://bugs.mysql.com/bug.php?id=118010
 
simplerezo said:
I got the same issue, but we need to increase that value (having 90000 tables)...

Bash: 
# sysctl kern.maxfilesperproc
kern.maxfilesperproc: 1885545
Click to expand...

I'm not doubting your actions, but still can't help ask: what's the value of your kern.maxfiles? Its default value is 64154 on 14.2 which is obviously a lot less than your current per-process limitation.
 
I don't think your assertion about default value of kern.maxfiles is right.
On the impacted system anyway its a lot above 32768 (and also kern.maxfilesperproc):
Bash: 
# sysctl kern.maxfiles
kern.maxfiles: 2094956
 
You must log in or register to reply here.
Back
Top