So. As it is known, mysqld it “one process – is a lot of threads”.In approached enough understanding thread it when process copies itself, and parental process transfers to threads what that a task for the decision.
Creation thread differs from usual fork () basically that fork generates +1 process whereas division on threads uses other, more productive technology of branching without cloning of the data in memory.
At each connection of the client, mysql creates thread which this connection processes.
If you have mysqld with average loading, then you should be convinced that mysqld works on normal realisation of thread. The basic glitches are shown at work on SMP (more than 1 processor). Pthread “is not able” to scatter normally threads on different processors and as a result we have a situation when Mysqld hangs only on one processor together with a heap of the kiddies. All other processors smoke, even if Mysqld has eaten all processor time of the processor. So, we will look what with your threads. If the server is poorly loaded and has 1-2 connections simultaneously in this optimisation of special sense is not present. Much we will feel result when the server seriously is loaded and has many competitive inquiries.
Let’s look at the status
#> mysqladmin extended-status | egrep Thread
Let’s pay attention to values:
| Threads_cached | 6 | | Threads_connected | 1 | | Threads_created | 7 | | Threads_running | 1 |
If Threads_cached is equal to zero, at you it is disconnected cache of threads. I.e. at each connection created new thread, and at switching-off it leaves in /dev/null. It is bad. When enabled cache of threads at switching-off of the client thread leaves in cache, and at new connection it is not created, and takes from cache. It is huge economy of resources at the big loadings. In some situations loading is decreased in times at use thread of cache.
– Threads_cached – Quantity threads in cache.
– Threads_connected – Roughly speaking, quantity of open connections
– Threads_running – How many threads now “work”
Threads_created – here a corner stone. How many threads has been created from start. If in cache is not present free thread, then it will be created. If cache is not present in general, at each connection it is created. And at switching-off killed. Too most occurs, if cache of threads too small. If this value is measured by thousand, you have problems. It should be hardly more Threads_cached ideally.
What to do if Threads_created has too high value? In my.cnf there is such parametre as thread_cache_size. It is necessary for increasing by experimental a way till that time while Threads_created will not accept an order of figures Threads_cached.
For definition of the initial value you should to learn how many threads happens on the average after start in the peak loadings. Or periodically to start such command:
# echo "SHOW GLOBAL STATUS" | mysql | grep Threads_connected | awk ' {print $2;}'
Have defined average value, have written down in my.cnf, have made server restart. Now need wait awhile. Look the status. If Threads_created continues to grow above value кеша, we increase a variable. And so to a victory, but I do not advise to raise this size above several hundred – risk to receive Out Of Memory.
Necessary to control Resident Set Size of process mysqld in long-term dynamics.
It is RSS column of the utility “htop”.
Means how many mysql now takes places in memory. I do not advise to raise it above half of volume of your memory so as you can get a problem, when will start compressing system caches.
Normal value thread_cache_size at which Threads_created keeps on suitable level, and mysql moderately guzzles memory fluctuates from 5 to 100 depending on loading and size of the accessible RAM. Too high value thread_cache_size can unreasonably eat superfluous memory.
If it has not rescued you, thread_cache exceeds conceivable norms and Mysql continues to guzzle memory and the processor and to generate threads – complex of optimisation is necessary:
1. Systems
2. Kernels
3. Logicians of scripts and inquiries
4. Other starting parametres mysqld
Good Luck .. ))