-- Purge Table dbms_recycle .purge_table ('');-- Restore table dbms_recycle .restore_table ('' ,'' ,'' );-- Show tables mysql > call dbms_recycle .show_tables (); +----------------- +--------------- +--------------- +-------------- +--------------------- +--------------------- + | SCHEMA | TABLE | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME | PURGE_TIME | +----------------- +--------------- +--------------- +-------------- +--------------------- +--------------------- + | __recycle_bin__ | __innodb_1063 | product_db | t1 | 2019-08-08 11 :01 :46 | 2019-08-15 11 :01 :46 | | __recycle_bin__ | __innodb_1064 | product_db | t2 | 2019-08-08 11 :01 :46 | 2019-08-15 11 :01 :46 | | __recycle_bin__ | __innodb_1065 | product_db | parent | 2019-08-08 11 :01 :46 | 2019-08-15 11 :01 :46 | | __recycle_bin__ | __innodb_1066 | product_db | child | 2019-08-08 11 :01 :46 | 2019-08-15 11 :01 :46 | +----------------- +--------------- +--------------- +-------------- +--------------------- +--------------------- +4 rows in set (0.00 sec)4.3 Returning GalaxyEngine 提供 returning 功能,支持 DML 语句返回 Resultset,同时提供了工具包(DBMS_TRANS)便于您快捷使用。 DBMS_TRANS.returning(<Field_list > ,<Statement > ); mysql> call dbms_trans.returning("*" , "insert into t(id) values(NULL),(NULL)" ); +----+------+---------------------+| id | col1 | col2 | +----+------+---------------------+| 1 | 1 | 2019-09-03 10:39:05 | | 2 | 1 | 2019-09-03 10:39:05 | +----+------+---------------------+2 rows in set (0 .01 sec) mysql> call dbms_trans.returning("id, col1, col2" , "update t set col1 = 2 where id >2" ); +----+------+---------------------+| id | col1 | col2 | +----+------+---------------------+| 3 | 2 | 2019-09-03 10:41:06 | | 4 | 2 | 2019-09-03 10:41:06 | +----+------+---------------------+2 rows in set (0 .01 sec) mysql> call dbms_trans.returning("id, col1, col2" , "delete from t where id < 3" ); +----+------+---------------------+| id | col1 | col2 | +----+------+---------------------+| 1 | 1 | 2019-09-03 10:40:55 | | 2 | 1 | 2019-09-03 10:40:55 | +----+------+---------------------+2 rows in set (0 .00 sec) 4.4 Statement Concurrency Control 为了应对突发的数据库请求流量、资源消耗过高的语句访问以及SQL访问模型的变化, 保证实例持续稳定运行,GalaxyEngine 提供基于语句规则的并发控制 CCL(Concurrency Control),并提供了工具包(DBMS_CCL)便于您快捷使用。 功能设计 SQL command: SQL命令类型,例如SELECT、UPDATE、INSERT、DELETE等。 Object: SQL命令操作的对象,例如TABLE、VIEW等。 规则持久化存储在系统表 mysql.concurrency_control. 接口设计
-- Add Rule dbms_ccl .add_ccl_rule ('' ,'' ,'' ,,'' );-- Delete Rule dbms_ccl .del_ccl_rule ();-- Show Rule dbms_ccl .show_ccl_rule ();-- Flush Rule dbms_ccl .flush_ccl_rule (); 4.5 Statement Outline 生产环境中,SQL语句的执行计划经常会发生改变,导致数据库不稳定。GalaxyStore 利用 Optimizer Hint 和Index Hint 让 MySQL 稳定执行计划,该方法称为 Statement Outline,并提供了工具包(DBMS_OUTLN)便于快捷使用。 功能设计
Statement Outline支持官方MySQL 8.0 的所有 hint 类型,分为如下两类: Optimizer Hint 根据作用域和 hint 对象,分为 Global level hint、Table/Index level hint、Join order hint等。 Index Hint 根据 Index Hint 的类型和范围进行分类。 增加的 outline 持久化存储在 mysql. outline
表中。 接口设计
-- Add optimizer Outline dbms_outln.add_optimizer_outline('' ,'' ,'' ,'' ,'' ); -- Add Index Outline dbms_outln.add_index_outline('' ,'' ,,'' ,'' ,'' ,'' ); -- Delete Outline dbms_outln.del_outline(); -- Show Outline mysql> call dbms_outln.show_outline(); +------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+ | ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT | +------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+ | 33 | outline_db | 36 bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER | | 1 | /*+ SET_VAR(foreign_key_checks=OFF) */ | 1 | 0 | SELECT * FROM `t1` WHERE `id` = ? | | 32 | outline_db | 36 bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER | | 1 | /*+ MAX_EXECUTION_TIME(1000) */ | 2 | 0 | SELECT * FROM `t1` WHERE `id` = ? | | 34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER | | 1 | /*+ BNL(t1,t2) */ | 1 | 0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2` | | 35 | outline_db | 5 a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 2 | /*+ QB_NAME(subq1) */ | 2 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` ) | | 36 | outline_db | 5 a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ | 2 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` ) | | 30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX | | 1 | ind_1 | 3 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ? | | 31 | outline_db | 33 c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX | | 2 | ind_2 | 1 | 0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? | +------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+7 rows in set (0.00 sec) 4.6 Performance Insight Performance Insight 是专注于实例负载监控、关联分析、性能调优的利器,帮助您迅速评估数据库负载,找到性能问题的源头,提升数据库的稳定性。 Performance Insight 介绍
Performance Insight由如下两部分组成: Object statisticsObject statistics查询表和索引的统计信息,包括如下两个表: TABLE_STATISTICS:记录读取和修改的行。 INDEX_STATISTICS:记录索引的读取行。 Performance pointPerformance point 提供实例的详细性能信息,方便您更快更准确地量化SQL的开销。Performance point包括如下三个维度: CPU:包括执行任务的总时间(Elapsed time)、CPU执行任务的时间(CPU time)等。 LOCK:包括服务器MDL锁时间、存储事务锁时间、互斥冲突(仅调试模式)、读写锁冲突等。 IO:数据文件读写时间、日志文件写入时间、逻辑读取、物理读取、物理异步读取等。 Object statistics 使用方法
确认参数 OPT_TABLESTAT 和 OPT_INDEXSTAT 的值为 ON 。示例如下: mysql> show variables like "opt_%_stat" ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | opt_indexstat | ON | | opt_tablestat | ON | +---------------+-------+ mysql> select * from TABLE_STATISTICS limit 10 ; +--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+ | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES | ROWS_INSERTED | ROWS_DELETED | ROWS_UPDATED | +--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+ | mysql | db | 2 | 0 | 0 | 0 | 0 | 0 | | mysql | engine_cost | 2 | 0 | 0 | 0 | 0 | 0 | | mysql | proxies_priv | 1 | 0 | 0 | 0 | 0 | 0 | | mysql | server_cost | 6 | 0 | 0 | 0 | 0 | 0 | | mysql | tables_priv | 2 | 0 | 0 | 0 | 0 | 0 | | mysql | user | 7 | 0 | 0 | 0 | 0 | 0 | | test | sbtest1 | 1686 | 142 | 184 | 112 | 12 | 18 | | test | sbtest10 | 1806 | 125 | 150 | 105 | 5 | 15 | | test | sbtest100 | 1623 | 141 | 182 | 110 | 10 | 21 | | test | sbtest11 | 1254 | 136 | 172 | 110 | 10 | 16 | +--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+ mysql> select * from INDEX_STATISTICS limit 10; +--------------+--------------+------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+--------------+------------+-----------+ | mysql | db | PRIMARY | 2 | | mysql | engine_cost | PRIMARY | 2 | | mysql | proxies_priv | PRIMARY | 1 | | mysql | server_cost | PRIMARY | 6 | | mysql | tables_priv | PRIMARY | 2 | | mysql | user | PRIMARY | 7 | | test | sbtest1 | PRIMARY | 2500 | | test | sbtest10 | PRIMARY | 3007 | | test | sbtest100 | PRIMARY | 2642 | | test | sbtest11 | PRIMARY | 2091 | +--------------+--------------+------------+-----------+ Performance point 使用方法 mysql> show variables like "%performance_point%" ; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | performance_point_dbug_enabled | OFF | | performance_point_enabled | ON | | performance_point_iostat_interval | 2 | | performance_point_iostat_volume_size | 10000 | | performance_point_lock_rwlock_enabled | ON | +---------------------------------------+-------+ 在 performance_schema 数据库查询 events_statements_summary_by_digest_supplement 表 mysql> select * from events_statements_summary_by_digest_supplement limit 10 ; +--------------------+----------------------------------+-------------------------------------------+--------------+ | SCHEMA_NAME | DIGEST | DIGEST_TEXT | ELAPSED_TIME | ...... +--------------------+----------------------------------+-------------------------------------------+--------------+ | NULL | 6b787dd1f9c6f6c5033120760a1a82de | SELECT @@`version_comment` LIMIT ? | 932 | | NULL | 2 fb4341654df6995113d998c52e5abc9 | SHOW SCHEMAS | 2363 | | NULL | 8a93e76a7846384621567fb4daa1bf95 | SHOW VARIABLES LIKE ? | 17933 | | NULL | dd148234ac7a20cb5aee7720fb44b7ea | SELECT SCHEMA ( ) | 1006 | | information_schema | 2fb4341654df6995113d998c52e5abc9 | SHOW SCHEMAS | 2156 | | information_schema | 74 af182f3a2bd265678d3dadb53e08da | SHOW TABLES | 3161 | | information_schema | d3a66515192fcb100aaef6f8b6e45603 | SELECT * FROM `TABLE_STATISTICS` LIMIT ? | 2081 | | information_schema | b3726b7c4c4db4b309de2dbc45ff52af | SELECT * FROM `INDEX_STATISTICS` LIMIT ? | 2384 | | information_schema | dd148234ac7a20cb5aee7720fb44b7ea | SELECT SCHEMA ( ) | 129 | | test | 2 fb4341654df6995113d998c52e5abc9 | SHOW SCHEMAS | 342 | +--------------------+----------------------------------+-------------------------------------------+--------------+ 注:需要同步打开 performance schema。 GalaxyEngine 后续还有更多更广泛的开源和文档支持计划,敬请关注。 GalaxyEngine 开源地址: https://github.com/ApsaraDB/galaxyengine
点击“阅读原文”查看 PolarDB-X 更多信息
浏览
135 点赞
评论
收藏
分享
手机扫一扫分享
举报
点赞
评论
收藏
分享
手机扫一扫分享
举报