U Ce@sddlmZddlmZddlmZmZddlmZm Z m Z m Z ddl Z ddl Z ddlZddlZddlmZmZeejdd d Zd d Zd dZddZGdddZdejeeedddZgfdddgdddejeeee eeee ee eeeed ddZddddejeeddd Zddd!ejeee eeee d"d#d$Z!dddd%ejeeeeed&d'd(Z"ejeeed)d*d+Z#d,d-d.d/d0d/dd1fd2d3d4d5d/dd1fd6d7d8d9d/dd:fd;d7d8dd?d@d/dd1fdAd7d8dBd/ddCfdDd>d?dEd/ddFfdGdHdIdJd/dKdLfdMd>d?dNd/dKdLfdOdPdQdRd/ddSfdTd>d?dUd/ddSfdVdWdXdYd/dKdZfd[d>d?d\d/dKd]fd^d_d`dad/dKdbfdcd-ddded/ddffdgd3d4dhd/ddffdidjdkdld/ddmfdndodpdqd/ddmfdrd>d?dsd/ddffdtdodpdud/ddvfdwdxdydzd/dd{fd|d>d?d}d/dd{fd~d7d8dd/ddfddodpdd/ddfdd>d?dd/ddfgZ$GdddZ%GdddZ&dggfejeee'e(eee ee ed ddZ)dgfejeeee edddZ*ejeee edddZ+edddZ,ejeeeedddZ-ddejeeedddZ.dejeedddZ/dejeedddZ0ejeeeedddZ1ejeeee dddZ2ejeeedddZ3dS)) Condition)Thread)mysqlError)CallableListOptionalTupleN) ShellExecutor split_phases)pathreturncCstj|ttjdS)N) date_time)zipfileZipInfotupledatetimenow timetuple)r r//usr/lib/mysqlsh/plugins/debug/sql_collector.py make_zipinfo"s  rcCs$|ddddddddS)N _:\/)replacesrrrsanitize(sr!cCs~|dkr dS|dkr"t|dS|dkr:|dddS|dkrR|dddS|dkrj|ddd S|ddd SdS) Niz bytesi0.2fz KiBi@z MiBlz GiBz TiB)int)nrrr format_bytes.sr%cCs|dkr dS|dkr$|dddS|dkr<|dddS|dkrT|dddS|dkrl|ddd S|d kr|d dd S|d kr|d dd S|dkr|dddS|dkr|dddS|dSdS)NlP9%Cr"z wl09^Q z dlb"(fz hl@_-GZz mlJ)z siʚ;z msi@Bz usiz nsz psr)Z picosecondsrrrformat_pico_time=s&r&c@sBeZdZddZddZeedddZgfeedd d Z d S) InstanceSessioncCs`||_|d\|_}d|kr0|dd}|d\}}}t|dt|dt||_dd|d D|_d d|d D|_ d d|d D|_ dd|dD|_ dd|dD}d|k|_ d|jk|_ |jdrPd|_z|jdd|_Wn"|jdd|_YnXn d|_d|_dS)Nz/select @@performance_schema <> 'OFF', @@version-r.i'dcSsg|] }|dqSrr.0trrr `sz,InstanceSession.__init__..z!show tables in performance_schemacSsg|] }|dqSr+rr,rrrr/dsz!show tables in information_schemacSsg|] }|dqSr+rr-rrrrr/hszshow tables in syscSsg|] }|dqSr+rr0rrrr/kszshow tables in mysqlcSsg|] }|dqSr+rr0rrrr/oszAselect engine from information_schema.engines where support<>'NO'Z NDBCluster rpd_nodesz1show schemas like 'mysql_innodb_cluster_metadata'TzFselect instance_id from mysql_innodb_cluster_metadata.v2_this_instancezselect instance_id from mysql_innodb_cluster_metadata.instances where cast(mysql_server_uuid as binary)=cast(@@server_uuid as binary)F)sessionrun_sql fetch_onehas_pfssplitr#version fetch_all pfs_tables is_tablesZ sys_tables mysql_tableshas_ndb has_rapidhas_innodbcluster instance_id)selfr3r8abcZsupported_enginesrrr__init__Vsb"      zInstanceSession.__init__cCs|jjSNr3urirArrr__str__szInstanceSession.__str__)rcCs|jjSrFrGrIrrrrHszInstanceSession.uri)sqlargsc CsJz|j||WStk rD}ztd|t|W5d}~XYnXdS)NzERROR running query: )r3r4 Exceptionprintstr)rArKrLerrrr4s zInstanceSession.run_sqlN) __name__ __module__ __qualname__rErJpropertyrOrHlistr4rrrrr'Ts 1r')zffntsv_outheaderc s:fdd}|t|dd}||W5QRXdS)Ncs<r|dD]}||d|dqdS)Nutf-8 )writeencode)flinerZrYrrr]s zwrite_tsv..writez.tsvw)openr)rWrXrYrZr]r_rrar write_tsvsrdTF)as_yamlas_tsvfilter formatters ignore_errorsinclude_warnings) rWrXr3queryrLrerfrgrhrirjrc s2fdd} dddddDdd7dtjd7z||} | } Wn.tk r}z| |WYSd}~XYnXg}g}g}|rg}| jD]}| |j q| d d |zJt | j dD]6}g}|r||}|sqi}i}t t|D]}||}||| j|j <| j|j |kr^|||}| |dk rtt|nd t|tkrd |krd |d d d }||| j|j <nBt|ttttdtfkr||| j|j <nt||| j|j <q$|r| d || || |qWn0tk r\}z| |WYSd}~XYnX| r|d}|r| d|i|r| d|D] }| d dd|Dqd| d7|rt|d|r.tdd,}|d|t|dW5QRX|S)Nc s^rDtdd"}|d|ddW5QRXgStdd|dS)Nz.errorrbz #  r[zERROR: While executing "z": )rcrr]r^rN)rPr_rXrZrirkrWrr handle_errors &z dump_query..handle_error # Query: rlcSsg|]}d|qSz# rr-lrrrr/szdump_query..z# z # Started: #  NULL"z\"z SHOW WARNINGSWarningsz # WarningscSsg|] }t|qSr)rO)r-r_rrrr/s# Execution Time: z # )rZ.yamlrbr[)joinr7rr isoformatr4get_execution_timerMcolumnsappend column_labeliterr5rangelenrOtyperr#boolfloatr9rdrcrr]r^yamldump_all)rWrXr3rkrLrerfrgrhrirjrnr1Zexecution_timerPZraw_outZyaml_outrYr`rDrowentryZ raw_entryifieldwarningsrbr_rrmr dump_querys         r)rergri)rWrXr3c Cst|||d||||dS)Nzselect * from rgreri)r)rWrXr3tablerergrirrr dump_tablesrreri)rWprefixr3tablesrerirc CsFi}|D]8}td|dt||t|||||d||<q|S)N - Gathering ...r)rNrr!)rWrr3rreriinforrrrcollect_tabless r)rerirj)rWrr3queriesrirjc Csi}|D]}t|tkr>t|dkr2|\} } d} qN|\} } } n||d} } } td| dz4t||t| || |||| d|| dd<Wqtk r} z| jt j j fkrW5d} ~ XYqXq|S)Nrr)rerirjrgrr) rrrrNrr!rrcoder ErrorCodeZER_NO_BINARY_LOGGING) rWrr3rrerirjrqlabelrkrgrPrrrcollect_queries%s0    r)rWrXr3rc Cs|t|dh}|D]Z}td|ddddd|dDd}||dz||}|d |d d|d |d d dd|j Dddg}t |j dD]8} t t | D]&} | | } || dk rt| ndqqd |}||ddWqtk rr} z*|d| ddtd| W5d} ~ XYqXqW5QRXdS)Nrbz - Executing rrorlcSsg|]}d|qSrprrqrrrr/Osz/collect_queries_single_file..r[rxz r\rsrtcSsg|] }|jqSr)r)r-rDrrrr/Xsruz # Error: zERROR: )rcrrNrzr7r]r^r4r|r}rr5rrr~rOr) rWrXr3rr_rkrZresr`rrrrPrrrcollect_queries_single_fileHsH    rZ host_summaryz%{TABLE}.statement_latency DESCcCs|d|dpdS)NZstatement_latencyrrr rPrrrkrcCs|d|dpddkSNZ statementsrrrrrrrlrhostZhost_summary_by_file_ioz%{TABLE}.io_latency DESCz+(e.io_latency-IFNULL(s.io_latency, 0)) DESCcCs|d|dpddkSNZiosrrrrrrrorZhost_summary_by_file_io_typez*%{TABLE}.host, %{TABLE}.total_latency DESCz9e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESCcCs|d|dpddkSNtotalrrrrrrrrrzhost,event_nameZhost_summary_by_stagescCs|d|dpddkSrrrrrrrurZ!host_summary_by_statement_latencyz%{TABLE}.total_latency DESCz1(e.total_latency-IFNULL(s.total_latency, 0)) DESCcCs|d|dpddkSrrrrrrrxrZhost_summary_by_statement_typecCs|d|dpddkSrrrrrrr{rzhost,statementZio_by_thread_by_latencycCs|d|dpddkSrrrrrrr~rzuser,thread_id,processlist_idio_global_by_file_by_bytesz%{TABLE}.total DESCz!(e.total-IFNULL(s.total, 0)) DESCcCs|d|dpddkSrrrrrrrrr*fileZio_global_by_file_by_latencycCs|d|dpddkSrrrrrrrrio_global_by_wait_by_bytesz%{TABLE}.total_requested DESCz5(e.total_requested-IFNULL(s.total_requested, 0)) DESCcCs|d|dpddkSrrrrrrrrZ event_nameZio_global_by_wait_by_latencycCs|d|dpddkSrrrrrrrrZschema_index_statisticszf(%{TABLE}.select_latency+%{TABLE}.insert_latency+%{TABLE}.update_latency+%{TABLE}.delete_latency) DESCz((e.select_latency+e.insert_latency+e.update_latency+e.delete_latency)-IFNULL(s.select_latency+s.insert_latency+s.update_latency+s.delete_latency, 0)) DESCcCsH|d|d|d|d|d|d|d|dp@ddkS)NZ rows_selectedZinsert_latencyZ rows_updatedZ rows_deletedZ rows_insertedrrrrrrrs"z"table_schema,table_name,index_nameZschema_table_statisticscCs|d|dpddkS)N total_latencyrrrrrrrrztable_schema,table_nameZ#schema_tables_with_full_table_scansz%{TABLE}.rows_full_scanned DESCz9(e.rows_full_scanned-IFNULL(s.rows_full_scanned, 0)) DESCcCs|d|dpddkS)NZrows_full_scannedrrrrrrrrzobject_schema,object_nameZ user_summaryz9(e.statement_latency-IFNULL(s.statement_latency, 0)) DESCcCs|d|dpddkSrrrrrrrruserZuser_summary_by_file_iocCs|d|dpddkSrrrrrrrrZuser_summary_by_file_io_typez$%{TABLE}.user, %{TABLE}.latency DESCz-e.user, (e.latency-IFNULL(s.latency, 0)) DESCcCs|d|dpddkSrrrrrrrrzuser,event_nameZuser_summary_by_stagesz*%{TABLE}.user, %{TABLE}.total_latency DESCz9e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESCcCs|d|dpddkSrrrrrrrrZ!user_summary_by_statement_latencycCs|d|dpddkSrrrrrrrrZuser_summary_by_statement_typecCs|d|dpddkSrrrrrrrrzuser,statementZ"wait_classes_global_by_avg_latencyzBIFNULL(%{TABLE}.total_latency / NULLIF(%{TABLE}.total, 0), 0) DESCzhIFNULL((e.total_latency-IFNULL(s.total_latency, 0)) / NULLIF((e.total - IFNULL(s.total, 0)), 0), 0) DESCcCs|d|dpddkSrrrrrrrrZ event_classZwait_classes_global_by_latencycCs|d|dpddkSrrrrrrrrZwaits_by_host_by_latencycCs|d|dpddkSrrrrrrrrz host,eventZwaits_by_user_by_latencycCs|d|dpddkSrrrrrrrrz user,eventZwaits_global_by_latencycCs|d|dpddkSrrrrrrrreventsc@sreZdZgdfeeeedddZej edddZ ej eddd Z ej ee d d d Z ej edd dZdS) SQLExecutorT)r3 custom_sql allow_phasescCsX||_t|}|s*|ds"|dr*tdg|_|D]}|jddt|Dq4dS)Nr rzFOption 'customSql' may not contain before:, during: or after: prefixescSsg|]\}}d||fqS)Zscript_r)r-rrKrrrr/sz(SQLExecutor.__init__..)r3r rrr~ enumerate)rAr3rrZphasesrrrrrrEs zSQLExecutor.__init__rWrcCs4|jd}|r0tdt||d|j|dddS)NrzExecuting custom sql scriptsz custom_sql-FrirrNrr3rArWrrrrrrexecutes zSQLExecutor.executecCs4|jd}|r0tdt||d|j|dddS)Nrz%Executing custom 'before' sql scriptszcustom_sql-before-Frrrrrrexecute_befores zSQLExecutor.execute_beforerWr iterationcCs:|jd}|r6tdt||d|d|j|dddS)Nr z/Executing custom 'during' iteration sql scriptszcustom_sql-iterationr(Frr)rArWrrrrrrrexecute_durings zSQLExecutor.execute_duringcCs4|jd}|r0tdt||d|j|dddS)Nrz$Executing custom 'after' sql scriptszcustom_sql-after-Frrrrrr execute_afters zSQLExecutor.execute_afterN)rQrRrSr'rrOrrErZipFilerrr#rrrrrrrs    rc@s"eZdZeedddZejeedddZ ejee ddd Z e ejee d d d Z d dZejedddZejedddZddZddZeejedddZejedddZejeee dddZejeddd Zejedd!d"Zejeee dd#d$Zejedd%d&Zd'S)(DiagnosticsSession)r3 innodb_mutexcCs||_d|_i|_i|_i|_i|_d|_d|_||_|jj rl|j d d|_ |j d d|_ n d|_ d|_ |jjrdd|j dD|_ng|_|j d|j d  \}}|r|d kr|jj rd |_|j d nd|_dS) NFzFselect sys.ps_is_consumer_enabled('events_waits_history_long') = 'YES'rzpselect EXISTS(SELECT 1 FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES')cSsg|]}d|dqS)zndbinfo.rrr,rrrr/sz/DiagnosticsSession.__init__..zshow tables in ndbinfoz%set session group_concat_max_len=2048z%select @@sql_log_bin, @@binlog_formatZROWTzSET SESSION sql_log_bin = 0)r3pfs_instrumentationmetrics metrics_info status_start status_endpfs_instrumentation_changedhas_performance_analyzerrr6r4r5pfs_events_wait_history_longpfs_memory_instrumentedr=r9ndbinfo_tablesdisabled_binlog)rAr3rZlog_binZ binlog_formatrrrrEsJ   zDiagnosticsSession.__init__)rWrrc Cs|dks t|jjr8|||||d|dn|dkrHtd|d|||_|||d|jjrt dz d D]}|j |q|d |_ WnDt k r}z&t d |d |t d d|_ W5d}~XYnXdS)N)currentmediumfullz -applied_r(rzAperformance_schema is disabled, instrumentation cannot be changedstartrz. - Preparing statement performance analyzer...)4DROP TEMPORARY TABLE IF EXISTS sys.tmp_digests_startzPCALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests_start', NULL)z?CALL sys.statement_performance_analyzer('snapshot', NULL, NULL)zJCALL sys.statement_performance_analyzer('save', 'tmp_digests_start', NULL)TzError executing : zRelated checks will be skippedF) AssertionErrorr3r6enable_pfs_instrumentscollect_pfs_configr fetch_statusrcollect_metricsrNr4rrM)rArWrrrrPrrrr$s.   zDiagnosticsSession.startrcCs ||||||||dSrF)rcollect_other_stats)rArWrrrrriterateGszDiagnosticsSession.iterate)delayrWriter_fncsbfdd}d_t}t||fd_jjdkr^|||q:dS)Ncs||d_||d}jsxtd|d||d7}tD]}jdjrZq"qZq"dS)NFrzCollecting metrics...r zselect sleep(1)) acquire end_threadnotifyreleaserNrrr3r4)condrrrrrrArWrrrunloopNs  z4DiagnosticsSession.start_background..runloop)targetrL)rrrthreadrrwaitr)rArrWrrrrrrrstart_backgroundKs  z#DiagnosticsSession.start_backgroundcCsd|_|jdS)NT)rrrzrIrrrstop_backgroundlsz"DiagnosticsSession.stop_backgroundrcCs|jjrtd||||d|||_|||dD]}|j|q<|jrt ||d|jddddD]}|j|qr| ||dS) Nz( - Writing delta of collected metrics...end)z-CALL sys.ps_statement_avg_latency_histogram()z%-statement_performance_analyzer-deltaziCALL sys.statement_performance_analyzer('delta', 'tmp_digests_start', 'with_runtimes_in_95th_percentile')Fre)rz>CALL sys.statement_performance_analyzer('cleanup', NULL, NULL)) r3r6rNdump_metrics_deltarrdump_status_deltar4rrcleanup)rArWrrrrrfinishps$  zDiagnosticsSession.finishcCs&|jjr||jr"|jddS)NzSET sql_log_bin = 1)r3r6restore_pfs_instrumentsrr4)rArWrrrrrszDiagnosticsSession.cleanupcCs||_|jd|dkrp|jdd}|dkr>td|jjdkr|jdd}|dkrtdnhtd |jd d |_|d kr|jd |jdn |dkr|jd|jd|jddS)Nz1CALL sys.ps_setup_disable_thread(CONNECTION_ID())rzKselect count(*) from performance_schema.setup_consumers where enabled='YES'rzCWARNING: performance_schema.setup_consumers is completely disabled.i8zIselect count(*) from performance_schema.setup_threads where enabled='YES'zAWARNING: performance_schema.setup_threads is completely disabled.z.Configuring Performance Schema InstrumentationzCALL sys.ps_setup_save(0)TrzUPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME NOT LIKE '%\_history%'zUPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME NOT LIKE 'wait/synch/%'rzQUPDATE performance_schema.setup_consumers SET ENABLED = 'YES'zzUPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'zzUPDATE performance_schema.threads SET INSTRUMENTED = 'YES' WHERE PROCESSLIST_ID <> CONNECTION_ID())rr3r4r5rNr8r)rAlevelrDrrrrsT  z)DiagnosticsSession.enable_pfs_instrumentscCs.|jdkr*|jr*td|jdd|_dS)Nrz+Restoring Performance Schema Configurationsz CALL sys.ps_setup_reload_saved()F)rrrNr3r4rIrrrrs z*DiagnosticsSession.restore_pfs_instruments)stagerWrc stttdfdd }i}tD]~^}}}|s2td|dd|}d|d|pVd } ||} td |d t||d |d |j| d| d||<q |S)N)rrcsjd|g}g}|D]\}|}|dkr:|dksJ|dkrV|dkrV|tq|drl|tq|ds|ds|d s|d s|d r|d s|tq|d dq|S)zb Assemble a query on a sys view, formatting columns to be human readable. vselect column_name from information_schema.columns where table_schema='sys' and table_name=? order by ordinal_positionrrrtotal_requestedlatency_memory_memory_allocated_read_written_writecount_cSs|SrFr)r_rrrrrzODiagnosticsSession.fetch_status..formatters_for_table..) r3r4r9lowerr~r%endswithr& startswith)rr}rhcolumnlcolumnrIrrformatters_for_tables>       z=DiagnosticsSession.fetch_status..formatters_for_tablez ORDER BY z%{TABLE}zx$zSELECT * FROM `sys`.`x$`rVz - Gathering sys.rz-raw/r)F)rerh) rOrrk_sys_views_deltarrrNrr3) rArrWrrstatus table_nameZorder_byrrkrhrrIrrs& zDiagnosticsSession.fetch_statusc stttttttfdfdd }tD]̐^}}}|||\}}g} | dd|tj|j |D]l\} } g} t |D]<\} }z| || | Wqt || | | YqXq| ddd| Dqrt ||d|| q*dS) N)rpkrcs&ddjd|gD}g}dd|D]}d|dd|dkrh|fddq.|d kr|d krfd d }||q.||fd krfdd }||q.|dddkr|dr|fddq.|dkrfdd }||q.|dr8ddfdd }||q.drX|fddq.dkr|dkrpdnd|dkrdndfdd }||q.|d s|d!s|d"s|d#s|d$rd%sfd&d }||q.fd'd }||q.||fS)(NcSsg|] }|dqSr+rr0rrrr/szVDiagnosticsSession.dump_status_delta..formatters_for_table..rcSs|dkr |St|SrFrrrrr float_or_none szYDiagnosticsSession.dump_status_delta..formatters_for_table..float_or_none,cs |SrFrrrrrrrzTDiagnosticsSession.dump_status_delta..formatters_for_table..rZ write_pctcst|dp d}|d}t|dp(d}|d}|dks\|dks\||||dkr`dSd||||||ddS)NZ total_readrZ total_writtenr*.2fr)r rPZe_readZs_readZ e_writtenZ s_writtenrrrfmts  zODiagnosticsSession.dump_status_delta..formatters_for_table..fmt))rr)rrcs4|}t|pd}|dkr(dSt||SNrrr%rrrrrr )s  )Zmax_Zmin__latencycs |dk rtt|SdSrFr&rrrrrr3s   Z avg_latencycslt|dp d}|d}t|dp(d}|d}|dksT|dksT||dkrXdSt||||S)Nrrrrr&r rPZe_latZs_late_totals_totalrrrr 7s  Z _avg_latencyicst|dpd}|d}t|dp:d}|d}|dksl|dksl||dkrpdSt||||S)Nrrr rr)rrrrr Esrcs0t|dk r*t|t|p$dndSr rrrrrrQs )avg_readZ avg_writeZ avg_writtenrreadZwrittenr]cst|dpd}|d}t|dp:d}|d}|dks`|dkrddS||}||}|dkrdSt||S)NZtotal_rrr )r rPrrZe_countZs_countr$d)rsuffixsuffix2rrr Xsrrrrrrcs4|}t|pd}|dkr(dSt||Sr r )r rPe_s_r rrr ls  cs2t|p d}|}|dkr(dS||Sr r)r rPrrr rrr xs  )r3r4r9rr~rr)rrr}Zfcolumnsrr rI)rrrrrrrsh              zBDiagnosticsSession.dump_status_delta..formatters_for_tablersrtcSsg|] }|pdqS)r(r)r-r rrrr/sz8DiagnosticsSession.dump_status_delta..z-delta.) rOr rrrr~rzziprrrrNrd)rArWrrrrrr}rhlinesZ start_rowZend_rowrrr rrIrrs*   z$DiagnosticsSession.dump_status_deltac Cs|jjs dS|dkr |d}n|d|d}t|||jdddd}|D]P}|d}|d}|d } |d } d |jkr| | g|j|<|j|g|qJdS) Nr-raw/iteration-z.metricszkSELECT Variable_name, REPLACE(Variable_value, ' ', '\\n') AS Variable_value, Type, Enabled FROM sys.metricsFrerj Variable_nameZVariable_valueTypeEnabledname)r3r6rrr setdefaultr~) rArWrrrXrrr"valuerenabledrrrrs*  z"DiagnosticsSession.collect_metricsc s<ddgdgddgfdd|jjD}|fdd|jjD7}|fd d|jjD7}|jjrr|d |jjrd D]}||jjkr~|d |q~d d}|jjdkrd}d}nd}d}dddd|dd|ddd|fdg}|jjr|dddg7}t|||j|d d!t |||j|d d!| ||dS)"NZ host_cacheZpersisted_variablespluginsZaudit_log_userZaudit_log_filtercs(g|] }|ds|krd|qS)Z replication_performance_schema.)rr,)other_pfs_tablesrrr/s z@DiagnosticsSession.collect_configs_and_state..csg|]}|krd|qS)zinformation_schema.rr,)r;rrr/scsg|]}|krd|qS)zmysql.rr,)r<rrr/szndbinfo.threadblocks)r2Zrpd_exec_statsZrpd_query_statsr'cSst|}d|d<|S)Nz*****)rUrrrrfilter_slave_master_infoszNDiagnosticsSession.collect_configs_and_state..filter_slave_master_infoi8ZREPLICAZREPLICASZSLAVEz SLAVE HOSTS)zglobal variablesaSELECT g.variable_name name, g.variable_value value /*!80000, i.variable_source source*/ FROM performance_schema.global_variables g /*!80000 JOIN performance_schema.variables_info i ON g.variable_name = i.variable_name */ ORDER BY namezXA RECOVER CONVERT xidzSHOW BINARY LOGSzSHOW zSHOW MASTER STATUSz STATUSzreplication master_infoz;SELECT * FROM mysql.slave_master_info ORDER BY Channel_name)zreplication relay_log_infoz{SELECT Channel_name, Sql_delay, Number_of_workers, Id FROM mysql.slave_relay_log_info ORDER BY Channel_name)zrapid table statusa SELECT rpd_table_id.ID, rpd_table_id.Name, rpd_tables.* FROM performance_schema.rpd_table_id, performance_schema.rpd_tables WHERE rpd_tables.ID = rpd_table_id.ID ORDER BY rpd_table_id.SCHEMA_NAME,rpd_table_id.TABLE_NAME)zrapid total table sizezCSELECT SUM(IFNULL(SIZE_BYTES,0)) FROM performance_schema.rpd_tables)zrapid avail_rnstate nodeszSELECT IFNULL(SUM(memory_total), 0), IFNULL(SUM(memory_usage), 0), IFNULL(SUM(BASEREL_MEMORY_USAGE),0) FROM performance_schema.rpd_nodes WHERE status = 'AVAIL_RNSTATE'Tr) r3r:r;r<r=r~r>r8rrr) rArWrrr.r+Z kw_replicaZ kw_replicasrr)r;r<r(rcollect_configs_and_statesZ        z,DiagnosticsSession.collect_configs_and_statecCs&dddddg}t|||j|dddS)N)z pfs actorsz-SELECT * FROM performance_schema.setup_actors)z pfs objectsz.SELECT * FROM performance_schema.setup_objects)z pfs consumerszSELECT NAME AS Consumer, ENABLED, sys.ps_is_consumer_enabled(NAME) AS COLLECTS FROM performance_schema.setup_consumers)zpfs instrumentsaSELECT SUBSTRING_INDEX(NAME, '/', 2) AS 'InstrumentClass', ROUND(100*SUM(IF(ENABLED = 'YES', 1, 0))/COUNT(*), 2) AS 'EnabledPct', ROUND(100*SUM(IF(TIMED = 'YES', 1, 0))/COUNT(*), 2) AS 'TimedPct' FROM performance_schema.setup_instruments GROUP BY SUBSTRING_INDEX(NAME, '/', 2) ORDER BY SUBSTRING_INDEX(NAME, '/', 2))z pfs threadszSELECT `TYPE` AS ThreadType, COUNT(*) AS 'Total', ROUND(100*SUM(IF(INSTRUMENTED = 'YES', 1, 0))/COUNT(*), 2) AS 'InstrumentedPct' FROM performance_schema.threads GROUP BY TYPETr)rr3)rArWrrrrrrsz%DiagnosticsSession.collect_pfs_configc Csddddddddd g }|jr&|d |jr<|d d d dg7}dddddg}|jrZ|dg7}|jjr|ddg7}|dd|jD7}|dg7}|dk r|d|d7}t|||j|ddt|||j|dddS)Nz!performance_schema.metadata_lockszperformance_schema.threadszsys.schema_table_lock_waitszsys.session_ssl_statusz sys.sessionzsys.processlistz'performance_schema.events_waits_currentzinformation_schema.innodb_trxz!information_schema.innodb_metricszsys.latest_file_ioz#sys.memory_by_host_by_current_bytesz%sys.memory_by_thread_by_current_bytesz#sys.memory_by_user_by_current_bytesz"sys.memory_global_by_current_byteszSHOW GLOBAL STATUSzSHOW ENGINE INNODB STATUSz%SHOW ENGINE PERFORMANCE_SCHEMA STATUSzSHOW FULL PROCESSLISTzSHOW OPEN TABLESzSHOW ENGINE INNODB MUTEXzSHOW ENGINE NDBCLUSTER STATUS)zndb memoryusagezSELECT node_id, memory_type, format_bytes(used) AS used, used_pages, format_bytes(total) AS total, total_pages, ROUND(100*(used/total), 2) AS 'Used %' FROM ndbinfo.memoryusagecSsg|]}|dkr|qS)zndbinfo.memoryusagerr,rrrr/<sz:DiagnosticsSession.collect_other_stats..zinformation_schema.FILESrr)Tr) rr~rrr3r=rrr)rArWrrrrrrrrsP    z&DiagnosticsSession.collect_other_statscCs |jjs dSdddddddd d d d d dddddddg}dd}d}||dd}d}|jD]\}} |rdg} tdt| dD]8} | d| | dkr| d| dd| dq| d d!g7} |d" | d# d$d%}|g} ||k} d} d&}d}t | D]\} }d'}||}| |d|| d&kr| s|dk rt |jd(| }t |jd(| d}|dk r| dk r|| krd)}n"|| d*|| ||d+d,}| ||} q| |j |7} |d" | d# d$qfW5QRXdS)-z Format all collected metrics iterations and format into a human readable form, also including delta values between each iteration. NZinnodb_buffer_pool_pages_totalZinnodb_page_sizeZlast_query_costZlast_query_partial_plansZqcache_total_blocksZslave_last_heartbeatZssl_ctx_verify_depthZssl_ctx_verify_modeZssl_session_cache_sizeZssl_verify_depthZssl_verify_mode ssl_versionZbuffer_flush_lsn_avg_rateZbuffer_flush_pct_for_dirtyZbuffer_flush_pct_for_lsnZbuffer_pool_pages_totalZlock_row_lock_time_avgZlock_row_lock_time_maxcSsz t|WSYdSXdSrFrrrrrasnum^s z4DiagnosticsSession.dump_metrics_delta..asnum2z-metrics.summary.tsvrbTrr zOutput zDelta (z -> )r r!rtrlr[FrrVzUNIX_TIMESTAMP()z 0 (0/sec)z (rz/sec))r3r6rcritemsrrr~r]rzr^rrr)rArWrZno_delta_namesr.Zmax_field_lengthr_firstr"valuesr`rZno_deltaprevZ prev_timeZnvaluer$deltatimerrrrGst    " z%DiagnosticsSession.dump_metrics_deltaN)rQrRrSr'rrErrrOrr#rrrrrrrrrrrrr,rrrrrrrrs2, # !80 P 2r) rWrr3 iterationsrpfsInstrumentationrr custom_shellc Cst|dd} t||d} zFt||dd} | ||| ||| ||| ||d|t|D]} td| dd|d| ||d| d| ||| | ||| | d7} | |dkrtd |d z| d |gWn t k rtd YnXqlq"ql|d kr4td| ||d| ||| ||Wn| ||YnXdS)NTrr diagnosticsz+Collecting performance metrics (iteration #r z of z)...z Sleeping for zs...zselect sleep(?)z^C - aborting...rz#Performance metrics collection done)r rrrr,rrrNrrr4KeyboardInterruptrrr) rWrr3r7rr8rrr9shelldiagcustomrrrrcollect_diagnosticss@         rA)rWrr3rrc Csft||d}zFt||dd}|||||||||d|||dW5|||XdS)Nr;Fr:)rrrrr,rr)rWrr3rrr?r@rrrcollect_diagnostics_onces   rB)rWrr3rc Csd}z|d}Wntk r}z|jtjjksD|jtjjkrP|WYhS|rtd|| t |dd&}| d| |d dW5QRX|WYSW5d}~XYnXd}d} td |D]p} d | d krd }nd | d krd } | ddkr| d dkrq| d } t ||d| |d| d |dqd}|sZ| r|dd } | dkr|d}n.z |dd } | rd}Wn YnX|S)Nz1show full tables in mysql_innodb_cluster_metadataz0ERROR: Could not query InnoDB Cluster metadata: z#mysql_innodb_cluster_metadata.errorrbs2show full tables in mysql_innodb_cluster_metadata rlr[Fz/Dumping mysql_innodb_cluster_metadata schema...Zasync_cluster_viewsrTZclusterset_viewsr z BASE TABLE)Zschema_versionzmysql_innodb_cluster_metadata.rgrzGselect cluster_type from mysql_innodb_cluster_metadata.v2_this_instancearzDselect count(*) from mysql_innodb_cluster_metadata.v2_cs_clustersetscs)r4r9rrrrZER_BAD_DB_ERRORZER_NO_SUCH_TABLErNrcrr]r^rr5) rWrr3ri cluster_typer1rPr_Z rs_possibleZ cs_possiblerrr.rrrcollect_cluster_metadatasv          rGr3cCsDzdd|dDWSdd|dDYSXdS)NcSsg|]}|d|dfqSrr rr0rrrr/sz(get_topology_members..zLselect instance_id, endpoint from mysql_innodb_cluster_metadata.v2_instancescSsg|]}|d|dfqSrIrr0rrrr/sz]select instance_id, addresses->>'$.mysqlClassic' from mysql_innodb_cluster_metadata.instances)r4r9rHrrrget_topology_memberss  rJ)rWr r3rirc Cs<|jdkr4tddd}t|||d|d|ddSdSdS) Ni8z - Gathering error_logcSsd|dkrdS|S)Nztemporary passwordr)rr*rrr filter_pwds z)collect_error_log_sql..filter_pwdzperformance_schema.error_logFrT)r8rNr)rWr r3rirKrrrcollect_error_log_sqls rLr)rWrr3ricCs4ddg}d|jkr|dt||||d|ddS)N)zslow queries in 95 pctilez=SELECT * FROM sys.statements_with_runtimes_in_95th_percentile)z%slow queries summary by rows examinedaGSELECT DIGEST, substr(DIGEST_TEXT, 1, 50), COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, round(SUM_ROWS_SENT/SUM_ROWS_EXAMINED, 5) ratio FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'select%' and (SUM_ROWS_SENT/SUM_ROWS_EXAMINED) < .5 ORDER BY SUM_ROWS_EXAMINED/SUM_ROWS_SENT desc limit 20slow_log)rMzSELECT * FROM mysql.slow_logTr)r<r~r)rWrr3rirrrrcollect_slow_queries1s  rN)rWrr3c Cs@td|d}|t|dd}|D]}|d|d}}|d|d|d d z8|jd ||gd D]} || dd d qWntk r$} zd|rtd |d|d| |d|d|d| d d ntd|d|d| W5d} ~ XYnX|dq4W5QRXdS)Nz8Collecting InnoDB Cluster accounts and grant informationzAselect user,host from mysql.user where user like 'mysql_innodb_%'zcluster_accounts.tsvrbrr z-- @rlr[zshow grants for ?@?)rLz"WARNING: Error getting grants for rzCould not get grants for z ERROR: Error getting grants for r\)rNr4r9rcrr]r^rM) rWrr3riZaccountsr_rrrr1rPrrrcollect_innodb_cluster_accountsIs4rPcCsF|r tdddddg}|r,|dddd g7}t||||d |d dS) Nz,Collecting Schema Information and Statistics)zschema tables without a PKaSELECT t.table_schema, t.table_name, t.table_rows, t.engine, t.data_length, t.index_length FROM information_schema.tables t LEFT JOIN information_schema.statistics s on t.table_schema=s.table_schema and t.table_name=s.table_name and s.index_name='PRIMARY' WHERE s.index_name is NULL and t.table_type = 'BASE TABLE' and t.table_schema not in ('performance_schema', 'sys', 'mysql', 'information_schema'))zschema routine sizezvSELECT ROUTINE_TYPE, COUNT(*), SUM(LENGTH(ROUTINE_DEFINITION)) FROM information_schema.ROUTINES GROUP BY ROUTINE_TYPE;)zschema table countz.SELECT count(*) FROM information_schema.tables)zschema unused indexesz'SELECT * FROM sys.schema_unused_indexes)zschema object overviewz(select * from sys.schema_object_overview)zschema top biggest tablesaselect t.table_schema, t.table_name, t.row_format, t.table_rows, t.avg_row_length, t.data_length, t.max_data_length, t.index_length, t.table_collation, json_objectagg(idx.index_name, json_object('columns', idx.col, 'type', idx.index_type, 'cardinality', idx.cardinality)) indexes, group_concat((select concat(c.column_name, ':', c.column_type) from information_schema.columns c where c.table_schema = t.table_schema and c.table_name = t.table_name and c.column_type in ('blob'))) blobs from information_schema.tables t join (select s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality, json_arrayagg(concat(c.column_name, ':', c.column_type)) col from information_schema.statistics s left join information_schema.columns c on s.table_schema=c.table_schema and s.table_name=c.table_name and s.column_name=c.column_name group by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality order by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality) idx on idx.table_schema=t.table_schema and idx.table_name = t.table_name where t.table_type = 'BASE TABLE' and t.table_schema not in ('mysql', 'information_schema', 'performance_schema') group by t.table_schema, t.table_name, t.engine, t.row_format, t.table_rows, t.avg_row_length, t.data_length, t.max_data_length, t.index_length, t.table_collation order by t.data_length desc limit 20)zschema table enginesa>SELECT ENGINE, COUNT(*) AS NUM_TABLES, sys.format_bytes(SUM(DATA_LENGTH)) AS DATA_LENGTH, sys.format_bytes(SUM(INDEX_LENGTH)) AS INDEX_LENGTH, sys.format_bytes(SUM(DATA_LENGTH+INDEX_LENGTH)) AS TOTAL FROM information_schema.TABLES GROUP BY ENGINE)zschema table infoz'SELECT * FROM information_schema.TABLESTr)rNr)rWrr3rrirrrrcollect_schema_statsgs*rQ)rWrr3schemarc Cs|t|dt|dt|dd}dd}|d||g}|srtd|d |d W5QRdS||}g}g} g} g} |d d kr|d t|dt|d} n|dt|dt|d} |dt|dt|} t | jdD]}| ||q|d||g} t | jdD]}| ||qN|d||g} t | jdD]}| ||q|d||g} t | jdD]}| ||q|||| || | | d}| t |dW5QRXdS)Nzreferenced_table-r)ryrbcSstt|ddS)Nrlz\n)jsonloadsrOr)rrrr pythonizesz%collect_table_info..pythonizezMSELECT * FROM information_schema.tables WHERE table_schema=? AND table_name=?zWARNING: Could not find table `z`.`z.` referenced in query (try 'USE schema' first)Z TABLE_TYPEZVIEWzSHOW CREATE VIEW rzSHOW CREATE TABLE r zSHOW INDEX IN z^SELECT * FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA=? AND EVENT_OBJECT_TABLE=?zMSELECT * FROM mysql.innodb_table_stats WHERE database_name=? AND table_name=?zMSELECT * FROM mysql.innodb_index_stats WHERE database_name=? AND table_name=?)z Table Namez Table Schemaz Table StatusZDDLZIndexesZTriggerszInnoDB Table StatszInnoDB Index Statsr[)rcrr!r4fetch_one_objectrNrquote_identifierr5rr~r]rdumpr^)rWrr3rRrr_rUZ table_statusZindexesZtriggersZ table_statsZ index_statsZddlrr1rrrrcollect_table_infos  rY)rWr3rkrrcCsdddg}|D]}||qt||d|d|dddt||d |d dd d g}|D]}||q`d d|fg}|jdkr|dd|ft||||ddS)N(SET SESSION optimizer_trace='enabled=on'zSET optimizer_trace_offset=-1zSET optimizer_trace_limit=1ZexplainEXPLAIN FTrzexplain-optimizer_trace0SELECT * FROM information_schema.optimizer_tracer)SET SESSION optimizer_trace='enabled=off'Z explain_jsonzEXPLAIN format=json i8Zexplain_analyzezEXPLAIN ANALYZE )rj)r4rr8r~r)rWr3rkrbeforerafterrrrr explain_querys4   r`)rWr3rkrc Cs@ddd|ddddd|ddd d g }t||d ||dS) Nz#SET SESSION use_secondary_engine=ONz SELECT NOW()r[z!SHOW SESSION STATUS LIKE 'rapid%'rZzSET optimizer_trace_offset=-2z~SELECT query, trace->'$**.Rapid_Offload_Fails', trace->'$**.secondary_engine_not_used' FROM information_schema.optimizer_tracer\z$SET SESSION use_secondary_engine=OFFr]zexplain-rapid.txt)r)rWr3rkrrrrrexplain_heatwave_querys ra)rV)F)FF)4Zmultiprocessingr threadingrmysqlshrrtypingrrrr rrSrr host_infor r rOrrr!r%r&r'rrUrdrrrdictrrrrrrr#rrArBrGrJrLrNrPrQrYr`rarrrrs:  B c   # "R<& 7  7    ! 9 I