MySQL Performance for DevOps

MySQL Performance for DevOps

• 1.
  MySQL Performance for DevOps SvetaSmirnova Principal Support Engineering Coordinator
• 2.
  l • MySQL Supportengineer • Author of • MySQL Troubleshooting • JSON UDF functions • FILTER clause for MySQL • Speaker • Percona Live, OOW, Fosdem, DevConf, HighLoad... Sveta Smirnova
• 3.
  •Introduction •Memory •Disk •CPU •Network •Query Tuning Table ofContents 3 ©2022 | Percona
• 4.
  Introduction
• 5.
  • Database server •25+ years of history • Popular forks • Percona Server for MySQL • MariaDB Server • Replication support from the beginning Percona Operator for MySQL What is MySQL? 5 ©2022 | Percona
• 6.
  Connectors: C, JDBC,ODBC, Python, ... Connection Pool: Authentication, Caches SQL interface Parser Optimizer Caches and Buffers: Global Engine-specific Storage engines: InnoDB, MyRocks, ... File system: Data, Index, logs, other files - mysqld - Connectors - Optimizer - Caches - Storage Engines - Management MySQL Architecture 6 ©2022 | Percona
• 7.
  Query Configuration Hardware What Affects Performance? 7©2022 | Percona
• 8.
  • SET [GLOBAL]var = NEW VALUE How to Change System Variables 8 ©2022 | Percona
• 9.
  • SET [GLOBAL]var = NEW VALUE • Command-line option --var=new value How to Change System Variables 9 ©2022 | Percona
• 10.
  • SET [GLOBAL]var = NEW VALUE • Command-line option --var=new value • Configuration file In the default location • Specified by option --defaults-file How to Change System Variables 10 ©2022 | Percona
• 11.
  In deploy/cr.yaml spec: secretsName: my-cluster-secrets pxc: ... configuration:| [mysqld] wsrep_debug=CLIENT max_connections=250 How to Change with Kubernetes 11 ©2022 | Percona
• 12.
  In deploy/cr.yaml In aConfigMap kubectl create configmap cluster1-pxc --from-file=my.cnf How to Change with Kubernetes 12 ©2022 | Percona
• 13.
  In deploy/cr.yaml In aConfigMap Using a Secret Object How to Change with Kubernetes 13 ©2022 | Percona
• 14.
  Memory
• 15.
  • Frequently accesseddata • InnoDB buffer pool • MyRocks recently inserted data • Table and thread cache Memory is Faster than Disk 15 ©2022 | Percona
• 16.
  • Frequently accesseddata • InnoDB buffer pool • MyRocks recently inserted data • Table and thread cache • Temporary objects • Implicit temporary tables • Prepared statements • User variables Memory is Faster than Disk 16 ©2022 | Percona
• 17.
  • Frequently accesseddata • InnoDB buffer pool • MyRocks recently inserted data • Table and thread cache • Temporary objects • Implicit temporary tables • Prepared statements • User variables • Frequently accessed data should be in memory Memory is Faster than Disk 17 ©2022 | Percona
• 18.
  • No swapping •sysctl vm.swappiness=1 System Memory Configuration 18 ©2022 | Percona
• 19.
  • No swapping •sysctl vm.swappiness=1 • NUMA interleave • Enable in BIOS System Memory Configuration 19 ©2022 | Percona
• 20.
  • No swapping •sysctl vm.swappiness=1 • NUMA interleave • Enable in BIOS • Kubernetes resources: requests: memory: 32G ... # limits: # memory: 32G System Memory Configuration 20 ©2022 | Percona
• 21.
  • innodb bufferpool size • Ideally should hold active data set MySQL Memory Configuration 21 ©2022 | Percona
• 22.
  • innodb bufferpool size • table open cache • The number of open tables for all threads • Increase when Connections in the PROCESSLIST are waiting for opening a table Value of global status variable Opened tables is larger than Open tables MySQL Memory Configuration 22 ©2022 | Percona
• 23.
  • innodb bufferpool size • table open cache • table definition cache • Size of the cache for table definitions • Increase when Value of Opened table definitions is larger than Open table definitions MySQL Memory Configuration 23 ©2022 | Percona
• 24.
  • innodb bufferpool size • table open cache • table definition cache • Increase OS open files limit if needed MySQL Memory Configuration 24 ©2022 | Percona
• 25.
  Disk
• 26.
  • Tables • Logfiles • Engine • Replication • Diagnostic • Temporary files MySQL Stores Data on the Disk 26 ©2022 | Percona
• 27.
  • Faster isbetter • SSD • NVMe • Spinning disk System Disk Configuration 27 ©2022 | Percona
• 28.
  • Faster isbetter • SSD • NVMe • Spinning disk • Parallel writes System Disk Configuration 28 ©2022 | Percona
• 29.
  • Faster isbetter • SSD • NVMe • Spinning disk • Parallel writes • Battery-backed cache System Disk Configuration 29 ©2022 | Percona
