Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL Performance SchemaOut of the Box in MySQL 5.7
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.
Mayank PrasadPrincipal Member Technical StaffOracle, MySQLNovember 20, 2015
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor StatementThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
2
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Need and Design
Instruments and instrumentation
Statistics tables
Use cases
What’s new in MySQL 5.7
1
2
3
4
5
3
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Need and Design
Instruments and instrumentation
Statistics tables
Use cases
What’s new in MySQL 5.7
1
2
3
4
5
4
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 5
Why Performance Schema?
SystemLow throughput?
?
DBAHot table?
NetworkHigh traffic on link?
StorageToo much Disk spin?App Developer
Slow application?
MySQL Developer Code contention?
End User stuck session?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7 Performance Schema : DesignBlock Diagram
MySQLServer
Instrumentation points(P_S hooks)
P_S Internal Buffers
P_SStorageEngine
StorageEngine
Interface StatisticsReport
FetchData
SQL Query
Statistics Collection Statistics Reporting
Collect DataStoreData
P_STables
6
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Need and Design
Instruments and instrumentation
Statistics tables
Use cases
What’s new in MySQL 5.7
1
2
3
4
5
7
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Enabled/Disabled
at runtime.
Instruments
8
• Name of monitored activity.• Tree like structure. Separated by ‘/’.• Left to right : More generic to more specific.
• 1000+ instruments in MySQL 5.7.• Stored in setup_instruments table.
wait/io/file/myisam/logstatement/sql/select
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Need and Design
Instruments and instrumentation
Statistics tables
Use cases
What’s new in MySQL 5.7
1
2
3
4
5
9
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Statistics Tables in Performance Schema
10
SETUP TABLES
Instruments
Actors
Objects
Consumers
TIMERS
EVENTS TABLES
Transactions
Statements
Stages
Waits
Idle
REPLICATIONSUMMARY
SYSTEM VARIABLES
STATUS VARIABLES
LOCK TABLES
Metadata locks
Table Handles
SUMMARY TABLES
EventsMemory
File I/O, Table I/O, Table locks
SocketConnection
…
CONNECTIONAttribute
Type
INSTANCETABLES
Mutex
RW_locks
File
Sockets
Cond MISC
By_global By_thread By_user/host By_account By_digest
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Need and Design
Instruments and instrumentation
Statistics tables
Use cases
What’s new in MySQL 5.7
1
2
3
4
5
11
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
What does Performance Schema provide …update setup_instruments set ENABLED='YES', TIMED='YES';
12
Connection 1 (Thread 24)start transaction; insert into test.t1 values('11'); commit; start transaction; insert into test.t1 values('12'); commit;start transaction; insert into test.t1 values('13'); commit;select * from test.t1;
Connection 2 (Thread 25)start transaction; insert into test.t2 values('21'); commit;
start transaction; insert into test.t2 values('22'); commit;
Three inserts
Twoinserts
LatestStatement
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
What does Performance Schema provide … (cont.)Statements Statistics
* Timer unit is PICOSECOND.
EVENTS_STATEMENTS_CURRENT
THREAD_ID 24 25
EVENT_NAME statement/sql/select
statement/sql/commit
TIMER_WAIT 876585000 15998287000
SQL_TEXT select * from test.t1 commit
ROWS_SENT 3 0
NO_INDEX_USED 0 0
SELECT_SCAN 1 0
13
EVENTS_STATEMENTS_SUMMARY_BY_THREAD_BY_EVENT_NAME
THREAD_ID 24 25
EVENT_NAME statement/sql/insert
statement/sql/insert
COUNT_STAR 3 2
SUM_TIMER_WAIT 35181659000 3477432000
SUM_ROWS_AFFECTED 3 2
SUM_SELECT_SCAN 0 0
SUM_NO_INDEX_USED 0 0
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
What does Performance Schema provide … (cont.)Statements Statistics (cont.)
* Timer unit is PICOSECOND.
14
EVENTS_STATEMENTS_SUMMARY_GLOBAL_BY_EVENT_NAME
EVENT_NAME statement/sql/insert statement/sql/commit
COUNT_STAR 5 5
SUM_TIMER_WAIT 38659091000 65812216000
… …
SUM_ROWS_AFFECTED 5 0
… … …
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Use case 1Description
15
• Multiple queries running for long on MySQL Server• Few long running query (taking lots of time)• No idea which one• No idea why• What to do ? …
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Use case 1Diagnosis
16
– THREAD_ID: 25– EVENT_ID: 89– EVENT_NAME: statement/sql/select– SQL_TEXT : select bla bla bla…;
• Wait ! There’s more!– SELECT_SCAN : 1– NO_INDEX_USED: 1
• Aha !!
SELECT * FROM events_statements_history WHERE TIMER_WAIT > ‘X’;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Use case 2Statements giving errors ( or warnings)
17
SELECT DIGEST_TEXT, SCHEMA_NAME, COUNT_STAR, SUM_ERRORS, SUM_WARNINGS FROM events_statements_summary_by_digest WHERE SUM_ERRORS > 0 ORDER BY SUM_ERRORS DESC limit 1\G;
EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
DIGEST_TEXT CREATE TEMPORARY TABLE IF NOT ... _logs` ( `id` INT8 NOT NULL )!
SCHEMA_NAME mem
COUNT_STAR 1725
SUM_ERRORS 1725
SUM_WARNINGS 0
FIRST_SEEN 2014-05-20 10:42:32
LAST_SEEN 2014-05-21 18:39:22
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Use case 3Description
18
• Multithreaded environment• My session is stuck• No idea why• What to do ? …
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Use case 3• What T1 is waiting for
– Say T1 is waiting for mutex_A (column OBJECT_INSTANCE_BEGIN)
• Lets see who has taken this mutex_A
– Ok, so thread T2 is holding mutex_A (column LOCKED_BY_THREAD_ID)
• Find out what thread t2 is waiting for
• And so on…
SELECT * FROM mutex_instances WHERE OBJECT_INSTANCE_BEGIN = mutex_A;
SELECT * FROM events_waits_current WHERE THREAD_ID = T2;
Diagnosis
19
SELECT * FROM events_waits_current WHERE THREAD_ID = T1;
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Summary TablesTables for replication statistics
20
Replication status
Connection Information Execute Information
Connection Configuration Connection StatusExecute Configuration Execute Status
Coordinator/SQL Thread
Worker Thread
replication_connection_configuration
replication_connection_status
replication_execute_status_by_coordinator
replication_execute_status_by_worker
replication_execute_status
replication_execute_configuration
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Summary TablesTables for replication statistics
Table Information
replication_connection_configuration (Host, Port, User etc.)
replication_connection_status (Server UUID, Receiver thread ID, Service State etc.)
replication_execute_configuration (Desired Delay)
replication_execute_status (Remaining Delay)
replication_execute_status_by_coordinator (Thread Id, Service State, Last Error info.)
replication_execute_status_by_worker (WID, WTID, Service State, Last error Info.)
21
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Replication Summary TablesTables for replication statistics
22
SHOW SLAVE STATUS (Limitations)
– No logical division of information.
– Lots of information packed together.
– No cherry picking (difficult for automation).
– Difficult to scale (more new fields).
Why Performance Schema Tables?
– Split logically into different tables.
– SQL Interface. Fetch what is required.
– Easier to extend.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Event Hierarchy
23
event_id
nesting_event_id
event_id
nesting_event_id
event_id
nesting_event_id
event_id
nesting_event_id
event_id
nesting_event_id
event_id
nesting_event_id
event_id
nesting_event_id
event_id
nesting_event_id
Transactions Statements Stages Waits
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Need and Design
Instruments and instrumentation
Statistics tables
Use cases
What’s new in MySQL 5.7
1
2
3
4
5
24
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
What’s new in MySQL 5.7
2525
EnhancementsNew Instruments
transactionsMemory usage
Stored programs
Prepared statements
Metadata locks
Connection type InnoDB
Stages
User variables
Replication summary
tables
History per session
Scalable memory
allocation
Configurable digest size
…
MySQL 5.7
Global/Session variables/status
87 Tables and 1000+ Instruments
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
SYS Schema (earlier known as P_S Helper)What’s new in MySQL 5.7
Performance Schema tables
Procedures Functions Views
Information Schema tables
Formatted Statistics
SYS SCHEMA
Many of common
day to day use cases
for DBAs debugging
And tuning
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Thank You