maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

You should hardly ever need to adjust table_open_cache_instances away from
defaults, but it sounds like you may need into bump your table_open_cache
by a 2-4x.

Are you using more than one node for writing? And does the new node get
used for anything immediately after joining?

On Thu, 28 Jul 2022, 01:20 Cédric Counotte, <cedric.counotte@xxxxxxxxxx>
wrote:

> I just tested using this setting, but it made no difference:
>         innodb_max_dirty_pages_pct_lwm=0.001
>
> Tested with 2 existing nodes running and sync'ed, and one new node being
> attached.
>
> As soon as SST sync ended, the first 2 nodes started piling queries,
> mostly stuck in commit.
> If the new node was used, all queries got stuck in opening tables.
>
> Please see attached screenshots showing part of the queues, which quickly
> reach 100+.
>
> It took about 20 minutes to recover from that situation without using the
> new node at all! It took another 20 minutes for the new node to become
> usable (eg not too slow).
>
>
> Restarted server, it did an IST, exact same result, just worse, pending
> queries above 200 this time!
> How is it possible for queries to be stuck for several minutes (10!!!)
> while in nominal state they take less than 0.2 seconds !?
>
>
> Saw this in logs just after the IST, however table_open_cache_instances =
> 16 is set in config file and live value is indeed 1!?
>
> 2022-07-27 23:55:09 11 [Warning] Detected table cache mutex contention at
> instance 1: 30% waits. Additional table cache instance cannot be activated:
> consider raising table_open_cache_instances. Number of active instances: 1.
>
>
> FWIW we have 350+ DB each with 100 tables, and doing a mariabackup
> requires ulimit -n 919200, servers are processing about 3.000
> queries/seconds during those tests, while it peaks at 10.000 during the
> day. A single server is capable of handling the peak load (verified earlier
> this morning) !
>