MySQL云数据库的性能优化和 Calvin Sun 07@华为

2020-03-01 112浏览

  • 1.MySQL云数据库的性能优化和 8 1 发展趋势 0 2 C C T D Calvin Sun, Huawei Cloud BU May 11, 2018
  • 2.Calvin Sun 2006.01 0 2 C C 2016.02 T D 8 1 2008.02 2017.10 2013.03
  • 3.Agenda • MySQL Key Performance Features • Huawei RDS MySQL Family & Performance Improvements • Challenges & Opportunities T D C C 0 2 8 1
  • 4.8 1 MySQL Key Performance0Features 2 C C T D
  • 5.MySQL Server Architecture MySQL Server 0 2 C C T D 8 1 MyRocks CSV 5
  • 6.MySQL / InnoDB Performance Features         Multi-Threaded Architecture MVCC with 2PL Group Commit Adaptive Flushing Purging Pre-Fetching Adaptive Hash Indexes Change Buffering 0 2 C C T D 8 1 6
  • 7.MySQL Sysbench : SQL Point Selects/sec MySQL 5.7: 3x Faster than MySQL 5.6 MySQL 5.7: 4x Faster than MySQL 5.5 8 1 1,600,000 QPS 0 2 MySQL Sysbench OLTP Read Only (SQL Point Selects) C C 1,800,000 Queries per Second 1,600,000 T D 1,400,000 1,200,000 1,000,000 800,000 600,000 400,000 MySQL 5.7 MySQL 5.6 200,000 MySQL 5.5 0 8 16 32 64 128 Connections 256 512 1,024 7 Intel(R) Xeon(R) CPU E7-8890 v3 4 sockets x 18 cores-HT (144 CPU threads) 2.5 Ghz, 512GB RAM Linux kernel 3.16
  • 8.OLTP_RW latin1 @MySQL 8.0 GA MySQL 8.0: 30% Faster than MySQL 5.7 MySQL 8.0: 50% Faster than MySQL 5.6 0 2 C C T D 8 1 8 45K (!!) TPS Sysbench OLTP_RW 10Mx8tab, trx_commit=1, 2S
  • 9.8 1 Huawei RDS MySQL Family & Performance Improvements T D C C 0 2
  • 10.RDS Cloud Services Easy to Scale Out-of-the-Box Obtain a production-ready relational database in minutes with a few clicks Security Protection T D Backup & Restore 0 2 C C VPCs, subnets, security groups, SSL protections and audit logs Automated backups, point-in-time restores, snapshots 8 1 Scale up CPU, memory and storage or deploy multiple read replicas for better transaction throughput Data Migration Easily bring data from external sources into Cloud RDS High Availability Highly available database services within or across availability zones
  • 11.MySQL Family on Huawei Cloud Product RDS MySQL Description 0 2 Server community edition RDS HWSQL 5.6 (Q1 2018) RDS HWSQL 5.7 (Q2 2018) 8 1 A value-added, cloud-based implementation of the MySQL C C An enhanced MySQL Server 5.6 for superior performance and T D availability, engineered by Huawei Cloud BU An enhanced MySQL Server 5.7 for superior performance and availability, engineered by Huawei Cloud BU
  • 12.Huawei RDS HWSQL Engineered by Huawei for superior performance and availability 8 1 Delivers all the capabilities of Huawei RDSplus:0 2 High-Performance Enhanced Reliability Approximately 3x the performance of MySQL Community Edition C C T D Improved Scalability Supports more database clients, more concurrent transactions, and larger server configurations Enhanced, loss-less semi-sync and semi-sync notification improves reliability and avoids possibility of data loss on standby takeover Fast Recovery on Failure Improved replication greatly reduces time to recover on failure and data lag on read replicas
  • 13.Huawei RDSHWSQL:Throughput  Up to 3x the throughput of community version MySQL server  Superior, consistent performance with large numbers of clients 0 2 C C T D 8 1 HWSQL
  • 14.MySQL 5.6 : Performance & Scalability Issues 8 1  High-concurrency workloads performance degradation 0 2 • > 1000 clients  big performance drop • Especially on modern large multi-core systems C C  Master-Slave replication lag/delay T D • Severe lag for single-database replication • MTS replication in 5.6 only works on different databases  Multi-core scalability bottlenecks • Workloads cannot scalable on multi-core systems (e.g., 32 cores) in read-heavy, write-heavy, and read-write mixed workloads
  • 15.HWSQL 5.6: Key Performance Features (1) 8 1  Improve high-concurrency workloads performance 0 2 • Thread-pool plugin  Solve master-slave replication lag C C • Transaction-level multi-threaded slave (MTS) parallel replication  Implement loss-less semi-sync replication T D • Make semi-sync master faster • Implement the loss-less semi-sync to prevent potential data loss
  • 16.HWSQL 5.6: Key Performance Features (2) 8 1  Multi-core scalability • Read-only transactions optimizations o Streamlining read-only transactions processing o MVCC readview reuse o Adaptive Hash Index (AHI) latch enhancement • Write transactions & logging optimizations o Group commit stages & notification o Redo log writing & flushing o Buffer pool LRU list scanning • Scalable memory manager integration & deep tuning T D C C 0 2
  • 17.HWSQL 5.7: Key Performance Features  All in HWSQL 5.6  SQL aggregate pushdown 0 2 8 1 • Pushdown query aggregate evaluation to storage engine to reduce overheads C C  Query cache T D • Scalable query cache with auto cache deactivation
  • 18.Thread-Pool Plugin MySQL Community HWSQL 0 2 C C T D 8 1 Thread-pool contains multiple thread groups (TG)  Each TG can have multiple, reusable threads (e.g., 4096), but only 1 (or very few) active  Connection is assigned to one of the TGs  Statements from a connection can be served by different threads in its assigned TG  Designed to distribute connections across TGs; no single TG becomes the bottleneck
  • 19.Transaction-level MTS Parallel Replication Transactions QPS 25,000 Replication delay (s) 800 700 20,000 Transaction load against master 600 500 15,000 Increasing replication delay time 10,000 C C Slave cannot keep up under load 0 100 200 300 Master QPS 400 500 600 Slave QPS 700 T D Transactions QPS 25,000 Transaction load against master Standby server is able to keep up 15,000 100 0 800 900 1000 Replication delay Replication Delay (s) 800 700 600 500 400 10,000 300 200 5,000 Minimal replication delay time 0 100 0 100 200 300 Master QPS 400 500 600 Slave QPS 700 800 900 1000 Replication Delay  Transactions are applied in parallel with multiple worker threads on slave if they do not interfere with each other, based on a scheduling algorithm of Logical Time Interval 8 1 0 2 300 200 5,000 20,000 400 Logical clock based MTS  The parallelization is at transaction level (No matter which database the transactions are applied to)  The slave SQL thread reads out transaction events from relay log, and schedules the transactions
  • 20.Loss-less Semi-sync Replication  Master waits for slave's ACK before committing (as opposedto:master waits for slave's ACK after committing). • Therefore, concurrent transactions do not see changes while this transaction waits for ack. 8 1 C C 0 2  Should a master fail, then any transaction that it may have externalized is also persisted on a slave. T D  Master can optionally wait for multiple ACKs • Master does not commit transaction until it receives N ACKs from N slaves.
  • 21.Page Write Optimizations Average 1ms latency when MySQL VM visits FusionStorage EVS, severely impact the buffer pool write performance. 8 1 MySQL VM • DW buffer 0 2 Buffer Pool n Buffer Pool 1 …. Page cleaner C C DW buffer T D Page cleaner Network ~1 ms latency MySQL Disk Vol FusionStorage EVS HWSQL 5.6 implemented the parallel double write page flushing • Changed from single double write buffer to one double write buffer per buffer pool instance。 • Introduced multiple page_cleaner threads. • Ported adaptive page flushing algorithm
  • 22.Group Commit & Notification Optimizations HWSQL T D C C 0 2 8 1
  • 23.SQL Aggregate Pushdown  Pushdown query aggregate evaluation to storage engine to reduce overheads between SQL server & storage engine  Let the optimizer determine the best access plan, including which indexes are used to access the tables 0 2 C C  Can pushdown all aggregate functions (sum, count, etc) through common interface T D • Query group-by pushdown • Query condition evaluation pushdown (eg, select count(*) from t1 where c1 > 3)  No API change and no compatibility impact  Subquery, stored procedure, UDF not pushdownable 8 1 Pushdown feasibility determined at end of plan refinement stage Example 1: SELECT COUNT(*) FROM LINEITEM lineitem is a table in a 10-GB TPC-H database. (roughly 60 million rows) time reduced from 9.33s to 4.25s, i.e. 54% Example 2: select sum(k) from … where id betweentime reduced from 21.12s to 15.76s, i.e. 25%
  • 24.Query Cache  Partition global query cache into multiple segments  Auto query cache deactivation • Global deactivation • Per-table deactivation 0 2 8 1  Lock free structures and more efficient hashing T D C C
  • 25.8 1 Challenges & Opportunities 0 2 C C T D
  • 26.Main Challenges 高安全性, 高可靠性、高可用性、高性能, 可扩展能力,以及运维自动化  Traditional RDBMS architectures are 30+ years old 8 1 •Gartner:By 2019, 90% of cloud DBMS architectures will 0 2 support separation of compute and storage, rendering those that do not as irrelevant in the overall market . C C  How to leverage latest hardwareadvances:T D •CPU:Multi-cores with NUMA •Storage:Optane SSDs (Coldstream & AEP) •Network:RDMA • Specialhardware:GPU, FPGA  Auto-scaling, self-tuning
  • 27.Huawei Cloud Native Database  Separation of compute and storage with a logical storage abstraction layer (SAL)  Exploit functionality provided by cloud storage 8 1 0 2 • HAfeatures:atomic write, replication, failover, … • Shared access (single writer, multiple readers) C C  Exploit properties of SSDs • Log is the database, avoid random writes to SSDs to minimize wear • Exploit good random read performance of SSDs T D  Multi-tenant support  Take advantages of new network technologies, e.g. RDMA  Pushdown operations close to data • Offloading work to storage nodes  Leverage advances in AI and ML for autonomous system
  • 28.Overview of Huawei Cloud Native Database  Master database server SQL Nodes Replica SQL node (read-only) Master SQL node (read-write) Replica SQL node (read-only) SAL Client SAL Client SAL Client C C RDMA Slice Slice Slice Server Server Storage Nodes 0 2 Storage Network Storage Abstraction Layer (SAL) Slice Redo log Slice Slice 8 1 Coherence traffic Coherence traffic T D • •  Read Replica database servers • • • Slice Slice Server Server Slice Slice Pages Shared, reliable, scale-out storage Slice Server Can handle read-only requests Enable fast failover Can be added at any time  Database data is partitioned across storage nodes • Slice Slice Handles all updates Writes to the WAL logs • • Pages are logically organized based on slice and distributed among slice servers Each slice is duplicated for reliability Log records for a page are sent to the corresponding slice  Slice Server • • • • Maintain multiple slices for different tenant databases Store and process log records Maintain and construct pages Serve page read requests
  • 29.T D C C 0 2 8 1
  • 30.