mysql&pgsql&db2数据库监控大锦集

强烈推荐大家看的时候直接打开实验楼里对应的课程环境【https://www.shiyanlou.com/courses/9】，跟着敲一遍，基本就会了，也就20分钟左右就能记个大概了。

本课程的主旨及目标

•简单的数据库介绍

•常用的监控命令、定位方法

简单的数据库介绍

这部分会简单的介绍常用的数据库

1、mysql

2、pgsql

3、db2

4、oracle

这些基本上能够覆盖到我们最常用的数据库了

后面如果有需要还会继续的补充其他的数据库进来

mysql简介：

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 

MySQL 是开源的，所以你不需要支付额外的费用。

MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL 使用标准的 SQL 数据语言形式。

MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。

MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。

MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。

MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

 

 

PostgreSQL简介：

PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统)，PostgreSQL 是全功能的自由软件数据库，很长时间以来，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统（MVCC）、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。

它包括了可以说是目前世界上最丰富的数据类型的支持，其中有些数据类型可以说连商业数据库都不具备， 比如 IP 类型和几何类型等.

PostgreSQL的优势：
1.开源免费，完全可以做到自主可控
2.最丰富数据类型（空间、几何、网络、jsonb）
3.CBO优化器，支持复杂连接，递归查询
4.支持自定义类型，自定义操作符，方法重载，OO特性
5.支持分区表、物化视图、触发器、函数
6.丰富的语言集成（pl/pgsql, java, c, python, perl, tcl)
7.丰富的索引类型，部分索引
8.全文检索，正则表达式

DB2简介：

IBM DB2 是美国IBM公司开发的一套关系型数据库管理系统，它主要的运行环境为UNIX（包括IBM自家的AIX）、Linux、IBM i（旧称OS/400）、z/OS，以及Windows服务器版本。

DB2主要应用于大型应用系统，具有较好的可伸缩性，可支持从大型机到单用户环境，应用于所有常见的服务器操作系统平台下。 DB2提供了高层次的数据利用性、完整性、安全性、可恢复性，以及小规模到大规模应用程序的执行能力，具有与平台无关的基本功能和SQL命令。DB2采用了数据分级技术，能够使大型机数据很方便地下载到LAN数据库服务器，使得客户机/服务器用户和基于LAN的应用程序可以访问大型机数据，并使数据库本地化及远程连接透明化。

DB2以拥有一个非常完备的查询优化器而著称，其外部连接改善了查询性能，并支持多任务并行查询。 DB2具有很好的网络支持能力，每个子系统可以连接十几万个分布式用户，可同时激活上千个活动线程，对大型分布式应用系统尤为适用。

 

Oracle简介：

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。

优点：

■ 可用性强

■ 可扩展性强

■ 数据安全性强

■ 稳定性强

MYSQL常用的监控命令、定位方法

查看表是否被锁：

直接在mysql命令行执行:

show engine innodb status\G;

查看造成死锁的sql语句，分析索引情况，然后优化sql.

然后show processlist,查看造成死锁占用时间长的sql语句。

show status like '%lock%‘

 

查看表被锁状态和结束死锁步骤：

1.查看表被锁状态

show OPEN TABLES where In_use > 0;//这个语句记录当前锁表状态

2.查询进程

show processlist //查询表被锁进程

查询到相应进程kill id

3.分析锁表的SQL

分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引

 

查看正在锁的事物：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事物：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

mysql查看执行sql语句的记录日志

show processlist;

select * from information_schema.`PROCESSLIST` where info is not null;

查看当前连接数

show status like 'thread%'

1、查看最大连接数

show variables like '%max_connections%';

2、修改最大连接数

set GLOBAL max_connections = 200;

 

PGSQL常用的监控命令、定位方法

查表的信息：SELECT * from pg_dist_shard;

#查看分片数

show citus.shard_count;

#修改分片数

alter system set citus.shard_count=xxxx;

