© 2018 Percona. 1 Peter Zaitsev Using MySQL for Distributed Database Architectures CEO, Percona PingCAP Infra Meetup, Shanghai, China, May 26, 2018 © 2018 Percona. 2 About Percona Solutions Percona. 3 Presentation Cover Basics Why Going Distributed How to do it © 2018 Percona. 4 Distributed ? MySQL Deployment on More than one System © 2018 Percona. 5 Modern MySQL Scalability Active Users Possible 15M of Daily Active Users counting time of day skew © 2018 Percona. 8 Distributed Systems Tend To be More Complicated to Develop Against More Complicated to Operate Have

1.8 Appendix with SQL operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.2 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.2.1 Background Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Impact on the System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Limitations . . . . . . . Implementation classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317 5.10 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317 5.10.1

1.8 Appendix with SQL operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.2 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.2.1 Background Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Impact on the System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Limitations . . . . . . . Implementation classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344 5.10 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344 5.10.1

Software 248 Recommended OS Parameters Settings 249 The hosts File 250 The sysctl.conf File 250 System Resources Limits 252 Core Dump 253 XFS Mount Options 253 Disk I/O Settings 254 Transparent Huge Huge Pages (THP) 255 IPC Object Removal 256 SSH Connection Threshold 256 Synchronizing System Clocks 258 To configure NTP 258 Creating the Greenplum Administrative User 259 Next Steps 260 Installing 269 To run the disk and stream tests 269 Initializing a Greenplum Database System 270 Initializing a Greenplum Database System 0 Overview 270 Initializing Greenplum Database 271 Creating the Initialization

Deprecated Features 282 Known Issues and Limitations 283 Platform Requirements 286 Operating System Requirements 286 Software Dependencies 287 Java 290 VMware Greenplum Tools and Extensions Compatibility Hadoop Distributions 294 VMware vSphere Requirements 294 Public Cloud Requirements 295 Operating System 295 Storage 295 Amazon Web Services (AWS) 295 Virtual Machine Type 295 Compute 296 Memory 296 Redundancy 329 About Database Statistics in Greenplum Database 329 System Statistics 330 Table Size 330 The pg_statistic System Table and pg_stats View 330 Sampling 334 Updating Statistics 334 Analyzing

Software 241 Recommended OS Parameters Settings 242 The hosts File 243 The sysctl.conf File 243 System Resources Limits 245 Core Dump 246 XFS Mount Options 246 Disk I/O Settings 247 Transparent Huge Huge Pages (THP) 248 IPC Object Removal 249 SSH Connection Threshold 249 Synchronizing System Clocks 251 To configure NTP 251 Creating the Greenplum Administrative User 252 Next Steps 253 Installing 261 To run the disk and stream tests 261 Initializing a Greenplum Database System 262 Initializing a Greenplum Database System 0 Overview 262 Initializing Greenplum Database 262 Creating the Initialization

Data Sharding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.2.2 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.2.3 Database Governance 14 Inline Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Distributed Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Hint Sharding Route Pagination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 3.2 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 3.2.1 Background

Greenplum Command Center 281 Hadoop Distributions 281 Public Cloud Requirements 281 Operating System 281 Storage 282 Security 282 Amazon Web Services (AWS) 282 Virtual Machine Type 282 Compute Redundancy 315 About Database Statistics in Greenplum Database 315 System Statistics 316 Table Size 316 The pg_statistic System Table and pg_stats View 316 Sampling 320 Updating Statistics 320 Analyzing 334 Verifying the Distributed Virtual Switch Settings 334 Creating the Distributed Port Groups 335 Configuring the Distributed Port Groups 336 Principles of VMware vSphere Distributed Switch to Uplink

26 Inline Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Distributed Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Hint Sharding Route Pagination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 4.3 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 4.3.1 Background 165 Governance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Distributed Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 DistSQL . . .

Greenplum Command Center 263 Hadoop Distributions 263 Public Cloud Requirements 263 Operating System 263 Storage 263 Security 264 Amazon Web Services (AWS) 264 Virtual Machine Type 264 Compute Redundancy 297 About Database Statistics in Greenplum Database 297 System Statistics 298 Table Size 298 The pg_statistic System Table and pg_stats View 298 Sampling 302 Updating Statistics 302 Analyzing 315 Verifying the Distributed Virtual Switch Settings 315 Creating the Distributed Port Groups 316 Configuring the Distributed Port Groups 317 Principles of VMware vSphere Distributed Switch to Uplink