Skip to content

Instantly share code, notes, and snippets.

@yoku0825

yoku0825/gist:b5e1244fda9a8b8f70cf5dcc9a8f2309

Created June 17, 2020 10:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yoku0825/b5e1244fda9a8b8f70cf5dcc9a8f2309 to your computer and use it in GitHub Desktop.
Save yoku0825/b5e1244fda9a8b8f70cf5dcc9a8f2309 to your computer and use it in GitHub Desktop.
Download ZIP
EXPLAIN ANALYZE
Raw
gistfile1.txt
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary> (actual time=0.001..0.016 rows=62 loops=1)
-> Temporary table with deduplication (actual time=99911.386..99911.423 rows=62 loops=1)
-> Filter: (is_metrics_list_analyze_90.`name` in ('lock_deadlocks','lock_row_lock_time_avg')) (actual time=99790.494..99908.201 rows=1178 loops=1)
-> Table scan on is_metrics_list_analyze_90 (actual time=0.002..40.599 rows=174298 loops=1)
-> Materialize (actual time=99788.102..99874.969 rows=174298 loops=1)
-> Window aggregate with buffering: first_value(daily_is_metrics_list.avg_count) OVER w_all, last_value(daily_is_metrics_list.avg_count) OVER w_all (actual time=98284.457..99478.783 rows=174298 loops=1)
-> Table scan on <temporary> (actual time=0.002..51.313 rows=174298 loops=1)
-> Temporary table (actual time=98283.337..98386.183 rows=174298 loops=1)
-> Window aggregate: (actual time=97824.019..98142.470 rows=174298 loops=1)
-> Sort row IDs: daily_is_metrics_list.hostname, daily_is_metrics_list.datadir, daily_is_metrics_list.`name`, last_90_days_calendar._date (actual time=97824.006..97893.492 rows=174298 loops=1)
-> Table scan on <temporary> (actual time=0.001..44.611 rows=174298 loops=1)
-> Temporary table (actual time=90393.608..90485.474 rows=174298 loops=1)
-> Nested loop left join (actual time=88750.924..88962.737 rows=174298 loops=1)
-> Table scan on last_90_days_calendar (actual time=0.001..0.031 rows=90 loops=1)
-> Materialize recursive CTE last_90_days_calendar (actual time=0.128..0.188 rows=90 loops=1)
-> Rows fetched before execution (actual time=0.000..0.001 rows=1 loops=1)
-> Repeat until convergence
-> Filter: (last_90_days_calendar._date > <cache>((curdate() - interval 89 day))) (cost=2.73 rows=1) (actual time=0.004..0.033 rows=44 loops=2)
-> Scan new records on last_90_days_calendar (cost=2.73 rows=2) (actual time=0.001..0.010 rows=45 loops=2)
-> Index lookup on daily_is_metrics_list using <auto_key0> (_date=last_90_days_calendar._date) (actual time=0.004..1.295 rows=1936 loops=90)
-> Materialize (actual time=986.124..987.942 rows=1936 loops=90)
-> Table scan on <temporary> (actual time=0.001..185.030 rows=433345 loops=1)
-> Aggregate using temporary table (actual time=86992.311..87299.362 rows=433345 loops=1)
-> Nested loop inner join (actual time=0.919..73935.971 rows=3865356 loops=1)
-> Nested loop left join (actual time=0.499..63.774 rows=31 loops=1)
-> Nested loop inner join (actual time=0.491..63.516 rows=31 loops=1)
-> Nested loop inner join (actual time=0.483..59.921 rows=31 loops=1)
-> Nested loop inner join (actual time=0.468..3.640 rows=31 loops=1)
-> Table scan on hostname_list (actual time=0.001..0.043 rows=21 loops=1)
-> Materialize (actual time=0.448..0.509 rows=21 loops=1)
-> Table scan on <temporary> (actual time=0.001..0.006 rows=21 loops=1)
-> Temporary table with deduplication (actual time=0.415..0.428 rows=21 loops=1)
-> Index lookup on variable_info using idx_variablename_ipaddr_port (variable_name='hostname') (cost=25.60 rows=31) (actual time=0.040..0.326 rows=31 loops=1)
-> Index lookup on variable_info using idx_variablename_ipaddr_port (variable_name='datadir', ipaddr=hostname_list.ipaddr) (cost=1.02 rows=1) (actual time=0.134..0.147 rows=1 loops=21)
-> Single-row index lookup on variable_info using PRIMARY (ipaddr=hostname_list.ipaddr, port=variable_info.`port`, variable_name='version') (cost=0.73 rows=1) (actual time=1.814..1.814 rows=1 loops=31)
-> Single-row index lookup on instance_info using PRIMARY (ipaddr=hostname_list.ipaddr, port=variable_info.`port`) (cost=0.25 rows=1) (actual time=0.115..0.115 rows=1 loops=31)
-> Single-row index lookup on slave_info using PRIMARY (ipaddr=hostname_list.ipaddr, port=variable_info.`port`) (cost=0.25 rows=1) (actual time=0.007..0.007 rows=1 loops=31)
-> Index lookup on is_innodb_metrics using idx_ipaddr_port_name (ipaddr=hostname_list.ipaddr, port=variable_info.`port`) (cost=134.45 rows=533) (actual time=123.641..2346.813 rows=124689 loops=31)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment