巡 检 人：小麦苗([QQ：646634621] [微信公众号：DB宝] [提供OCP、OCM、高可用、OceanBase和PostgreSQL最实用的培训])
巡检时间：2021-11-03 14:35:48
版 本 号：v7.0.0
修改日期：2021-10-31 18:28:00

[转到页底]

● 版本信息

VARIABLE_NAME	VARIABLE_VALUE
version_comment	OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
version	3.1.1
version_compile_machine
version_compile_os

● 检查集群状态

cluster_id	cluster_name	created	cluster_role	cluster_status	switchover#	switchover_status	switchover_info	current_scn	standby_became_primary_scn	primary_cluster_id	protection_mode	protection_level	redo_transport_options
1	lhrob311cluster	2021-11-03 10:06:29.087800	PRIMARY	VALID	0	NOT ALLOWED		1635921352116588	0		MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE

● 检查所有服务器的状态

gmt_create	gmt_modified	svr_ip	svr_port	id	zone	inner_port	with_rootserver	status	block_migrate_in_time	build_version	stop_time	start_service_time	first_sessid	with_partition	last_offline_time
2021-11-03 10:07:29.148223	2021-11-03 14:35:32.437762	172.72.8.11	2882	1	zone1	2881	1	active	0	3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14)	0	2021-11-03 10:42:42.335045	0	1	0
2021-11-03 10:07:28.665950	2021-11-03 14:35:32.434561	172.72.8.12	2882	2	zone2	2881	0	active	0	3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14)	0	2021-11-03 10:42:43.335302	0	1	0
2021-11-03 10:07:28.675445	2021-11-03 14:35:32.403304	172.72.8.13	2882	3	zone3	2881	0	active	0	3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14)	0	2021-11-03 10:42:42.334795	0	1	0

● 查看集群中的整体资源分配情况

zone	observer	cpu_summary	mem_summary_gb	disk_summary_gb	c_weight	m_weight	d_weight	unit_num
zone1	172.72.8.11:2882	2.5 : 10	1.5000 : 6.0000	2.0000 : 2.0000	0.50	0.50	0.00	1
zone2	172.72.8.12:2882	2.5 : 10	1.5000 : 6.0000	2.0000 : 2.0000	0.50	0.50	0.00	1
zone3	172.72.8.13:2882	2.5 : 10	1.5000 : 6.0000	2.0000 : 2.0000	0.50	0.50	0.00	1

● 资源分配概况

pool_name	unit_id	unit_config_name	svr_ip	status	min_cpu	max_cpu	min_memory_gb	max_memory_gb
sys_pool	1	sys_unit_config	172.72.8.11	ACTIVE	2.5	5	1.5000	1.8000
sys_pool	2	sys_unit_config	172.72.8.12	ACTIVE	2.5	5	1.5000	1.8000
sys_pool	3	sys_unit_config	172.72.8.13	ACTIVE	2.5	5	1.5000	1.8000

● 查看系统中定义的resource unit规格

gmt_create	gmt_modified	unit_config_id	name	max_cpu	min_cpu	max_memory	min_memory	max_iops	min_iops	max_disk_size	max_session_num
2021-11-03 10:10:17.855398	2021-11-03 10:10:17.855398	1	sys_unit_config	5	2.5	1932735283	1610612736	10000	5000	2147483648	9223372036854775807

● 查看系统中已经分配的resource unit

gmt_create	gmt_modified	unit_id	resource_pool_id	group_id	zone	svr_ip	svr_port	migrate_from_svr_ip	migrate_from_svr_port	manual_migrate	status	replica_type
2021-11-03 10:10:18.035771	2021-11-03 10:10:18.035771	1	1	0	zone1	172.72.8.11	2882		0	0	ACTIVE	0
2021-11-03 10:10:18.115743	2021-11-03 10:10:18.115743	2	1	0	zone2	172.72.8.12	2882		0	0	ACTIVE	0
2021-11-03 10:10:18.194571	2021-11-03 10:10:18.194571	3	1	0	zone3	172.72.8.13	2882		0	0	ACTIVE	0

● 将已经分配的resource unit和具体的租户对应起来

gmt_create	gmt_modified	resource_pool_id	name	unit_count	unit_config_id	zone_list	tenant_id	replica_type	is_tenant_sys_pool
2021-11-03 10:10:17.941977	2021-11-03 10:10:18.282057	1	sys_pool	1	1	zone1;zone2;zone3	1	0	0

● 查看memstore的使用情况

● 查看非memstore内存分类情况,按租户区分

Zone	svr_ip	tenant_name	hold_gb	used_gb	used_percent	alloc_count	used_count	free_count
zone1	172.72.8.11	tenant 500	6.3919	6.1687	96.5075	0	104417	0
zone3	172.72.8.13	tenant 500	6.1972	5.9765	96.4387	0	102879	0
zone2	172.72.8.12	tenant 500	6.0656	5.8500	96.4449	0	94921	0
zone2	172.72.8.12	sys	0.4889	0.4836	98.9232	0	38261	0
zone1	172.72.8.11	sys	0.3796	0.3746	98.6877	0	36991	0
zone3	172.72.8.13	sys	0.3780	0.3721	98.4537	0	38091	0
zone1	172.72.8.11	tenant 999	0.0329	0.0329	99.9542	0	33	0
zone2	172.72.8.12	tenant 999	0.0329	0.0329	99.9542	0	33	0
zone3	172.72.8.13	tenant 999	0.0329	0.0329	99.9542	0	33	0
zone1	172.72.8.11	tenant 501	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 504	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 512	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 505	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 510	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 506	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 507	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 502	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 508	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 503	0.0002	0.0002	98.9474	0	30	0
zone1	172.72.8.11	tenant 509	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 507	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 501	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 506	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 503	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 510	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 508	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 504	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 502	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 509	0.0002	0.0002	98.9474	0	30	0
zone2	172.72.8.12	tenant 505	0.0002	0.0002	98.9474	0	30	0

● 查看非memstore内存分类情况,按租户和模块(mod_name)区分

Zone	svr_ip	tenant_name	mod_name	hold_gb	used_gb	used_percent	alloc_count	used_count	free_count
zone1	172.72.8.11	tenant 500	CO_STACK	1.5930	1.5930	100.0000	0	1	0
zone2	172.72.8.12	tenant 500	CO_STACK	1.5901	1.5901	100.0000	0	1	0
zone3	172.72.8.13	tenant 500	CO_STACK	1.5901	1.5901	100.0000	0	1	0
zone1	172.72.8.11	tenant 500	CallbackTask	0.8984	0.8941	99.5138	0	4	0
zone2	172.72.8.12	tenant 500	CallbackTask	0.8984	0.8941	99.5138	0	4	0
zone3	172.72.8.13	tenant 500	CallbackTask	0.8984	0.8941	99.5138	0	4	0
zone1	172.72.8.11	tenant 500	glibc_malloc	0.5265	0.5132	97.4779	0	28533	0
zone3	172.72.8.13	tenant 500	glibc_malloc	0.5111	0.4986	97.5564	0	28537	0
zone2	172.72.8.12	tenant 500	glibc_malloc	0.5110	0.4986	97.5569	0	28496	0
zone1	172.72.8.11	tenant 500	LinearHashMap	0.3361	0.3355	99.8267	0	8805	0
zone2	172.72.8.12	tenant 500	LinearHashMap	0.3096	0.3091	99.8499	0	6875	0
zone2	172.72.8.12	sys	OB_MEMSTORE	0.3066	0.3065	99.9790	0	157	0
zone3	172.72.8.13	tenant 500	LinearHashMap	0.2882	0.2877	99.8353	0	7124	0
zone1	172.72.8.11	tenant 500	OB_KVSTORE_CACHE	0.2660	0.2598	97.6466	0	12	0
zone2	172.72.8.12	tenant 500	OB_KVSTORE_CACHE	0.2660	0.2598	97.6466	0	12	0
zone3	172.72.8.13	tenant 500	OB_KVSTORE_CACHE	0.2660	0.2598	97.6466	0	12	0
zone3	172.72.8.13	sys	OB_MEMSTORE	0.1699	0.1698	99.9790	0	87	0
zone3	172.72.8.13	tenant 500	ConcurObjPool	0.1681	0.1663	98.8993	0	1468	0
zone1	172.72.8.11	tenant 500	ConcurObjPool	0.1678	0.1660	98.8995	0	1438	0
zone1	172.72.8.11	sys	OB_MEMSTORE	0.1640	0.1640	99.9790	0	84	0
zone2	172.72.8.12	tenant 500	ConcurObjPool	0.1599	0.1582	98.9169	0	1003	0
zone1	172.72.8.11	tenant 500	IlogMemstoCurso	0.1328	0.1328	99.9531	0	583	0
zone1	172.72.8.11	tenant 500	FixeSizeBlocAll	0.1328	0.1250	94.1180	0	5	0
zone2	172.72.8.12	tenant 500	FixeSizeBlocAll	0.1328	0.1250	94.1180	0	5	0
zone3	172.72.8.13	tenant 500	FixeSizeBlocAll	0.1328	0.1250	94.1180	0	5	0
zone1	172.72.8.11	tenant 500	LogHotCache	0.1289	0.1250	96.9697	0	2	0
zone2	172.72.8.12	tenant 500	LogHotCache	0.1289	0.1250	96.9697	0	2	0
zone3	172.72.8.13	tenant 500	LogHotCache	0.1289	0.1250	96.9697	0	2	0
zone1	172.72.8.11	tenant 500	ClogMgr	0.1192	0.1172	98.3593	0	5	0
zone2	172.72.8.12	tenant 500	ClogMgr	0.1192	0.1172	98.3593	0	5	0

