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
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%
l 20 Member at a Database Department DBA DB1 Team Oracle, ElasticSearch DB2 Team SQL Server DB3 Team MongoDB BigDataPlatformTeamHbase,Hadoop,Cubrid MySQL Redis
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
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
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
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
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
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
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
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
Enterprise Edition l Thread Pool l Function for MySQL Enterprise Edition l Improve performance by limiting concurrency execution of MySQL connection threads Source: . - // -8
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
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
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)
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 .
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
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
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
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
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
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
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
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
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
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
line_developers
toricky6
prof18
mipypf
miyuki2203
minako__ph
ostk0069
ruyadorno
mugi_uno
yusukebe
os1ma
kinocoboy2
kasacchiful
geeforr
jeffersonlam
shlominoach
akmur
lara
chriscoyier
maggiecrowley
tanoku
bcantrill
samanthasiow
mraible
jlugia