MySQL服务器未得到充分利用； thread_running小于2 [英] MySQL server not fully utilized; threads_running is lesser than 2
问题描述
我正在运行MySql 5.6，我注意到在对服务器进行基准测试(一段时间内并行执行几百个查询)时，在m1.large aws实例上CPU利用率从未超过50％.
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).
我已将thread_cache变量设置为50，将max_connections设置为500.当我从shell执行以下命令时，
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分机| grep Threads_created
mysqladmin -u root -ppassword -r -i 1 ext | grep Threads_created
我注意到创建的线程永远不会超过3
I notice that Threads created never crosses 3
mysqladmin -u root -ppassword -r -i 1分机| grep Threads_running
mysqladmin -u root -ppassword -r -i 1 ext | grep Threads_running
我注意到正在运行的线程永远不会超过3，有时会变成-1.
I notice that Threads running never crosses 3, sometimes becomes -1.
显示状态； (从MySql Console中运行此程序)
SHOW STATUS; (Ran this from MySql Console)
我注意到，尽管thread_cache_size为50，但Threads_Cached为0.
I notice that Threads_Cached is 0 in spite of the thread_cache_size being 50.
我正在运行前端服务器以连接以将请求转发到mysql.我正在使用大小为50的连接池.在这里，threads_created是否应该更改为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?
更新:
我将前端服务器从Underwow更新为Jetty.我现在正在使用c3p0进行连接池.我已配置为打开50个连接，现在我可以注意到threads_running和threads_running最多可达50个.
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.
但是，我的mysql进程的CPU使用率仍未超过60％.
机器详细信息:AWS m1.large实例，2个内核(4个vCPU)，7.5GB RAM MySQL版本:5.6 引擎:MyISAM 行数:8500万 查询类型:只读 查询:从表中选择a，b，c，其中text =? AND日期> =? AND日期< =?; 我在文本，日期字段上有一个复合索引，当我对该查询运行EXPLAIN时，我可以看到该索引正在使用.
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.
谢谢， V
推荐答案
多少个内核?当您看到50％时，有多少连接正在主动运行?我猜您有2个核心，并且有一个活动连接.由于MySQL每次连接使用的内核数不超过一个，因此占50％.
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"是一个令人困惑的概念.这是怎么回事:当一个新的客户端尝试连接时，mysqld(服务器)在其线程缓存"中查找是否有连接.如果没有，它将为该连接创建一个新的操作系统进程".这是一项耗时的工作，因此需要缓存.
"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.
当连接断开连接(并且没有连接池")时，该进程将放入线程缓存中.但是线程缓存的上限为thread_cache_size.对于Windows，此VARIABLE的值应为0，对于UNIX通常应为10.但是价值并不重要.
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控制可以同时连接多少个客户端.通常，他们忙于做其他事情，因此SHOW PROCESSLIST说"Sleep". wait_timeout将强行将睡眠时间超过该设置的人断开连接(如果尚未自愿断开连接的人).
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).
STATUS值Threads_created表示自MySQL启动以来发生了多少个新连接. Threads_running表示当前已连接多少，但未显示睡眠". (-1似乎是错误的".)例如，Threads_cached = 0意味着3个客户端已连接并且它们仍处于连接状态，再加上您从未连接超过3个客户端.
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.
您没有达到50，因为您没有足够的客户需要50.
You have not hit 50 because you don't have enough clients to need 50.
连接池和Thread_cache用于相同的缓存目的，但方式不同.两者都有是可以的，但是同时拥有它们并不会带来太多收益.
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.
这篇关于MySQL服务器未得到充分利用； thread_running小于2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
