Hide Forgot
Description of problem: this is a branch from https://bugzilla.redhat.com/show_bug.cgi?id=754285 that I found filed under 'pam' last month. After upgrading to fc16 from fc15 mysql suddenly ran out of file descriptors when dumping large number of tables. Version-Release number of selected component (if applicable): systemd-37-3.fc16 mysql-server-5.5.18-1.fc15.x86_64 mysql-server-5.5.19-1.fc16.x86_64 How reproducible: Always Steps to Reproduce: 1. Using script calling mysqldump to backup many databases individually containing many tables Actual results: After a few databases backed up, subsequent calls to mysqldump fails with: "Out of resources when opening file '/dev/shm/#sql_23a7_2.MYI' (Errcode: 24) when trying to dump tablespaces" (ulimit/open_files_limit was 1024) Expected results: Success with no errors (Under sysv, mysql sets ulimit/open_files_limit to (2 x table_cache) + max_connections + 10) (umlit/open_files_limit should have been 16494) Additional info: Looked like file descriptors running out, but 8192 was enough when running under fc15 the day before, and my.cnf hadn't changed. I tried adding ulimit -n 8192 to rc.local, reboot. Still no joy. Backup failed again after a few databases. Here is some info: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ uname -a Linux 3.1.5-6.fc16.x86_64 #1 SMP Thu Dec 15 16:14:44 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux rpm -qa | grep -i mysql-server mysql-server-5.5.18-1.fc16.x86_64 ps aux | grep mysqld * /usr/libexec/mysqld * --open-files-limit=8192 * ulimit -n; 8192 (never needed to do this before) /etc/security/limits.conf * soft nofile 8192 * hard nofile 8192 mysql -p -Be "show variables like 'open_files_limit';" Variable_name Value open_files_limit 1024 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Another two fc16 slaves show the same things. mysqld starts with --open-files-limit=8192 as desired, and presumably ulimit -n 8192 is being called in the /usr/bin/mysqld_safe as root, but something is still preventing mysqld exceeding 1024, and this makes the database unusable with the desired table cache. Reducing the table cache to 500 allowed the backup process to complete, although it needs a table cache of a few thousand, and 2-3x more file descriptors. 1024 is totally inadequate as a default. The same commands on the live fc15 server (16GB i7 2600K): ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ uname -r; 2.6.40.6-0.fc15.x86_64 rpm -qa | grep -i mysql-server; mysql-server-5.5.18-1.fc15.x86_64 ps aux | grep mysqld; mysql * /usr/libexec/mysqld * --open-files-limit=8192 * ulimit -n; 1024 mysql -p -Be "show variables like 'open_files_limit';" Variable_name Value open_files_limit 16494 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Interesting, that open files limit is more than double than asked for, and this page explains why nicely: http://blogs.oracle.com/rainy/entry/changing_process_max_file_descriptor mysql-server is the same on both fc15 and 16. The fc15 system doesn't have any modifications to /etc/security/limits.conf ulimit -n is not set in rc.local Workaround: Add to /lib/systemd/system/mysqld.service [Service] LimitNOFILE=32768 However, this sets a hard limit, mysql can't set it itself: my.cnf: table_cache = 8192 open-files-limit = 16384 systemctl --system daemon-reload systemctl restart mysqld.service mysql -u root -p -Be "show variables like 'open_files_limit'" Variable_name Value open_files_limit 32768
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.