On Fri, 21 Mar 2025 at 16:19, Derick Turner <derick@e-learndesign.co.uk> wrote:
Thanks Gordon,
That's a good shout. Current settings for table_open_cache is set to default of 2000. I've run a test to see what the difference in the opened_tables value is in the space of a minute and that returned 3426. So should definitely look at increasing that value.
table_definition_cache is currently 103784
That sounds very high, unless you genuinely have > 100K tables in your database (which seems rather high)
Is there a rubric for working out what these should be set to?
Ideally table_definition_cache should be set to the number of tables you have, up to about 128K. Maybe 256K at a push. Beyond that you will start to run into file handle limits even if you set LimitNOFILE to 1M in your service script. table_open_cache is less straightforward. Ideall you should size it so that it never gets maxed out. Usually at least equal to table_definition_cache, often multiples. If you are running into file handle limits, then you can assess whether it is passably adequate by checking the table cache hit rate and making sure it doesn't drop below 99%. A decent monitoring tool like Percona's PMM or Shattered Silicon's SSM have graphs for that. SSM also has a tuning advisor dashboard for the most important clearly determinable tunables.
I've re-run the MySQLTuner script against the DB but it's not mentioned anything about adjusting table_open_cache. It gave a suggestion of a small increase to the table_definition_cache (106246)
MySQLTuner is, IMHO, somewhere in the grey area between useless and misleading. Several of the things it says are based on premises that are outright false. -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net