On Fri, 21 Mar 2025 at 17:36, Derick Turner <[email protected]> wrote: > > On 21/03/2025 14:32, Gordan Bobic via discuss wrote: > > On Fri, 21 Mar 2025 at 16:19, Derick Turner <[email protected]> > 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) > > Database servers have 221 databases defined and, yes, 108080 ibd files.
That is on the high side, but you are probably OK unless that grows by more than about 20% in the near future. > 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%. > > OK - Sounds like I need to do some tuning calculations then. Or look at the > overall infrastructure and split the databases off onto separate sets of > clusters (if we are getting into diminishing returns territory) You are probably OK for now unless you plan to add thousands more tables in the near future. > 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. > > Thanks for that - I'll look at getting SSM set up. See here: https://shatteredsilicon.net/downloads/ Feel free to drop me a message off list, happy to talk you through it. -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