• 30.
  • innodb logfile size • Should hold changes for an hour InnoDB Disk Configuraiton 30 ©2022 | Percona
• 31.
  • innodb logfile size • Should hold changes for an hour • Too low InnoDB Disk Configuraiton 31 ©2022 | Percona
• 32.
  • innodb logfile size • Should hold changes for an hour • Good InnoDB Disk Configuraiton 32 ©2022 | Percona
• 33.
  • innodb logfile size • innodb io capacity • Default is too small for fast disks • Up to number of IOPS your disk can handle • Do not set too high! InnoDB Disk Configuraiton 33 ©2022 | Percona
• 34.
  • innodb logfile size • innodb io capacity • innodb flush method • In most cases: O DIRECT • Test on your filesystem! InnoDB Disk Configuraiton 34 ©2022 | Percona
• 35.
  • Changing these compromizedurability! Synchronization 35 ©2022 | Percona
• 36.
  • Changing these compromizedurability! • innodb flush log at trx commit 1: full ACID, default Synchronization 36 ©2022 | Percona
• 37.
  • Changing these compromizedurability! • innodb flush log at trx commit 1: full ACID, default 2: logs written at each commit, flushed per second MySQL can handle up to 1M INSERTs per second Safe with PXC, Galera and InnoDB Clusters Synchronization 37 ©2022 | Percona
• 38.
  • Changing these compromizedurability! • innodb flush log at trx commit 1: full ACID, default 2: logs written at each commit, flushed per second 0: logs are written and flushed once per second Synchronization 38 ©2022 | Percona
• 39.
  • Changing these compromizedurability! • innodb flush log at trx commit 1: full ACID, default 2: logs written at each commit, flushed per second 0: logs are written and flushed once per second • Once per second not guaranteed for 0 or 2 DDL can cause faster flushing Scheduling may delay flushing Synchronization 39 ©2022 | Percona
• 40.
  • Changing these compromizedurability! • innodb flush log at trx commit • sync binlog 0: Synchronization handled by the system 1: At each transaction commit, default No transaction lost N: After N binary log group commits In case of power or OS crash not flushed transactions can be lost Synchronization 40 ©2022 | Percona
• 41.
  CPU
• 42.
  • One threadper connection • CPU used only for active threads How MySQL Uses CPU 42 ©2022 | Percona
• 43.
  • One threadper connection • CPU used only for active threads • Background work by storage engines How MySQL Uses CPU 43 ©2022 | Percona
• 44.
  Server Storage Engine Connection andEngine Threads 44 ©2022 | Percona
• 45.
  ? <= CPUcores? What Happens with Threads 45 ©2022 | Percona
• 46.
  ? <= CPUcores? Yes Executed simultaneously What Happens with Threads 46 ©2022 | Percona
• 47.
  ? <= CPUcores? Yes Executed simultaneously No Wait in a queue What Happens with Threads 47 ©2022 | Percona
• 48.
  ? <= CPUcores? Yes Executed simultaneously No Wait in a queue ? Does the disk support parallel write? What Happens with Threads 48 ©2022 | Percona
• 49.
  ? <= CPUcores? Yes Executed simultaneously No Wait in a queue ? Does the disk support parallel write? Yes Write happens What Happens with Threads 49 ©2022 | Percona
• 50.
  ? <= CPUcores? Yes Executed simultaneously No Wait in a queue ? Does the disk support parallel write? Yes Write happens No Wait in a queue What Happens with Threads 50 ©2022 | Percona
• 51.
  • IO scheduler •[noop] or [deadline] • sudo echo noop > /sys/block/DISK/queue/scheduler or sudo echo deadline > /sys/block/DISK/queue/scheduler System CPU Configuration 51 ©2022 | Percona
• 52.
  • IO scheduler •[noop] or [deadline] • sudo echo noop > /sys/block/DISK/queue/scheduler or sudo echo deadline > /sys/block/DISK/queue/scheduler • CPU governor • Set to performance System CPU Configuration 52 ©2022 | Percona
• 53.
  • IO scheduler •[noop] or [deadline] • sudo echo noop > /sys/block/DISK/queue/scheduler or sudo echo deadline > /sys/block/DISK/queue/scheduler • CPU governor • Set to performance • More cores is better System CPU Configuration 53 ©2022 | Percona
• 54.
  • Kubernetes resources: requests: cpu: "16" limits: cpu:"32" Kuberetes CPU Configuration 54 ©2022 | Percona
• 55.
  • innodb threadconcurrency • 0 or number of CPU cores Thread Pool MySQL CPU Configuration 55 ©2022 | Percona
• 56.
  Network
• 57.
  • User connections •Replication • Each replica creates one connection to the source server MySQL Uses Network for 57 ©2022 | Percona