● 统计每台机器上数据盘的使用情况

svr_ip	total_gb	used_gb
172.72.8.11	2.0000	0.1914
172.72.8.12	2.0000	0.1289
172.72.8.13	2.0000	0.1836

● 统计每个zone里数据盘的使用情况

zone	total_gb	used_gb
zone1	2.0000	0.1914
zone3	2.0000	0.1836
zone2	2.0000	0.1289

● 检查集群合并的状态

gmt_create	gmt_modified	zone	name	value	info
2021-11-03 10:10:19.190939	2021-11-03 10:10:19.190939		merge_status	0	IDLE
2021-11-03 10:10:19.194123	2021-11-03 10:10:19.194123	zone1	merge_status	0	IDLE
2021-11-03 10:10:19.197273	2021-11-03 10:10:19.197273	zone2	merge_status	0	IDLE
2021-11-03 10:10:19.199384	2021-11-03 10:10:19.199384	zone3	merge_status	0	IDLE

● 查询所有租户

gmt_create	gmt_modified	tenant_id	tenant_name	replica_num	zone_list	primary_zone	locked	collation_type	info	read_only	rewrite_merge_version	locality	logonly_replica_num	previous_locality	storage_format_version	storage_format_work_version	default_tablegroup_id	compatibility_mode	drop_tenant_time	status	in_recyclebin
2021-11-03 10:10:18.606549	2021-11-03 10:10:18.606549	1	sys	-1	zone1;zone2;zone3	zone1;zone2,zone3	0	0	system tenant	0	0	FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3	0		0	0	-1	0	-1	TENANT_STATUS_NORMAL	0

● 查询所有用户

Host	User	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin
%	root	Y	Y	Y	Y	Y	Y	N	N	Y	Y	Y	Y	Y	Y	Y	N	N	N	N	N	Y	Y	N	N	Y	N	N	N					0	0	0	0	ob_native_password
%	ORAAUDITOR	Y	Y	Y	Y	Y	Y	N	N	Y	Y	Y	Y	Y	Y	Y	N	N	N	N	N	Y	Y	N	N	Y	N	N	N					0	0	0	0	ob_native_password
%	proxyro	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0	ob_native_password

数据库总体概况

● 数据库基本信息

now_date	user	CURRENT_USER1	CONNECTION_ID	db_name	Server_version	autocommit
2021-11-03 14:35:55	root@192.168.66.64	root@%	3221727675		3.1.1	1

● 当前数据库实例的所有数据库及其容量大小

● 数据库对象

● 查看数据库的运行状态

-------------- mysql Ver 14.14 Distrib 5.7.30, for Win64 (x86_64) Connection id: 89 Current database: Current user: root@192.168.66.64 SSL: Not in use Using delimiter: ; Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14) Protocol version: 10 Connection: 192.168.66.35 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 28814 Active --------------

● 占用空间最大的前10张大表

