巡 检 人：小麦苗([QQ：646634621] [微信公众号：DB宝] [提供OCP、OCM、高可用、MySQL和PostgreSQL最实用的培训]) 巡检时间：2023-03-06 14:31:06 版 本 号：v7.0.0 修改日期：2023-3-6
[转到页底]
● 数据库基本信息
| now_date | user | CURRENT_USER1 | CONNECTION_ID | db_name | Server_version | all_db_size_MB | all_datafile_size_MB | datadir | SOCKET | log_error | autocommit | log_bin | server_id |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2023-03-06 14:31:10 | root@192.168.27.230 | root@% | 74 | 8.6.2.43-R7-free.110605 | 13.19 | /home/gbase/GBase/userdata/gbase8a/ | /tmp/gbase_8a_5258.sock | /home/gbase/GBase/log/gbase8a/system.log | 1 | 0 | 0 |
● 版本信息
| VARIABLE_NAME | VARIABLE_VALUE |
|---|---|
| VERSION_COMMENT | GBase Enterprise Server - Advanced Edition (Commercial) |
| VERSION_COMPILE_MACHINE | x86_64 |
| VERSION_COMPILE_OS | unknown-linux-gnu |
● 查询当前节点cluster层磁盘空间使用信息
| HOST | DIR_TYPE | PATH | DIR_SIZE_G | FILESYTEM | SIZE_G | USED_G | AVAIL_G | PCT |
|---|---|---|---|---|---|---|---|---|
| gbase8a | datadir | /home/gbase/GBase/userdata/gbase8a/ | 0.01 | overlay | 1019 | 370 | 649 | 36% |
| gbase8a | gbase_cache_data | /home/gbase/GBase/tmpdata/cache_gbase8a | 0.00 | overlay | 1019 | 370 | 649 | 36% |
| gbase8a | logdir | /home/gbase/GBase/log/gbase8a/ | 0.00 | overlay | 1019 | 370 | 649 | 36% |
● 查询内存使用情况
| HOST | PHSICAL_MEMORY_G | SWAP_SIZE_G | PCT | UPPER_LIMIT | CURRENT_USED | MEMORY_PEAK | MEMORY_PEAK_TIMESTAMP |
|---|---|---|---|---|---|---|---|
| gbase8a | 63 | 34 | 80% | 83006704844 | 0 | 0 | 1970-01-01 08:00:00 |
● 当前数据库实例的所有数据库及其容量大小
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | table_rows | data_size_mb | index_size_mb | all_size_mb | max_size_mb | free_size_mb | disk_size_mb |
|---|---|---|---|---|---|---|---|---|---|
| sbtest | utf8 | utf8_general_ci | 27885568 | 8.56 | 0.00 | 8.56 | 0.00 | 0.00 | |
| gbase | utf8 | utf8_general_ci | 128777 | 2.33 | 2.41 | 4.75 | 2903934763007.99 | 0.00 | |
| information_schema | utf8mb4 | utf8mb4_general_ci | 0.00 | 0.00 | 0.00 | 2415919565.11 | 0.00 | ||
| performance_schema | utf8mb4 | utf8mb4_general_ci | 0.00 | 0.00 | 0.00 | 109.12 | 0.00 | ||
| lhrdb | utf8 | utf8_general_ci | |||||||
| gctmpdb | utf8 | utf8_general_ci | |||||||
| gclusterdb | utf8 | utf8_general_ci |
● 数据库对象
| db_name | ob_type | sums |
|---|---|---|
| gbase | TABLE | 40 |
| sbtest | PROCEDURE | 1 |
| sbtest | TABLE | 1 |
● 查看数据库的运行状态
| -------------- D:\db\MySQL\mysql-5.7.30-winx64\bin\mysql Ver 14.14 Distrib 5.7.30, for Win64 (x86_64) Connection id: 74 Current database: Current user: root@192.168.27.230 SSL: Not in use Using delimiter: ; Server version: 8.6.2.43-R7-free.110605 GBase Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: 172.18.0.14 via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 5258 Uptime: 1 day 22 hours 43 min 50 sec Threads: 3 Questions: 4616 Slow queries: 0 Opens: 3675 Flush tables: 1 Open tables: 63 Queries per second avg: 0.27 -------------- |
● 占用空间最大的前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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sbtest | t1 | BASE TABLE | EXPRESS | 2023-03-04 16:20:54 | 2023-03-04 17:18:19 | utf8_general_ci | 27885568 | 8.56 | 0.00 | 8.56 | 0.00 | |
| gbase | time_zone_transition | BASE TABLE | GsSYS | 2019-04-11 13:39:02 | 2020-03-25 09:32:35 | utf8mb4_general_ci | 116660 | 1.89 | 2.10 | 3.99 | 0.00 | |
| gbase | time_zone_transition_type | BASE TABLE | GsSYS | 2019-04-11 13:39:03 | 2020-03-25 09:32:35 | utf8mb4_general_ci | 8582 | 0.37 | 0.12 | 0.49 | 0.00 | |
| gbase | time_zone_name | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2020-03-25 09:32:35 | utf8mb4_general_ci | 1750 | 0.04 | 0.07 | 0.12 | 0.00 | |
| gbase | time_zone | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2020-03-25 09:32:35 | utf8mb4_general_ci | 1750 | 0.01 | 0.01 | 0.03 | 0.00 | |
| gbase | columns_priv | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2020-03-25 09:32:35 | utf8mb4_bin | 0 | 0.00 | 0.00 | 0.00 | 0.00 | |
| gbase | password_history | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2023-03-04 15:37:51 | utf8mb4_bin | 11 | 0.00 | 0.00 | 0.00 | 0.00 | |
| gbase | proc | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2023-03-04 16:24:11 | utf8mb4_general_ci | 1 | 0.00 | 0.00 | 0.00 | 0.00 | |
| gbase | func | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2020-03-25 09:32:35 | utf8mb4_bin | 2 | 0.00 | 0.00 | 0.00 | 0.00 | |
| gbase | user | BASE TABLE | GsSYS | 2019-04-11 13:39:01 | 2023-03-04 17:18:21 | utf8mb4_bin | 9 | 0.00 | 0.00 | 0.00 | 0.00 |
● 所有存储引擎列表
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
|---|---|---|---|---|---|
| MRG_GSSYS | YES | Collection of identical GsSYS tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| EXPRESS | DEFAULT | Express storage engine | YES | YES | NO |
| GsSYS | YES | GsSYS engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
● 存储引擎和DB的数量关系
| ENGINE | counts |
|---|---|
| EXPRESS | 1 |
| GsSYS | 49 |
| MEMORY | 37 |
| TABLE_SCHEMA | ENGINE | counts |
|---|---|---|
| gbase | GsSYS | 40 |
| information_schema | GsSYS | 9 |
| information_schema | MEMORY | 30 |
| performance_schema | MEMORY | 7 |
| sbtest | EXPRESS | 1 |
● 查询所有用户
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| % | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| % | gbase | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| % | lhr | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| 127.0.0.1 | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| 127.0.0.1 | gbase | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| localhost | gbase | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| 127.0.0.1 | lhr | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
| localhost | lhr | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
● 查询数据库中的用户与用户组role的关联关系
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
|---|
● 查询所有支持的字符集
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
|---|---|---|---|
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
| hp8 | hp8_english_ci | HP West European | 1 |
| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |
| latin1 | latin1_swedish_ci | cp1252 West European | 1 |
| latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 |
| swe7 | swe7_swedish_ci | 7bit Swedish | 1 |
| ascii | ascii_general_ci | US ASCII | 1 |
| ujis | ujis_japanese_ci | EUC-JP Japanese | 3 |
| sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 |
| hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 |
| tis620 | tis620_thai_ci | TIS620 Thai | 1 |
| euckr | euckr_korean_ci | EUC-KR Korean | 2 |
| koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 |
| gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 |
| greek | greek_general_ci | ISO 8859-7 Greek | 1 |
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |
| latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 |
| armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 |
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 |
| cp866 | cp866_general_ci | DOS Russian | 1 |
| keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 |
| macce | macce_general_ci | Mac Central European | 1 |
| macroman | macroman_general_ci | Mac West European | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |
| cp1256 | cp1256_general_ci | Windows Arabic | 1 |
| cp1257 | cp1257_general_ci | Windows Baltic | 1 |
| binary | binary | Binary pseudo charset | 1 |
| geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |
| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |
● 一些重要的参数
| VARIABLE_NAME | VARIABLE_VALUE |
|---|---|
| AUTOCOMMIT | ON |
| DATADIR | /home/gbase/GBase/userdata/gbase8a/ |
| GBASE_COMPRESSION_NUM_METHOD | 5 |
| GBASE_COMPRESSION_SAMPLING | 1 |
| GBASE_COMPRESSION_STR_METHOD | 5 |
| HAVE_COMPRESS | YES |
| LOG_ERROR | /home/gbase/GBase/log/gbase8a/system.log |
| LOG_OUTPUT | FILE |
| LOG_QUERIES_NOT_USING_INDEXES | OFF |
| LOG_SLAVE_UPDATES | OFF |
| LONG_QUERY_TIME | 10.000000 |
| LOWER_CASE_TABLE_NAMES | 1 |
| MAX_CONNECTIONS | 100 |
| MAX_CONNECT_ERRORS | 10 |
| MAX_USER_CONNECTIONS | 0 |
| PID_FILE | /home/gbase/GBase/log/gbase8a/gbased.pid |
| QUERY_CACHE_SIZE | 8388608 |
| QUERY_CACHE_TYPE | ON |
| READ_ONLY | OFF |
| SERVER_ID | 0 |
| SLAVE_COMPRESSED_PROTOCOL | OFF |
| SOCKET | /tmp/gbase_8a_5258.sock |
| SQL_MODE | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH |
| TIME_ZONE | +08:00 |
| TX_ISOLATION | REPEATABLE-READ |
| _GBASE_DELETE_FILTER_COMPRESSED | OFF |
● 查看当前连接到数据库的用户和Host
| USER | HOST |
|---|---|
| root | 192.168.27.230:63880 |
| root | gbase8a:56458 |
| root | gateway:33236 |
● 查看加载进度
| SCN | DB_NAME | TB_NAME | IP | STATE | START_TIME | ELAPSED_TIME | AVG_SPEED | PROGRESS | TOTAL_SIZE | LOADED_SIZE | LOADED_RECORDS | SKIPPED_RECORDS | DATA_SOURCE | SQL_CMD |
|---|
● 查看历史加载结果
| TASK_ID | DB_NAME | TB_NAME | USER | ACCESS_IP | HOST_IP | START_TIME | END_TIME | ELAPSED_TIME | TOTAL_SIZE | AVERAGE_SPEED | LOADED_RECORDS | SKIPPED_RECORDS | IGNORED_FILES | RESULT | SQL_CMD | MESSAGE |
|---|
● 查询所有线程（排除sleep线程）
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
|---|---|---|---|---|---|---|---|
| 45 | root | gateway:33236 | Killed | 162335 | checking permissions | 0 |
● sleep线程TOP20
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
|---|---|---|---|---|---|---|---|
| 73 | root | gbase8a:56458 | Sleep | 12 |
● 有多少线程正在使用表
● 锁详情
● 查看服务器的状态
| VARIABLE_NAME | VARIABLE_VALUE |
|---|---|
| COM_LOCK_TABLES | 0 |
| COM_SHOW_TABLE_LOCKS | 0 |
| COM_UNLOCK_TABLES | 0 |
| KEY_BLOCKS_NOT_FLUSHED | 0 |
| KEY_BLOCKS_UNUSED | 6687 |
| KEY_BLOCKS_USED | 7 |
| QCACHE_FREE_BLOCKS | 1 |
| QCACHE_TOTAL_BLOCKS | 4 |
| TABLE_LOCKS_IMMEDIATE | 405 |
| TABLE_LOCKS_WAITED | 29 |
| TABLE_LOCK_REGISTERED_TABLES | 1 |
● 查询SQL的整体消耗百分比
| state | total_r | pct_r | calls | r/call |
|---|
● 高可用重要参数
| VARIABLE_NAME | VARIABLE_VALUE |
|---|---|
| AUTO_INCREMENT_OFFSET | 1 |
| MAX_BINLOG_SIZE | 1073741824 |
| AUTO_INCREMENT_INCREMENT | 1 |
| BINLOG_FORMAT | STATEMENT |
| SERVER_ID | 0 |
| SQL_SLAVE_SKIP_COUNTER | |
| SQL_LOG_BIN | ON |
| SYNC_BINLOG | 0 |
| LOG_SLAVE_UPDATES | OFF |
| LOG_BIN | OFF |
| READ_ONLY | OFF |
| SLAVE_SKIP_ERRORS | OFF |
| EXPIRE_LOGS_DAYS | 0 |
| SLAVE_EXEC_MODE | STRICT |
| MAX_ALLOWED_PACKET | 1048576 |
● 性能参数统计
| VARIABLE_NAME | VARIABLE_VALUE |
|---|---|
| COM_DELETE | 0 |
| COM_INSERT | 1 |
| COM_SELECT | 4392 |
| CONNECTIONS | 75 |
| CREATED_TMP_DISK_TABLES | 1613 |
| CREATED_TMP_FILES | 0 |
| CREATED_TMP_TABLES | 8579 |
| HANDLER_READ_RND_NEXT | 39217 |
| OPEN_FILES | 101 |
| OPENED_TABLES | 3757 |
| SORT_MERGE_PASSES | 0 |
| SORT_RANGE | 0 |
| SORT_ROWS | 654 |
| SORT_SCAN | 288 |
| TABLE_LOCKS_IMMEDIATE | 405 |
| TABLE_LOCKS_WAITED | 29 |
| UPTIME | 168233 |
● 插件信息
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE |
|---|---|---|---|---|---|---|---|---|---|
| binlog | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | GBase AB | 0 | |||
| partition | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | Mikael Ronstrom, GBase AB | 0 | |||
| CSV | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | Brian Aker, GBase AB | 0 | |||
| EXPRESS | 0.1 | ACTIVE | STORAGE ENGINE | 80602.0 | Express | 0 | |||
| GsSYS | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | GBase AB | 0 | |||
| MRG_GSSYS | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | GBase AB | 0 | |||
| MEMORY | 1.0 | ACTIVE | STORAGE ENGINE | 80602.0 | GBase AB | 0 |
● 自增ID的使用情况（前20条）
| table_schema | table_name | engine | Auto_increment |
|---|---|---|---|
| gbase | time_zone | GsSYS | 1751 |
● 无主键或唯一键的表（前100条）
| table_schema | table_name |
|---|---|
| gbase | audit_log |
| gbase | cache_access_info |
| gbase | general_log |
| gbase | memory_heap_info |
| gbase | new_index_conditions |
| gbase | new_index_fields |
| gbase | processlist |
| gbase | resource_pool_events |
| gbase | resource_pool_usage_history |
| gbase | sql_trace |
| sbtest | t1 |