집 >데이터 베이스 >MySQL 튜토리얼 >MySQL 성능 튜닝 및 테스트 방법
Mysql 서비스가 일정 기간 실행된 후 알 수 없는 이유로 속도가 느려지는 이유를 어떻게 알 수 있나요?
데이터베이스 성능 평가에는 핵심적인 역할을 하기 때문이 아니라 데이터베이스를 명확하게 나타낼 수 있기 때문에 데이터베이스 성능을 평가하는 데 매우 중요한 여러 가지 지표가 있습니다. 특정 분야의 능력.
IOPS: 초당 입출력 작업, 초당 처리되는 IO 요청 수입니다.
우리는 I/O가 초당 300M 읽기, 200M 쓰기와 같은 디스크의 읽기 및 쓰기 기능이라는 것을 알고 있습니다. 이는 데이터 처리량(I/O 기능의 또 다른 주요 지표)이지만 IOPS code>는 읽기 및 쓰기의 데이터 처리량이 아닙니다. IOPS는 <code>초당 처리할 수 있는 I/O 요청 수
를 나타냅니다. IOPS
指的可不是读写的数据吞吐量,IOPS 指的是每秒能够处理的 I/O 请求次数
。
如果想I/O 系统响应够快,那么 IOPS 越高越好,因为IOPS 和硬件有关,所以,要提高IOPS,就目前来看基本只能拼硬件,传统方案是使用多块磁盘通过 RAID 条带后,使 I/O 读写能力获得提升,我们也可以使用固态硬盘SSD来提升IOPS,不过固态硬盘成本可能比较大。
QPS:Query Per Second,每秒请求(查询)次数。
这个参数非常重要,可以直观的反映系统的性能,这就像IOPS衡量磁盘每秒钟能接收多少次请求。
我们可以在MySQL命令行模式下执行 status
命令,返回的最后一行输出信息中就包含 QPS 指标。
TPS:Transaction Per Second,每秒事务数。
TPS参数MySQL原生没有提供,如果需要我们自己算,可以利用计算的公式:
TPS = (Com_commit + Com_rollback) / Seconds
这个公式有两个状态变量,分别代表提交次数和回滚次数,Seconds 就是我们定义的时间间隔。
TPCC-MySQL 由Percona基于TPCC规范开发的一套MySQL基准测试程序,我们使用这套工具来测试前面的三个重要指标。
具体的安装,可以看这这两篇博文 mysql压力测试工具tpcc-mysql安装测试使用,mysql性能测试-tpcc,TPCC更能模拟线上业务。
如果数据库参数配置合理,则可以大大的提高运行效率,即最大化利用系统资源。
max_connections:指定 MySQL 服务端最大并发连接数
,值得范围从 1~10 万,默认值为151.
这个参数非常重要,因为它决定了同时最多能有多少个会话连接到 MySQL 服务。设定该参数时,根据数据库服务器的配置和性能,一般将参数值设置在 500~2000 都没太大的问题。
max_connect_errors:指定允许连接不成功的最大尝试次数
,值得范围从 1~2^64 之间,在 5.6.6 版本默认值是 100。
一定不要忽视这个参数,如果尝试连接的错误数量超过该参数指定值,则服务器就不再允许新的连接,没错,就是拒绝连接,尽管 MySQL 仍在提供服务,但无法创建新的连接了。可以使用 FLUSH HOSTS
,使状态清零或重新启动数据库服务,不过这个代价太高了,一般不会这么干,所以,这个参数的默认值太小,这里建议将之设置为 10 万以上的量级
。
这两个参数都与连接会话的自动超时断开有关,前者用于指定关闭交互连接前等待的时间,后者用于指定关闭非交互连接前的等待时间,单位均是秒,默认值均为 28800,即 8 个小时。
skip-name-resolve:可以将其简单的理解为禁用 DNS 解析,注意啊,这个是服务端的行为,连接时不检查客户端主机名,而只使用IP。如果制定了该参数,那么在创建用户及授予权限时,HOST 列必须是IP而不能是主机名。建议启用该参数,对于加快网络连接有一定的帮助,等于是跳过了主机名的解析。
back_log:指定 MySQL 连接请求队列中存放的最大连接请求数量
,在 5.6.6
版本之前
,默认是 50 个,最大值不超过 65535。在 5.6.6
版本之后
,默认值为 -1,表示由MySQL自动调节,所谓自行调节其实也有规则,即 50+(max_connections/5)。
该参数主要应对短时间内有大量的连接请求,MySQL 主线程无法及时为每一个连接请求分配(或创建)连接的线程,怎么办呢,它也不能直接拒绝,于是就将一部分请求放到等待队列中待处理
status
명령을 실행할 수 있으며 반환된 출력 정보의 마지막 줄에는 QPS 표시기가 포함되어 있습니다. 🎜🎜3.TPS🎜🎜TPS: 초당 트랜잭션, 초당 트랜잭션 수. 🎜TPS 매개변수는 MySQL에서 기본적으로 제공되지 않습니다. 이를 직접 계산해야 하는 경우 다음 계산 공식을 사용할 수 있습니다. 🎜select count(*) from innodb; 1 row in set (1.91 sec) select sql_no_cache count(*) from innodb; 1 row in set (0.25 sec)🎜 이 공식에는 제출 수와 롤백 수를 각각 나타내는 두 개의 상태 변수가 있습니다. 우리가 정의하는 시간 간격. 🎜🎜2. TPCC 테스트의 주요 지표🎜🎜TPCC-MySQL은 TPCC 사양을 기반으로 Percona에서 개발한 MySQL 벤치마크 프로그램으로, 이전 세 가지 중요한 지표를 테스트하는 데 사용됩니다. 🎜🎜1. TPCC 도구 설치 및 사용 🎜🎜특정 설치의 경우 mysql 스트레스 테스트 도구 tpcc-mysql 설치 및 테스트, mysql 성능 테스트-tpcc, TPCC는 온라인 비즈니스를 더 잘 시뮬레이션할 수 있다는 두 가지 블로그 게시물을 읽을 수 있습니다. 🎜🎜3. 데이터베이스 매개변수 구성 최적화🎜🎜데이터베이스 매개변수를 합리적으로 구성하면 운영 효율성이 크게 향상될 수 있습니다. 즉, 시스템 리소스의 활용도가 최대화됩니다. 🎜🎜1. 연결 관련 매개변수🎜
최대 동시 연결 수
를 지정하며 값 범위는 10,000~100,000이며 기본값은 151.🎜 이 매개변수는 MySQL 서비스에 동시에 연결할 수 있는 최대 세션 수를 결정하므로 매우 중요합니다. 본 파라미터 설정 시 데이터베이스 서버의 구성 및 성능에 따라 일반적으로 파라미터 값을 500~2000 사이로 설정하는 것은 큰 문제가 되지 않습니다. 🎜실패한 연결 시도의 최대 횟수를 지정합니다
. 값 범위는 1~2^64이며 버전 5.6.6의 기본값은 100입니다. 🎜🎜이 매개변수를 무시하지 마십시오. 연결 시도 중 오류 수가 이 매개변수에 지정된 값을 초과하면 서버는 더 이상 새 연결을 허용하지 않습니다. 예, MySQL은 여전히 서비스를 제공하고 있습니다. , 새 연결을 만들 수 없습니다. FLUSH HOSTS
를 사용하여 상태를 지우거나 데이터베이스 서비스를 다시 시작할 수 있지만 비용이 너무 높아 일반적으로 수행되지 않습니다. 따라서 이 매개변수의 기본값이 너무 작습니다. .여기에서는 100,000 이상으로 설정하는 것이 좋습니다
. 🎜5.6.6
버전 이전에 MySQL <code>연결 요청 대기열
에 저장된 최대 연결 요청 수를 지정합니다. , 기본값은 50이고 최대값은 65535를 초과하지 않습니다. 5.6.6
버전 이후에는 기본값이 -1입니다. 이는 MySQL에 의해 자동으로 조정된다는 의미입니다. 소위 자체 조정에는 실제로 규칙이 있습니다. 즉, 50+( 최대_연결/5). 🎜🎜이 매개변수는 주로 짧은 시간 내에 많은 수의 연결 요청을 처리하는 데 사용됩니다. MySQL 메인 스레드는 시간 내에 각 연결 요청에 대한 연결 스레드를 할당(또는 생성)할 수 없습니다. 요청의 일부가 처리를 위해 대기 큐에 배치됩니다
. 대기 큐의 길이는 back_log의 매개변수 값입니다. 그러면 후속 연결이 됩니다. 요청이 거부됩니다. 🎜sync_binlog:指定同步二进制日志文件的平率,默认为0.
如果要性能,则指定该参数为0,为了安全起见则指定该参数值为 1.
expire_logs_day:指定设置二进制日志文件的生命周期,超出则将自动被删除
,参数值以天为单位,值得范围从0~99,默认值是0,建议将该参数设置为 7~14 之间,保存一到两周就足够了。
max_binlog_size: 指定二进制日志的大小,值得范围从 4KB~1GB,默认为 1GB。
thread_cache_size:指定MySQL为快速重用而缓存的线程数量。值得范围从 0~16384,默认值为0.
一般当客户端中断连接后,为了后续再有连接创建时,能够快速创建成功,MySQL 会将客户端中断的连接放入缓存区,而不是马上中断释放资源。这样当有新的客户端请求连接时,就可以快速创建成功。因此,本参数最好保持一定的数量,建议设置在 300~500
之间均可.另外,线程缓存的命中率也是一项比较重要的监控指标,计算规则为(1-Threads_created/Connections)* 100%,我们可以通过该指标来优化和调整thread_cache_size参数。
sql_cache意思是说,将查询结果放入查询缓存中。
sql_no_cache意思是查询的时候不缓存查询结果。
sql_buffer_result意思是说,在查询语句中,将查询结果缓存到临时表中。
这三者正好配套使用。sql_buffer_result将尽快释放表锁,这样其他sql就能够尽快执行。
使用 FLUSH QUERY CACHE 命令,你可以整理查询缓存,以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。
RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。
那么mysql到底是怎么决定到底要不要把查询结果放到查询缓存中呢?
是根据query_cache_type
这个变量来决定的。
这个变量有三个取值:0,1,2,分别代表了off、on、demand。
mysql默认为开启 on
意思是说,如果是0,那么query cache
是关闭的。
如果是1,那么查询总是先到查询缓存中查找,即使使用了sql_no_cache仍然查询缓存,因为sql_no_cache只是不缓存查询结果,而不是不使用查询结果。
select count(*) from innodb; 1 row in set (1.91 sec) select sql_no_cache count(*) from innodb; 1 row in set (0.25 sec)
如果是2,DEMAND。
在my.ini中增加一行
query_cache_type=2
重启mysql服务
select count(*) from innodb; 1 row in set (1.56 sec) select count(*) from innodb; 1 row in set (0.28 sec)
没有使用sql_cache,好像仍然使用了查询缓存
select sql_cache count(*) from innodb; 1 row in set (0.28 sec)
使用sql_cache查询时间也一样,因为sql_cache只是将查询结果放入缓存,没有使用sql_cache查询也会先到查询缓存中查找数据
结论:只要query_cache_type没有关闭,sql查询总是会使用查询缓存,如果缓存没有命中则开始查询的执行计划到表中查询数据。
query cache优缺点
优点很明显,对于一些频繁select query,mysql直接从cache中返回相应的结果集,而不用再从表table中取出,减少了IO开销。
即使query cache的收益很明显,但是也不能忽略它所带来的一些缺点:
query语句的hash计算和hash查找带来的资源消耗。mysql会对每条接收到的select类型的query进行hash计算然后查找该query的cache是否存在,虽然hash计算和查找的效率已经足够高了,一条query所带来的消耗可以忽略,但一旦涉及到高并发,有成千上万条query时,hash计算和查找所带来的开销就的重视了;
query cache的失效问题。如果表变更比较频繁,则会造成query cache的失效率非常高。表变更不仅仅指表中的数据发生变化,还包括结构或者索引的任何变化;
对于不同sql但同一结果集的query都会被缓存,这样便会造成内存资源的过渡消耗。sql的字符大小写、空格或者注释的不同,缓存都是认为是不同的sql(因为他们的hash值会不同);
相关参数设置不合理会造成大量内存碎片,相关的参数设置会稍后介绍。
合理利用query cache
query cache有利有弊,合理的使用query cache可以使其发挥优势,并且有效的避开其劣势。
并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更,那么就应该避免在变化频繁的table上使用query cache。mysql中针对query cache有两个专用的sql hint:SQL_NO_CACHE和SQL_CACHE,分别表示强制不使用和强制使用query cache,通过强制不使用query cache,可以让mysql在频繁变化的表上不使用query cache,这样减少了内存开销,也减少了hash计算和查找的开销;
更多有关query cache详情文章,请看这里的原文:mysql query cache优化
query_cache_size:指定用于缓存查询结果集的内存区大小,该参数值应为 1024 的整数倍。
这个参数不能太大,也不能太小,查询缓存至少会需要 40KB 的空间分配给其自身结构,太小时缓存结果集就没有意义,热点数据保存不了多少,而且总是很快就被刷新出去;但也不能太大,否则可能过多占用内存资源,影响整机性能,再说太大也没有意义,因为即便数据不被刷新,但只要源数据发生变更,缓存中的数据也就自动失效了,这种情况下分配多大都没有意义
。个人建议设置不要超过 256MB
。
query_cache_limit:用来控制查询缓存,能够缓存的单条 SQL 语句生成的最大结果集,默认是 1MB,超出的就不要进入查询缓存。这个大小对于很多场景都够了,缩小可以考虑,加大就不用了。
sort_buffer_size:指定单个会话能够使用的排序区的大小,默认值为 256KB,建议设置为 1~4MB
之间。
read_buffer_size:指定随机读取时的数据缓存区大小,默认是 256KB,最大能够支持4GB,适当加大本参数,对于提升全表扫描的效率会有帮助。
innodb_buffer_pool_size:指定InnoDB引擎专用的缓存区大小,用来缓存表对象的数据及索引信息,默认值为 128MB,最大能够支持(2^64 -1)B.
如果你有很多事务的更新,插入或删除很操作,通过修改innodb_buffer_pool 大小这个参数会大量的节省了磁盘I / O
。
innodb_buffer_pool_size 是个全局参数,其所分配的缓存区将供所有被访问到的InnoDb表对象使用,若MySQL数据库中的表对象以 InnoDb 为主,那么本参数的值就越大越好,官方文档中建议,可以将该参数设置为服务器物理内存的70%~80%
。
innodb_buffer_instances:指定 InnoDB 缓存池分为多少个区域来使用,值得范围从 1~64,默认值为-1,表示由 InnoDB 自行调整。
只有当innodb_buffer_pool_size参数值大于1GB时,本参数才有效,那么本参数怎么设置呢?个人感觉可以参照 InnoDB 缓存池的大小,以 GB 为单位,每GB指定一个instances
。例如当innodb_buffer_pool_size设置为16GB时,则指定 innodb_buffer_instances 设置为 16 即可。
测试服务器有 16GB的物理内存,假定其峰值最大的连接数为 500 个,表对象使用InnoDB 存储引擎,我们的内存参数如何配置呢?
具体配置如下:
(1)、首先,为操作系统预留 20% 的内存,约为 3GB。
(2)、与线程相关的几个关键参数设置如下:
sort_buffer_size=2m read_buffer_size=2m read_rnd_buffer_size=2m join_buffer_size=2m
预计连接数达到峰值时,线程预计最大将有可能占用 500 *(2+2+2+2)= 4GB
内存(理论最大值)。
(3)、剩下的空间 16-3-4=9GB,就可以全部都分配给InnoDB 的缓存池,设定相关的参数如下:
innodb_buffer_pool_size=9g innodb_thread_concurrency=8 innodb_flush_method=O_DIRECT innodb_log_buffer_size=16m innodb_flush_log_at_trx_commit=2
想要了解MySQL服务当前在做什么,有个非常重要并且极为常用的命令:
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST
命令将每一个连接的线程,作为一条独立的记录输出。
还有相似的语句,
SHOW PROFILES 和 SHOW PROFILE可以获取会话执行语句过程中,资源的使用情况。
위 내용은 MySQL 성능 튜닝 및 테스트 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!