Update on this one.

I upped the open_table_cache to 30,000 to see what impact this would have.  I would have expected the memory foot print of the MariaDB process to grow to about 30GB but it has stayed at ~27GB (as reported by btop).  This was from the SQL calculation:

SELECT @@innodb_buffer_pool_size + @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@table_open_cache * @@open_files_limit + (@@sort_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size + @@join_buffer_size + @@thread_stack + @@binlog_cache_size) * @@max_connections AS MaxMemoryEstimate;

Value for that returns 32082607104 (29.88GB  if my maths is right).

The variable is correct in the DB when I query it, but is this expected?  Is it being limited by some other server/OS limit? If the memory usage remains the same I can increase it without having to add more RAM - I just don't want it to suddenly spike, run out of available resource and come up against the OOM Killer again.

Kind regards

Derick

On Fri, 21 Mar 2025 at 17:36, Derick Turner <derick@e-learndesign.co.uk> wrote:

On 21/03/2025 14:32, Gordan Bobic via discuss wrote:

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)

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.

-- 
Derick Turner - He/Him