关系数据库到云数据库的实践

1、关系数据库到云数据库 腾讯TXSQL的变迁与实践AgendaTXSQL IntroductionTXSQL UsabilityTXSQL OptimizationsTXSQL Enterprise FeaturesTXSQL User CasesTXSQL IntroductionTencent Cloud Relational Database Service11.2 CDB Tencent Cloud Relational Database ServiceRelational Database as a ServiceFully Managed & Zero AdminApplicatio

2、n CompatibilityChoice of Multiple Database EnginesTens of Thousands DB Instances,PB+ Storage1.3 TXSQL Tencent DB kernelTengXun MySQL(Tencent MySQL)MySQL Branch Maintained by CDBOfficial designed Kernel of MySQLTencentDB:RDBMS:MySQL(TXSQL）Mariadb SQLServer PostgreSQLDDBMS:Mariadb( TDSQL) Percona Post

3、greSQL(Tbase)NOSQL:Redis, Memcached,CKV+ MongoDBNewSQL:CynosDBOthers:Snova Elastic SearchTXSQL：1.4 Tencent DB CustomersTXSQL UsabilityTencent Cloud Relational Database Service22.1 ASYNC DROP TABLEGoal:Avoid IO peak when dropping a table with big file sizeImplementation:Rename the ibd file to a tempo

4、rary fileTruncate it by fixed size(such as 128M) each time in backgroundRemove it finally2.2 ALTER TABLE NO_WAIT | TIMEOUTALTER TABLE table NO_WAIT | WAIT nDROP TABLE table NO_WAIT | WAIT nTRUNCATE TABLE table NO_WAIT | WAIT nOPTIMIZE TABLE table NO_WAIT | WAIT nRENAME TABLE table_src NO_WAIT | WAIT

5、 n TO table_dst2.3 Event Scheduler ImproveMasterSlaveHAEvent(enable)Event(SLAVESIDE_DISABLED)Master2.4 Slave Read_Master_Log_Pos ImproveExec_Master_Log_PosRead_Master_Log_PosSql thread: Last exec posIo thread: Pulled data posTrx2 end posTrx1 end posTrx2 end pos Master CrashMaster binlogSlave relaylo

6、g2.X MoreKill idle sessioninvisible indexMemory storage convert innodbTXSQL OptimizationsTencent Cloud Relational Database Service33.1 Replication OptimizationsSlave Lock SplitSlave IO OptimizationParallel Replication3.1.1 Slave Lock SplitLock_log split on slave to improve sql thread performance3.1.

7、2 Slave IO OptimizationCache binlog events inside a transaction and sync once60000500004000030000200001000003264128256512768originaloptimized3.1.3 Parallel ReplicationTable based parallel replication60000500004000030000200001000001248163264同步能力测试masterDATABASELOGICAL_LOCKTABLE3.2 Transaction Schedul

8、ing optimizationFCFS（First Come First Served)CATS (Contention-Aware Transaction Scheduling)3.2 Transaction Scheduling optimization3.X More InnoDBRead View OptimizationOffset LimitSumCount(*)TXSQL Enterprise FeaturesTencent Cloud Relational Database Service4mysqldSession 1Session 2Session nAudit Agen

9、tFlush threadCTSDB clusterAudit centerAudit logAudit centerAudit centerAUDIT RULE4.1TXSQL AUDITAUDIT RULE: IP, DB, TABLE, USER, TYPE, RUNTIME = regex性能影响有多少？04.2TXSQL EncryptionKMS : Key Management Service by Tencent CloudCAM : Cloud Access Management Service by Tencent CloudIntegrate KMS & CAM with

10、 MySQL TDEImplement Keyring interfacesKerying Pluginskeyring_filekeyring_okv : commercial, key managed in ORACLE Key VaultKeyring_kmsKMS PluginCAMKMS4.3TXSQL Thread Pool4.XMoreColumn level encryptionTXSQL User CasesTencent Cloud Relational Database Service55.1 Changyou Database OptimizationMonitor T

11、ools:monitorpstackpt-pmpperfpstack & pt-pmp:Adjustment: table_open_cache_instances=32 metadata_locks_hash_instances=32 performance_schema=OFF5.1.1 Changyou Server Configurationsmonitor:Adjustment:innodb_spin_wait_delay = 6innodb_sync_spin_loops = 20perf:5.1.2 Changyou Innodb Configurationsmonitor:pt-pmp:5.1.3 InnoDB Read View Optimization PatchSolution:Optimize read view based on Percona & MySQL 5.7Final Results:010020030050040051015readinnodb-rtrocksdb-rtinnodb-tpsrocksdb-tps01002003004005003510writeinnodb-rtrocksdb