mysql数据库性能跟哪些参数有关系_MySQL数据库各项性能详细参数查看脚本

最新推荐文章于 2023-06-29 20:30:34 发布
最新推荐文章于 2023-06-29 20:30:34 发布
阅读量167 收藏
点赞数
文章标签： mysql数据库性能跟哪些参数有关系
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33817140/article/details/113457017
版权

本脚本主要用于查看MySQL的各项性能参数，以及推荐性能指标，脚本在附件中

运行结果：

[root@cacti ~]# Python mysqlpulse.py

Host[localhost]:

User[root]:

Password root@localhost:

database[mysql]:

mysqlpulse.py:323: Warning: The foreign data source you are trying to reference does not exist. Data source error:  error: 1054  'Unknown column 'alias' in 'field list''

cursor.execute(sql)

mysqlpulse.py:323: Warning: The foreign data source you are trying to reference does not exist. Data source error:  error: 1146  'Table 'center_app.middleware' doesn't exist'

cursor.execute(sql)

=============MySQL info=============

Connection id        : 207464

Current database     : mysql

Current user         : root@localhost

SSL                  : DISABLED

MySQL VERSION        : 5.1.59-log MySQL Community Server (GPL)

MySQL client info    : 5.1.59

Protocol version     : 10

Connection           : Localhost via UNIX socket

Server characterset  : latin1

Db     characterset  : latin1

Client characterset  : latin1

Conn.  characterset  : latin1

collation_connection : latin1_swedish_ci

collation_database   : latin1_swedish_ci

collation_server     : latin1_swedish_ci

Uptime               : 7240897s

============processlist=============

processlist rowcount =27

status time>0 threads list:

Id User  Host   db  Command  Time  State Info

status time>0 threads count=0

=========MySQL status pulse=========

-----------------------------------------------------------------------------------------------------------------------------

Reads/Writes status                            total                        current                        proposal

-----------------------------------------------------------------------------------------------------------------------------

Reads:Writes ratio(Reads/Writes)          : 1.28:1 (652.14M/511.02M)       1:22.68 (182.00/4.03k)

QPS(Questions/Uptime)                     : 173.07/s (1.17G/6.91M)         73.87/s (4.33k/60.00)

TPS(Questions/Uptime)                     : 2.27/s (15.69M/6.91M)          0.98/s (59.00/60.00)

Table locks waited ratio(waited/immediate): 1:5.22k (111.33k/580.78M)      0:1 (0.00/4.23k)               0:1

select per second(select/Uptime)          : 94.44/s (652.14M/6.91M)        3.03/s (182.00/60.00)

insert per second(insert/Uptime)          : 2.75/s (19.00M/6.91M)          0.90/s (54.00/60.00)

update per second(update/Uptime)          : 70.10/s (484.09M/6.91M)        67.90/s (3.98k/60.00)

delete per second(delete/Uptime)          : 1.13/s (7.81M/6.91M)           0.00/s (0.00/60.00)

replace per second(replace/Uptime)        : 0.02/s (121.14k/6.91M)         0.00/s (0.00/60.00)

Bytes sent per second(sent/Uptime)        : 191.95k/s (1.29T/6.91M)        13.66k/s (819.64k/60.00)

Bytes received per second(re/Uptime)      : 45.39k/s (313.44G/6.91M)       16.67k/s (1000.48k/60.00)

------------------------------------

Slow and Sort queries status

------------------------------------

Slow queries Ratio(Slow/Questions)        : 0.67% (8.01M/1.17G)            0.25% (11.00/4.33k)            Lower than 0

Slow queries PS(Slow/Uptime)              : 1.16/s (8.01M/6.91M)           0.18/s (11.00/60.00)           Lower than 0

Full join PS(full/Uptime)                 : 0.00/s (260.00/6.91M)          0.00/s (0.00/60.00)            Lower than 0

Sort merge passes PS(merge/Uptime)        : 0.00/s (1.00/6.91M)            0.00/s (0.00/60.00)            Lower than 0

