Workaround: edit /etc/systemd/system/multi-user.target.wants/mariadb.service

Uncomment line 99:
LimitNOFILE=16364

systemctl daemon-reload
systemctl restart mariadb

a new config file /etc/systemd/system/mariadb.service.d/nofilelimt.conf would be a more standard location to add/change settings.

[mysqld-safe] open-files-limit has always been needed to raise the fd limit. In systemd its now in a different place. What's the desired fix?

Its becoming clear we need to document what's changing from a my.cnf to systemd standpoint. The correct fix? Can we test what mysql 5.7.9 does, because there is systemd stuff there too, but I wonder if they've figured out a way to make sure that my.cnf is still respected. Either way, this is important from a DBA standpoint (for umpteen years, its always been in my.cnf).

Apparently, commit 6346d1de2fdaa8a8359082871eac49402cb0e472 broke the functionality.
LimitNOFILE was not commented in the initial file.

IIRC raising max_connections without raising open_files_limit will be "ignored" even by pre-systemd scripts.

LimitNOFILE is commented out because our pre-systemd scripts didn't adjust open_files_limit silently either.

There's also post-install script mariadb-service-convert, which converts open_files_limit (my.cnf) to LimitNOFILE (systemd service). But it should be relevant for upgrades mostly.

According to http://mysqlserverteam.com/mysql-5-7-native-systemd-support/ mysql.service has "User=mysql" and "ExecStart=/usr/sbin/mysqld --daemonize $MYSQLD_OPTS" and there is no LimitNOFILE. They should behave similarly.

With the above in mind, is there anything to fix?

serg, since there's no feedback, could you review my findings and if you agree, please reassign to Ian for documentation.

@svoj What do you suggest to do exactly? Just document the behavior?

Yes, I suggest just document the behavior.

I'm not really happy with a solution which equals to "hey, let's break behavior for everybody using Debian 8", to the premises that we shouldn't change LimitNOFILE silently.

tanj, could you elaborate which behavior was broken?

Open files limit has never put a constraint on max_connections in the past.
What is the relationship between the two exactly? max_connections is just a theoretical limit, so why is it limited to 214?

Each connection needs file descriptor, thus one cannot have max_connections bigger than open_files_limit. 214 - is autosized value for default open files limit set by OS: 1024 (default open_files_limit) - 10 (some reserved value) - 400 * 2 (2 x minimum table open cache size). This is valid for both 10.0 and 10.1.

If one starts mysqld --max_connections=3000, open_files_limit gets adjusted automatically. Assuming default open files limit is 1024, this will only work when mysqld is started as root. Which should be the case for our SysV scripts.

With systemd mysqld is started as mysql, thus it can't adjust open files limit. So we end up with max_connections set to 214 (calculated as explained above).

As an option we could let systemd start mysqld as root, but I doubt our community will like it (since it's not the systemd way of starting things).

OK, I was not aware of the fact that open_files_limit was adjusted automatically. That makes more sense now.
I would still recommend to raise LimitNOFILE to a sensible number in the service file (e.g. 16384). After all, we are in 2015 and 16K descriptors is nothing for a capable system. Not raising this limit will hurt us a lot with many unnecessary support issues which will just waste our time.

I did some research and I could find just a few rather negligible items against increasing LimitNOFILE by default:

• 16K descriptors is 2% of total available descriptors on my system (8Gb memory)
• increased memory usage: per-fd kernel structures seem to be allocated on demand and never freed
• handling extra unused file descriptors may have negative performance impact
• security: there were bugs in select()/poll() that may result in stack/heap corruption with RLIMIT_NOFILE > 1024

It could've been a nice solution if we could just increase hard limit for open files limit. Alas systemd doesn't allow to set hard/soft limits separately.

If raising LimitNOFILE to 16K will make 90% of our users happy, then we should probably go ahead and increase it. What I'm now concerned about is that max_connections doesn't affect LimitNOFILE anymore (as it affected open_files_limit before).

Anyway, I'm leaving this up to serg to decide.

Was part of a problem here: https://groups.google.com/d/msg/codership-team/pVKQ7LLj5HE/XfWQXcAiEQAJ

2% of file descriptors is still quite small part of the OS compared to the limited number of other things that use a large amount of file descriptors.

I hadn't heard of security bugs in select/poll. Is this something that is exploitable in mariadb? Is it more than a DoS of a service (which is the case when insufficient FDs)?

concerned about is that max_connections doesn't affect LimitNOFILE anymore

Does the conversion script need to take into account max_connections to set LimitNOFile?

Do we need a pre-script to issue warnings on max_connections > LimitNOFile?

I'm still in favour of a 16K default.

Regarding select/poll bugs, for example this one: https://sourceware.org/bugzilla/show_bug.cgi?id=10352 (which is closed, but I believe I've seen active bugs too). I doubt MariaDB is actually affected by this, but didn't check.

Conversion script as it is now is only meaningful for upgrades, while original report is about clean installation.

I believe if we add extra pre-script it will further complicate our systemd script, while major purpose of all this effort was simplicity.

Right, let's increase the limit to 16K.

There's, of course, an option of chaging to User=root and mysqld --user=mysql, that is, starting mysqld as root. Then it'll be able to change open-files-limit and do memlock too, if requested.

Fixed by increasing the limit up to 16K.

If think that there should be a warning or an error if someone is trying to set the max_connection or table_(open|definition)_cache to a value too high which could lead the server to exhaust all its file descriptors.

And is there a warning thrown if the open_files_limit is set on my.cnf on a system using the systemd script?

There're two warnings, which are issued if --log-warnings is set and --open-files-limit is not set:

Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld

Could not increase number of max_open_files to more than %u (request: %u)

There is no warning thrown if open_files_limit is set on a system using the systemd script.