db_name	table_name	TABLE_TYPE	ENGINE	CREATE_TIME	UPDATE_TIME	TABLE_COLLATION	table_rows	tb_size_mb	index_size_mb	all_size_mb	free_size_mb	disk_size_mb
oceanbase	__all_database	BASE TABLE		2021-11-03 10:09:45.724008	2021-11-03 10:09:45.724008	utf8mb4_general_ci
oceanbase	__all_table_privilege	BASE TABLE		2021-11-03 10:09:45.849708	2021-11-03 10:09:45.849708	utf8mb4_general_ci
oceanbase	__all_user_history	BASE TABLE		2021-11-03 10:09:45.683787	2021-11-03 10:09:45.683787	utf8mb4_general_ci
oceanbase	__all_tablegroup_history	BASE TABLE		2021-11-03 10:09:45.772828	2021-11-03 10:09:45.772828	utf8mb4_general_ci
oceanbase	__all_tablegroup	BASE TABLE		2021-11-03 10:09:45.754526	2021-11-03 10:09:45.754526	utf8mb4_general_ci
oceanbase	__all_tenant_history	BASE TABLE		2021-11-03 10:09:45.821018	2021-11-03 10:09:45.821018	utf8mb4_general_ci
oceanbase	__all_tenant	BASE TABLE		2021-11-03 10:09:45.794295	2021-11-03 10:09:45.794295	utf8mb4_general_ci
oceanbase	__all_database_history	BASE TABLE		2021-11-03 10:09:45.739541	2021-11-03 10:09:45.739541	utf8mb4_general_ci
oceanbase	__all_meta_table	BASE TABLE		2021-11-03 10:09:45.624036	2021-11-03 10:09:45.624036	utf8mb4_general_ci
oceanbase	__all_user	BASE TABLE		2021-11-03 10:09:45.655829	2021-11-03 10:09:45.655829	utf8mb4_general_ci

● 占用空间最大的前10个索引

● 所有存储引擎列表

ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
OceanBase	YES	Supports transactions	YES	NO	NO

● 存储引擎和DB的数量关系

ENGINE	counts
	993

TABLE_SCHEMA	ENGINE	counts
oceanbase		574
information_schema		28
mysql		7
SYS		383
test		1

● 查询OceanBase支持的所有字符集

CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
binary	binary	Binary pseudo charset	1
utf8mb4	utf8mb4_general_ci	UTF-8 Unicode	4

● 一些重要的参数

VARIABLE_NAME	VARIABLE_VALUE
autocommit	ON
sql_mode	STRICT_ALL_TABLES
time_zone	+8:00
tx_isolation	READ-COMMITTED
datadir	/usr/local/mysql/data/
lower_case_table_names	2
read_only	OFF
max_user_connections	0
query_cache_size	1048576
query_cache_type	OFF
long_query_time	10
max_connections	4294967295
server_id	0

● 查看当前连接到数据库的用户和Host

USER	HOST
root	172.72.8.14:45358
root	172.72.8.14:45396
root	172.72.8.14:60862
root	172.72.8.14:60816
root	172.72.8.14:60136
proxyro	172.72.8.14:60048

● 按照登录用户+登录服务器查看登录信息

login_user	login_ip	login_count
root	172.72.8.14	5
proxyro	172.72.8.14	1

● 按照登录用户+数据库+登录服务器查看登录信息

database_name	login_user	login_ip	login_count
oceanbase	root	172.72.8.14	2
NULL	root	172.72.8.14	3
oceanbase	proxyro	172.72.8.14	1

锁情况

● 查询所有线程（排除sleep线程）

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO

● sleep线程TOP20

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
3221749986	root	172.72.8.14:60816	oceanbase	Sleep	7488	SLEEP
3221605123	root	172.72.8.14:45358	oceanbase	Sleep	7241	SLEEP
3222248764	root	172.72.8.14:60136	NULL	Sleep	1	SLEEP
3222078881	proxyro	172.72.8.14:60048	oceanbase	Sleep	1	SLEEP
3221750042	root	172.72.8.14:60862	NULL	Sleep	0	SLEEP

SQL部分

索引部分

数据库性能

● 性能参数统计
Uptime 14192

其它

● 无主键或唯一键的表（前100条）

table_schema	table_name
test	test

● 自增ID的使用情况（前20条）

table_schema	table_name	engine	Auto_increment