0

I have a dedicated server i7-4770 with 32GB RAM. On server installed CentOs 7.8, MariaDB 10.5.8, PHP 7.4.14, Nginx 16.1. All tables in the database are InnoDB.

When the load on the database increases significantly and I see more than 20,000,000 requests per hour in PHPMyAdmin, the server dies unexpectedly. After I reboot the server from the hosting company's control panel - the server is working fine. But as soon as the load on the database becomes large, the server dies again. In the logs of MariaDB 10.5.8, before the server dies, only 1 error appears:

[Warning] Detected table cache mutex contention at instance 1: 22% waits. Additional table cache instance cannot be activated: consider raising table_open_cache_instances. Number of active instances: 1.

I think the reason for the death of the server is in her. But the problem is that when I specify table_open_cache_instances = 8 and restart the MariaDB, the request

SHOW VARIABLES LIKE 'table_open_cache_instances'

gives me a value of 1. After reboot server value also remains 1. Other values such as max_connections are set correctly.

My /etc/my.cnf.d/server.cnf (=my.cnf)

slow_query_log      = 1
slow_query_log_file = /var/log/mariadb/slow_queries.log
long_query_time     = 1

max_connections = 836
key_buffer_size = 256M
read_buffer_size = 16M
join_buffer_size = 16M

query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M

tmp_table_size = 512M
max_heap_table_size = 512M

innodb_buffer_pool_size = 22G
innodb_log_file_size = 512M

innodb_flush_log_at_trx_commit = 0

thread_cache_size = 512

table_open_cache = 207000
open_files_limit = 207000

table_definition_cache     = 1400

table_open_cache_instances = 8

How can I solve this problem? How do I edit my MariaDB 10.5.8 config?

2

I assume you hit the open_files_limit. This should be indicated somewhere further up of your warning about the instances in your MariaDB Error Log.

The problem here is, that MariaDB behaves differently than MySQL:

In MySQL the number of FD (open_files_limit) used is: table_open_cache x 2 + ... In MariaDB the number of DD used is table_open_cache x table_open_cache_instances x 2 + ...

https://mariadb.com/kb/en/server-system-variables/#table_open_cache_instances

Why they made it different I do not know.

So in your case you have:

table_open_cache = 207000 x table_open_cache_instances = 8 = requiring 1.6M TOC entries but you only have defined 207k as open_files_limit = 207000

So either lower the table_open_cache by at least 8 times or increase the open_files_limit by at least 8 times.

3 additional thoughts:

  • Do you really need 200k or 1.6M FD??? That happens only very rarely when you have multi-tenant set-ups or 100ks of tables.

See also here: https://fromdual.com/mysql-shared-hosting-configuration

  • MariaDB: table_open_cache x table_open_cache_instances x 2 + max_connections + 40
  • MySQL: table_open_cache x 2 + max_connections + 10

Regards, Oli

1
  Analysis of GLOBAL STATUS and VARIABLES:

Observations:

  • Version: 10.5.8-MariaDB-log
  • 32 GB of RAM
  • Uptime = 07:15:44; some GLOBAL STATUS values may not be meaningful yet.
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Since you are not using MyISAM, you could decrease key_buffer_size from 256M to 20M.

Table_open_cache_hits = 2347 /sec
Table_open_cache_misses = 35 /sec
That's a reasonable ratio. But can you explain why so many tables are being used so fast? Thise are both "high" numbers.

Was there some reason for having this hige a value? ( innodb_lru_scan_depth ) = 1,536 256 might be better.

The buffer_pool_size is 22G, but the status seems to imply that the dataset, or at least the working set, is much lower than that. If this is the case, then the machine size (RAM size) is unnecessarily large. Of course, if you expect the dataset to grow, you have a lot of room for growth.

If your disk is SSD, there are a couple of settings to change; see below.

lock_wait_timeout = 86,400 -- That may hide issues that should be taken care of.

Some clues that there is heavy use of temp tables in SELECTs (automatically generated by the Optimizer). Let's see some of the common queries; maybe they can be improved.

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 256M = 0 -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 268435456) to avoid unnecessary memory usage.

