Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
I set table_open_cache in my config file as instructed, however it still gets set to 431 instead of 2000, this is in MySQL 5.7.12
Comment | File | Size | Author |
---|---|---|---|
#17 | apdqc-2733549-16-open_files_limit.patch | 2.44 KB | mikeytown2 |
#11 | apdqc-2733549-10-open-files-limit.patch | 16.42 KB | mikeytown2 |
#6 | apdqc-2733549-6-open_files_limit.patch | 3.22 KB | mikeytown2 |
|
Comments
Comment #2
MustangGB CreditAttribution: MustangGB commentedI've tried adjusting open_files_limit and table_definition_cache but it makes no difference.
The only adjustment that worked was reducing max_connections to 10, which raised table_open_cache to 502, so not much of an improvement.
Comment #3
MustangGB CreditAttribution: MustangGB commentedI *think* this is the solution: http://stackoverflow.com/a/35515570
Perhaps we could update the documentation to include this information, or at least some hints of how to solve this.
Comment #4
mikeytown2 CreditAttribution: mikeytown2 commentedYeah systemd put open file limit in a new location... need to adjust the documentation for this.
Comment #5
mikeytown2 CreditAttribution: mikeytown2 commentedTalked with our devops/linux guy.
https://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd....
Later versions of systemd (>218) have added a new systemctl interface for modifying unit behavior. Run
sudo systemctl edit mysqld.service
and add this into the file while in edit mode:Finally notify systemd of the new config
Comment #6
mikeytown2 CreditAttribution: mikeytown2 commentedComment #8
MustangGB CreditAttribution: MustangGB commentedI would mark NW, but the testing bot has already done it for me.
The issue is the warning that was appearing was the one relating to table_open_cache (i.e. The table_open_cache value should be increased) and not open_files_limit.
In fact I never saw the open_files_limit warning at all, so this would be completely hidden to anyone hitting the same wall that I did.
Comment #9
mikeytown2 CreditAttribution: mikeytown2 commentedFound an article where they explain how the limits get picked
https://blog.wnohang.net/index.php/2014/05/03/mysql-file-limit-table-cac...
Working on bringing max_connections, table_open_cache, & open_files_limit logic together.
Comment #11
mikeytown2 CreditAttribution: mikeytown2 commentedCommitted this. Please let me know if you have any other suggestions on how to improve it.
Comment #12
MustangGB CreditAttribution: MustangGB commentedYes much better, tested and it works and looks great.
Comment #13
MustangGB CreditAttribution: MustangGB commentedActually looks like this doesn't work with MySQL 5.5 (not sure about 5.6), so should probably add a version check, if it's an older version do it the old way with open_files_limit, if it's a newer version do it the new way with LimitNOFILE.
Comment #14
mikeytown2 CreditAttribution: mikeytown2 commentedSo
LimitNOFILE=65535
doesn't work in your case?Comment #15
MustangGB CreditAttribution: MustangGB commentedIt works fine for 5.7.
For 5.5 I didn't have any of the expected config files on my system, and creating them made no difference, however setting open_files_limit directly in my.cnf did work fine (whereas it didn't work in 5.7).
And as mentioned I don't have a 5.6 setup to test.
Comment #17
mikeytown2 CreditAttribution: mikeytown2 commentedThis has been committed
Comment #19
mikeytown2 CreditAttribution: mikeytown2 commentedAdded in the link to how to find your my.cnf
Comment #20
MustangGB CreditAttribution: MustangGB commentedGreat stuff, should help others out, sometimes all you need is a pointer of where to go.
Comment #22
MustangGB CreditAttribution: MustangGB commentedFor future me (or anyone else that might find this useful) on Ubuntu 16.04 and MySQL 5.7 the best location to use is:
/etc/systemd/system/mysql.service.d/limits.conf
Note: The path contains
mysql
rather thanmysqld
I changed locations from
/lib/systemd/system/mysql.service
due to it getting overwritten by updates.