MySQL 8 Increasing table_open_cache in Ubuntu 20

Last Updated on May 16, 2021 EDT by Jordan

So if you’ve ever had to increase table_open_cache in Ubuntu 20, you’ll probably find that it doesn’t work the traditional way. Which is mind blowing! But I have a guess as to why. It’s to stop you from blowing up your server from too many open files!

I had updated /etc/mysql/mysql.conf.d/mysqld.cnf with the following

table_open_cache = 8000

However, MySQL was only showing the value set at 4945

mysql> SHOW VARIABLES LIKE '%table_open%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 4945  |
| table_open_cache_instances | 16    |
+----------------------------+-------+
2 rows in set (0.00 sec)

After some Google searching, I found this thread https://askubuntu.com/questions/1241013/cannot-increase-mysql-table-open-cache-in-ubuntu-20-04

Which pointed me to /lib/systemd/system/mysql.service and the line LimitNOFILE

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
Type=notify
User=mysql
Group=mysql
PIDFile=/run/mysqld/mysqld.pid
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
LimitNOFILE=10000

# Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

Based on the information in the post, the LimitNOFILE is causing an automatic change to the “table_open_cache” variable. Modifying it to say 20000 will allow you to go with an 8000 table_open_cache. Copy and pasting into WordPress wouldn’t have done this post justice. So I screenshotted it.

After making the changes and restarting MySQL I got table_open_cache set to 8000

Did you like this article?