Sort range PS(range/Uptime)               : 0.17/s (1.16M/6.91M)           0.05/s (3.00/60.00)            Lower than 0

Sort rows PS(rows/Uptime)                 : 20.56/s (142.01M/6.91M)        12.38/s (743.00/60.00)         Lower than 0

Sort scan PS(scan/Uptime)                 : 0.38/s (2.64M/6.91M)           0.28/s (17.00/60.00)           Lower than 0

------------------------------------

connections status

------------------------------------

Thread cache hits(created/Total)          : 99.88% (249.00/202.60k)        100.00% (0.00/1.00)            Higher than 0

Connections used ratio(Max used/Max)      : 25.17% (38.00/151.00)          0.00% (0.00/151.00)            Lower than 90

Aborted connects ratio(Aborted/Max)       : 168.21% (254.00/151.00)        0.00% (0.00/151.00)            Lower than 0

Threads running                           : 1.00

------------------------------------

temp table and Open tables/files status

------------------------------------

Temp tables to Disk ratio(disk/tmp)       : 3.21% (90.65k/2.75M)           0.00% (0.00/18.00)             Lower than 0

Open tables/table open cache/Opened tables: (246.00/512.00/41.50k)         (0.00/512.00/0.00)             1:1:1

Opened files PS(Opened/Uptime)            : 0.07/s (506.48k/6.91M)         0.00/s (0.00/60.00)            Lower than 0

------------------------------------

InnoDB Status

------------------------------------

Innodb buffer read hits(Disk/total)       : 99.91% (2.65M/2.74G)           100.00% (0.00/157.28k)         Higher than 99.99

Innodb dblwr pages written:dblwr writes   : 73.42:1 (144.62M/1.97M)                                       Lower than 64:1

Innodb buffer pages used ratio(free/total): 100.00% (0.00/24.00k)                                         Lower than 99.99

Innodb buffer pages dirty ratio(dirty)    : 3.78% (928.00/24.00k)

Innodb buffer pages flushed PS(flushed)   : 20.94/s (144.62M/6.91M)        11.90/s (714.00/60.00)

Innodb buffer pool pages misc             : 325.00

Innodb row lock waits PS(waits/Uptime)    : 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)

Innodb row lock current waits             : 0.00                           0.00

Innodb row lock time avg                  : 443.00                         0.00

Innodb row lock time max                  : 50.09k                         0.00

Innodb row lock time total                : 10.86M                         0.00

Innodb rows read PS(read/Uptime)          : 466.70/s (3.15G/6.91M)         626.40/s (36.70k/60.00)

Innodb rows inserted PS(inserted/Uptime)  : 246.39/s (1.66G/6.91M)         135.67/s (7.95k/60.00)

Innodb rows updated PS(updated/Uptime)    : 69.96/s (483.08M/6.91M)        67.90/s (3.98k/60.00)

Innodb data reads PS(reads/Uptime)        : 0.50/s (3.46M/6.91M)           0.00/s (0.00/60.00)

Innodb data writes PS(writes/Uptime)      : 9.54/s (65.89M/6.91M)          6.12/s (367.00/60.00)

Innodb data fsyncs PS(fsyncs/Uptime)      : 1.15/s (7.91M/6.91M)           0.87/s (52.00/60.00)

Innodb data pending reads PS(reads/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)

Innodb data pending writes PS(write/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)

Innodb data pending fsyncs PS(fsync/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)

------------------------------------

key buffer Status

------------------------------------

key buffer used ratio(used/size)          : 0.05% (185.00k/384.00M)                                       Lower than 99.99

key buffer read hit ratio(reads/request)  : 100.00% (183.00/208.97M)       100.00% (0.00/0.00)            Higher than 99.99

key buffer write hit ratio(writes/request): 81.37% (8.95M/48.03M)          100.00% (0.00/0.00)            Higher than 99.99

------------------------------------

Qcache Status

------------------------------------

