《03-深度解析 AliSQL 8.0 特性和改进(23页).pdf》由会员分享,可在线阅读,更多相关《03-深度解析 AliSQL 8.0 特性和改进(23页).pdf(23页珍藏版)》请在三个皮匠报告上搜索。
1、冷香阿里云智能高级技术专家深度解析 AliSQLAliSQL 8.08.0 特性和改进赵建伟高级技术专家云智能-OLTP产品部-AliSQL 内核小组Agenda Performance Insight&DiagnosePerformance Insight&Diagnose New FeatureNew Feature Stability ImprovementStability ImprovementAgenda Performance Insight&DiagnosePerformance Insight&Diagnose New FeatureNew Feature Stability
2、 ImprovementStability ImprovementPerformance InsightDBADBA和开发视角和开发视角TransactioTransaction nBinlogBinlogSizeSizeLock CountLock CountElapsed Elapsed TimeTimeObjectObjectRead WriteRead WriteIndex Index UsageUsageStatementStatementCPU/Elapsed CPU/Elapsed TimeTimeMDL/Trans MDL/Trans LockLockIO StatsIO St
3、atsConcurrencyConcurrency研发视角Performance SchemaPerformance Insight Object StatisticsObject StatisticsTable statistics 是业务系统 scale 的数据支撑Index statistics 是业务系统优化 index 的数据支撑Performance Insight Statement StatisticsStatement StatisticsCPU ELAPSED_TIME CPU_TIMELOCK SERVER_LOCK_TIME TRANSACTION_LOCK_TIMEC
4、oncurrency MUTEX_SPINS MUTEX_WAITS RWLOCK_SPIN_WAITS RWLOCK_SPIN_ROUNDSIO DATA_READS DATA_WRITES LOGICAL_READS PHYSICAL_READS PHYSICAL_ASYNC_READSPerformance Insight Statement StatisticsStatement StatisticsCPU IntensiveMDL BlockTrans BlockPerformance Insight Statement StatisticsStatement StatisticsI
5、O IntensiveConcurrencyPerformance Diagnose 云产品深度依赖,可诊断性至关重云产品深度依赖,可诊断性至关重要要Slot:10000Interval:2SDurable:5HInnoDB IO 表现Agenda Performance Insight&DiagnosePerformance Insight&Diagnose New FeatureNew Feature Stability ImprovementStability ImprovementSequence EngineSequence Syntax:Sequence Syntax:CREATE
6、 CREATE SEQUENCE IF NOT EXISTS SEQUENCE IF NOT EXISTS schema.seqschema.seqSTART WITH START WITH MINVALUE MINVALUE MAXVALUE MAXVALUE INCREMENT BY INCREMENT BY CACHE CACHE|NOCACHE|NOCACHECYCLE|NOCYCLECYCLE|NOCYCLE;SELECT NEXTVAL(SELECT NEXTVAL(seqseq););SELECT CURRVAL(SELECT CURRVAL(seqseq););InnoDBHa
7、ndler Interface语法解析Cache管理自治事务Sequence 访问Customized ReadView 自定义自定义 ReadViewReadView(CrossCross-sessionsessionconsistentconsistent)SyntaxEXPORT CONSISTENT SNAPSHOT LOCALLOCALRELEASE CONSISTENT SNAPSHOT$snap_idSTART TRANSACTION WITH CONSISTENT SNAPSHOT$snap_idSession 1Session 2ReadViewReadViewClone P
8、roxy 将可以跨 session 做并行计算Global ReadView(PolarDB)全局全局 ReadViewReadView(CrossCross-Node consistentNode consistent)SyntaxEXPORT CONSISTENT SNAPSHOT CLUSTERCLUSTERRELEASE CONSISTENT SNAPSHOT$snap_idSTART TRANSACTION WITH CONSISTENT SNAPSHOT$snap_id Proxy 将可以跨节点做并行计算MasterReplicaReplicaData FileRedo log f
9、ileClientCreate RV1ClientClientRV1RV1/*RV1*/Query/*RV1*/QueryData FileData FileAgenda Performance Insight&DiagnosePerformance Insight&Diagnose New FeatureNew Feature Stability ImprovementStability ImprovementStatement Concurrency control SQL command根据 statement 的类型,例如 SELECT,UPDATE,INSERT,DELETE;Obj
10、ect根据 statement 操作的对象进行控制,例如 TABLE,VIEW;keywords根据 statement 语句的关键字进行控制;1.CCL 规则设计2.CCL 接口设计DBMS_CCL.add_ccl_rule();DBMS_CCL.del_ccl_rule();DBMS_CCL.show_ccl_rule();DBMS_CCL.flush_ccl_rule();Statement Outline Optimizer Hint根据作用域(query block)和 hint 对象,分为:Global level hint,Table/Index level hint,Join
11、order hint等等 Index Hint主要根据 index hint 的类型(USE,FORCE,IGNORE)和scope(FOR JOIN,FOR ORDER BY,FOR GROUP BY)进行分类1.Outline 规则设计2.Outline 接口设计 DBMS_OUTLN.add_index_outline();增加 index hint DBMS_OUTLN.add_optimizer_outline();增加 optimizer hint DBMS_OUTLN.preview_outline();预览某一个 SQL 语句命中 outline 的情况 DBMS_OUTLN.
12、show_outline();展示内存中可用的所有 outline 及命中情况 DBMS_OUTLN.del_outline();删除内存和持久化表中的 outline DBMS_OUTLN.flush_outline();刷新所有的 outline,从 mysql.outline 表中重新loadAsync Purge InnoDB Data File Big Table drop Big Table drop 的成本的成本 单机文件系统单机文件系统 EXT4EXT4PagePage CacheCache 回收回收M Meta eta 信息信息 flushflushJournalJourna
13、l 日志写入日志写入Async Purge InnoDB Data File DDL AtomicDDL AtomicCREATE TABLE数据库和文件系统一致性保证(日志补偿机制)2.修改DD1.开启事务3.开启事务4.插入DDL log7.创建表空间和文件5.提交事务6.删除DDL log8.提交 DD 事务如果DD 事务失败或者crash,Replay DDL log 清理文件DROP TABLE2.修改DD1.开启事务3.插入DDL log5.开启事务4.提交事务如果 DD 事务失败,Do nothing;如果 crash,启动 DDL log recovery 清理文件6.Repl
14、ay DDL log删除表空间和文件7.删除 DDL log8.提交事务Async Purge InnoDB Data FileDROP TABLE2.修改DD1.开启事务3.插入DDL log4.提交事务5.Replay DDL log11.删除 DDL log12.提交事务6.开启事务7.插入 Purge DDL log8.提交事务9.Rename Data File10.插入队列InnoDB File Purge Thread1.从队列中取一个文件2.ftruncate 文件3.如果文件 threshold:回到步骤1;如果 crash,DDL log recovery 保证atomic
15、Multi-Queue Thread PoolThread Schedulingone-thread-per-connectionPriority Thread PoolMulti-Queue Thread Pool CPU 时间片公平调度 线程切换开销线性增长 无业务识别能力 线程切换开销稳定 业务识别,事务优先 无 SQL 复杂度判断 线程切换开销稳定 针对不同的SQL,识别事务,复制查询,短平快 SQL等建立多队列,提升稳定和吞吐TP:在大规模连接和复杂混合 SQL 模型下,保持MySQL 持续稳定吞吐能力Implicit Primary Key 增加一个增加一个 implicit co
16、lumn implicit column 和和 keykey Slave SQL apply Slave SQL apply 索引选择优先索引选择优先 implicit keyimplicit key背景:分区表 Constraint 需要带分区键,所以 PK-Key大量的 NULL 导致 UK 并不是 SQL apply 的优先选择Transaction management Kill idle transaction Non-transaction First QueryTransaction Isolation comparisonMySQLOracleREAD UNCOMMITTEDREAD UNCOMMITTEDREAD COMMITTEDREAD COMMITTEDREPEATABLE READREPEATABLE READ(DefaultDefault)SERIALIZABLESERIALIZABLEREAD COMMITTEDREAD COMMITTED(DefaultDefault)SERIALIZABLESERIALIZABLERR 级别以下的第一条 select 语句不启动事务MySQL 各种阻塞频发事务空闲超时:Set kill_idle_transaction_timeout=xxx(seconds)VS