Currently running Mysql 5.6 with the default one client per thread connection model. I want to switch to the thread_pool model but was wonderiing if the max_connections variable in my.cnf becomes obsolete once you switch to thread_pools. Does anyone know for sure?
Yes and no:
Imagine you have a quad core processor with HT, so you end up with 8 possible threads (default if you don’t manually set thread_pool_size).
You are also using max_connections=100.
Without using thread pool feature, if you have 80 incoming connections MySQL would try to handle all connections in parallel. So if all of your connections are just reading you would end up with 80 concurrent queries at maximum (or 100 due to max_connections).
When using thread pool feature you would end up with only running 8 queries in parallel. So what will happen to the other 72 connections? They will hang in the queue and wait for execution.
So is max_connections now obsolete when using thread pooling? No! max_connections are controlling your “backlog”, i.e. how many connections are allowed to wait.
Imagine, if you would set max_connections to the same value like thread_pool_size your applications would probably throw “Too many connections” errors when you have more than 8 clients.
It is important to understand that when using thread pooling feature your max_connections setting no longer affects your memory usage like before. Before using a thread pool you had to calculate buffer settings & co. * max_connections to make sure you won’t run out of memory when all possible connections are active. Now when using thread pool you only have to multiply with thread_pool_size when calculating memory usage which allows you to spend more memory on other things.