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

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

MustangGB created an issue. See original summary.

MustangGB’s picture

I'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.

MustangGB’s picture

Component: Code » Documentation
Category: Support request » Feature request

I *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.

mikeytown2’s picture

Yeah systemd put open file limit in a new location... need to adjust the documentation for this.

mikeytown2’s picture

Talked with our devops/linux guy.
https://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd....

sudo mkdir -p /etc/systemd/system/mysqld.service.d
sudo sh -c "printf '[Service]\nLimitNOFILE = infinity\nLimitMEMLOCK = infinity\n' >> /etc/systemd/system/mysqld.service.d/limits.conf"

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:

[Service]
LimitNOFILE = infinity
LimitMEMLOCK = infinity

Finally notify systemd of the new config

sudo systemctl daemon-reload
sudo systemctl restart mysqld
mikeytown2’s picture

Status: Active » Needs review
FileSize
3.22 KB

Status: Needs review » Needs work

The last submitted patch, 6: apdqc-2733549-6-open_files_limit.patch, failed testing.

MustangGB’s picture

I 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.

mikeytown2’s picture

Found 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.

  • mikeytown2 committed 2b7a492 on 7.x-1.x
    Issue #2733549 by mikeytown2: Can't change table_open_cache/...
mikeytown2’s picture

Title: Can't change table_open_cache » Can't change table_open_cache/max_connections due to open_files_limit
Status: Needs work » Fixed
FileSize
16.42 KB

Committed this. Please let me know if you have any other suggestions on how to improve it.

MustangGB’s picture

Yes much better, tested and it works and looks great.

MustangGB’s picture

Status: Fixed » Needs work

Actually 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.

mikeytown2’s picture

So LimitNOFILE=65535 doesn't work in your case?

MustangGB’s picture

It 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.

  • mikeytown2 committed 848a796 on 7.x-1.x
    Issue #2733549 by mikeytown2: Give more info on setting the...
mikeytown2’s picture

Status: Needs work » Fixed
FileSize
2.44 KB

This has been committed

  • mikeytown2 committed 743331c on 7.x-1.x
    Issue #2733549 by mikeytown2: Give more info on setting the...
mikeytown2’s picture

Added in the link to how to find your my.cnf

MustangGB’s picture

Great stuff, should help others out, sometimes all you need is a pointer of where to go.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

MustangGB’s picture

For 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 than mysqld

I changed locations from /lib/systemd/system/mysql.service due to it getting overwritten by updates.