Movatterモバイル変換

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

• 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

# 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

• 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

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

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

• 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 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_* 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

• 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

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

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

• 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 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

• 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

• 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

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

• 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

• 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

• 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

Thank you!/svetsmirnova /svetasmirnova/svetsmirnova /SvetaSmirnova

Movatterモバイル変換

Change Language

MySQL Database Monitoring: Must, Good and Nice to Have

Embed presentation

Recommended

More Related Content

What's hot

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

More from Sveta Smirnova

Recently uploaded

MySQL Database Monitoring: Must, Good and Nice to Have