I am running MySql 5.6 and I noticed that the CPU utilization never crosses 50% on an m1.large aws instance, when I benchmark the server (a few hundred queries executed in parallel over a period of time).
I have set the thread_cache variable to 50 and max_connections 500. When I execute the following commands from shell,
mysqladmin -u root -ppassword -r -i 1 ext | grep Threads_created
I notice that Threads created never crosses 3
mysqladmin -u root -ppassword -r -i 1 ext | grep Threads_running
I notice that Threads running never crosses 3, sometimes becomes -1.
SHOW STATUS; (Ran this from MySql Console)
I notice that Threads_Cached is 0 in spite of the thread_cache_size being 50.
I am running a front end server to connect to forward requests to mysql. I am using a connection pool of size 50. Shouldn't the threads_created change to 50 right here? Is my understanding of this right?
UPDATE:
I updated my front end server from undertow to Jetty. I am now using c3p0 to do the connection pooling. I have configured to have 50 connections open and now I am able to notice threads_running and threads_running go up to 50.
However, my CPU utilization still does not go beyond 60% for my mysql process.
Machine details: AWS m1.large instance, 2 cores(4 vCPUs), 7.5GB RAM MySQL Version : 5.6 Engine: MyISAM Rows: 85 millions Query type: Read only Query: SELECT a,b,c FROM table WHERE text = ? AND date >= ? AND date <= ?; I have a composite index on text,date fields and when I run EXPLAIN on this query, I am able to see that the index is being used.
Thanks, V
How many cores? How many connections are actively doing things when you see 50%? I'll guess that you have 2 cores and you have one connection active. Since MySQL does not use more than one core per connection, that's 50%.
The "Thread" values you mention are OK. Details...
"Thread_cache" is a confusing concept. Here's what's going on: When a new client tries to connect, mysqld (the server) looks in its "thread cache" to see if there are any there. If not, it will create a new Operating System 'process' for that connection. This is a moderately time consuming task, hence the desire for the cache.
When a connection disconnects (and there is no "connection pooling"), the process is put into the thread cache. But the thread cache is capped at thread_cache_size. That value of this VARIABLE should be 0 for Windows, and typically 10 for unix. But the value does not matter a lot.
max_connections controls how many clients can simultaneously be connected. Usually they are busy doing other things, so SHOW PROCESSLIST says "Sleep". wait_timeout will forcibly disconnect those who Sleep longer than that setting (if they have not already voluntarily disconnected).
The STATUS value Threads_created says how many new connections have happened since MySQL started. Threads_running says how many are currently connected, but not 'Sleep'. (-1 seems 'wrong'.) Threads_cached = 0 could mean, for example, that 3 clients have connected and they are still connected, plus you never had more than 3 connected.
Since you have a "connection pool", connections will tend to never die.
You have not hit 50 because you don't have enough clients to need 50.
The connection pool and the Thread_cache are serving the same caching purpose, but in different ways. It's OK to have both, but you don't gain much by having both.