My SQL at LINE

My SQL at LINE

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

53850955f15249a1a9dc49df6113e400?s=128

LINE Developers

August 17, 2019
Tweet

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+
  7. None
  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 BigDataPlatformTeamHbase,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 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%
  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