• 58.
  • As fastas possible • Speed of the line RTT • Bandwidth • Stability To avoid TCP packet re-submission Network Should Be 58 ©2022 | Percona
• 59.
  • Clients canwork • Asynchronous replica will delay • Synchronous clusters will be not functional • Node disconnects with default options • Very slow response times with adjusted configuration On the Internet connection 59 ©2022 | Percona
• 60.
  Query Tuning
• 61.
  • You communicatewith database using queries • Even via NoSQL interface • They are not SQL queries, but still queries Heart of the application 61 ©2022 | Percona
• 62.
  • You communicatewith database using queries • Even via NoSQL interface • They are not SQL queries, but still queries • Data, that you request, matters • 1,000,000,000 rows vs 1 row Heart of the application 62 ©2022 | Percona
• 63.
  Query sent Connection Pool: Authentication,Caches; SQL interface; Parser Optimizer Storage engines Hardware Query execution workflow 63 ©2022 | Percona
• 64.
  d001 d003 d008 d009 d003****** d009****** d008****** d009****** d001****** d003****** d009****** d008****** d009****** d001****** d008****** d008****** - B-Tree Mostly -LSM Tree - R-Tree Spatial - Hash Memory SE - Engine’s MySQL Indexes 64 ©2022 | Percona
• 65.
  mysql> SELECT first_name,last_name, salary FROM employees -> JOIN salaries USING(emp_no) -> WHERE salary = (SELECT MAX(salary) FROM salaries); +------------+-----------+--------+ | first_name | last_name | salary | +------------+-----------+--------+ | Tokuyasu | Pesch | 158220 | +------------+-----------+--------+ 1 row in set (2,05 sec) Effect of an Index 65 ©2022 | Percona
• 66.
  mysql> ALTER TABLEsalaries ADD INDEX(salary); Query OK, 0 rows affected (9,65 sec) Records: 0 Duplicates: 0 Warnings: 0 Effect of an Index 66 ©2022 | Percona
• 67.
  mysql> SELECT first_name,last_name, salary FROM employees -> JOIN salaries USING(emp_no) -> WHERE salary = (SELECT MAX(salary) FROM salaries); +------------+-----------+--------+ | first_name | last_name | salary | +------------+-----------+--------+ | Tokuyasu | Pesch | 158220 | +------------+-----------+--------+ 1 row in set (0,00 sec) Effect of an Index 67 ©2022 | Percona
• 68.
  MySQL Query Tuningfor DevOps How MySQL Indexes Work 68 ©2022 | Percona
• 69.
  • Temporary tables •tmp table size • max heap table size • default tmp storage engine Optimizer Configuration 69 ©2022 | Percona
• 70.
  • Temporary tables •Buffers for query execution • join buffer size JOIN conditions, not using indexes Optimizer Configuration 70 ©2022 | Percona
• 71.
  • Temporary tables •Buffers for query execution • join buffer size • read buffer size Caching indexes for ORDER BY Bulk insert into partitions Caching result of nesting queries Optimizer Configuration 71 ©2022 | Percona
• 72.
  • Temporary tables •Buffers for query execution • join buffer size • read buffer size • read rnd buffer size Multi-Range Read optimization Optimizer Configuration 72 ©2022 | Percona
• 73.
  • Temporary tables •Buffers for query execution • join buffer size • read buffer size • read rnd buffer size • select into buffer size SELECT INTO OUTFILE SELECT INTO DUMPFILE Optimizer Configuration 73 ©2022 | Percona
• 74.
  • Temporary tables •Buffers for query execution • join buffer size • read buffer size • read rnd buffer size • select into buffer size • sort buffer size ORDER BY GROUP BY Optimizer Configuration 74 ©2022 | Percona
• 75.
  • Temporary tables •Buffers for query execution • join buffer size • read buffer size • read rnd buffer size • select into buffer size • sort buffer size • Change only at the session level! Optimizer Configuration 75 ©2022 | Percona
• 76.
  • Hardware RAM: moreis better Disk: SSD or NVMe CPU: more cores, better concurrency Net: highest speed possible Conclusion 76 ©2022 | Percona
• 77.
  • Hardware • Configuration •InnoDB innodb buffer pool size innodb log file size innodb thread concurrency innodb io capacity innodb flush method innodb flush log at trx commit • Server sync binlog table open cache table definition cache Conclusion 77 ©2022 | Percona
• 78.
  • Hardware • Configuration •Query Performance • Add indexes • Adjust Optimization buffers tmp table size join buffer size read buffer size read rnd buffer size select into buffer size sort buffer size Conclusion 78 ©2022 | Percona
• 79.
  Troubleshooting hardware resources Troubleshootingconfiguration issues MySQL Query Tuning for DevOps Percona Monitoring and Management Percona Kubernetes Operators More information 79 ©2022 | Percona
• 80.
  SvetaSmirnova svetasmirnova @svetsmirnova svetsmirnova Thank you! 80 ©2022| Percona