Movatterモバイル変換


[0]ホーム

URL:


Sveta Smirnova, profile picture
Uploaded bySveta Smirnova
PDF, PPTX334 views

MySQL Database Monitoring: Must, Good and Nice to Have

The document discusses MySQL database monitoring and performance, emphasizing the importance of collecting information to resolve support cases. It outlines practices for monitoring, including the use of slow query logs and performance schema, along with error messages related to storage and file operations. Best practices and configurations related to database performance are highlighted throughout the document.

Embed presentation

Download as PDF, PPTX
MySQL Database Monitoring:Must, Good and Nice to HaveSveta SmirnovaPrincipal Support Engineering Coordinator
• MySQL Support Engineer• AuthorMySQL TroubleshootingMySQL Cookbook, 4th Edition• JSON UDF functions• FILTER clause for MySQL• Speaker• Percona Live, OOW, Fosdem,DevConf, HighLoad...Sveta Smirnova
• Solving Support cases• Sometimes we cannot resolve the issuebecause necessary information was notcollected in time• Reasons are• Lack of knowledge• Fear of performance impact• Let’s discuss what can help and forwhich price!Motivation3© Copyright 2023 Percona ® LLC. All rights reserved
must Should be always ONMust-Good-Nice4© Copyright 2023 Percona ® LLC. All rights reserved
must Should be always ONgood Useful data, little effect on overallperformanceMust-Good-Nice5© Copyright 2023 Percona ® LLC. All rights reserved
must Should be always ONgood Useful data, little effect on overallperformancenice May slow down database but veryuseful in certain situationsMust-Good-Nice6© Copyright 2023 Percona ® LLC. All rights reserved
2023-05-16T12:44:14.776227Z 0 [System] [MY-011323] [Server]X Plugin ready for connections.Bind-address: ’::’ port: 13020, socket: /tmp/mysqlx.1.sock2023-05-16T12:44:14.776295Z 0 [System] [MY-010931] [Server]/home/sveta/Percona-Server-8.0.32-24/bin/mysqld: ready for connections.Version: ’8.0.32-24’ socket: ’/tmp/mysqld.1.sock’ port: 13000Percona Server (GPL), Release 24, Revision e5c6e9d2.Error Log: Normal Operation7© Copyright 2023 Percona ® LLC. All rights reserved
2023-05-16T21:01:34.090936Z 0 [System] [MY-010116] [Server]/Percona-Server-8.0.32-24/bin/mysqld (mysqld 8.0.32-24) starting as process 20063972023-05-16T21:01:34.103406Z 0 [Warning] [MY-010091] [Server] Can’t create test file/Percona-Server-8.0.32-24/data/mysqld_tmp_file_case_insensitive_test.lower-test2023-05-16T21:01:34.152150Z 0 [ERROR] [MY-010187] [Server] Could not open file’/Percona-Server-8.0.32-24/data/Delly-7390.err’ for error logging: Permission denied2023-05-16T21:01:34.152288Z 0 [ERROR] [MY-010119] [Server] Aborting2023-05-16T21:01:34.152856Z 0 [System] [MY-010910] [Server]/Percona-Server-8.0.32-24/bin/mysqld: Shutdown complete (mysqld 8.0.32-24)Percona Server (GPL), Release 24, Revision e5c6e9d2-debug.Error Log: Immediate Answer8© Copyright 2023 Percona ® LLC. All rights reserved
2023-05-17T00:06:38.658777Z 11 [ERROR] [MY-012639] [InnoDB] Write to file./test/joinit7.ibd failed at offset 80740352, 1048576 bytes should have been written,only 929792 were written. Operating system error number 28. Check that your OS andfile system support files of this size. Check also that the disk is not full ora disk quota exceeded.2023-05-17T00:06:38.658801Z 11 [ERROR] [MY-012640] [InnoDB] Error number 28 means’No space left on device’2023-05-17T00:06:38.658820Z 11 [Warning] [MY-012145] [InnoDB] Error while writing4194304 zeroes to ./test/joinit7.ibd starting at offset 796917762023-05-17T00:06:38.669221Z 11 [ERROR] [MY-013132] [Server] The table ’joinit7’ is fullError Log: Invisible Issue9© Copyright 2023 Percona ® LLC. All rights reserved
2023-05-17T06:50:19.749143Z 0 [ERROR] InnoDB: Operating system error number 2 in afile operation.May 17 13:50:22 Delly mysqld[24814]: 2023-05-17T06:50:19.429496Z 0 [Note]InnoDB: Apply batch completedMay 17 13:50:22 Delly mysqld[24814]: InnoDB: Progress in percent: 12 13 14 15 16 1718 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39May 17 13:50:22 Delly mysqld[24814]: 2023-05-17T06:50:19.304439Z 0 [Note]InnoDB: Starting an apply batch of log records to the database...Error Log: "Unexpected" Corruption10© Copyright 2023 Percona ® LLC. All rights reserved
• Always ON• Keep outside of data directory• Do not rotate too often• MUST HAVEError Log: Best Practices11© Copyright 2023 Percona ® LLC. All rights reserved
Database Performance Influencers12© Copyright 2023 Percona ® LLC. All rights reserved
Database Performance Influencers13© Copyright 2023 Percona ® LLC. All rights reserved
Query
• Collects slow queriesSlow Query Log15© Copyright 2023 Percona ® LLC. All rights reserved
• Collects slow queries• Tunable parameters• long_query_time• log_slow_admin_statements• log_queries_not_using_indexes• min_examined_row_limit• log_slow_replica_statementsSlow Query Log16© Copyright 2023 Percona ® LLC. All rights reserved
• Collects slow queries• Tunable parameters• Tunable verbositylog_slow_extralog_slow_verbositySlow Query Log17© Copyright 2023 Percona ® LLC. All rights reserved
# Time: 2023-05-17T11:08:23.065564Z# User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.003821 Lock_time: 0.000018 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytess_sent: 187SET timestamp=1684321703;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: minimal Verbosity18© Copyright 2023 Percona ® LLC. All rights reserved
# Time: 2023-05-17T11:08:30.038499Z# User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.001777 Lock_time: 0.000024 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytes_sent: 187# Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0# Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# Filesort: No Filesort_on_disk: No Merge_passes: 0SET timestamp=1684321710;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: standard Verbosity19© Copyright 2023 Percona ® LLC. All rights reserved
# Time: 2023-05-17T11:08:36.662992Z# User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.002478 Lock_time: 0.000033 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytes_sent: 187# Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0# InnoDB_trx_id: 0# Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# Filesort: No Filesort_on_disk: No Merge_passes: 0# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000# InnoDB_pages_distinct: 1SET timestamp=1684321716;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: full Verbosity20© Copyright 2023 Percona ® LLC. All rights reserved
# Time: 2023-05-17T11:08:43.170370Z# User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.001809 Lock_time: 0.000020 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytes_sent: 187# Profile_starting: 0.000675 Profile_starting_cpu: 0.000405Profile_Executing_hook_on_transaction_begin.: 0.000025Profile_Executing_hook_on_transaction_begin._cpu: 0.000023Profile_starting: 0.000046 Profile_starting_cpu: 0.000046...Profile_closing_tables_cpu: 0.000075 Profile_freeing_items: 0.000075Profile_freeing_items_cpu: 0.000075 Profile_logging_slow_query: 0.000006Profile_logging_slow_query_cpu: 0.000006# Profile_total: 0.001829 Profile_total_cpu: 0.001556SET timestamp=1684321723;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: profiling verbosity21© Copyright 2023 Percona ® LLC. All rights reserved
# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== ============= ===== ======= ===== ==============# 1 0x61C2A843B38413D1 63.1758 99.9% 4 15.7939 1.81 SELECT titles# 2 0x6B17FAAA0C13F2B4 0.0307 0.0% 11 0.0028 0.00 SELECT departments# 3 0x90C659E5DE65EC22 0.0174 0.0% 1 0.0174 0.00 SELECT information_...# 4 0xE3A3649C5FAC418D 0.0151 0.0% 21 0.0007 0.00 SELECT# 5 0x02BBA8FDF2596F42 0.0121 0.0% 2 0.0060 0.01 SELECT departments# MISC 0xMISC 0.0077 0.0% 13 0.0006 0.0 <4 ITEMS>...Slow Query Log by pt_query_digest22© Copyright 2023 Percona ® LLC. All rights reserved
# Query 2: 0.00 QPS, 0.00x concurrency, ID 0x6B17FAAA0C13F2B4 at byte 20896111# Scores: V/M = 0.00# Time range: 2023-05-17T10:27:00 to 2023-05-17T11:08:43# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 21 11# Exec time 0 31ms 1ms 10ms 3ms 4ms 2ms 2ms# Lock time 52 324us 17us 64us 29us 40us 12us 23us# Rows sent 12 10 0 1 0.91 0.99 0.29 0.99...# SHOW TABLE STATUS FROM ‘test‘ LIKE ’departments’G# SHOW CREATE TABLE ‘test‘.‘departments‘G# EXPLAIN /*!50100 PARTITIONS*/SELECT * FROM ‘departments‘ WHERE dept_no=’d003’GSlow Query Log by pt_query_digest23© Copyright 2023 Percona ® LLC. All rights reserved
• Can grow fast with long_query_time=0• Does not depend on the database size• Depends on the query activity• Higher query rate - faster log growth• Safe to use with comparatively low load• For high load: plan log rotation• NICE TO HAVESlow Query Log: Disk IO24© Copyright 2023 Percona ® LLC. All rights reserved
• Statements events• Prepared statements• Statements digests• Stages eventsPerformance Schema25© Copyright 2023 Percona ® LLC. All rights reserved
• Examine more rows than return/change• Use disk instead of memory• Full table scan instead of index• This is not full list!Why statements are slow?26© Copyright 2023 Percona ® LLC. All rights reserved
mysql> SELECT THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS,-> ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED-> FROM performance_schema.events_statements_history-> WHERE NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1G********************** 1. row **********************TID: 10124SQL_TEXT: select emp_no, first_name, last_name from employeeRS: 97750RE: 397774CREATED_TMP_TABLES: 0NO_INDEX_USED: 1NO_GOOD_INDEX_USED: 0...Which Queries Do Not Use Indexes?27© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select name from setup_instruments where name like ’statement%’;+-----------------------------------------------+| name |+-----------------------------------------------+| statement/sql/select || statement/sql/create_table || statement/sql/create_index || statement/sql/alter_table || statement/sql/update || statement/sql/insert || statement/sql/insert_select |...Statements Configuration28© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select name from setup_consumers where name like ’%statement%’;+--------------------------------+| name |+--------------------------------+| events_statements_current | -- Current statements| events_statements_history | -- Last 10 statements| events_statements_history_long | -- Last 1K statements| statements_digest | -- Digests+--------------------------------+4 rows in set (0.00 sec)Statements Events Configuration29© Copyright 2023 Percona ® LLC. All rights reserved
• CREATED_TMP_DISK_TABLES• CREATED_TMP_TABLES• SELECT_FULL_JOIN• SELECT_RANGE_CHECK• SELECT_SCAN• SORT_MERGE_PASSES• SORT_SCANImportant Field Names30© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_CREATED_TMP_DISK_TABLES,-> SUM_SELECT_FULL_JOIN, SUM_SELECT_RANGE , SUM_SELECT_SCAN , SUM_NO_INDEX_USED,-> SUM_ROWS_SENT, SUM_ROWS_EXAMINED-> from events_statements_summary_by_digest where SUM_NO_INDEX_USED > 0G*************************** 1. row ***************************DIGEST: 3884185b07312b354c4918f2368d8fe2c431aeb8e39bf8ff5c3dcc6837DIGEST_TEXT: SELECT ‘c‘ FROM ‘sbtest1‘ WHERE ‘id‘ BETWEEN ? AND ?COUNT_STAR: 1501791SUM_CREATED_TMP_DISK_TABLES: 0SUM_SELECT_FULL_JOIN: 0SUM_SELECT_RANGE: 1501840SUM_SELECT_SCAN: 4SUM_NO_INDEX_USED: 4SUM_ROWS_SENT: 150872400SUM_ROWS_EXAMINED: 152872000...Digests: Which Do Not Use Indexes?31© Copyright 2023 Percona ® LLC. All rights reserved
• Combined statistics• events_statements_summary_by_account_by_event_name• events_statements_summary_by_host_by_event_name• events_statements_summary_by_thread_by_event_name• events_statements_summary_by_user_by_event_name• events_statements_summary_global_by_event_name5.7+: events_statements_summary_by_programDigest Tables32© Copyright 2023 Percona ® LLC. All rights reserved
• Combined statistics• Histogram Summary• events_statements_histogram_by_digest• events_statements_histogram_global• Do NOT mix with Optimizer histograms!Digest Tables33© Copyright 2023 Percona ® LLC. All rights reserved
• Combined statistics• Histogram Summary• events_statements_summary_by_digest• SCHEMA_NAME• DIGEST• DIGEST_TEXT8.0+: QUERY_SAMPLE_TEXTDigest Tables34© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select name from setup_consumers where name like ’%statement%’;+--------------------------------+| name |+--------------------------------+| events_statements_current | -- Current statements| events_statements_history | -- Last 10 statements| events_statements_history_long | -- Last 1K statements| statements_digest | -- Digests+--------------------------------+4 rows in set (0.00 sec)Statements Digests Configuration35© Copyright 2023 Percona ® LLC. All rights reserved
mysql> SELECT query, total_latency, no_index_used_count, rows_sent,-> rows_examined-> FROM sys.statements_with_full_table_scans-> WHERE db=’employees’ AND query NOT LIKE ’%performance_schema%’G********************** 1. row **********************query: SELECT COUNT ( ‘emp_no‘ ) FROM ... ‘emp_no‘ ) WHERE ‘title‘ = ?total_latency: 805.37 msno_index_used_count: 1rows_sent: 1rows_examined: 397774...sys: Which Do Not Use Indexes?36© Copyright 2023 Percona ® LLC. All rights reserved
• statement_analysis• statements_with_full_table_scans• statements_with_runtimes_in_95th_percentile• statements_with_sorting• statements_with_temp_tables• statements_with_errors_or_warningsImportant Views in sys Schema37© Copyright 2023 Percona ® LLC. All rights reserved
• Contains current prepared statementsTable prepared_statements_instances38© Copyright 2023 Percona ® LLC. All rights reserved
• Contains current prepared statements• Statistics by• Which thread owns the statement• How many times executed• Optimizer statistics, similar toevents_statements_*Table prepared_statements_instances39© Copyright 2023 Percona ® LLC. All rights reserved
• Instrumentsstatement/sql/prepare_sqlstatement/sql/execute_sqlstatement/com/Preparestatement/com/ExecutePrepared Statements: configuration40© Copyright 2023 Percona ® LLC. All rights reserved
• Instruments• Consumers• prepared_statements_instancesPrepared Statements: configuration41© Copyright 2023 Percona ® LLC. All rights reserved
• Instruments• Consumers• Sizemysql> select @@performance_schema_max_prepared_statements_instancesG*************************** 1. row ***************************@@performance_schema_max_prepared_statements_instances: -11 row in set (0.00 sec)Prepared Statements: configuration42© Copyright 2023 Percona ® LLC. All rights reserved
mysql1> prepare stmt from ’select count(*) from employees where hire_date > ?’;Query OK, 0 rows affected (0.00 sec)Statement preparedmysql1> set @hd=’1995-01-01’;Query OK, 0 rows affected (0.00 sec)mysql1> execute stmt using @hd;+----------+| count(*) |+----------+| 34004 |+----------+1 row in set (1.44 sec)Example: Prepared Statement43© Copyright 2023 Percona ® LLC. All rights reserved
mysql1> prepare stmt from ’select count(*) from employees where hire_date > ?’;Query OK, 0 rows affected (0.00 sec)Statement preparedmysql1> set @hd=’1995-01-01’;Query OK, 0 rows affected (0.00 sec)mysql1> execute stmt using @hd;+----------+| count(*) |+----------+| 34004 |+----------+1 row in set (1.44 sec)• Try EXECUTE with different valuesExample: Prepared Statement44© Copyright 2023 Percona ® LLC. All rights reserved
mysql2> select statement_name, sql_text, owner_thread_id, count_reprepare,-> count_execute, sum_timer_execute from prepared_statements_instancesG*************************** 1. row ***************************statement_name: stmtsql_text: select count(*) from employees where hire_date > ?owner_thread_id: 22count_reprepare: 0count_execute: 3sum_timer_execute: 41565613680001 row in set (0.00 sec)mysql1> drop prepare stmt;Query OK, 0 rows affected (0.00 sec)mysql2> select * from prepared_statements_instancesGEmpty set (0.00 sec)Example: diagnosis45© Copyright 2023 Percona ® LLC. All rights reserved
• Instrumented instruction called• Corresponding field updated• Counter incremented• Time recorded• Query recorded• ...• More often you call instruction - higheroverhead!How Performance Schema Works?46© Copyright 2023 Percona ® LLC. All rights reserved
Performance Schema Impact47© Copyright 2023 Percona ® LLC. All rights reserved
• Performance Schema triggers datacollection when hits event• For statement instrumentation thisusually means once per statement• Low impact• GOOD TO HAVEStatements Instrumentation: Impact48© Copyright 2023 Percona ® LLC. All rights reserved
• events_stages_* tablesStatements Deep Dive49© Copyright 2023 Percona ® LLC. All rights reserved
• events_stages_* tables• Same information as in tableINFORMATION_SCHEMA.PROCESSLIST or SHOWPROCESSLIST output• init• executing• Opening tablesStatements Deep Dive50© Copyright 2023 Percona ® LLC. All rights reserved
• events_stages_* tables• Same information as in tableINFORMATION_SCHEMA.PROCESSLIST or SHOWPROCESSLIST output• init• executing• Opening tables• Replacement for SHOW PROFILEStatements Deep Dive51© Copyright 2023 Percona ® LLC. All rights reserved
• events_stages_* tables• Same information as in tableINFORMATION_SCHEMA.PROCESSLIST or SHOWPROCESSLIST output• init• executing• Opening tables• Replacement for SHOW PROFILE• Only server-level• No storage engine information!Statements Deep Dive52© Copyright 2023 Percona ® LLC. All rights reserved
• Instrumentsmysql> select name from setup_instruments where name like ’stage%’;+----------------------------------------------------+| name |+----------------------------------------------------+| stage/sql/After create || stage/sql/allocating local table || stage/sql/preparing for alter table || stage/sql/altering table || stage/sql/committing alter table to storage engine || stage/sql/Changing master || stage/sql/Checking master version |...• Enable those you want to examineStages: configuration53© Copyright 2023 Percona ® LLC. All rights reserved
• Instruments• Consumersmysql> select name from setup_consumers where name like ’%stage%’;+----------------------------+| name |+----------------------------+| events_stages_current | -- Current stages| events_stages_history | -- Last 10 stages| events_stages_history_long | -- Last 1K stages+----------------------------+3 rows in set (0.00 sec)Stages: configuration54© Copyright 2023 Percona ® LLC. All rights reserved
• Everything, related to temporary tables• EVENT_NAME LIKE ’stage/sql/%tmp%’• Everything, related to locks• EVENT_NAME LIKE ’stage/sql/%lock%’• Everything in state "Waiting for"• EVENT_NAME LIKE ’stage/%/Waiting for%’• Frequently met issuesStages Shortcuts55© Copyright 2023 Percona ® LLC. All rights reserved
• Everything, related to temporary tables• Everything, related to locks• Everything in state "Waiting for"• Frequently met issues• EVENT_NAME=’stage/sql/freeing items’• EVENT_NAME=’stage/sql/Sending data’• EVENT_NAME=’stage/sql/cleaning up’• EVENT_NAME=’stage/sql/closing tables’• EVENT_NAME=’stage/sql/end’Stages Shortcuts56© Copyright 2023 Percona ® LLC. All rights reserved
mysql> SELECT eshl.event_name, sql_text, eshl.timer_wait/1000000000000 w_s-> FROM performance_schema.events_stages_history_long eshl-> JOIN performance_schema.events_statements_history_long esthl-> ON (eshl.nesting_event_id = esthl.event_id)-> WHERE eshl.timer_wait > 1*10000000000G*************************** 1. row ***************************event_name: stage/sql/Sending datasql_text: SELECT COUNT(emp_no) FROM employees JOIN salaries USING(emp_no)WHERE hire_date=from_datew_s: 0.81701 row in set (0.00 sec)Stages: Which Run Critically Long?57© Copyright 2023 Percona ® LLC. All rights reserved
• Calculated several times per query• NICE TO HAVEStages Performance58© Copyright 2023 Percona ® LLC. All rights reserved
• Graphical interface• Query statistics• EXPLAIN• Everything you need to tune yourqueries!Query Analytics (QAN) in PMM59© Copyright 2023 Percona ® LLC. All rights reserved
Example 1: QAN in PMM60© Copyright 2023 Percona ® LLC. All rights reserved
Example 2: QAN in PMM61© Copyright 2023 Percona ® LLC. All rights reserved
Example 3: QAN in PMM62© Copyright 2023 Percona ® LLC. All rights reserved
Example 4: QAN in PMM63© Copyright 2023 Percona ® LLC. All rights reserved
Example 5: QAN in PMM64© Copyright 2023 Percona ® LLC. All rights reserved
Example 6: QAN in PMM65© Copyright 2023 Percona ® LLC. All rights reserved
• Uses either slow query log ofPerformance Schema statementdigests• Same performance impact as for theslow query log or Performance Schemastatement digests• Nice GUI• GOOD TO HAVEQuery Analytics (QAN) in PMM66© Copyright 2023 Percona ® LLC. All rights reserved
Concurrency
Transaction 1 Table Rows Transaction 2Read 1 Read2Read 3Read 4 Read5 Read6Read 7 ReadParallel Reads68© Copyright 2023 Percona ® LLC. All rights reserved
Transaction 1 Table Rows Transaction 2Write 1 Read2Read 3Write 4 Write5 Read6Read 7 WriteParallel Writes69© Copyright 2023 Percona ® LLC. All rights reserved
Transaction 1 Table Rows Transaction 2 MDLWrite 1 Read Conflict2 AllowedRead 3 ConflictWrite 4 Write Conflict5 Read Conflict6Read 7 Write ConflictParallel DDL70© Copyright 2023 Percona ® LLC. All rights reserved
• Table METADATA_LOCKS in PerformanceSchemaMDL Diagnostics71© Copyright 2023 Percona ® LLC. All rights reserved
• Table METADATA_LOCKS in PerformanceSchema• Which thread is waiting for a lockMDL Diagnostics72© Copyright 2023 Percona ® LLC. All rights reserved
• Table METADATA_LOCKS in PerformanceSchema• Which thread is waiting for a lock• Which thread holds the lockMDL Diagnostics73© Copyright 2023 Percona ® LLC. All rights reserved
• Table METADATA_LOCKS in PerformanceSchema• Which thread is waiting for a lock• Which thread holds the lock• Not only for tables:GLOBAL, SCHEMA, TABLE, FUNCTION, PROCEDURE, EVENT, COMMIT, USER LEVELLOCK, TABLESPACEMDL Diagnostics74© Copyright 2023 Percona ® LLC. All rights reserved
• Instruments• wait/lock/metadata/sql/mdlMETADATA_LOCKS: configuration75© Copyright 2023 Percona ® LLC. All rights reserved
• Instruments• wait/lock/metadata/sql/mdl• Consumers• METADATA_LOCKSMETADATA_LOCKS: configuration76© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select processlist_id, object_type, lock_type, lock_status, source-> from metadata_locks join threads on (owner_thread_id=thread_id)-> where object_schema=’employees’ and object_name=’titles’G*************************** 1. row ***************************processlist_id: 4object_type: TABLElock_type: EXCLUSIVElock_status: PENDING -- waitssource: mdl.cc:3263*************************** 2. row ***************************processlist_id: 5object_type: TABLElock_type: SHARED_READlock_status: GRANTED -- holdssource: sql_parse.cc:5707METADATA_LOCKS: example77© Copyright 2023 Percona ® LLC. All rights reserved
• Approximately once per statement• Almost no impact• MUST HAVEMETADATA_LOCKS: impact78© Copyright 2023 Percona ® LLC. All rights reserved
• Information about locks, held by engineTable DATA_LOCKS79© Copyright 2023 Percona ® LLC. All rights reserved
• Information about locks, held by engine• Only for engines with own lockingmodelsTable DATA_LOCKS80© Copyright 2023 Percona ® LLC. All rights reserved
• Information about locks, held by engine• Only for engines with own lockingmodels• Currently only InnoDBTable DATA_LOCKS81© Copyright 2023 Percona ® LLC. All rights reserved
• Information about locks, held by engine• Only for engines with own lockingmodels• Currently only InnoDB• Replacement for I_S tables• INNODB_LOCKS• INNODB_LOCK_WAITSTable DATA_LOCKS82© Copyright 2023 Percona ® LLC. All rights reserved
• Which lock is held*************************** 4. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 2408:0:393:2ENGINE_TRANSACTION_ID: 2408THREAD_ID: 34OBJECT_SCHEMA: testOBJECT_NAME: tINDEX_NAME: PRIMARYLOCK_TYPE: RECORDLOCK_MODE: XLOCK_STATUS: GRANTEDLOCK_DATA: 12345Table DATA_LOCKS83© Copyright 2023 Percona ® LLC. All rights reserved
• Which lock is held• Which lock is requested*************************** 2. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 2409:0:393:2ENGINE_TRANSACTION_ID: 2409THREAD_ID: 36OBJECT_SCHEMA: testOBJECT_NAME: tINDEX_NAME: PRIMARYLOCK_TYPE: RECORDLOCK_MODE: XLOCK_STATUS: WAITINGLOCK_DATA: 12345Table DATA_LOCKS84© Copyright 2023 Percona ® LLC. All rights reserved
• Which lock is held• Which lock is requested• Both record-level and table levelp_s> select * from data_locksG*************************** 1. row ***************************...LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 2. row ***************************...LOCK_TYPE: RECORDTable DATA_LOCKS85© Copyright 2023 Percona ® LLC. All rights reserved
• Maps lock waits with granted locksTable DATA_LOCK_WAITS86© Copyright 2023 Percona ® LLC. All rights reserved
• Maps lock waits with granted locks• Only granted blocking othertransactionsp_s> select ENGINE, ... from data_lock_waitsG*************************** 1. row ***************************ENGINE: INNODBREQUESTING_ENGINE_LOCK_ID: 2409:0:393:2REQUESTING_ENGINE_TRANSACTION_ID: 2409REQUESTING_THREAD_ID: 36BLOCKING_ENGINE_LOCK_ID: 2408:0:393:2BLOCKING_ENGINE_TRANSACTION_ID: 2408BLOCKING_THREAD_ID: 341 row in set (0,01 sec)Table DATA_LOCK_WAITS87© Copyright 2023 Percona ® LLC. All rights reserved
• Partition• Subpartition• Lock data• Requesting and blocking thread idNew in DATA_LOCKS, missed in I_S88© Copyright 2023 Percona ® LLC. All rights reserved
• View innodb_lock_waitsIn sys Schema89© Copyright 2023 Percona ® LLC. All rights reserved
• View innodb_lock_waits• Takes additional information fromINFORMATION_SCHEMA.INNODB_TRXIn sys Schema90© Copyright 2023 Percona ® LLC. All rights reserved
• View innodb_lock_waitssys> select locked_table, ...-> from innodb_lock_waitsG*************************** 1. row ***************************locked_table: ‘test‘.‘t‘ blocking_pid: 4locked_index: PRIMARY blocking_query: NULLlocked_type: RECORD blocking_trx_rows_locked: 1waiting_trx_rows_locked: 1 blocking_trx_rows_modified: 1waiting_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 4waiting_pid: 6 sql_kill_blocking_connection: KILL 4waiting_query: UPDATE t SET f=’bar’ WHERE id=12345In sys Schema91© Copyright 2023 Percona ® LLC. All rights reserved
Unlike most Performance Schema data collection,there are no instruments for controlling whether data lockinformation is collected or system variables for controllingdata lock table sizes. The Performance Schema collectsinformation that is already available in the server, so thereis no memory or CPU overhead to generate this informa-tion or need for parameters that control its collection.MySQL User Reference Manual• MUST HAVEData Locks: Impact92© Copyright 2023 Percona ® LLC. All rights reserved
• All information about what is runninginside InnoDB• Writes to file every 15 seconds• Same information is available if runSHOW ENGINE INNODB STATUS interactively• Can generate a lot of data• NICE TO HAVEInnoDB Status File93© Copyright 2023 Percona ® LLC. All rights reserved
• Table INNODB_METRICS in InformationSchema• All information about what is runninginside InnoDB• Same information can be found inSHOW ENGINE INNODB STATUS• Easier to process• NICE TO HAVEINNODB_METRICS: InnoDB Monitor94© Copyright 2023 Percona ® LLC. All rights reserved
• Logs all deadlocks into the file• Latest deadlock is visible in theSHOW ENGINE INNODB STATUS• Useful when debugging frequentdeadlocks• NICE TO HAVEInnoDB Deadlock Logger95© Copyright 2023 Percona ® LLC. All rights reserved
Hardware
• Memory, used by internal mysqldstructuresMemory Diagnostic in P_S97© Copyright 2023 Percona ® LLC. All rights reserved
• Memory, used by internal mysqldstructures• Aggregated by• Global• Thread• Account• Host• UserMemory Diagnostic in P_S98© Copyright 2023 Percona ® LLC. All rights reserved
• Memory, used by internal mysqldstructures• Aggregated by• Global• Thread• Account• Host• User• Nice views in sys schemaMemory Diagnostic in P_S99© Copyright 2023 Percona ® LLC. All rights reserved
• Instrumentsmysql> select name from setup_instruments where name like ’memory%’;+-----------------------------------------------------+| name |+-----------------------------------------------------+...| memory/sql/Gtid_state::group_commit_sidno_locks || memory/sql/Mutex_cond_array::Mutex_cond || memory/sql/TABLE_RULE_ENT || memory/sql/Rpl_info_table || memory/sql/Rpl_info_file::buffer || memory/sql/db_worker_hash_entry || memory/sql/rpl_slave::check_temp_dir || memory/sql/rpl_slave::command_buffer || memory/sql/binlog_ver_1_event || memory/sql/SLAVE_INFO |...Memory: configuration100© Copyright 2023 Percona ® LLC. All rights reserved
• Instruments• Consumers• Digest tables in Performance Schema• Views in sys schemaMemory: configuration101© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select thread_id tid, user, current_allocated ca, total_allocated-> from sys.memory_by_thread_by_current_bytes;+-----+-------------------------+-------------+-----------------+| tid | user | ca | total_allocated |+-----+-------------------------+-------------+-----------------+| 1 | sql/main | 2.53 GiB | 2.69 GiB || 150 | root@127.0.0.1 | 4.06 MiB | 32.17 MiB || 146 | sql/slave_sql | 1.31 MiB | 1.44 MiB || 145 | sql/slave_io | 1.08 MiB | 2.79 MiB |...| 60 | innodb/io_read_thread | 0 bytes | 384 bytes || 139 | innodb/srv_purge_thread | -328 bytes | 754.21 KiB || 69 | innodb/io_write_thread | -1008 bytes | 34.28 KiB || 68 | innodb/io_write_thread | -1440 bytes | 298.05 KiB || 74 | innodb/io_write_thread | -1656 bytes | 103.55 KiB || 4 | innodb/io_log_thread | -2880 bytes | 132.38 KiB || 72 | innodb/io_write_thread | -7632 bytes | 1.10 MiB |+-----+-------------------------+-------------+-----------------+145 rows in set (2.65 sec)Memory Usage by Thread102© Copyright 2023 Percona ® LLC. All rights reserved
mysql> select * from sys.memory_by_thread_by_current_bytes-> order by current_allocated descG*************************** 1. row ***************************thread_id: 152user: lj@127.0.0.1current_count_used: 325current_allocated: 36.00 GiBcurrent_avg_alloc: 113.43 MiBcurrent_max_alloc: 36.00 GiBtotal_allocated: 37.95 GiB...• Find threads, eating memory, in asecond!Threads Statistics103© Copyright 2023 Percona ® LLC. All rights reserved
• memory_summary_by_account_by_event_name• memory_summary_by_host_by_event_name• memory_summary_by_thread_by_event_name• memory_summary_by_user_by_event_name• memory_summary_global_by_event_nameRAW Performance Schema tables104© Copyright 2023 Percona ® LLC. All rights reserved
• memory_summary_by_account_by_event_name• memory_summary_by_host_by_event_name• memory_summary_by_thread_by_event_name• memory_summary_by_user_by_event_name• memory_summary_global_by_event_name• You must enable memoryinstrumentation!RAW Performance Schema tables105© Copyright 2023 Percona ® LLC. All rights reserved
• memory_summary_by_account_by_event_name• memory_summary_by_host_by_event_name• memory_summary_by_thread_by_event_name• memory_summary_by_user_by_event_name• memory_summary_global_by_event_name• You must enable memoryinstrumentation!• sys schema includes user nameRAW Performance Schema tables106© Copyright 2023 Percona ® LLC. All rights reserved
• NAME@HOST - regular userUsers in sys.memory_* tables107© Copyright 2023 Percona ® LLC. All rights reserved
• NAME@HOST - regular user• System users• sql/main• innodb/*• ...Users in sys.memory_* tables108© Copyright 2023 Percona ® LLC. All rights reserved
• NAME@HOST - regular user• System users• sql/main• innodb/*• ...• Data comes from table THREADSUsers in sys.memory_* tables109© Copyright 2023 Percona ® LLC. All rights reserved
• When memory allocation happens• Must be enabled before use• If memory allocation is notinstrumented, it won’t be visible• Not 100% reliable!• Table itself holds little amount ofmemory: low impact• Finds source of memory leakimmediately!• MUST HAVEMemory Diagnostics: Impact110© Copyright 2023 Percona ® LLC. All rights reserved
• Operating system instruments• Global status variables• Engine status• SHOW ENGINE INNODB STATUS• Always availableIO Diagnostics111© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard Disk DetailsIO Diagnostics in PMM112© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard MySQL InnoDB Details/InnoDB Disk IOIO Diagnostics in PMM113© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard MySQL InnoDB Details/InnoDB LoggingIO Diagnostics in PMM114© Copyright 2023 Percona ® LLC. All rights reserved
• Operating system instruments• top, atop, htop• ps, pidstat• mpstat• iostatCPU Diagnostics115© Copyright 2023 Percona ® LLC. All rights reserved
• Table THREADS in Performance Schemamysql> select thread_id, processlist_id, thread_os_id, type, name from threads;+-----------+----------------+--------------+------------+----------------------------------------+| thread_id | processlist_id | thread_os_id | type | name |+-----------+----------------+--------------+------------+----------------------------------------+| 1 | NULL | 17983 | BACKGROUND | thread/sql/main || 2 | NULL | 17984 | BACKGROUND | thread/sql/thread_timer_notifier || 3 | NULL | 17985 | BACKGROUND | thread/innodb/io_ibuf_thread |...| 23 | 2 | 18009 | FOREGROUND | thread/sql/one_connection || 24 | 3 | 18011 | FOREGROUND | thread/sql/one_connection |+-----------+----------------+--------------+------------+----------------------------------------+CPU Diagnostics116© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard CPU Utilization DetailsCPU Diagnostics in PMM117© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard MySQL Instance SummaryCPU Diagnostics in PMM118© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard MySQL InnoDB Details/InnoDB Contention/MiscCPU Diagnostics in PMM119© Copyright 2023 Percona ® LLC. All rights reserved
• Dashboard MySQL InnoDB Details/MySQL SummaryCPU Diagnostics in PMM120© Copyright 2023 Percona ® LLC. All rights reserved
• Same as built-in and operating-systeminstruments• Provides nice GUI• Helps to answer on questions where theproblem goes• GOOD TO HAVEIO and CPU in PMM: Impact121© Copyright 2023 Percona ® LLC. All rights reserved
• Error log file: do not rotate too often!• Performance Schema• METADATA_LOCKS• DATA_LOCKS• DATA_LOCK_WAITS• Memory diagnosticsMust Have122© Copyright 2023 Percona ® LLC. All rights reserved
• Performance Schema• Statements digests• Prepared statements• PMM• QAN in PMMGood to Have123© Copyright 2023 Percona ® LLC. All rights reserved
• Slow query log• Performance Schema• Statements• Stages• InnoDB• Status file• INNODB_METRICS• Deadlock loggerNice to Have124© Copyright 2023 Percona ® LLC. All rights reserved
PMM DemoPerformance Schema BenchmarksReferences125© Copyright 2023 Percona ® LLC. All rights reserved
Thank you!/svetsmirnova /svetasmirnova/svetsmirnova /SvetaSmirnova

Recommended

PDF
Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics
PDF
Upgrade from MySQL 5.7 to MySQL 8.0
PDF
MariaDB Server Performance Tuning & Optimization
PDF
Replication Troubleshooting in Classic VS GTID
PDF
The InnoDB Storage Engine for MySQL
PDF
MySQL 8.0 Optimizer Guide
PDF
The MySQL Query Optimizer Explained Through Optimizer Trace
PDF
Advanced MySQL Query Tuning
PDF
MySQL Performance Schema in Action
PDF
MySQL Parallel Replication: All the 5.7 and 8.0 Details (LOGICAL_CLOCK)
PDF
Webinar slides: An Introduction to Performance Monitoring for PostgreSQL
PDF
MariaDB 10.11 key features overview for DBAs
PDF
Parallel Replication in MySQL and MariaDB
PDF
Why MySQL Replication Fails, and How to Get it Back
PDF
Introduction into MySQL Query Tuning for Dev[Op]s
PDF
ClickHouse tips and tricks. Webinar slides. By Robert Hodges, Altinity CEO
PDF
The Full MySQL and MariaDB Parallel Replication Tutorial
PPTX
ProxySQL for MySQL
PDF
ClickHouse Data Warehouse 101: The First Billion Rows, by Alexander Zaitsev a...
PDF
InnoDB MVCC Architecture (by 권건우)
PDF
Introduction to Redis
PDF
PostgreSQL Replication High Availability Methods
PDF
Wars of MySQL Cluster ( InnoDB Cluster VS Galera )
PDF
B+Tree Indexes and InnoDB
PDF
InnoDB Locking Explained with Stick Figures
PPTX
PDF
Using Optimizer Hints to Improve MySQL Query Performance
PPTX
Introduction to NOSQL databases
PDF
MySQL Performance Schema in 20 Minutes
PDF
MySQL Performance Schema in Action: the Complete Tutorial

More Related Content

PDF
Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics
PDF
Upgrade from MySQL 5.7 to MySQL 8.0
PDF
MariaDB Server Performance Tuning & Optimization
PDF
Replication Troubleshooting in Classic VS GTID
PDF
The InnoDB Storage Engine for MySQL
PDF
MySQL 8.0 Optimizer Guide
PDF
The MySQL Query Optimizer Explained Through Optimizer Trace
PDF
Advanced MySQL Query Tuning
Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics
Upgrade from MySQL 5.7 to MySQL 8.0
MariaDB Server Performance Tuning & Optimization
Replication Troubleshooting in Classic VS GTID
The InnoDB Storage Engine for MySQL
MySQL 8.0 Optimizer Guide
The MySQL Query Optimizer Explained Through Optimizer Trace
Advanced MySQL Query Tuning

What's hot

PDF
MySQL Performance Schema in Action
PDF
MySQL Parallel Replication: All the 5.7 and 8.0 Details (LOGICAL_CLOCK)
PDF
Webinar slides: An Introduction to Performance Monitoring for PostgreSQL
PDF
MariaDB 10.11 key features overview for DBAs
PDF
Parallel Replication in MySQL and MariaDB
PDF
Why MySQL Replication Fails, and How to Get it Back
PDF
Introduction into MySQL Query Tuning for Dev[Op]s
PDF
ClickHouse tips and tricks. Webinar slides. By Robert Hodges, Altinity CEO
PDF
The Full MySQL and MariaDB Parallel Replication Tutorial
PPTX
ProxySQL for MySQL
PDF
ClickHouse Data Warehouse 101: The First Billion Rows, by Alexander Zaitsev a...
PDF
InnoDB MVCC Architecture (by 권건우)
PDF
Introduction to Redis
PDF
PostgreSQL Replication High Availability Methods
PDF
Wars of MySQL Cluster ( InnoDB Cluster VS Galera )
PDF
B+Tree Indexes and InnoDB
PDF
InnoDB Locking Explained with Stick Figures
PPTX
PDF
Using Optimizer Hints to Improve MySQL Query Performance
PPTX
Introduction to NOSQL databases
MySQL Performance Schema in Action
MySQL Parallel Replication: All the 5.7 and 8.0 Details (LOGICAL_CLOCK)
Webinar slides: An Introduction to Performance Monitoring for PostgreSQL
MariaDB 10.11 key features overview for DBAs
Parallel Replication in MySQL and MariaDB
Why MySQL Replication Fails, and How to Get it Back
Introduction into MySQL Query Tuning for Dev[Op]s
ClickHouse tips and tricks. Webinar slides. By Robert Hodges, Altinity CEO
The Full MySQL and MariaDB Parallel Replication Tutorial
ProxySQL for MySQL
ClickHouse Data Warehouse 101: The First Billion Rows, by Alexander Zaitsev a...
InnoDB MVCC Architecture (by 권건우)
Introduction to Redis
PostgreSQL Replication High Availability Methods
Wars of MySQL Cluster ( InnoDB Cluster VS Galera )
B+Tree Indexes and InnoDB
InnoDB Locking Explained with Stick Figures
Using Optimizer Hints to Improve MySQL Query Performance
Introduction to NOSQL databases

Similar to MySQL Database Monitoring: Must, Good and Nice to Have

PDF
MySQL Performance Schema in 20 Minutes
PDF
MySQL Performance Schema in Action: the Complete Tutorial
PDF
Query Optimization with MySQL 5.6: Old and New Tricks - Percona Live London 2013
PDF
Percona Live 2012PPT: MySQL Query optimization
PDF
MySQL Performance Tuning. Part 1: MySQL Configuration (includes MySQL 5.7)
PDF
Query Optimization with MySQL 5.6: Old and New Tricks
PPTX
MySQL performance tuning
PDF
Maximizing SQL Reviews and Tuning with pt-query-digest
PDF
Scaling MySQL Strategies for Developers
PDF
MySQL Tuning using digested slow-logs
PDF
Quick Wins
PDF
Beyond php - it's not (just) about the code
PDF
介绍 Percona 服务器 XtraDB 和 Xtrabackup
ODP
Beyond php it's not (just) about the code
ODP
Beyond php - it's not (just) about the code
PDF
SDPHP - Percona Toolkit (It's Basically Magic)
PDF
16 MySQL Optimization #burningkeyboards
PDF
Troubleshooting MySQL Performance
PPTX
Работа с индексами - лучшие практики для MySQL 5.6, Петр Зайцев (Percona)
 
PDF
Using MySQL in a web-scale environment
MySQL Performance Schema in 20 Minutes
MySQL Performance Schema in Action: the Complete Tutorial
Query Optimization with MySQL 5.6: Old and New Tricks - Percona Live London 2013
Percona Live 2012PPT: MySQL Query optimization
MySQL Performance Tuning. Part 1: MySQL Configuration (includes MySQL 5.7)
Query Optimization with MySQL 5.6: Old and New Tricks
MySQL performance tuning
Maximizing SQL Reviews and Tuning with pt-query-digest
Scaling MySQL Strategies for Developers
MySQL Tuning using digested slow-logs
Quick Wins
Beyond php - it's not (just) about the code
介绍 Percona 服务器 XtraDB 和 Xtrabackup
Beyond php it's not (just) about the code
Beyond php - it's not (just) about the code
SDPHP - Percona Toolkit (It's Basically Magic)
16 MySQL Optimization #burningkeyboards
Troubleshooting MySQL Performance
Работа с индексами - лучшие практики для MySQL 5.6, Петр Зайцев (Percona)
 
Using MySQL in a web-scale environment

More from Sveta Smirnova

PDF
Database in Kubernetes: Diagnostics and Monitoring
PDF
MySQL Cookbook: Recipes for Developers
PDF
MySQL Cookbook: Recipes for Your Business
PDF
How to Avoid Pitfalls in Schema Upgrade with Galera
PDF
MySQL Performance for DevOps
PDF
Современному хайлоду - современные решения: MySQL 8.0 и улучшения Percona
PDF
MySQL 2024: Зачем переходить на MySQL 8, если в 5.х всё устраивает?
PDF
How to Avoid Pitfalls in Schema Upgrade with Percona XtraDB Cluster
PDF
Что нужно знать о трёх топовых фичах MySQL
PDF
How to migrate from MySQL to MariaDB without tears
PDF
War Story: Removing Offensive Language from Percona Toolkit
PDF
MySQL Performance for DevOps
PDF
Производительность MySQL для DevOps
PDF
MySQL Test Framework для поддержки клиентов и верификации багов
PDF
Modern solutions for modern database load: improvements in the latest MariaDB...
PDF
Introduction to MySQL Query Tuning for Dev[Op]s
PDF
How Safe is Asynchronous Master-Master Setup?
PDF
Billion Goods in Few Categories: How Histograms Save a Life?
PDF
A Billion Goods in a Few Categories: When Optimizer Histograms Help and When ...
PDF
How Safe is Asynchronous Master-Master Setup?
Database in Kubernetes: Diagnostics and Monitoring
MySQL Cookbook: Recipes for Developers
MySQL Cookbook: Recipes for Your Business
How to Avoid Pitfalls in Schema Upgrade with Galera
MySQL Performance for DevOps
Современному хайлоду - современные решения: MySQL 8.0 и улучшения Percona
MySQL 2024: Зачем переходить на MySQL 8, если в 5.х всё устраивает?
How to Avoid Pitfalls in Schema Upgrade with Percona XtraDB Cluster
Что нужно знать о трёх топовых фичах MySQL
How to migrate from MySQL to MariaDB without tears
War Story: Removing Offensive Language from Percona Toolkit
MySQL Performance for DevOps
Производительность MySQL для DevOps
MySQL Test Framework для поддержки клиентов и верификации багов
Modern solutions for modern database load: improvements in the latest MariaDB...
Introduction to MySQL Query Tuning for Dev[Op]s
How Safe is Asynchronous Master-Master Setup?
Billion Goods in Few Categories: How Histograms Save a Life?
A Billion Goods in a Few Categories: When Optimizer Histograms Help and When ...
How Safe is Asynchronous Master-Master Setup?

Recently uploaded

PDF
Constraints First - Why Our On-Prem Ticketing System Starts With Limits, Not ...
PPTX
NSF Converter Software to Convert NSF to PST, EML, MSG
PDF
Operating System (OS) :UNIT-I Introduction to Operating System BCA SEP SEM-II...
PDF
How Modern Custom Software is Revolutionizing Mortgage Lending Processes -Ma...
PDF
IT Estate Modernization: Transform Your Infrastructure for the Future .pdf
PPTX
Modern Claims Automation Solutions for Operational Agility
PPTX
Magnet-AXIOM_overview_tool_cyber_tool.pptx
PPTX
Managed Splunk Partner vs In-House: Cost, Risk & Value Comparison
PDF
Blueprint to build quality before the code exists - StackConnect Milan 2025
PDF
Here’s the case study that shows how companies lose ₹2–6 Cr annually due to m...
PDF
API_SECURITY CONSULTANCY SERVICES IN USA
PDF
Navigating SEC Regulations for Crypto Exchanges Preparing for a Compliant Fut...
PDF
Imed Eddine Bouchoucha | computer engineer | software Architect
PDF
Design and Analysis of Algorithms(DAA): Unit-II Asymptotic Notations and Basi...
PDF
Transforming Compliance Through Policy & Procedure Management
PPTX
GDS Integration Solution | GDS Integration Service
PDF
Cybersecurity Alert- What Organisations Must Watch Out For This Christmas Fes...
PPTX
AI Clinic Management Software for Pulmonology Clinics Bringing Clarity, Contr...
PDF
Influence Without Power - Why Empathy is Your Best Friend.pdf
PDF
KoderXpert – Odoo, Web & AI Solutions for Growing Businesses
Constraints First - Why Our On-Prem Ticketing System Starts With Limits, Not ...
NSF Converter Software to Convert NSF to PST, EML, MSG
Operating System (OS) :UNIT-I Introduction to Operating System BCA SEP SEM-II...
How Modern Custom Software is Revolutionizing Mortgage Lending Processes -Ma...
IT Estate Modernization: Transform Your Infrastructure for the Future .pdf
Modern Claims Automation Solutions for Operational Agility
Magnet-AXIOM_overview_tool_cyber_tool.pptx
Managed Splunk Partner vs In-House: Cost, Risk & Value Comparison
Blueprint to build quality before the code exists - StackConnect Milan 2025
Here’s the case study that shows how companies lose ₹2–6 Cr annually due to m...
API_SECURITY CONSULTANCY SERVICES IN USA
Navigating SEC Regulations for Crypto Exchanges Preparing for a Compliant Fut...
Imed Eddine Bouchoucha | computer engineer | software Architect
Design and Analysis of Algorithms(DAA): Unit-II Asymptotic Notations and Basi...
Transforming Compliance Through Policy & Procedure Management
GDS Integration Solution | GDS Integration Service
Cybersecurity Alert- What Organisations Must Watch Out For This Christmas Fes...
AI Clinic Management Software for Pulmonology Clinics Bringing Clarity, Contr...
Influence Without Power - Why Empathy is Your Best Friend.pdf
KoderXpert – Odoo, Web & AI Solutions for Growing Businesses

MySQL Database Monitoring: Must, Good and Nice to Have

  • 1.
    MySQL Database Monitoring:Must,Good and Nice to HaveSveta SmirnovaPrincipal Support Engineering Coordinator
  • 2.
    • MySQL SupportEngineer• AuthorMySQL TroubleshootingMySQL Cookbook, 4th Edition• JSON UDF functions• FILTER clause for MySQL• Speaker• Percona Live, OOW, Fosdem,DevConf, HighLoad...Sveta Smirnova
  • 3.
    • Solving Supportcases• Sometimes we cannot resolve the issuebecause necessary information was notcollected in time• Reasons are• Lack of knowledge• Fear of performance impact• Let’s discuss what can help and forwhich price!Motivation3© Copyright 2023 Percona ® LLC. All rights reserved
  • 4.
    must Should bealways ONMust-Good-Nice4© Copyright 2023 Percona ® LLC. All rights reserved
  • 5.
    must Should bealways ONgood Useful data, little effect on overallperformanceMust-Good-Nice5© Copyright 2023 Percona ® LLC. All rights reserved
  • 6.
    must Should bealways ONgood Useful data, little effect on overallperformancenice May slow down database but veryuseful in certain situationsMust-Good-Nice6© Copyright 2023 Percona ® LLC. All rights reserved
  • 7.
    2023-05-16T12:44:14.776227Z 0 [System][MY-011323] [Server]X Plugin ready for connections.Bind-address: ’::’ port: 13020, socket: /tmp/mysqlx.1.sock2023-05-16T12:44:14.776295Z 0 [System] [MY-010931] [Server]/home/sveta/Percona-Server-8.0.32-24/bin/mysqld: ready for connections.Version: ’8.0.32-24’ socket: ’/tmp/mysqld.1.sock’ port: 13000Percona Server (GPL), Release 24, Revision e5c6e9d2.Error Log: Normal Operation7© Copyright 2023 Percona ® LLC. All rights reserved
  • 8.
    2023-05-16T21:01:34.090936Z 0 [System][MY-010116] [Server]/Percona-Server-8.0.32-24/bin/mysqld (mysqld 8.0.32-24) starting as process 20063972023-05-16T21:01:34.103406Z 0 [Warning] [MY-010091] [Server] Can’t create test file/Percona-Server-8.0.32-24/data/mysqld_tmp_file_case_insensitive_test.lower-test2023-05-16T21:01:34.152150Z 0 [ERROR] [MY-010187] [Server] Could not open file’/Percona-Server-8.0.32-24/data/Delly-7390.err’ for error logging: Permission denied2023-05-16T21:01:34.152288Z 0 [ERROR] [MY-010119] [Server] Aborting2023-05-16T21:01:34.152856Z 0 [System] [MY-010910] [Server]/Percona-Server-8.0.32-24/bin/mysqld: Shutdown complete (mysqld 8.0.32-24)Percona Server (GPL), Release 24, Revision e5c6e9d2-debug.Error Log: Immediate Answer8© Copyright 2023 Percona ® LLC. All rights reserved
  • 9.
    2023-05-17T00:06:38.658777Z 11 [ERROR][MY-012639] [InnoDB] Write to file./test/joinit7.ibd failed at offset 80740352, 1048576 bytes should have been written,only 929792 were written. Operating system error number 28. Check that your OS andfile system support files of this size. Check also that the disk is not full ora disk quota exceeded.2023-05-17T00:06:38.658801Z 11 [ERROR] [MY-012640] [InnoDB] Error number 28 means’No space left on device’2023-05-17T00:06:38.658820Z 11 [Warning] [MY-012145] [InnoDB] Error while writing4194304 zeroes to ./test/joinit7.ibd starting at offset 796917762023-05-17T00:06:38.669221Z 11 [ERROR] [MY-013132] [Server] The table ’joinit7’ is fullError Log: Invisible Issue9© Copyright 2023 Percona ® LLC. All rights reserved
  • 10.
    2023-05-17T06:50:19.749143Z 0 [ERROR]InnoDB: Operating system error number 2 in afile operation.May 17 13:50:22 Delly mysqld[24814]: 2023-05-17T06:50:19.429496Z 0 [Note]InnoDB: Apply batch completedMay 17 13:50:22 Delly mysqld[24814]: InnoDB: Progress in percent: 12 13 14 15 16 1718 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39May 17 13:50:22 Delly mysqld[24814]: 2023-05-17T06:50:19.304439Z 0 [Note]InnoDB: Starting an apply batch of log records to the database...Error Log: "Unexpected" Corruption10© Copyright 2023 Percona ® LLC. All rights reserved
  • 11.
    • Always ON•Keep outside of data directory• Do not rotate too often• MUST HAVEError Log: Best Practices11© Copyright 2023 Percona ® LLC. All rights reserved
  • 12.
    Database Performance Influencers12©Copyright 2023 Percona ® LLC. All rights reserved
  • 13.
    Database Performance Influencers13©Copyright 2023 Percona ® LLC. All rights reserved
  • 14.
  • 15.
    • Collects slowqueriesSlow Query Log15© Copyright 2023 Percona ® LLC. All rights reserved
  • 16.
    • Collects slowqueries• Tunable parameters• long_query_time• log_slow_admin_statements• log_queries_not_using_indexes• min_examined_row_limit• log_slow_replica_statementsSlow Query Log16© Copyright 2023 Percona ® LLC. All rights reserved
  • 17.
    • Collects slowqueries• Tunable parameters• Tunable verbositylog_slow_extralog_slow_verbositySlow Query Log17© Copyright 2023 Percona ® LLC. All rights reserved
  • 18.
    # Time: 2023-05-17T11:08:23.065564Z#User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.003821 Lock_time: 0.000018 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytess_sent: 187SET timestamp=1684321703;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: minimal Verbosity18© Copyright 2023 Percona ® LLC. All rights reserved
  • 19.
    # Time: 2023-05-17T11:08:30.038499Z#User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.001777 Lock_time: 0.000024 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytes_sent: 187# Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0# Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# Filesort: No Filesort_on_disk: No Merge_passes: 0SET timestamp=1684321710;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: standard Verbosity19© Copyright 2023 Percona ® LLC. All rights reserved
  • 20.
    # Time: 2023-05-17T11:08:36.662992Z#User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.002478 Lock_time: 0.000033 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytes_sent: 187# Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0# InnoDB_trx_id: 0# Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# Filesort: No Filesort_on_disk: No Merge_passes: 0# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000# InnoDB_pages_distinct: 1SET timestamp=1684321716;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: full Verbosity20© Copyright 2023 Percona ® LLC. All rights reserved
  • 21.
    # Time: 2023-05-17T11:08:43.170370Z#User@Host: root[root] @ localhost [] Id: 29# Schema: employees Last_errno: 0 Killed: 0# Query_time: 0.001809 Lock_time: 0.000020 Rows_sent: 1 Rows_examined: 1Rows_affected: 0 Bytes_sent: 187# Profile_starting: 0.000675 Profile_starting_cpu: 0.000405Profile_Executing_hook_on_transaction_begin.: 0.000025Profile_Executing_hook_on_transaction_begin._cpu: 0.000023Profile_starting: 0.000046 Profile_starting_cpu: 0.000046...Profile_closing_tables_cpu: 0.000075 Profile_freeing_items: 0.000075Profile_freeing_items_cpu: 0.000075 Profile_logging_slow_query: 0.000006Profile_logging_slow_query_cpu: 0.000006# Profile_total: 0.001829 Profile_total_cpu: 0.001556SET timestamp=1684321723;SELECT * FROM ‘departments‘ WHERE dept_no=’d003’;Slow Query Log: profiling verbosity21© Copyright 2023 Percona ® LLC. All rights reserved
  • 22.
    # Profile# RankQuery ID Response time Calls R/Call V/M Item# ==== ================== ============= ===== ======= ===== ==============# 1 0x61C2A843B38413D1 63.1758 99.9% 4 15.7939 1.81 SELECT titles# 2 0x6B17FAAA0C13F2B4 0.0307 0.0% 11 0.0028 0.00 SELECT departments# 3 0x90C659E5DE65EC22 0.0174 0.0% 1 0.0174 0.00 SELECT information_...# 4 0xE3A3649C5FAC418D 0.0151 0.0% 21 0.0007 0.00 SELECT# 5 0x02BBA8FDF2596F42 0.0121 0.0% 2 0.0060 0.01 SELECT departments# MISC 0xMISC 0.0077 0.0% 13 0.0006 0.0 <4 ITEMS>...Slow Query Log by pt_query_digest22© Copyright 2023 Percona ® LLC. All rights reserved
  • 23.
    # Query 2:0.00 QPS, 0.00x concurrency, ID 0x6B17FAAA0C13F2B4 at byte 20896111# Scores: V/M = 0.00# Time range: 2023-05-17T10:27:00 to 2023-05-17T11:08:43# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 21 11# Exec time 0 31ms 1ms 10ms 3ms 4ms 2ms 2ms# Lock time 52 324us 17us 64us 29us 40us 12us 23us# Rows sent 12 10 0 1 0.91 0.99 0.29 0.99...# SHOW TABLE STATUS FROM ‘test‘ LIKE ’departments’G# SHOW CREATE TABLE ‘test‘.‘departments‘G# EXPLAIN /*!50100 PARTITIONS*/SELECT * FROM ‘departments‘ WHERE dept_no=’d003’GSlow Query Log by pt_query_digest23© Copyright 2023 Percona ® LLC. All rights reserved
  • 24.
    • Can growfast with long_query_time=0• Does not depend on the database size• Depends on the query activity• Higher query rate - faster log growth• Safe to use with comparatively low load• For high load: plan log rotation• NICE TO HAVESlow Query Log: Disk IO24© Copyright 2023 Percona ® LLC. All rights reserved
  • 25.
    • Statements events•Prepared statements• Statements digests• Stages eventsPerformance Schema25© Copyright 2023 Percona ® LLC. All rights reserved
  • 26.
    • Examine morerows than return/change• Use disk instead of memory• Full table scan instead of index• This is not full list!Why statements are slow?26© Copyright 2023 Percona ® LLC. All rights reserved
  • 27.
    mysql> SELECT THREAD_IDTID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS,-> ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED-> FROM performance_schema.events_statements_history-> WHERE NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1G********************** 1. row **********************TID: 10124SQL_TEXT: select emp_no, first_name, last_name from employeeRS: 97750RE: 397774CREATED_TMP_TABLES: 0NO_INDEX_USED: 1NO_GOOD_INDEX_USED: 0...Which Queries Do Not Use Indexes?27© Copyright 2023 Percona ® LLC. All rights reserved
  • 28.
    mysql> select namefrom setup_instruments where name like ’statement%’;+-----------------------------------------------+| name |+-----------------------------------------------+| statement/sql/select || statement/sql/create_table || statement/sql/create_index || statement/sql/alter_table || statement/sql/update || statement/sql/insert || statement/sql/insert_select |...Statements Configuration28© Copyright 2023 Percona ® LLC. All rights reserved
  • 29.
    mysql> select namefrom setup_consumers where name like ’%statement%’;+--------------------------------+| name |+--------------------------------+| events_statements_current | -- Current statements| events_statements_history | -- Last 10 statements| events_statements_history_long | -- Last 1K statements| statements_digest | -- Digests+--------------------------------+4 rows in set (0.00 sec)Statements Events Configuration29© Copyright 2023 Percona ® LLC. All rights reserved
  • 30.
    • CREATED_TMP_DISK_TABLES• CREATED_TMP_TABLES•SELECT_FULL_JOIN• SELECT_RANGE_CHECK• SELECT_SCAN• SORT_MERGE_PASSES• SORT_SCANImportant Field Names30© Copyright 2023 Percona ® LLC. All rights reserved
  • 31.
    mysql> select DIGEST,DIGEST_TEXT, COUNT_STAR, SUM_CREATED_TMP_DISK_TABLES,-> SUM_SELECT_FULL_JOIN, SUM_SELECT_RANGE , SUM_SELECT_SCAN , SUM_NO_INDEX_USED,-> SUM_ROWS_SENT, SUM_ROWS_EXAMINED-> from events_statements_summary_by_digest where SUM_NO_INDEX_USED > 0G*************************** 1. row ***************************DIGEST: 3884185b07312b354c4918f2368d8fe2c431aeb8e39bf8ff5c3dcc6837DIGEST_TEXT: SELECT ‘c‘ FROM ‘sbtest1‘ WHERE ‘id‘ BETWEEN ? AND ?COUNT_STAR: 1501791SUM_CREATED_TMP_DISK_TABLES: 0SUM_SELECT_FULL_JOIN: 0SUM_SELECT_RANGE: 1501840SUM_SELECT_SCAN: 4SUM_NO_INDEX_USED: 4SUM_ROWS_SENT: 150872400SUM_ROWS_EXAMINED: 152872000...Digests: Which Do Not Use Indexes?31© Copyright 2023 Percona ® LLC. All rights reserved
  • 32.
    • Combined statistics•events_statements_summary_by_account_by_event_name• events_statements_summary_by_host_by_event_name• events_statements_summary_by_thread_by_event_name• events_statements_summary_by_user_by_event_name• events_statements_summary_global_by_event_name5.7+: events_statements_summary_by_programDigest Tables32© Copyright 2023 Percona ® LLC. All rights reserved
  • 33.
    • Combined statistics•Histogram Summary• events_statements_histogram_by_digest• events_statements_histogram_global• Do NOT mix with Optimizer histograms!Digest Tables33© Copyright 2023 Percona ® LLC. All rights reserved
  • 34.
    • Combined statistics•Histogram Summary• events_statements_summary_by_digest• SCHEMA_NAME• DIGEST• DIGEST_TEXT8.0+: QUERY_SAMPLE_TEXTDigest Tables34© Copyright 2023 Percona ® LLC. All rights reserved
  • 35.
    mysql> select namefrom setup_consumers where name like ’%statement%’;+--------------------------------+| name |+--------------------------------+| events_statements_current | -- Current statements| events_statements_history | -- Last 10 statements| events_statements_history_long | -- Last 1K statements| statements_digest | -- Digests+--------------------------------+4 rows in set (0.00 sec)Statements Digests Configuration35© Copyright 2023 Percona ® LLC. All rights reserved
  • 36.
    mysql> SELECT query,total_latency, no_index_used_count, rows_sent,-> rows_examined-> FROM sys.statements_with_full_table_scans-> WHERE db=’employees’ AND query NOT LIKE ’%performance_schema%’G********************** 1. row **********************query: SELECT COUNT ( ‘emp_no‘ ) FROM ... ‘emp_no‘ ) WHERE ‘title‘ = ?total_latency: 805.37 msno_index_used_count: 1rows_sent: 1rows_examined: 397774...sys: Which Do Not Use Indexes?36© Copyright 2023 Percona ® LLC. All rights reserved
  • 37.
    • statement_analysis• statements_with_full_table_scans•statements_with_runtimes_in_95th_percentile• statements_with_sorting• statements_with_temp_tables• statements_with_errors_or_warningsImportant Views in sys Schema37© Copyright 2023 Percona ® LLC. All rights reserved
  • 38.
    • Contains currentprepared statementsTable prepared_statements_instances38© Copyright 2023 Percona ® LLC. All rights reserved
  • 39.
    • Contains currentprepared statements• Statistics by• Which thread owns the statement• How many times executed• Optimizer statistics, similar toevents_statements_*Table prepared_statements_instances39© Copyright 2023 Percona ® LLC. All rights reserved
  • 40.
  • 41.
    • Instruments• Consumers•prepared_statements_instancesPrepared Statements: configuration41© Copyright 2023 Percona ® LLC. All rights reserved
  • 42.
    • Instruments• Consumers•Sizemysql> select @@performance_schema_max_prepared_statements_instancesG*************************** 1. row ***************************@@performance_schema_max_prepared_statements_instances: -11 row in set (0.00 sec)Prepared Statements: configuration42© Copyright 2023 Percona ® LLC. All rights reserved
  • 43.
    mysql1> prepare stmtfrom ’select count(*) from employees where hire_date > ?’;Query OK, 0 rows affected (0.00 sec)Statement preparedmysql1> set @hd=’1995-01-01’;Query OK, 0 rows affected (0.00 sec)mysql1> execute stmt using @hd;+----------+| count(*) |+----------+| 34004 |+----------+1 row in set (1.44 sec)Example: Prepared Statement43© Copyright 2023 Percona ® LLC. All rights reserved
  • 44.
    mysql1> prepare stmtfrom ’select count(*) from employees where hire_date > ?’;Query OK, 0 rows affected (0.00 sec)Statement preparedmysql1> set @hd=’1995-01-01’;Query OK, 0 rows affected (0.00 sec)mysql1> execute stmt using @hd;+----------+| count(*) |+----------+| 34004 |+----------+1 row in set (1.44 sec)• Try EXECUTE with different valuesExample: Prepared Statement44© Copyright 2023 Percona ® LLC. All rights reserved
  • 45.
    mysql2> select statement_name,sql_text, owner_thread_id, count_reprepare,-> count_execute, sum_timer_execute from prepared_statements_instancesG*************************** 1. row ***************************statement_name: stmtsql_text: select count(*) from employees where hire_date > ?owner_thread_id: 22count_reprepare: 0count_execute: 3sum_timer_execute: 41565613680001 row in set (0.00 sec)mysql1> drop prepare stmt;Query OK, 0 rows affected (0.00 sec)mysql2> select * from prepared_statements_instancesGEmpty set (0.00 sec)Example: diagnosis45© Copyright 2023 Percona ® LLC. All rights reserved
  • 46.
    • Instrumented instructioncalled• Corresponding field updated• Counter incremented• Time recorded• Query recorded• ...• More often you call instruction - higheroverhead!How Performance Schema Works?46© Copyright 2023 Percona ® LLC. All rights reserved
  • 47.
    Performance Schema Impact47©Copyright 2023 Percona ® LLC. All rights reserved
  • 48.
    • Performance Schematriggers datacollection when hits event• For statement instrumentation thisusually means once per statement• Low impact• GOOD TO HAVEStatements Instrumentation: Impact48© Copyright 2023 Percona ® LLC. All rights reserved
  • 49.
    • events_stages_* tablesStatementsDeep Dive49© Copyright 2023 Percona ® LLC. All rights reserved
  • 50.
    • events_stages_* tables•Same information as in tableINFORMATION_SCHEMA.PROCESSLIST or SHOWPROCESSLIST output• init• executing• Opening tablesStatements Deep Dive50© Copyright 2023 Percona ® LLC. All rights reserved
  • 51.
    • events_stages_* tables•Same information as in tableINFORMATION_SCHEMA.PROCESSLIST or SHOWPROCESSLIST output• init• executing• Opening tables• Replacement for SHOW PROFILEStatements Deep Dive51© Copyright 2023 Percona ® LLC. All rights reserved
  • 52.
    • events_stages_* tables•Same information as in tableINFORMATION_SCHEMA.PROCESSLIST or SHOWPROCESSLIST output• init• executing• Opening tables• Replacement for SHOW PROFILE• Only server-level• No storage engine information!Statements Deep Dive52© Copyright 2023 Percona ® LLC. All rights reserved
  • 53.
    • Instrumentsmysql> selectname from setup_instruments where name like ’stage%’;+----------------------------------------------------+| name |+----------------------------------------------------+| stage/sql/After create || stage/sql/allocating local table || stage/sql/preparing for alter table || stage/sql/altering table || stage/sql/committing alter table to storage engine || stage/sql/Changing master || stage/sql/Checking master version |...• Enable those you want to examineStages: configuration53© Copyright 2023 Percona ® LLC. All rights reserved
  • 54.
    • Instruments• Consumersmysql>select name from setup_consumers where name like ’%stage%’;+----------------------------+| name |+----------------------------+| events_stages_current | -- Current stages| events_stages_history | -- Last 10 stages| events_stages_history_long | -- Last 1K stages+----------------------------+3 rows in set (0.00 sec)Stages: configuration54© Copyright 2023 Percona ® LLC. All rights reserved
  • 55.
    • Everything, relatedto temporary tables• EVENT_NAME LIKE ’stage/sql/%tmp%’• Everything, related to locks• EVENT_NAME LIKE ’stage/sql/%lock%’• Everything in state "Waiting for"• EVENT_NAME LIKE ’stage/%/Waiting for%’• Frequently met issuesStages Shortcuts55© Copyright 2023 Percona ® LLC. All rights reserved
  • 56.
    • Everything, relatedto temporary tables• Everything, related to locks• Everything in state "Waiting for"• Frequently met issues• EVENT_NAME=’stage/sql/freeing items’• EVENT_NAME=’stage/sql/Sending data’• EVENT_NAME=’stage/sql/cleaning up’• EVENT_NAME=’stage/sql/closing tables’• EVENT_NAME=’stage/sql/end’Stages Shortcuts56© Copyright 2023 Percona ® LLC. All rights reserved
  • 57.
    mysql> SELECT eshl.event_name,sql_text, eshl.timer_wait/1000000000000 w_s-> FROM performance_schema.events_stages_history_long eshl-> JOIN performance_schema.events_statements_history_long esthl-> ON (eshl.nesting_event_id = esthl.event_id)-> WHERE eshl.timer_wait > 1*10000000000G*************************** 1. row ***************************event_name: stage/sql/Sending datasql_text: SELECT COUNT(emp_no) FROM employees JOIN salaries USING(emp_no)WHERE hire_date=from_datew_s: 0.81701 row in set (0.00 sec)Stages: Which Run Critically Long?57© Copyright 2023 Percona ® LLC. All rights reserved
  • 58.
    • Calculated severaltimes per query• NICE TO HAVEStages Performance58© Copyright 2023 Percona ® LLC. All rights reserved
  • 59.
    • Graphical interface•Query statistics• EXPLAIN• Everything you need to tune yourqueries!Query Analytics (QAN) in PMM59© Copyright 2023 Percona ® LLC. All rights reserved
  • 60.
    Example 1: QANin PMM60© Copyright 2023 Percona ® LLC. All rights reserved
  • 61.
    Example 2: QANin PMM61© Copyright 2023 Percona ® LLC. All rights reserved
  • 62.
    Example 3: QANin PMM62© Copyright 2023 Percona ® LLC. All rights reserved
  • 63.
    Example 4: QANin PMM63© Copyright 2023 Percona ® LLC. All rights reserved
  • 64.
    Example 5: QANin PMM64© Copyright 2023 Percona ® LLC. All rights reserved
  • 65.
    Example 6: QANin PMM65© Copyright 2023 Percona ® LLC. All rights reserved
  • 66.
    • Uses eitherslow query log ofPerformance Schema statementdigests• Same performance impact as for theslow query log or Performance Schemastatement digests• Nice GUI• GOOD TO HAVEQuery Analytics (QAN) in PMM66© Copyright 2023 Percona ® LLC. All rights reserved
  • 67.
  • 68.
    Transaction 1 TableRows Transaction 2Read 1 Read2Read 3Read 4 Read5 Read6Read 7 ReadParallel Reads68© Copyright 2023 Percona ® LLC. All rights reserved
  • 69.
    Transaction 1 TableRows Transaction 2Write 1 Read2Read 3Write 4 Write5 Read6Read 7 WriteParallel Writes69© Copyright 2023 Percona ® LLC. All rights reserved
  • 70.
    Transaction 1 TableRows Transaction 2 MDLWrite 1 Read Conflict2 AllowedRead 3 ConflictWrite 4 Write Conflict5 Read Conflict6Read 7 Write ConflictParallel DDL70© Copyright 2023 Percona ® LLC. All rights reserved
  • 71.
    • Table METADATA_LOCKSin PerformanceSchemaMDL Diagnostics71© Copyright 2023 Percona ® LLC. All rights reserved
  • 72.
    • Table METADATA_LOCKSin PerformanceSchema• Which thread is waiting for a lockMDL Diagnostics72© Copyright 2023 Percona ® LLC. All rights reserved
  • 73.
    • Table METADATA_LOCKSin PerformanceSchema• Which thread is waiting for a lock• Which thread holds the lockMDL Diagnostics73© Copyright 2023 Percona ® LLC. All rights reserved
  • 74.
    • Table METADATA_LOCKSin PerformanceSchema• Which thread is waiting for a lock• Which thread holds the lock• Not only for tables:GLOBAL, SCHEMA, TABLE, FUNCTION, PROCEDURE, EVENT, COMMIT, USER LEVELLOCK, TABLESPACEMDL Diagnostics74© Copyright 2023 Percona ® LLC. All rights reserved
  • 75.
    • Instruments• wait/lock/metadata/sql/mdlMETADATA_LOCKS:configuration75© Copyright 2023 Percona ® LLC. All rights reserved
  • 76.
    • Instruments• wait/lock/metadata/sql/mdl•Consumers• METADATA_LOCKSMETADATA_LOCKS: configuration76© Copyright 2023 Percona ® LLC. All rights reserved
  • 77.
    mysql> select processlist_id,object_type, lock_type, lock_status, source-> from metadata_locks join threads on (owner_thread_id=thread_id)-> where object_schema=’employees’ and object_name=’titles’G*************************** 1. row ***************************processlist_id: 4object_type: TABLElock_type: EXCLUSIVElock_status: PENDING -- waitssource: mdl.cc:3263*************************** 2. row ***************************processlist_id: 5object_type: TABLElock_type: SHARED_READlock_status: GRANTED -- holdssource: sql_parse.cc:5707METADATA_LOCKS: example77© Copyright 2023 Percona ® LLC. All rights reserved
  • 78.
    • Approximately onceper statement• Almost no impact• MUST HAVEMETADATA_LOCKS: impact78© Copyright 2023 Percona ® LLC. All rights reserved
  • 79.
    • Information aboutlocks, held by engineTable DATA_LOCKS79© Copyright 2023 Percona ® LLC. All rights reserved
  • 80.
    • Information aboutlocks, held by engine• Only for engines with own lockingmodelsTable DATA_LOCKS80© Copyright 2023 Percona ® LLC. All rights reserved
  • 81.
    • Information aboutlocks, held by engine• Only for engines with own lockingmodels• Currently only InnoDBTable DATA_LOCKS81© Copyright 2023 Percona ® LLC. All rights reserved
  • 82.
    • Information aboutlocks, held by engine• Only for engines with own lockingmodels• Currently only InnoDB• Replacement for I_S tables• INNODB_LOCKS• INNODB_LOCK_WAITSTable DATA_LOCKS82© Copyright 2023 Percona ® LLC. All rights reserved
  • 83.
    • Which lockis held*************************** 4. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 2408:0:393:2ENGINE_TRANSACTION_ID: 2408THREAD_ID: 34OBJECT_SCHEMA: testOBJECT_NAME: tINDEX_NAME: PRIMARYLOCK_TYPE: RECORDLOCK_MODE: XLOCK_STATUS: GRANTEDLOCK_DATA: 12345Table DATA_LOCKS83© Copyright 2023 Percona ® LLC. All rights reserved
  • 84.
    • Which lockis held• Which lock is requested*************************** 2. row ***************************ENGINE: INNODBENGINE_LOCK_ID: 2409:0:393:2ENGINE_TRANSACTION_ID: 2409THREAD_ID: 36OBJECT_SCHEMA: testOBJECT_NAME: tINDEX_NAME: PRIMARYLOCK_TYPE: RECORDLOCK_MODE: XLOCK_STATUS: WAITINGLOCK_DATA: 12345Table DATA_LOCKS84© Copyright 2023 Percona ® LLC. All rights reserved
  • 85.
    • Which lockis held• Which lock is requested• Both record-level and table levelp_s> select * from data_locksG*************************** 1. row ***************************...LOCK_TYPE: TABLELOCK_MODE: IXLOCK_STATUS: GRANTEDLOCK_DATA: NULL*************************** 2. row ***************************...LOCK_TYPE: RECORDTable DATA_LOCKS85© Copyright 2023 Percona ® LLC. All rights reserved
  • 86.
    • Maps lockwaits with granted locksTable DATA_LOCK_WAITS86© Copyright 2023 Percona ® LLC. All rights reserved
  • 87.
    • Maps lockwaits with granted locks• Only granted blocking othertransactionsp_s> select ENGINE, ... from data_lock_waitsG*************************** 1. row ***************************ENGINE: INNODBREQUESTING_ENGINE_LOCK_ID: 2409:0:393:2REQUESTING_ENGINE_TRANSACTION_ID: 2409REQUESTING_THREAD_ID: 36BLOCKING_ENGINE_LOCK_ID: 2408:0:393:2BLOCKING_ENGINE_TRANSACTION_ID: 2408BLOCKING_THREAD_ID: 341 row in set (0,01 sec)Table DATA_LOCK_WAITS87© Copyright 2023 Percona ® LLC. All rights reserved
  • 88.
    • Partition• Subpartition•Lock data• Requesting and blocking thread idNew in DATA_LOCKS, missed in I_S88© Copyright 2023 Percona ® LLC. All rights reserved
  • 89.
    • View innodb_lock_waitsInsys Schema89© Copyright 2023 Percona ® LLC. All rights reserved
  • 90.
    • View innodb_lock_waits•Takes additional information fromINFORMATION_SCHEMA.INNODB_TRXIn sys Schema90© Copyright 2023 Percona ® LLC. All rights reserved
  • 91.
    • View innodb_lock_waitssys>select locked_table, ...-> from innodb_lock_waitsG*************************** 1. row ***************************locked_table: ‘test‘.‘t‘ blocking_pid: 4locked_index: PRIMARY blocking_query: NULLlocked_type: RECORD blocking_trx_rows_locked: 1waiting_trx_rows_locked: 1 blocking_trx_rows_modified: 1waiting_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 4waiting_pid: 6 sql_kill_blocking_connection: KILL 4waiting_query: UPDATE t SET f=’bar’ WHERE id=12345In sys Schema91© Copyright 2023 Percona ® LLC. All rights reserved
  • 92.
    Unlike most PerformanceSchema data collection,there are no instruments for controlling whether data lockinformation is collected or system variables for controllingdata lock table sizes. The Performance Schema collectsinformation that is already available in the server, so thereis no memory or CPU overhead to generate this informa-tion or need for parameters that control its collection.MySQL User Reference Manual• MUST HAVEData Locks: Impact92© Copyright 2023 Percona ® LLC. All rights reserved
  • 93.
    • All informationabout what is runninginside InnoDB• Writes to file every 15 seconds• Same information is available if runSHOW ENGINE INNODB STATUS interactively• Can generate a lot of data• NICE TO HAVEInnoDB Status File93© Copyright 2023 Percona ® LLC. All rights reserved
  • 94.
    • Table INNODB_METRICSin InformationSchema• All information about what is runninginside InnoDB• Same information can be found inSHOW ENGINE INNODB STATUS• Easier to process• NICE TO HAVEINNODB_METRICS: InnoDB Monitor94© Copyright 2023 Percona ® LLC. All rights reserved
  • 95.
    • Logs alldeadlocks into the file• Latest deadlock is visible in theSHOW ENGINE INNODB STATUS• Useful when debugging frequentdeadlocks• NICE TO HAVEInnoDB Deadlock Logger95© Copyright 2023 Percona ® LLC. All rights reserved
  • 96.
  • 97.
    • Memory, usedby internal mysqldstructuresMemory Diagnostic in P_S97© Copyright 2023 Percona ® LLC. All rights reserved
  • 98.
    • Memory, usedby internal mysqldstructures• Aggregated by• Global• Thread• Account• Host• UserMemory Diagnostic in P_S98© Copyright 2023 Percona ® LLC. All rights reserved
  • 99.
    • Memory, usedby internal mysqldstructures• Aggregated by• Global• Thread• Account• Host• User• Nice views in sys schemaMemory Diagnostic in P_S99© Copyright 2023 Percona ® LLC. All rights reserved
  • 100.
    • Instrumentsmysql> selectname from setup_instruments where name like ’memory%’;+-----------------------------------------------------+| name |+-----------------------------------------------------+...| memory/sql/Gtid_state::group_commit_sidno_locks || memory/sql/Mutex_cond_array::Mutex_cond || memory/sql/TABLE_RULE_ENT || memory/sql/Rpl_info_table || memory/sql/Rpl_info_file::buffer || memory/sql/db_worker_hash_entry || memory/sql/rpl_slave::check_temp_dir || memory/sql/rpl_slave::command_buffer || memory/sql/binlog_ver_1_event || memory/sql/SLAVE_INFO |...Memory: configuration100© Copyright 2023 Percona ® LLC. All rights reserved
  • 101.
    • Instruments• Consumers•Digest tables in Performance Schema• Views in sys schemaMemory: configuration101© Copyright 2023 Percona ® LLC. All rights reserved
  • 102.
    mysql> select thread_idtid, user, current_allocated ca, total_allocated-> from sys.memory_by_thread_by_current_bytes;+-----+-------------------------+-------------+-----------------+| tid | user | ca | total_allocated |+-----+-------------------------+-------------+-----------------+| 1 | sql/main | 2.53 GiB | 2.69 GiB || 150 | root@127.0.0.1 | 4.06 MiB | 32.17 MiB || 146 | sql/slave_sql | 1.31 MiB | 1.44 MiB || 145 | sql/slave_io | 1.08 MiB | 2.79 MiB |...| 60 | innodb/io_read_thread | 0 bytes | 384 bytes || 139 | innodb/srv_purge_thread | -328 bytes | 754.21 KiB || 69 | innodb/io_write_thread | -1008 bytes | 34.28 KiB || 68 | innodb/io_write_thread | -1440 bytes | 298.05 KiB || 74 | innodb/io_write_thread | -1656 bytes | 103.55 KiB || 4 | innodb/io_log_thread | -2880 bytes | 132.38 KiB || 72 | innodb/io_write_thread | -7632 bytes | 1.10 MiB |+-----+-------------------------+-------------+-----------------+145 rows in set (2.65 sec)Memory Usage by Thread102© Copyright 2023 Percona ® LLC. All rights reserved
  • 103.
    mysql> select *from sys.memory_by_thread_by_current_bytes-> order by current_allocated descG*************************** 1. row ***************************thread_id: 152user: lj@127.0.0.1current_count_used: 325current_allocated: 36.00 GiBcurrent_avg_alloc: 113.43 MiBcurrent_max_alloc: 36.00 GiBtotal_allocated: 37.95 GiB...• Find threads, eating memory, in asecond!Threads Statistics103© Copyright 2023 Percona ® LLC. All rights reserved
  • 104.
    • memory_summary_by_account_by_event_name• memory_summary_by_host_by_event_name•memory_summary_by_thread_by_event_name• memory_summary_by_user_by_event_name• memory_summary_global_by_event_nameRAW Performance Schema tables104© Copyright 2023 Percona ® LLC. All rights reserved
  • 105.
    • memory_summary_by_account_by_event_name• memory_summary_by_host_by_event_name•memory_summary_by_thread_by_event_name• memory_summary_by_user_by_event_name• memory_summary_global_by_event_name• You must enable memoryinstrumentation!RAW Performance Schema tables105© Copyright 2023 Percona ® LLC. All rights reserved
  • 106.
    • memory_summary_by_account_by_event_name• memory_summary_by_host_by_event_name•memory_summary_by_thread_by_event_name• memory_summary_by_user_by_event_name• memory_summary_global_by_event_name• You must enable memoryinstrumentation!• sys schema includes user nameRAW Performance Schema tables106© Copyright 2023 Percona ® LLC. All rights reserved
  • 107.
    • NAME@HOST -regular userUsers in sys.memory_* tables107© Copyright 2023 Percona ® LLC. All rights reserved
  • 108.
    • NAME@HOST -regular user• System users• sql/main• innodb/*• ...Users in sys.memory_* tables108© Copyright 2023 Percona ® LLC. All rights reserved
  • 109.
    • NAME@HOST -regular user• System users• sql/main• innodb/*• ...• Data comes from table THREADSUsers in sys.memory_* tables109© Copyright 2023 Percona ® LLC. All rights reserved
  • 110.
    • When memoryallocation happens• Must be enabled before use• If memory allocation is notinstrumented, it won’t be visible• Not 100% reliable!• Table itself holds little amount ofmemory: low impact• Finds source of memory leakimmediately!• MUST HAVEMemory Diagnostics: Impact110© Copyright 2023 Percona ® LLC. All rights reserved
  • 111.
    • Operating systeminstruments• Global status variables• Engine status• SHOW ENGINE INNODB STATUS• Always availableIO Diagnostics111© Copyright 2023 Percona ® LLC. All rights reserved
  • 112.
    • Dashboard DiskDetailsIO Diagnostics in PMM112© Copyright 2023 Percona ® LLC. All rights reserved
  • 113.
    • Dashboard MySQLInnoDB Details/InnoDB Disk IOIO Diagnostics in PMM113© Copyright 2023 Percona ® LLC. All rights reserved
  • 114.
    • Dashboard MySQLInnoDB Details/InnoDB LoggingIO Diagnostics in PMM114© Copyright 2023 Percona ® LLC. All rights reserved
  • 115.
    • Operating systeminstruments• top, atop, htop• ps, pidstat• mpstat• iostatCPU Diagnostics115© Copyright 2023 Percona ® LLC. All rights reserved
  • 116.
    • Table THREADSin Performance Schemamysql> select thread_id, processlist_id, thread_os_id, type, name from threads;+-----------+----------------+--------------+------------+----------------------------------------+| thread_id | processlist_id | thread_os_id | type | name |+-----------+----------------+--------------+------------+----------------------------------------+| 1 | NULL | 17983 | BACKGROUND | thread/sql/main || 2 | NULL | 17984 | BACKGROUND | thread/sql/thread_timer_notifier || 3 | NULL | 17985 | BACKGROUND | thread/innodb/io_ibuf_thread |...| 23 | 2 | 18009 | FOREGROUND | thread/sql/one_connection || 24 | 3 | 18011 | FOREGROUND | thread/sql/one_connection |+-----------+----------------+--------------+------------+----------------------------------------+CPU Diagnostics116© Copyright 2023 Percona ® LLC. All rights reserved
  • 117.
    • Dashboard CPUUtilization DetailsCPU Diagnostics in PMM117© Copyright 2023 Percona ® LLC. All rights reserved
  • 118.
    • Dashboard MySQLInstance SummaryCPU Diagnostics in PMM118© Copyright 2023 Percona ® LLC. All rights reserved
  • 119.
    • Dashboard MySQLInnoDB Details/InnoDB Contention/MiscCPU Diagnostics in PMM119© Copyright 2023 Percona ® LLC. All rights reserved
  • 120.
    • Dashboard MySQLInnoDB Details/MySQL SummaryCPU Diagnostics in PMM120© Copyright 2023 Percona ® LLC. All rights reserved
  • 121.
    • Same asbuilt-in and operating-systeminstruments• Provides nice GUI• Helps to answer on questions where theproblem goes• GOOD TO HAVEIO and CPU in PMM: Impact121© Copyright 2023 Percona ® LLC. All rights reserved
  • 122.
    • Error logfile: do not rotate too often!• Performance Schema• METADATA_LOCKS• DATA_LOCKS• DATA_LOCK_WAITS• Memory diagnosticsMust Have122© Copyright 2023 Percona ® LLC. All rights reserved
  • 123.
    • Performance Schema•Statements digests• Prepared statements• PMM• QAN in PMMGood to Have123© Copyright 2023 Percona ® LLC. All rights reserved
  • 124.
    • Slow querylog• Performance Schema• Statements• Stages• InnoDB• Status file• INNODB_METRICS• Deadlock loggerNice to Have124© Copyright 2023 Percona ® LLC. All rights reserved
  • 125.
    PMM DemoPerformance SchemaBenchmarksReferences125© Copyright 2023 Percona ® LLC. All rights reserved
  • 126.

[8]ページ先頭

©2009-2025 Movatter.jp