Bug 785243

I see no bug here.  The mysql server can't raise its hard ulimit because it doesn't run as root, so its number of open files is constrained by LimitNOFILE or the system-wide default.  In my testing, as long as you set LimitNOFILE high enough in the service file, you can configure open-files-limit as you please and the effective setting will track that.  In particular I do *not* see the behavior you claim whereby open_files_limit is locked to LimitNOFILE even if a lower value is requested by my.cnf.  You have not shown the whole contents of your my.cnf, but as pointed out in that blog entry, there are a few calculations between what's in the file and what it tries to actually use, so I suspect the reason the numbers seem different is somewhere in those details.

Please recheck your results with that in mind.

Thanks very much Tom, but you already answered for me in 754285!

I'm very conscious of not wasting our valuable time, which is why I was careful to post just the required amount of information in an easily digestible structure to help avoid unnecessary effort for the developers.

No, I didn't mean that LimitNOFILE sets the open-files-limit, but that it sets a hard ceiling. Under SysV, mysqlsafe running as root would override it to satisfy open-files-limit.

The real issue is not knowing about this change of behaviour when upgrading, nor even of the existence of LimitNOFILE so for others' benefit, I suggest adding this to mysqld.service:

[Service]
#Uncomment if you need to increase open-files-limit
#LimitNOFILE=1024

(In reply to comment #2)
> The real issue is not knowing about this change of behaviour when upgrading,
> nor even of the existence of LimitNOFILE so for others' benefit, I suggest
> adding this to mysqld.service:
> 
> [Service]
> #Uncomment if you need to increase open-files-limit
> #LimitNOFILE=1024

Makes sense, I'll add something like that in the next package turn.

On further thought, we can't just suggest that, because you're not supposed to modify unit files in-place.  In view of other gripes on the subject such as bug #768594, I'm thinking of adding text like this to the head of mysqld.service:

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file /etc/systemd/system/mysqld.service,
# containing
#		.include /lib/systemd/system/mysqld.service
#		...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase open-files-limit beyond 1024,
# you need to increase LimitNOFILE in these settings, so create a file like
#		.include /lib/systemd/system/mysqld.service
#		[Service]
#		LimitNOFILE=10000

This unfortunately won't help anyone who hasn't found the unit file in the first place, but at least those who do find it will stand some chance of doing the right thing.

Good idea Tom.

I think anyone running into the open-files-limit problem should arrive here fairly quickly for the solution.

systemd is new to me and will be to a lot of people, and after two mysql-server updates already, modifying service files each time would become tiresome.

(The wiki text was a bit heavy-going for something so simple.)

Just a slight mod to your last text:
# For example, if you want to increase open-files-limit beyond 1024,
# you need to increase LimitNOFILE, so create a file called
# "/etc/systemd/system/mysqld.service" containing:
#  .include /lib/systemd/system/mysqld.service
#  [Service]
#  LimitNOFILE=10000

I think that should do it...
Thanks for your support.

I copy-edited that a bit more and pushed it into git.  I don't feel a need to create a mysql update in the stable branches right now, but this documentation will be in the next updates.