Exploring Percona XtraDB Cluster using Performance Schema Krunal Bauskar PXC Product Lead (Percona Inc.)
Agenda ● PXC and Performance Schema ● Understanding PXC instruments ● Enabling P_S for monitoring PXC ● Monitoring threads ● Monitoring files ● Monitoring Stages ● Monitoring Mutexes/Cond-Variables
PXC and Performance Schema ● PXC-5.6 had limited support for PFS ● Starting PXC-5.7 support has been extended to cover more instruments as part of PFS. ● Idea is to use PFS for monitoring and tracking performance issues in PXC-5.7 ● Development to PFS will continue and we plan to make it much more helpful in upcoming releases. ● Is PFS alternative to logging? ○ No. It is not. Both has different use-cases but can use-cases can overlap.
Understanding PXC instruments ● PFS has concept of instruments. DB internal objects can be called instrument. Like mutexes, condition variables, files, threads, etc... ● User can decide what all instruments to monitor based on need. ● PXC objects to monitor: ○ PXC threads (doesn’t generate event directly) ○ PXC files (doesn’t generate event directly) ○ PXC stages ○ PXC mutexes ○ PXC condition variables
PXC threads ● Applier Thread(s): Meant to apply replicated write-sets. There can more than one applier threads. ● Rollback Thread: If local transaction needs to be force aborted (due conflict with replicated transaction) applier thread with high priority transaction add this thread to a queue and signal rollback thread. Rollback thread then rolls back the given transaction. ● IST Receiver/Sender Thread: As name suggest they are responsible for doing IST. Comes into effect only when there is needed for IST. ● Gcache File Remove Thread: Dedicated thread meant to get rid of gcache file thereby keeping watch on disk-space. ● Receiver Thread: Read the packet received on cluster channel and invoke appropriate action. ● Service Thread: Handle the administrative work. ● GComm Thread: Low level gcomm-communication thread. ● SST Joiner/Donor Thread: Used during SST. ● Write-set Checkum Thread: if checksum needs to be done
PXC files ● Ring Buffer File: GCache file that is used as circular buffer for caching write-sets. ● GCache Page File: On-demand page store pages created as part of GCache Infrastructure. ● RecordSet File: Created to temporary cache record-set generated during transaction processing. (Certification keys and binary log data). ● Grastate File/Gvwstate file: grastate cache the state of cluster (marked unsafe during normal cluster operation) and gvwstate caches known good primary component state. [Not monitored through PFS as these are generally static and changes during configuration change only]
PXC Stages ● Any PXC action passes through different stages as commented below: ○ Writing/Deleting/Updating Rows: Corresponding DML in progress. ○ Applying write-sets: Applier thread is in process of applying write-set. ○ Commit/Rollback: Commit/Rollback transaction. ○ Replicating Commit: pre-commit action to put write-set on group channel. ○ Replaying: Replaying of locally aborted transaction. ○ Preparing for TO Isolation: Trigger TO isolation. ○ Applier Idle: Applier is waiting for next set of workload/write-sets to process. ○ Rollback/Aborted Idle: Rollback thread waiting watching queue for aborted transaction. ○ Aborter Active: Found a transaction to abort.
PXC mutex/condition-variables ● There are tons of them and most of them are self explanatory. wsrep_ready, wsrep_sst_init, wsrep_sst, wsrep_rollback, wsrep_slave_threads, wsrep_desync, wsrep_sst_thread, wsrep_replaying, etc.. certification_mutex, stats collection mutex, monitoring mutexes, recvbuf mutex, transaction handling mutex, saved state mutexes, etc.. To start with you may need to just keep important mutex say monitoring mutex as they control parallel replication or for that matter rollback mutex as it control rollback activity or replaying mutex as it control replaying of transaction.
Enabling P_S instruments for PXC ● Setup_consumer should be enabled based on what user needs or objective is ● Setup_instrument: select * from setup_instruments where name like '%wsrep%' or name like '%galera%'; Set the ENABLED and TIME to YES. Check the MySQL guide on how to do this iteratively or startup. +----------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/LOCK_wsrep_ready | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_sst | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_sst_init | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_rollback | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_replaying | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_slave_threads | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_desync | NO | NO | | wait/synch/mutex/sql/LOCK_wsrep_thd | NO | NO | …... | stage/wsrep/wsrep: replicating commit | NO | NO | | stage/wsrep/wsrep: waiting on replaying | NO | NO | | stage/wsrep/wsrep: in pre-commit stage | NO | NO | | stage/wsrep/wsrep: preparing for TO isolation | NO | NO | | stage/wsrep/wsrep: replaying trx | NO | NO | | stage/wsrep/wsrep: applier idle | NO | NO | | stage/wsrep/wsrep: in rollback thread | NO | NO | | stage/wsrep/wsrep: aborter idle | NO | NO | | stage/wsrep/wsrep: aborter active | NO | NO |
Monitoring threads ● Threads mysql> select * from threads where name like '%wsrep%' or name like '%galera%'; +-----------+-----------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+ ------------------+---------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | +-----------+-----------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+ ------------------+---------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ | 3 | thread/galera/THREAD_galera_service_thd | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 28425 | | 4 | thread/galera/THREAD_galera_gcommconn | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 28426 | | 5 | thread/galera/THREAD_galera_receiver | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | YES | YES | NULL | 28427 | | 6 | thread/sql/THREAD_wsrep_rollbacker | BACKGROUND | NULL | NULL | NULL | NULL | NULL | NULL | wsrep: aborter idle | NULL | NULL | NULL | YES | YES | NULL | 28428 | | 7 | thread/sql/THREAD_wsrep_applier | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 14 | NULL | NULL | NULL | NULL | YES | YES | NULL | 28429 | | 35 | thread/sql/THREAD_wsrep_sst_donor | BACKGROUND | NULL | NULL | NULL | NULL | NULL | 4 | NULL | NULL | NULL | NULL | YES | YES | NULL | 28791 | +-----------+-----------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+ ------------------+---------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ 6 rows in set (0.01 sec) ● Thread-ID help us associate the events with respective thread. ● Above snapshot was captured on DONOR node when SST was in progress.
Recommend
More recommend