Qcache queries hits ratio(hits/reads)     : 93.45% (609.41M/652.14M)       42.31% (77.00/182.00)          Higher than 30.00

Qcache hits inserts ratio(hits/inserts)   : 1.59k% (609.41M/37.40M)        101.32% (77.00/76.00)          Higher than 300.00

Qcache memory used ratio(free/total)      : 54.62% (7.26M/16.00M)

Qcache prune ratio(prunes/inserts)        : 5.31% (1.99M/37.40M)           0.00% (0.00/76.00)

Qcache block Fragmnt ratio(free/total)    : 23.65% (2.75k/11.63k)

------------------------------------

Slave Status

------------------------------------

Slave running status                      : OFF                            OFF

Slave open temp tables                    : 0.00                           0.00

Slave transactions PS(transactions/Uptime): 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)

Slave received PS(heartbeats/Uptime)      : 0.00/s (0.00/6.91M)            0.00/s (0.00/60.00)

脚本附件下载

具体下载目录在 /2012年资料/8月/10日/MySQL数据库各项性能详细参数查看脚本/0b1331709591d260c1c78e86d0c51c18.png

历史阿牛
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MYSQL— perror 错误码详情
道行尚浅（老道）的博客
12-04 11万+
[root@localhost ~]# cat test_nothread.py import paramiko import threading import os def ssh2(ip,username,passwd,cmd): file_path='/root/perror.log' logfile=file(file_path,'a') try:
MYSQL使用FEDERATED远程同步表
博客
03-19 1034
CREATE TABLE `tb1` ( `year` year(4) DEFAULT NULL, `month` int(10) unsigned DEFAULT NULL, `day` int(10) unsigned DEFAULT NULL ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_bin CONNECTI...
mysql数据库实例参数对比脚本_mysql数据库各项性能详细参数查看脚本
weixin_31855625的博客
02-02 325
本脚本主要用于查看mysql的各项性能参数，以及推荐性能指标，脚本在附件中运行结果：[root@cacti ~]# python mysqlpulse.pyHost[localhost]:User[root]:Password root@localhost:database[mysql]:mysqlpulse.py:323: Warning: The foreign data source you...
Mysql 数据库相关性能参数
phzy20131004的博客
08-20 341
1、查询连接数相关配置 2、查看当前连接线程数 3、查看和修改慢查询时间阈值 ; //显示当前慢查询时间 ;//修改慢查询时间(当前会话) //（全局变量）4、 查看最近执行的语句 5、 查询是否锁表 6、显示正在运行的线程 具体字段描述：详见 show processlist详解show processlist;只列出前100条，如果想全列出请使用show full processlist;7、查看所有事务信息 查看正在锁的事务 8、杀死在锁事务对应进程 9、 查看服务器状态 10、 查看超时时
数据库报错
weixin_45953930的博客
07-01 478
一、报错：Duplicate entry ‘adad909’ for key ‘username’ 解决：添加：DROP INDEX username ON t_user;
9 关系查询及其优化.zip_MySQL数据库_Shell__MySQL数据库_Shell_
08-09
总的来说，理解和掌握关系查询优化及MySQL数据库与Shell脚本的结合使用，能够帮助数据库管理员提高工作效率，确保数据库系统的稳定性和性能。在实际工作中，我们需要不断学习和实践，以便更好地应对各种数据库管理...
shell脚本连接、读写、操作mysql数据库实例
09-10
本文将详细介绍如何使用shell脚本连接MySQL数据库，并进行读写操作。 首先，连接MySQL数据库通常使用`mysql`命令行工具。在shell脚本中，我们可以指定用户名、密码和数据库名来建立连接。例如： ```bash mysql -u ...
Linux自动备份MySQL数据库脚本代码
12-15
在脚本中可设置需要备份的数据库表清单，并且会将备份文件通过gzip压缩。需要注意的是，这段脚本仅适用数据一致性要求不高的环境。 代码如下:#!/bin/bash mysql_pwd=”password”mysql_dump=”/usr/local/mysql/bin/...
mysql数据库全量备份脚本
01-23
直接使用
mysql性能优化脚本mysqltuner.pl使用介绍
12-15
MySQLTuner 使用方法： Downloading and using MySQLTuner is actually a very simple process: wget mysqltuner.pl perl mysqltuner.pl If you’d rather not invoke perl every time, just make it executable: ...
mysql加载外部数据出错_导入外部mysql数据失败
weixin_42528780的博客
01-19 875
导入mysql数据时，提示导入数据失败，错误信息如下：java.lang.Exception: migrate table com.fr.config.entity.Entity failedat com.fr.decision.migration.manager.AbstractTransferManager.copyTable(AbstractTransferManager.java:338)...
mysql数据库各项性能详细参数查看脚本
weixin_34402408的博客
06-20 328
本脚本主要用于查看mysql的各项性能参数，以及推荐性能指标，脚本在附件中 运行结果： [root@cacti ~]# python mysqlpulse.py Host[localhost]: User[root]: Password root@localhost: database[mysql]: mysqlpulse.py:323: Warning: The foreign ...
mysql： Error Codes and Messages
weixin_34239169的博客
08-24 798
Appendix B. Error Codes and MessagesTable of ContentsB.1. Server Error Codes and MessagesB.2. Client Error Codes and MessagesThis appendix lists the errors that may appear when you call MySQL from any...
Mysql-error code汇总
热门推荐
文学超的博客
07-03 5万+
OS error code 1: Operation not permitted OS error code 2: No such file or directory OS error code 3: No such process OS error code 4: Interrupted system call OS error code 5: In
mysql截止5.7版本全部异常汇总 Error SQLSTATE 中英文对照
(๑•̀ㅂ•́)و✧ 不努力未来永远只是个梦
01-20 3459
mysql截止5.7版本全部异常代码汇总 ERROR SQLSTATE 中英文对照
fabricsharp学习
最新发布
Herbert
06-29 305
与我们的解释一致，我们在图 12 中表明，除 Focc-s 之外的所有系统的吞吐量都以相似的速率下降。正如预期的那样，依赖图上的可达性更新采用 FabricSharp 中最大比例的延迟，因为必须遍历来自传入事务的所有可达事务。然而，Focc-s 需要比 Fabric++ 长一点，因为它需要额外识别冲突的交易，而不仅仅是根据访问的记录索引事务，如 Fabric++ 所示。与我们的预期相反，Fabric++ 不会在更大的块上实现更高的吞吐量，即使有更多的事务可用于重新排序。一方面，形成块需要更长的时间。
MYSQL错误对照 消息
bcat
05-15 4730
注:SQLSTATE-error列表: 參考:http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html Error: 1000 SQLSTATE: HY000 (ER_HASHCHK) Message: hashchk Error: 1001 SQLSTATE: HY000 (ER_NISAMCHK) Messag...
ORA_ERROR大全
大白菜的姑娘
12-11 1万+
常见错误： -60 ORA00060: deadlock detected while waiting for resource 一般错误：   - 1 ORA00001: unique constraint (.) violated   -17 ORA00017: session requested to set trace event   -18 ORA00018: maximum 
[MYSQL]游標應用11/13
那一天
11-13 1万+
1.游標語法:  declare 游標名 cursor for select語句;  /*游標名不可重復,select語句不能有into  open 游標名;  fetch 游標名 into 變數1,孌數2...;  close 游標名;P.S.實例參考1   實例參考2注:SQLSTATE-error列表:參考:http://dev.mysql.com/doc/refman/5.0/
MySQL数据库详细介绍pdf（8）
02-22
MySQL数据库详细介绍pdf（8）中介绍了如何使用PHP编写基于Web的应用程序，其中使用了MySQL数据库。PHP是一种脚本语言，可以在Web页面中编写包含嵌入式代码的动态内容，这些代码在访问页面时会执行并将输出发送到Web...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值