My SQL at LINE

2019/8/17に行われたCOSCUP 2019での登壇資料です
https://coscup.org/2019/

Transcript

1. MySQL at LINE Kentaro Kitagawa, IT service center - database department - db1 team 2019/08/17 COSCUP 2019
2. 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
3. • About LINE Corporation • About LINE Database • MySQL Operation • MySQL 8.0 • MySQL Monitoring Agenda
4. About LINE Corp
5. MAU:164million (Top 4 Regions) 81million 44million 21million 18million
6. Daily Messages : 5billion+ (max) 4.1billion+(Avg.) Peak of Users Traffic : 1Tbps+ Physical Servers : 40,000+
8. 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%
9. About LINE Database
10. RDBMS Databases NOSQL
11. Percentage of Databases Redis 77% HBase 12% MongoDB 7% Elasticsearch 4% MySQL 84% Cubrid 14% SQLServer 1% Oracle Database 1% RDBMS NOSQL
12. Basic Service Configuration
13. l 20 Member at a Database Department DBA DB1 Team
    
    Oracle, ElasticSearch DB2 Team
    
    SQL Server DB3 Team
    
    MongoDB BigDataPlatformTeam
    
    Hbase,Hadoop,Cubrid MySQL Redis
14. 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
15. 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
16. MySQL Operation
17. 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
18. Percentage of MySQL Version MySQL
    1 year ago MySQL
    Now 5.7 32% 5.6 44% 5.5 21% 5.1 3% 5.7 51% 5.6 32% 5.5 15% 5.1 2%
19. 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
20. MySQL Operation l In order to solve them, Improve by l Standardization l Automation
21. 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
22. Standardization l Integrated Management tool for DBA (called mondb+)
23. 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
24. 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
25. l Automation Install MySQL AS-IS Manual Install TO-BE Auto Install Automation
26. 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
27. Enterprise Edition l Thread Pool l Function for MySQL Enterprise Edition l Improve performance by limiting concurrency execution of MySQL connection threads Source: . - // -8
28. 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 )(
29. 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
30. 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
31. MySQL 8.0
32. 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)
33. MySQL8.0 l Benchmark Result by difference of collation_server l New features l Instant Alter Table l Row Based Replication Monitoring Function
34. 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 .
35. 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
36. l Operation of adding column before MySQL 5.5 l pt-online-schema-change(pt-osc) l Implement maintenance Adding Column Before MySQL5.5
37. 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
38. 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
39. 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
40. l MySQL 5.7 and earlier, it took a long time to identify the cause of the delay SQL Row-based replication Monitoring Function
41. 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
42. 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
43. MySQL Monitoring
44. DBONE Project MySQL Enterprise Monitor Enterprise Monitor Remin/Relumin Cloudera MMS nPod l A lot of Monitoring tools
45. 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
46. ≈ DBONE Project
47. DBONE Project
48. DBONE Project
49. 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
50. Real Time Monitoring l QPS and OS Resource l Com_xx l OS metric is obtained from node exporter
51. Real Time Monitoring l Threads l Display currently Running Threads l Get from Performance_schema.threads table
52. Real Time Monitoring l Statement_Digest Info l Understand the trend of statement l Get from events_statements_summary_by_digest table
53. Real Time Monitoring l Replication Info l Get from SHOW SLAVE STATUS
54. 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
55. 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
56. 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
57. DEMO
58. l MySQL on k8s l AWS (RDS) Next Step l MySQL HA l Orchestrator l MySQL 8.0
59. Looking for Engineer  
      
          
60. QUESTIONS ?
61. THANK YOU