$30 off During Our Annual Pro Sale. View Details »

My SQL at LINE

My SQL at LINE

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

LINE Developers
PRO

August 17, 2019
Tweet

More Decks by LINE Developers

Other Decks in Programming

Transcript

  1. MySQL at LINE
    Kentaro Kitagawa, IT service center - database department - db1 team
    2019/08/17 COSCUP 2019

    View Slide

  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

    View Slide

  3. • About LINE Corporation
    • About LINE Database
    • MySQL Operation
    • MySQL 8.0
    • MySQL Monitoring
    Agenda

    View Slide

  4. About LINE Corp

    View Slide

  5. MAU:164million
    (Top 4 Regions)
    81million 44million 21million 18million

    View Slide

  6. Daily Messages :
    5billion+ (max)
    4.1billion+(Avg.)
    Peak of Users Traffic :
    1Tbps+
    Physical Servers :
    40,000+

    View Slide

  7. View Slide

  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%

    View Slide

  9. About LINE Database

    View Slide

  10. RDBMS
    Databases
    NOSQL

    View Slide

  11. Percentage of Databases
    Redis
    77%
    HBase
    12%
    MongoDB
    7%
    Elasticsearch
    4%
    MySQL
    84%
    Cubrid
    14%
    SQLServer
    1%
    Oracle Database
    1%
    RDBMS NOSQL

    View Slide

  12. Basic Service Configuration

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  16. MySQL Operation

    View Slide

  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

    View Slide

  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%

    View Slide

  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

    View Slide

  20. MySQL Operation
    l In order to solve them, Improve by
    l Standardization
    l Automation

    View Slide

  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

    View Slide

  22. Standardization
    l Integrated Management tool for DBA (called mondb+)

    View Slide

  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

    View Slide

  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

    View Slide

  25. l Automation Install MySQL
    AS-IS
    Manual Install
    TO-BE
    Auto Install
    Automation

    View Slide

  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

    View Slide

  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

    View Slide

  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 )(

    View Slide

  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

    View Slide

  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

    View Slide

  31. MySQL 8.0

    View Slide

  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)

    View Slide

  33. MySQL8.0
    l Benchmark Result by difference of collation_server
    l New features
    l Instant Alter Table
    l Row Based Replication Monitoring Function

    View Slide

  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 .

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  43. MySQL Monitoring

    View Slide

  44. DBONE Project
    MySQL
    Enterprise
    Monitor
    Enterprise Monitor
    Remin/Relumin
    Cloudera
    MMS
    nPod
    l A lot of Monitoring tools

    View Slide

  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

    View Slide


  46. DBONE Project

    View Slide

  47. DBONE Project

    View Slide

  48. DBONE Project

    View Slide

  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

    View Slide

  50. Real Time Monitoring
    l QPS and OS Resource
    l Com_xx
    l OS metric is obtained from node exporter

    View Slide

  51. Real Time Monitoring
    l Threads
    l Display currently Running Threads
    l Get from Performance_schema.threads table

    View Slide

  52. Real Time Monitoring
    l Statement_Digest Info
    l Understand the trend of statement
    l Get from events_statements_summary_by_digest table

    View Slide

  53. Real Time Monitoring
    l Replication Info
    l Get from SHOW SLAVE STATUS

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  57. DEMO

    View Slide

  58. l MySQL on k8s
    l AWS (RDS)
    Next Step
    l MySQL HA
    l Orchestrator
    l MySQL 8.0

    View Slide

  59. Looking for Engineer


    View Slide

  60. QUESTIONS ?

    View Slide

  61. THANK YOU

    View Slide