Search code examples
multithreadingmariadbthreadpoolmariadb-10.5

threadpool thread could not be created

We got this warning on the MariaDb server ,never saw this before

Warning : threadpool thread could not be created :Resource temporarily unavailable,current number of threads in pool 12

Google isnt helpful at this time with this & chatGPT is down !

Server is running fine we haven't seen any errors

Any idea what it could be ?

Updates :

enter image description here

enter image description here

in term of reports we don't see any peak in memory usage.

Our CNF File:

[mysqld]
log-bin
server-id       = 1


skip-external-locking
skip-name-resolve = 1
innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
key_buffer_size               = 64K  
max_allowed_packet            = 1G 
thread_stack                  = 292K 
thread_cache_size             = 64 
table_open_cache              = 16000 
table_definition_cache        = 9000  ##  ((table_open_cache+400)/2)
innodb_change_buffer_max_size = 5 

join_buffer_size        = 256K 
max_connections         = 4000 


innodb_buffer_pool_size = 80G 
innodb_log_file_size    = 8G 


# Rolando :
innodb_log_buffer_size  =16M
innodb_read_io_threads  =12
innodb_write_io_threads =12
net_buffer_length       =1M


# Remove the STRICT_TRANS_TABLES which was added as default by MariaDB After 10.2.4
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

tmp_table_size                 = 768M 
max_heap_table_size            = 768M

#maximum size of a single resultset in the cache.
query_cache_limit              = 2M

#maximum amount of data that may be stored in the cache
query_cache_size               = 0
query_cache_type               = 0

# EXPERIMENTAL
# To be confirmed  instead of one-thread-per-connection
thread_handling=one-thread-per-connection  # thread_handling=pool-of-threads
innodb_flush_neighbors=0
innodb_io_capacity=800 ## was 1600
# END EXPERIMENTAL

#  deadlock error
transaction-isolation = READ-COMMITTED
binlog_format = row
innodb_autoinc_lock_mode = 2


log_bin           = /logs/mysql/mysql-bin.log
expire_logs_days  = 2
binlog_cache_size = 16M

#Slow query
log_output=FILE
slow_query_log
slow_query_log_file=/logs/mysql/slow-query.log
long_query_time=10.0


ignore-db-dir=lost+found

## Adding Performance_schema
innodb_monitor_enable=all
performance_schema=ON
performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON
Solution

  • The error is from this area of code.

    The "Resource temporary unavailable" is an operating system error from attempting to create a thread.

    If we look at pthread_create the EAGAIN error indicates potential causes of:

    • EAGAIN Insufficient resources to create another thread. (which would be short of memory)

    • EAGAIN A system-imposed limit on the number of threads was encountered. There are a number of limits that may trigger this error: the RLIMIT_NPROC soft resource limit (set via setrlimit(2)), which limits the number of processes and threads for a real user ID, was reached; the kernel's system-wide limit on the number of processes and threads, /proc/sys/kernel/threads-max, was reached (see proc(5)); or the maximum number of PIDs, /proc/sys/kernel/pid_max, was reached (see proc(5)).

    While systemd does have a LimitNPROC, its not set by default and with current threads = 12 it seems tiny, so my guess its running out of memory.