So I'm running a MariaDB 10 database on a single core server; not super powerful, but then my needs aren't huge either.

However, one thing I've noticed is that if I look in htop I have around 28 mysql entries visible in the list, which I believe is one process plus 27 threads (since htop lists threads like processes by default).

However, this seems like a lot, as my database is configured to use a thread pool with a limit of 8 threads, rather than the default one thread per connection, and it isn't even using all of these (it's currently on two), so what are the other 25 threads for? Is there a way that I can reduce this number?

I know it's not necessarily a problem, however I have been experiencing infrequent slow queries (>0.5s rather than the usual <1ms, as most of my queries are very simple) and it seems like having too many threads could be a potential culprit if too many are suddenly waking up at once.

Is this number of threads normal? Is there a way that I can further reduce it, or are these extra threads doing something important?

sudo grep thread /etc/mysql/my.cnf

thread_stack            = 256K
thread_cache_size       = 8
thread_handling         = pool-of-threads
thread_pool_max_threads = 8
thread_pool_stall_limit = 200
  • Please post the output of "sudo grep thread /etc/mysql/my.cnf" – Rui F Ribeiro Dec 6 '15 at 12:02
  • Duplicate of stackoverflow.com/questions/621516/… ? – steve Dec 6 '15 at 12:31
  • 2
    @RuiFRibeiro added the requested values. @steve I'm not sure if it's a duplicate, there's definitely overlap, but I know about settings like thread_cache_size, but like I say, I'm seeing at least 27 threads which is what I'm unclear on given that my limit is set to eight. I can understand that MariaDB may have some extra threads for others tasks, but does it really need so many? Are there are other settings that these might be controlled from? – Haravikk Dec 6 '15 at 22:06
  • 2
    Also wanted to note that with MySQL on 28 entries in htop I only have a value of 4 for Threadpool_threads (3 idle, 1 running), so I'm not even on the maximum that I've set, so there are actually 23 or more extra threads. – Haravikk Dec 6 '15 at 22:22
  • Could you tell me the number of "max_connections" in my.cnf? – Rui F Ribeiro Dec 7 '15 at 0:02

Those threads serve different purposes. There is a main thread that starts and waits until shutdown, there are several background threads in Innodb and Aria, and so on.

Attach gdb and do thread apply all bt to see what they are doing - most likely they are sitting there doing nothing waiting for for some event to happen.

Your Answer

