mysql threadsrunning,MySQL服务器未得到充分利用； thread_running小于2

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.