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.couno...@1check.com> 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) ! >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
