0

We have a MariaDB server (10.5.16) that has recently become pretty unreliable. A simple query like SELECT * FROM INFORMATION_SCHEMA.TABLES LIMIT 1 hangs with "Opening tables" as the state (and is still at "Opening tables" 30 minutes later). On another server that query returns in 200ms.

A simple insert in a small table takes 15 seconds. The server has 32 GB of RAM, a 500 GB SSD, plenty of available disk space, and runs CentOS 7.9.2009.

Here is the output of SHOW VARIABLES \G: https://pastebin.com/yZZ1HNEm

I updated innodb_buffer_pool_size to 5GB (the previous value * 40) using SET GLOBAL, and will switch innodb_flush_method to O_DIRECT on the next restart.

Any ideas on why everything has ground to a halt? I'm seeing quite a few in a state of "Opening tables".

Any settings you'd change? As the innodb_buffer_pool_size = 128MB implies this server hasn't been optimized and I'm still the new guy so change is incremental.

Thanks!

2
  • show global status and show global variables would be ideal. Any query optimizations on INFORMATION_SCHEMA.* are adhoc (hence only somtimes simple), so your "simple query" is really opening every table. and the LIMIT 1 isn't being applied to the end. As such you've probably ran out of table_open_cache. Once that's resolved your insert will hopefully have resolved itself.
    – danblack
    Commented Jan 7, 2023 at 1:48
  • innodb_buffer_pool_size should be set to about 70% of available RAM. The VARIABLES you provided still says 128M, which is much too small. How many tables do you have? If you re=fetch the VARIABLES and GLOBAL STATUS, please do the 1-value-per line, not the \G format.
    – Rick James
    Commented Jan 7, 2023 at 5:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.