How to make MySQL for the Cloud Lixun Peng Staff Database Engineer - - PowerPoint PPT Presentation

How to make MySQL for the Cloud

Lixun Peng Staff Database Engineer @ Alibaba Cloud Senior Engineer @ MariaDB Foundation

2

Agenda

• Our Architecture in the Cloud
• The Challenge of MySQL in the Cloud
• The Requirement of MySQL in the Cloud

Architecture in the Cloud

Proxy, Authentication, Failover, Read-Only Instance, etc.

4

Architecture Overview

5

Access Architecture

6

Next Generation

The Challenge in the Cloud

Situation, Problems etc.

8

Situation in the Cloud

• Everything is uncontrollable
• Most users don't have DBAs
• Most users aren’t familiar with database
• Many users’ applications are downloaded from internet
• Many users will not modify the table structure or queries as our suggestions

9

Problems

• These will cause problems
• Users often trigger MySQL bugs
• Users often used all resources
• Users often modify the wrong data
• Users will effect with each others
• Failover can’t interrupt transactions
• How to solve?
• Improve the MySQL code.

The Requirement in the Cloud

Proxy-related, Multi-UserGroup, Implicit Primary Key, Thread Minitor, Flashback, etc.

11

Change User

• When connection is re-using, the connection maybe service for different

users, so we need to change the user.

• Proxy always use ROOT account to connect MySQL, then switch to

normal user.

• Provide a CLI function
• set change_user user='n_user_1', ip='10.232.64.76';
• MySQL 5.7 also provide similar feature

12

Reset Connection

• Proxy will re-use the connections
• One connection maybe used by different session
• Connection should clear all previous session information before re-use
• Provide a CLI function
• set resetconnection=1;
• MySQL 5.7 also provide similar feature

13

Bypass User IP

• Because it’s Proxy to connect MySQL, not user clients
• SHOW PROCESSLIST will show Proxy’s IPs
• Users will feel confused, what’s these IPs?
• Proxy will query: SET READ_HOST = User_IP
• SHOW PROCESSLIST will not show THD::host
• SHOW PROCESSLIST will show THD::read_host

14

Multi-UserGroup

• Account System in the Cloud
• User Account: For Customers, just can operate table structure and DML.
• Management Account: For Internal Application, used by Monitor, Tasks, etc.
• System Account: For Replication and Backup, can’t effect by user accounts.
• MySQL just support
• SUPER Users: Can’t change GLOBAL variables, unlimited connections.
• Non-Super Users: Can change GLOBAL variables, limited by max_connection.
• Obviously, MySQL current account system isn’t enough for the Cloud.

15

Multi-UserGroup

• What we have done
• Root UserList: Just contains root user, for initializing and urgent operations,

unlimited connection, all privileges.

• Maintain UserList: Has SUPER privilege, use independent connection counter and
• ther limitations.
• Sysinfo UserList: Use independent connection counter and other limitations.
• Normal UserList: Limited by max_connection.
• Next Plan
• MySQL 8.0 and MariaDB provided the ROLE
• We plan to add max_connection and other limitations on ROLE.

16

Implicit Primary Key

• The importance of the primary key to replication
• For user data safety, binlog_format is best to ROW
• If the binlog_format=ROW, then Slave will try to use Primary Key to find the row
• If an Event doesn’t contain Primary Key, Slave will try to scan full table to find this row

17

Implicit Primary Key

• Try to improve (MariaDB also has this feature)
• If found the Event doesn’t contain primary key, but contains UNIQUE key, then use

the first unique key to find the row.

• If can’t find unique keys, then try to use the first normal key.
• If can’t find any keys, then continue to use the full table scan.

18

Implicit Primary Key

• How to solve non-index table?
• If CREATE TABLE hasn’t any unique keys or auto_increment column, then create an

auto_increment column and create an index on it.

• If ALTER TABLE wants to change the table to non-unique keys and auto_increment

column, then create an auto_increment column and create an index on it.

• Hide implicit primary keys in all DML operations (Include CHECKSUM)
• When users add the unique/primary key, MySQL will remove implicit primary key

19

Implicit Primary Key

Delay Delay

Without IPK With IPK

20

Thread Monitor

• Memory Limitation in MySQL
• MySQL just can limit the InnoDB Buffer Pool Size, and join buffer size, sort buffer size

for the threads.

• In fact, threads will apply for many memory from MEM_ROOT
• Problems
• Every instances have a memory limitation in the Cloud
• But we can just know the process’s memory size
• We don’t know the threads cost
• Even the users found the instances used too much memory, but don’t know which

threads used the most of memory, which thread should be killed to release the resource.

21

Thread Monitor

• We add more information in SHOW FULL PROCESSLIST
• Memory_used: The total cost of this thread
• Memory_used_by_query: The cost of executing the current query
• Read IOPS and other temporary tables cost

22

Flashback

• Anyone can make mistakes, include DBAs.
• After users mishandle their data, we need to recover from the failure as

soon as possible. Then we need a way to recover the data from the correct snapshot, and if possible, do it online and fast.

• We can, of course, recover data from the last full backup set and

incremental binary logs, but if a user’s database is too huge, it could take a while!

• This is particularly frustrating as it can only be a small amount of the data

that needs to be modified, but we still need to recover the whole database.

23

Flashback

• Flashback allows you to reverse the last mis-operations from binary logs.
• More often than not this will be a small activity, so the speed is much

faster than recovery from a full backup.

• And we don’t need to stop the instance server to do carry this out. That’s

very important for the Cloud users.

• That’s why I have implemented the Flashback feature as it provides the

ability to achieve this.

24

Flashback

• The less the number of mishandled rows, the greater the size of data,

then the flashback is more benefit.

Data Size Mishandled Data Size Flashback Full Backup Set 10 GB 1 GB 1 Hour 30 Mins 10 GB 1 MB 5 Mins 30 Mins 1 TB 1 GB 1 Hour 1 Day 1 TB 1 MB 5 Mins 1 Day

25

IO Monitor

• Many instances in one machine
• Sometimes users will complain Queries performance is affected by other

users

• We need to confirm the IO is really slow
• So, we add a innodb_io_latency_warning variable, if an IO executed

more than innodb_io_latency_warning us, then print this IO in the error log.

26

IO Monitor

• Error log will show
• 2017-09-26 04:33:37 22607 [Warning] InnoDB: AIO write len: 16384, latency: 119us.
• 2017-09-26 04:33:47 22607 [Warning] InnoDB: Log flush len: 512, latency: 161us.
• 2017-09-26 04:33:48 22607 [Warning] InnoDB: Log flush len: 512, latency: 118us.
• 2017-09-26 04:33:48 22607 [Warning] InnoDB: AIO write len: 16384, latency: 116us.

27

AliSQL

• All features you can find in the AliSQL now or later:
• https://github.com/Alibaba/AliSQL

28

Thanks

PengLiXun@gmail.com