( table_open_cache ) = 10,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Table_open_cache_misses ) = 911,982 / 26144 = 35 /sec -- May need to increase table_open_cache (now 10000)

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 22528M / 1 = 22528MB -- Size of each buffer_pool instance. -- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,536 * 1 = 1,536 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 1). Also check for swapping.

( innodb_lru_scan_depth ) = 1,536 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 1,191,526 * 16384 / 22528M = 82.6% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,191,526 / 1419440 = 83.9% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 23622320128) is bigger than necessary?

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,734,142,976 / 22528M = 15.8% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 26,144 / 60 * 512M / 352526848 = 663 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 536870912). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890).

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( character_set_server ) = character_set_server = utf8 -- Charset problems may be helped by setting character_set_server (now utf8) to utf8mb4. That is the future default.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Created_tmp_tables ) = 2,497,985 / 26144 = 96 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( tmp_table_size ) = 512M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 536870912) to avoid running out of RAM. Perhaps no more than 64M.

( Connections ) = 3,289,327 / 26144 = 125 /sec -- Connections -- Increase wait_timeout (now 28800); use pooling?

( thread_cache_size / Max_used_connections ) = 512 / 297 = 172.4% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Acl_database_grants = 0
Acl_users = 3
Aria_pagecache_read_requests = 4.3 /HR
Aria_pagecache_write_requests = 0.55 /HR
Com_show_fields = 0
Com_show_tables = 0
Handler_read_first = 2.5 /HR
Handler_tmp_update = 0
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.00057
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_ibuf_merged_inserts = 0
Innodb_ibuf_merges = 0
Innodb_master_thread_active_loops = 0
Innodb_max_trx_id = 1.72e+6
Innodb_mem_adaptive_hash = 0
Innodb_rows_updated = 1.7 /HR
Memory_used_initial = 6.06e+7
innodb_adaptive_max_sleep_delay = 0
innodb_background_scrub_data_check_interval = 0
innodb_background_scrub_data_interval = 0
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400

Abnormally large:

(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 872
10 * read_buffer_size = 160MB
Com_select = 2344 /sec
Feature_check_constraint = 0.14 /HR
Feature_locale = 16 /HR
Handler_commit = 2347 /sec
Handler_tmp_write = 15143 /sec
Innodb_buffer_pool_pages_free = 1.19e+6
Open_streams = 4
Select_range = 147 /sec
Sort_priority_queue_sorts = 96 /sec
Sort_scan = 96 /sec
Table_open_cache_hits = 2347 /sec
Threads_cached = 185
innodb_lru_scan_depth / innodb_io_capacity = 7.68
innodb_open_files = 10,000
max_heap_table_size = 512MB
max_relay_log_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 512MB
performance_schema_max_cond_classes = 90
performance_schema_max_statement_classes = 222
tmp_memory_table_size = 512MB

Abnormal strings:

aria_recover_options = BACKUP,QUICK
binlog_row_metadata = NO_LOG
disconnect_on_expired_password = OFF
histogram_type = DOUBLE_PREC_HB
init_connect = SET NAMES utf8 collate utf8_general_ci
innodb_fast_shutdown = 1
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
use_stat_tables = PREFERABLY_FOR_QUERIES
wsrep_debug = NONE
wsrep_load_data_splitting = OFF
0

I specify table_open_cache_instances = 8

That must be changed in the configuration [which it seems to be??], else it is lost in the next restart. (This applies to most settings.)

Meanwhile, this is much too high:

table_open_cache = 207000

Lower that to 10000 for starters.

Then provide SHOW GLOBAL STATUS LIKE 'Table_open_cache%'; so we discuss whether it is at a good value.

What is the value of Max_used_connections? It may be high, indicating that client code is allowing "too many" connections, thereby leading to a variety of mutex contentions, etc.

For a deeper analysis, please provide SHOW GLOBAL STATS and SHOW VARIABLES. Also look into the Slowlog; perhaps speeding up some queries can alleviate pressure on the table cache. http://mysql.rjweb.org/doc.php/mysql_analysis

5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.