Slide 1
Slide 1 text
MySQL at LINE
Kentaro Kitagawa, IT service center - database department - db1 team
2019/08/17 COSCUP 2019
Slide 2
Slide 2 text
l Kentaro Kitagawa ()
l LINE Corp.
l IT Service Center - Database dept.- DB1 Team
l Database Engineer
l MySQL / Oracle Database / Redis
Introduction
@keny_lala
Slide 3
Slide 3 text
• About LINE Corporation
• About LINE Database
• MySQL Operation
• MySQL 8.0
• MySQL Monitoring
Agenda
Slide 4
Slide 4 text
About LINE Corp
Slide 5
Slide 5 text
MAU:164million
(Top 4 Regions)
81million 44million 21million 18million
Slide 6
Slide 6 text
Daily Messages :
5billion+ (max)
4.1billion+(Avg.)
Peak of Users Traffic :
1Tbps+
Physical Servers :
40,000+
Slide 7
Slide 7 text
No content
Slide 8
Slide 8 text
LINE Group
7,400+ employees / 2,200+ engineers
LINE Corporation
2,200+ employees / 600+ engineers
Engineers are from 29 nationalities
and the ratio of foreign employees is 37%
Slide 9
Slide 9 text
About LINE Database
Slide 10
Slide 10 text
RDBMS
Databases
NOSQL
Slide 11
Slide 11 text
Percentage of Databases
Redis
77%
HBase
12%
MongoDB
7%
Elasticsearch
4%
MySQL
84%
Cubrid
14%
SQLServer
1%
Oracle Database
1%
RDBMS NOSQL
Slide 12
Slide 12 text
Basic Service Configuration
Slide 13
Slide 13 text
l 20 Member at a Database Department
DBA
DB1 Team Oracle, ElasticSearch
DB2 Team SQL Server
DB3 Team MongoDB
BigDataPlatformTeamHbase,Hadoop,Cubrid
MySQL
Redis
Slide 14
Slide 14 text
l Middleware construction such as MySQL
l Schema Management
l Backup Recovery Management
l ACL Management
l Query Tuning
l Troubleshooting
l Index Design
l Table Design
l Development of Operation Tool
REGULAR TASKS
Slide 15
Slide 15 text
l One DBA for each service
l About 15 services per DBA
l Confirm new service information to details
l QPS/OPS
l Daily data growth
l Data retention period
l Scalability
l Query check
l Database server spec is decided by each DBA
OUR WAY OF WORKING
Slide 16
Slide 16 text
MySQL Operation
Slide 17
Slide 17 text
l 4000+ MySQL Instances
l MySQL Enterprise Edition(5%) and MySQL Community Edition(95%)
MySQL at LINE
l Maximum 12 Read Replicas in one service
l Maximum 47 shards in one service
Slide 18
Slide 18 text
Percentage of MySQL Version
MySQL1 year ago MySQLNow
5.7
32%
5.6
44%
5.5
21%
5.1
3%
5.7
51%
5.6
32%
5.5
15%
5.1
2%
Slide 19
Slide 19 text
MySQL Operation
l Difficulties in large scale MySQL operation
l The instances are rapidly increasing and management is difficult
l There are many services
l Different services have different MySQL versions and server spec
l It takes lots of time for Manual operation
l Installing, Database ACL , changing Schema
l Communication cost with developers is high
l Due to the complex DB configuration, it depends on a particular DBA
Slide 20
Slide 20 text
MySQL Operation
l In order to solve them, Improve by
l Standardization
l Automation
Slide 21
Slide 21 text
l There are no fork products like Percona Server and MariaDB
l Fixed in same minor version
l Perform major version upgrades aggressively
l It might change from MySQL8.0
Standardization
l No complex MySQL configuration
l Replication filter
l Cascade Replication
l All the same High Availability Solution
Slide 22
Slide 22 text
Standardization
l Integrated Management tool for DBA (called mondb+)
Slide 23
Slide 23 text
l In-house tool for all database products
l Web UI
l The following items cab be viewed and set
l Service / Instance information list
l Automatic installation
l Automatic slave addition
l Slow log information
l Backup information
l Real time QPS information
l Alert information
l And so on..
Standardization
Slide 24
Slide 24 text
l Automation
l Installation
l Private Cloud
l ACL Registration
l Backup setting and Management
l High Availability Configuration
l Monitoring setting
l Add Slave
Automation
Slide 25
Slide 25 text
l Automation Install MySQL
AS-IS
Manual Install
TO-BE
Auto Install
Automation
Slide 26
Slide 26 text
l Private Cloud
l Called “Verda”
l Based on OpenStack
l MySQL
l In-house component
l Redis Cluster
l OpenStack Trove
l Elastic Search
l In-house component
l k8s
l Launched project for cost
optimization using MySQL
on k8s
Automation
Slide 27
Slide 27 text
Enterprise Edition
l Thread Pool
l Function for MySQL Enterprise Edition
l Improve performance by limiting concurrency execution of MySQL connection threads
Source: . - // -8
Slide 28
Slide 28 text
Thread Pool
l Thread Pool
l Benchmarking of Write Only
1 0 1 0 1 0 1 0 1 0
2 0)43
1 0 553 ) 1 0 553 )(
Slide 29
Slide 29 text
Thread Pool
l The graph is one of services before implementing Thread pool
l Some problems
l Sudden traffic with push notifications and events
l A lot of updates come at once, commits get stuck
l Running Threads builds up
Slide 30
Slide 30 text
Thread Pool
l It is stable after implementing Thread Pool
l Recommend to implement latest version of MySQL
l Good for simple queries and high concurrency
Slide 31
Slide 31 text
MySQL 8.0
Slide 32
Slide 32 text
MySQL8.0
l There is no MySQL8 in production yet
l Investigate all new features
l About over 250 new functions at 8.0.15
l 66 /: . 8 6 / 1/ 6 1/ . 6 .- 6 1 0 6 -0 /: .
l Even with 8.0.16, there are about 36 new functions such as CHECK Constraints
l 66 /: . 8 6 / 1/ 6 /: . / -06 0 0 . - 0 ..: 8 -. .
l Take a lot of benchmarks
l I/O Bound or CPU Bound
l Read/Write or Write Only
l Option
l collation_server
l log_slow_extra
l sync_binlog
l Encryption(InnoDB log file, binary log, undo log)
Slide 33
Slide 33 text
MySQL8.0
l Benchmark Result by difference of collation_server
l New features
l Instant Alter Table
l Row Based Replication Monitoring Function
Slide 34
Slide 34 text
MySQL8.0
l Benchmark result of TPS by difference of Collation
l Sysbench (Read/Write)
l All data is on memory
l The ratio is based on the default utf8mb4_0900_ai_ci of MySQL 8.0 as 1
() 0 7 1 8 9 6 3 0 7 38 0 7 4 0 7 4 5
9 .
9 .
9 .
Slide 35
Slide 35 text
Instant ALTER TABLE
l An operation that ALTER TABLE processing completes immediately by changing only Data Dictionary
l Add column
l Set and Delete Defaults value
l Add VIRTUAL column
l Add elements of ENUM,SET Type
l Limitations for instant add column
l Adding columns at last, not in the middle of existing columns
l Except COMPRESSD row format table
l Except the table has Full Text Index
l Except Temporary Table
l Changing the schema of a large table was hard
l In MySQL 5.7 and earlier, We operated in various ways
Slide 36
Slide 36 text
l Operation of adding column before MySQL 5.5
l pt-online-schema-change(pt-osc)
l Implement maintenance
Adding Column Before MySQL5.5
Slide 37
Slide 37 text
l Operation of adding column of MySQL 5.6 and MySQL5.7
l By using InnoDB Online DDL
l But, Slave delays for large tables
l Frequently, adding columns using Rolling Schema Upgrade
Adding Column MySQL5.6 and 5.7
Slide 38
Slide 38 text
l Alter table of slaves first by using Online DDL and Alter the table of master at last.
l set sql_log_bin=OFF
l ALTER TABLE t1 ADD col1 int;
Adding Column MySQL5.6 and 5.7
Slide 39
Slide 39 text
l Have to care 2 points
1. binlog_format = ROW
l Adding to anything other than the last column of the table will cause a replication error.
l Adding to the last column
2. binlog_format = STATEMENT
l A replication error occurs if INSERT INTO is executed without specifying a column
l INSERT INTO a values (1,null);
l Check those statements from sys.statement_analysis
Adding Column MySQL5.6 and 5.7
Slide 40
Slide 40 text
l MySQL 5.7 and earlier, it took a long time to identify the cause of the delay SQL
Row-based replication Monitoring Function
Slide 41
Slide 41 text
l Add a new stage from MySQL 8.0
l Applying batch of row changes (write) Inserting in RBR
l Applying batch of row changes (update) Updating in RBR
l Applying batch of row changes (delete) Deleting in RBR
Row-based replication Monitoring Function
Slide 42
Slide 42 text
l binlog_rows_query_log_events option
l SQL statement is displayed in the INFO column of SHOW PROCESSLIST
l It needs to set ON because default is OFF
Row-based replication Monitoring Function
Slide 43
Slide 43 text
MySQL Monitoring
Slide 44
Slide 44 text
DBONE Project
MySQL
Enterprise
Monitor
Enterprise Monitor
Remin/Relumin
Cloudera
MMS
nPod
l A lot of Monitoring tools
Slide 45
Slide 45 text
l Integrated monitoring of multiple solutions
l Combination of 16 OSS to realize at low cost
l Make it easy to change and add new solutions
l Low communication costs by sharing UI and screen with developers
l Send alerts to Slack, mail and LINE notify
DBONE Project
Role Solution
Collector Prometheus exporter(mysqld_exporter) / td-agent
Stored Prometheus / Elastic Search
Display Grafana
Alert Alertmanager / alerta
Slide 46
Slide 46 text
≈
DBONE Project
Slide 47
Slide 47 text
DBONE Project
Slide 48
Slide 48 text
DBONE Project
Slide 49
Slide 49 text
Real Time Monitoring
l myStatusgo
l In-house Real Time Monitoring Tool for MySQL
l Some shards and multiple slaves in each service
l MySQL information of the whole service (see in one screen)
l Github: 0 / . . /
l Display the following information
l QPS
l OS Resource
l Threads_Info
l Statement_digest
l Buffer Pool Info
l Handler Info / InnoDB row Info
l InnoDB Lock Info
l Table IO Statistic
Slide 50
Slide 50 text
Real Time Monitoring
l QPS and OS Resource
l Com_xx
l OS metric is obtained from node exporter
Slide 51
Slide 51 text
Real Time Monitoring
l Threads
l Display currently Running Threads
l Get from Performance_schema.threads table
Slide 52
Slide 52 text
Real Time Monitoring
l Statement_Digest Info
l Understand the trend of statement
l Get from events_statements_summary_by_digest table
Slide 53
Slide 53 text
Real Time Monitoring
l Replication Info
l Get from SHOW SLAVE STATUS
Slide 54
Slide 54 text
Real Time Monitoring
l Handlar/InnoDB Row Info
l Get from handlar_xx of SHOW GLOBAL STATUS
l Get from innodb_row_xx of SHOW GLOBAL STATUS
Slide 55
Slide 55 text
Real Time Monitoring
l InnoDB Buffer Pool Info
l Check Innodb Buffer Pool status
l Get from innodb_buffer_pool_pages_xx of Information_schema.INNODB_METRIC table
Slide 56
Slide 56 text
Real Time Monitoring
l Table I/O Statistics Info
l Information of I/O request of each table
l Information on the number of SELECT/DML requests for each table
l Use performance_schema.table_io_waits_summary_by_table and file_summary_by_instance
Slide 57
Slide 57 text
DEMO
Slide 58
Slide 58 text
l MySQL on k8s
l AWS (RDS)
Next Step
l MySQL HA
l Orchestrator
l MySQL 8.0
Slide 59
Slide 59 text
Looking for Engineer
Slide 60
Slide 60 text
QUESTIONS ?
Slide 61
Slide 61 text
THANK YOU