select pg_reload_conf();

1.1.11  重置计数器

select rds.pg_stat_statements_reset();--重置计数器

select pg_stat_statements_reset();--重置计数器

create extension pg_stat_statements;--创建

整个实例的所有sql都会统计，每次查询都是从上次清理至今的一个sql执行情况

cn和worker是独立的，最好都清理一下，有些sql在cn上看不到，因为真正业务是在worker

1.1.12  连接数监控

SQL客户端：

select count(*) from pg_stat_activity where state not like '%idle';

这个在worker上执行，看活动连接数量连接的执行sql和执行时间，包括连接状态，还有CPU和IO

select count(*) from pg_stat_activity where state = 'active';

后台服务器：

su – postgres

pg_activity -h127.0.0.1 -Uadmin -d lobadbw

pg_activity可以看连接的执行sql和执行时间，包括连接状态，还有CPU和IO

1.1.13  pgb连接数监控

psql "service=admin dbname=pgbouncer port=60001" -c "show lists"|grep -i used_servers

psql "service=admin dbname=pgbouncer port=60002" -c "show lists"|grep -i used_servers

psql "service=admin dbname=pgbouncer port=60003" -c "show lists"|grep -i used_servers

psql "service=admin dbname=pgbouncer port=60004" -c "show lists"|grep -i used_servers

到cn的postgres用户下执行

慢SQL:

select * from pg_stat_statements order by total_time desc limit 5;

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5; 

select queryid,calls,total_time,mean_time,max_time,rows,shared_blks_hit,shared_blks_read,substring(regexp_replace(query,E'[\n\r \t]*\n[\n\r\t ]*',' ','g'),1,2055)

    from rds.pg_stat_statements

    order by total_time      desc limit 50;

 

慢SQL日志路径

/pgsql/data/log/postgresql.log

1.1.16  解决慢SQL方法

对于上面的方法查出来的慢SQL，首先需要做的可能是Cancel或Kill掉他们，使业务先恢复：

select pg_cancel_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();

select pg_terminate_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();

如果这些SQL确实是业务上必需的，则需要对他们做优化。这方面有“三板斧”：

1、对查询涉及的表，执行 ANALYZE <table>或VACUUM ANZLYZE <table>，更新表的统计信息，使查询计划更准确。注意，为避免对业务影响，最好在业务低峰执行。

2、执行explain (query text)或explain (buffers true, analyze true, verbose true) (query text)命令，查看SQL的执行计划（注意，前者不会实际执行SQL，后者会实际执行而且能得到详细的执行信息），对其中的Table Scan涉及的表，建立索引。

3、重新编写SQL，去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等到，都是进一步深度优化SQL的手段，这里不再深入说明。

 

DB2常用的监控命令、定位方法

使用DB2数据库监控，监控前先切换到响应的用户

Db2top连接到数据库：

Db2top –d <dbname>

db2top -d zpsrun      或db2top -d 库名 -u 用户名 -p 密码

Db2top运行时的键盘命令：

一般的监控流程：

1】切换用户（su - user），登录连接数据库(db2top -d 数据库名)会进入这个界面

2】按键盘D(注意这里是大写的D)，会进入下个界面

 3】按键盘键 z（从高到底排序）/*Z（从低到高排序）*/（这里还是要注意大小写），这时候屏幕会出现Column number for descending sort:来提示你输入按哪一列来进行排序（从0开始数），我们一般是按平均时间来抓一段时间内的慢sql，所以这里输入4（按Avg ExecTime排序) 平均执行时间单位是“秒”，然后enter回车就能监控sql执行时间由高到低的动态展示了

4】当我们发现慢sql了，想去copy的时候，需要按键Shift+L，这时候屏幕会出现Enter SQL hash string:来提示你输入慢sql对应的HashValue的值（也就是上面图中0列的值），按enter回车，这时会弹出Query text中的sql就可以复制了