plugins/wb.admin/frontend/wb_admin_perfschema_reports.py (439 lines of code) (raw):
# Copyright (c) 2013, 2020, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is designed to work with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms, as
# designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have either included with
# the program or referenced in the documentation.
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
# the GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
import mforms
import grt
import json
import os
import sys
from workbench.log import log_error, log_debug
from wb_admin_perfschema import WbAdminPSBaseTab
from threading import Thread
from wb_common import to_unicode
unit_formatters = {
"us" : lambda x: "%.2f" % (x / 1000000.0),
"ms" : lambda x: "%.2f" % (x / 1000000000.0),
"s" : lambda x: "%.2f" % (x / 1000000000000.0),
"h:m:s" : lambda x: "%i:%02i:%.02f" % ((int)(x / (60*60*1000000000000.0)), (int)(x / (60*1000000000000.0)) % 60, (x / 1000000000000.0)%60),
"Bytes" : lambda x: "%.0f" % x,
"KB": lambda x: "%.2f" % (x / 1000.0),
"MB": lambda x: "%.2f" % (x / 1000000.0),
"GB": lambda x: "%.2f" % (x / 1000000000.0),
}
time_units = ["us", "ms", "s", "h:m:s"]
byte_units = ["Bytes", "KB", "MB", "GB"]
known_column_types = {
"Integer" : (mforms.IntegerColumnType, None),
"LongInteger" : (mforms.LongIntegerColumnType, None),
"Float" : (mforms.FloatColumnType, None),
"Time" : (mforms.NumberWithUnitColumnType, "us"),
"Bytes" : (mforms.NumberWithUnitColumnType, "Bytes"),
"String" : (mforms.StringColumnType, None),
"StringLT" : (mforms.StringLTColumnType, None),
"NumberWithUnit" : (mforms.NumberWithUnitColumnType, None)
}
class PSHelperViewTab(mforms.Box):
category = None
caption = None
def __init__(self, owner):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self._owner = owner
self.set_spacing(8)
if sys.platform == 'win32':
self.set_back_color("#FFFFFF")
self._refresh = None
self._busy = False
self._tree = None
self._title = None
self._check_timeout = None
self._wait_table = None
def __del__(self):
if self._check_timeout:
mforms.Utilities.cancel_timeout(self._check_timeout)
self._check_timeout = None
def init_ui(self):
if self._title:
return
if self._wait_table:
self._pbar.stop()
self._pbar = None
self.remove(self._wait_table)
self._wait_table = None
self._title = mforms.newLabel(to_unicode(self.caption))
self._title.set_style(mforms.BigBoldStyle)
self.add(self._title, False, True)
self._column_file = None
if self.description:
self._description = mforms.newLabel(to_unicode(self.description))
self.add(self._description, False, True)
self._tree = mforms.newTreeView(mforms.TreeFlatList|mforms.TreeAltRowColors|mforms.TreeShowColumnLines)
self._tree.set_selection_mode(mforms.TreeSelectMultiple)
self._tree.add_column_resized_callback(self._tree_column_resized)
c = 0
self._hmenu = mforms.newContextMenu()
self._hmenu.add_will_show_callback(self._header_menu_will_show)
self._tree.set_header_menu(self._hmenu)
self._column_types = []
self._column_units = []
self._column_names = []
self._column_titles = []
for i, (column, cname, ctype, length) in enumerate(self.get_view_columns()):
unit = None
if type(ctype) is tuple:
ctype, unit = ctype
unit = grt.root.wb.state.get("wb.admin.psreport:unit:%s:%i" % (self.view, i), unit)
width = min(max(length, 40), 300)
width = grt.root.wb.state.get("wb.admin.psreport:width:%s:%i" % (self.view, i), width)
label = to_unicode(self.column_label(column))
self._column_units.append(unit)
self._column_names.append(cname)
self._column_titles.append(label)
self._column_types.append(ctype)
if unit:
self._tree.add_column(ctype, label + " (%s)" % unit, width, False)
else:
self._tree.add_column(ctype, label, width, False)
c += 1
self._tree.end_columns()
self._tree.set_allow_sorting(True)
self.add(self._tree, True, True)
bbox = mforms.newBox(True)
bbox.set_spacing(12)
btn = mforms.newButton()
btn.set_text("Export...")
btn.add_clicked_callback(self.do_export)
bbox.add(btn, False, True)
btn = mforms.newButton()
btn.set_text("Copy Selected")
btn.add_clicked_callback(self.do_copy)
bbox.add(btn, False, True)
btn = mforms.newButton()
btn.set_text("Copy Query")
btn.add_clicked_callback(self.do_copy_query)
bbox.add(btn, False, True)
self._refresh = mforms.newButton()
self._refresh.set_text("Refresh")
self._refresh.add_clicked_callback(self.do_refresh)
bbox.add_end(self._refresh, False, True)
self.add(bbox, False, True)
def get_query(self):
return "SELECT * FROM `%s`.`%s`" % (self._owner.sys, self.view)
def execute(self):
return self._owner.ctrl_be.exec_query(self.get_query())
def run_query(self):
try:
self.result = self.execute()
error = None
except Exception as e:
error = str(e)
log_error("Error executing '%s': %s\n" % (self.get_query(), error))
def check_if_finished(self):
self._check_timeout = None
if self.result is None:
return True
self.run_query_finished(None)
return False
def do_refresh(self):
self._refresh.set_text("Refresh")
self._tree.show(True)
self.refresh()
def _get_node_values(self, node):
row = []
for col in range(len(self._column_types)):
if self._column_types[col] in [mforms.IntegerColumnType, mforms.LongIntegerColumnType]:
row.append(str(node.get_long(col)))
elif self._column_types[col] in [mforms.FloatColumnType]:
row.append(str(node.get_float(col)))
else:
row.append(node.get_string(col))
return row
def _fmt_node(self, node):
return ", ".join(self._get_node_values(node))
def do_export(self):
chooser = mforms.FileChooser(mforms.SaveFile)
chooser.set_title("Export Report")
chooser.add_selector_option("format", "Format:", "CSV|csv")
if chooser.run_modal():
save_path = "%s.csv" % chooser.get_path() if not chooser.get_path().endswith(".csv") else chooser.get_path()
with open(save_path, 'w') as csvfile:
try:
import csv
output = csv.writer(csvfile, quoting = csv.QUOTE_MINIMAL)
output.writerow([self.caption])
output.writerow(self._column_titles)
root = self._tree.root_node()
for r in range(root.count()):
node = root.get_child(r)
output.writerow(self._get_node_values(node))
except Exception as e:
log_error("Error exporting PS report: %s\n" % e)
mforms.Utilities.show_error("Export Report", "Error exporting PS report.\n%s" % e, "OK", "", "")
def do_copy_query(self):
mforms.Utilities.set_clipboard_text(self.get_query())
def do_copy(self):
text = [", ".join(self._column_titles)]
for node in self._tree.get_selection():
text.append(self._fmt_node(node))
mforms.Utilities.set_clipboard_text("\n".join(text))
def refresh(self):
if not self._tree:
self._pbar = mforms.newProgressBar()
self._pbar.set_indeterminate(True)
self._pbar.start()
self._pbar.set_size(400, -1)
self._wait_table = mforms.newTable()
self._wait_table.set_row_spacing(8)
self._wait_table.set_row_count(2)
self._wait_table.set_column_count(1)
self._wait_table.set_padding(-1)
self._wait_table.add(mforms.newLabel("Querying performance schema %s..." % self.caption.encode("utf8")), 0, 1, 0, 1, mforms.HFillFlag)
self._wait_table.add(self._pbar, 0, 1, 1, 2, mforms.HFillFlag)
self.add(self._wait_table, True, True)
if not self._busy:
self._busy = True
if self._refresh:
self._refresh.set_enabled(False)
self.result = None
self._thr = Thread(target=self.run_query)
self._check_timeout = mforms.Utilities.add_timeout(1.0, self.check_if_finished)
self._thr.start()
def run_query_finished(self, error):
result = self.result
self._thr.join()
self._thr = None
self._busy = False
if self._refresh:
self._refresh.set_enabled(True)
if error:
if self._wait_table:
self._pbar.stop()
self._pbar = None
self.remove(self._wait_table)
self._wait_table = None
mforms.Utilities.show_error("Error Executing Report Query", error, "OK", "", "")
return
self.init_ui()
self._tree.clear()
if result is not None:
while result.nextRow():
node = self._tree.add_node()
for i, cname in enumerate(self._column_names):
try:
if self._column_types[i] == mforms.IntegerColumnType:
s = result.intByName(self._column_names[i])
node.set_long(i, s or 0)
elif self._column_types[i] == mforms.LongIntegerColumnType:
s = result.stringByName(self._column_names[i])
node.set_long(i, int(s) if s else 0)
elif self._column_types[i] == mforms.FloatColumnType:
unit = self._column_units[i]
node.set_float(i, result.floatByName(self._column_names[i]))
elif self._column_types[i] == mforms.NumberWithUnitColumnType:
unit = self._column_units[i]
if unit and unit_formatters[unit]:
formatter = unit_formatters[unit]
node.set_string(i, formatter(float(result.stringByName(self._column_names[i]))))
else:
s = result.stringByName(self._column_names[i])
if i == self._column_file and self._owner.instance_info.datadir:
s = s.replace(self._owner.instance_info.datadir, "<datadir>")
node.set_string(i, s or "")
else:
s = result.stringByName(self._column_names[i])
if i == self._column_file and self._owner.instance_info.datadir:
s = s.replace(self._owner.instance_info.datadir, "<datadir>")
node.set_string(i, s or "")
except Exception as e:
import traceback
traceback.print_exc()
log_error("Error handling column %i (%s) of report for %s: %s\n" % (i, cname, self.view, e))
def get_view_columns(self):
result = self._owner.ctrl_be.exec_query("DESCRIBE `%s`.%s" % (self._owner.sys, self.view))
columns = []
if result is not None:
while result.nextRow():
dtype = result.stringByName("Type")
name = result.stringByName("Field")
if name.endswith("atency") or name.endswith("ead") or name.endswith("ritten"):
ctype = mforms.NumberWithUnitColumnType
length = 80
elif dtype.lower().startswith("char") and "(" in dtype:
try:
length = int(dtype[dtype.find("(")+1:-1]) * 10
except Exception as e:
log_error("Error parsing datatype %s from PS view %s: %s\n" % (dtype, self.view, e))
length = 120
ctype = mforms.StringColumnType
elif dtype.lower().startswith("varchar") and "(" in dtype:
try:
length = min(int(dtype[dtype.find("(")+1:-1]) * 10, 150)
except Exception as e:
log_error("Error parsing datatype %s from PS view %s: %s\n" % (dtype, self.view, e))
length = 120
ctype = mforms.StringColumnType
elif dtype.lower().startswith("decimal"):
length = 50
ctype = mforms.IntegerColumnType
elif dtype.lower().startswith("bigint"):
length = 50
ctype = mforms.LongIntegerColumnType
else:
length = 80
ctype = mforms.StringColumnType
columns.append((name, ctype, length))
# do some special treatment for some known columns
if name == "file":
self._column_file = len(columns)-1
return columns
def column_label(self, colname):
return " ".join(s.capitalize() for s in colname.replace("_", " ").split(" "))
def _header_menu_will_show(self, parent):
column = self._tree.get_clicked_header_column()
# This reset should onle be done when the Context Menu will be initially shown.
# On submenus should be skipped or they will be shown on the top left corner of the UI
if parent is None:
self._hmenu.remove_all()
item = self._hmenu.add_item_with_title("Set Display Unit", lambda: None, "Change Unit", "change_unit")
unit = self._column_units[column]
if unit in time_units:
for label in time_units:
i = item.add_item_with_title(label, lambda self=self, column=column, label=label: self._change_unit(column, label), label, label)
if unit == label:
i.set_checked(True)
elif unit in byte_units:
for label in byte_units:
i = item.add_item_with_title(label, lambda self=self, column=column, label=label: self._change_unit(column, label), label, label)
if unit == label:
i.set_checked(True)
else:
item.set_enabled(False)
def _tree_column_resized(self, column):
if column >= 0:
width = self._tree.get_column_width(column)
grt.root.wb.state["wb.admin.psreport:width:%s:%i" % (self.view, column)] = width
def _change_unit(self, column, unit):
self._tree.set_column_title(column, self._column_titles[column] + " (%s)" % unit)
self._column_units[column] = unit
grt.root.wb.state["wb.admin.psreport:unit:%s:%i" % (self.view, column)] = unit
self.refresh()
js_column_types = {
"Integer" : (mforms.IntegerColumnType, None),
"LongInteger" : (mforms.LongIntegerColumnType, None),
"LongInteger:ms" : (mforms.FloatColumnType, lambda x: x / 1000000.0),
"LongInteger:s" : (mforms.FloatColumnType, lambda x: x / 1000000000.0),
"Float" : (mforms.FloatColumnType, None),
"Float:ms" : (mforms.FloatColumnType, lambda x: x / 1000000.0),
"Float:s" : (mforms.FloatColumnType, lambda x: x / 1000000000.0),
"String" : (mforms.StringColumnType, None),
"StringLT" : (mforms.StringLTColumnType, None),
}
class JSSourceHelperViewTab(PSHelperViewTab):
query = None
view = None
limit = None
def __init__(self, owner, data):
PSHelperViewTab.__init__(self, owner)
self.category = data["category"]
self.caption = data["caption"]
self.description = data["description"]
self.view = data["view"]
self.query = "select * from sys.`%s`" % self.view
if "limit" in data:
self.query += " limit %s" % data["limit"]
self.columns = []
for label, name, type, width in data["columns"]:
self.columns.append((label, name, known_column_types[type], width))
def column_label(self, label):
return label.encode("utf8")
def get_query(self):
return self.query
def get_view_columns(self):
if self.columns:
return self.columns
else:
if not self.view:
log_error("report '%s' is missing column list\n" % self.caption)
return []
return PSHelperViewTab.get_view_columns(self)
class WbAdminPerformanceSchema(WbAdminPSBaseTab):
@classmethod
def wba_register(cls, admin_context):
admin_context.register_page(cls, "Performance", "Performance Reports", "Performance Reports", False)
@classmethod
def identifier(cls):
return "admin_performance_reports"
def __init__(self, ctrl_be, instance_info, main_view):
WbAdminPSBaseTab.__init__(self, ctrl_be, instance_info, main_view)
self.min_server_version = (5,6,6)
self._selected_report = None
self.set_standard_header("title_performance_reports.png", self._instance_info.name, "Performance Reports")
def create_ui(self):
known_views = []
res = self.ctrl_be.exec_query("show full tables from sys where Table_type='VIEW'")
while res.nextRow():
known_views.append(res.stringByIndex(1))
self.content = mforms.newBox(True)
self.content.set_spacing(12)
self.tree = mforms.newTreeView(mforms.TreeDefault)
self.tree.add_column(mforms.IconStringColumnType, "Report", 250, False)
self.tree.end_columns()
self.tree.set_size(250, -1)
self.tree.add_changed_callback(self._report_selected)
self.content.add(self.tree, False, True)
self.tabview = mforms.newTabView(mforms.TabViewTabless)
self.content.add(self.tabview, True, True)
self.relayout() # force relayout for mac
self.pages = []
try:
report_data = json.load(open(os.path.join(mforms.App.get().get_resource_path("sys"), "sys_reports.js")))
except Exception as e:
log_error("Error loading sys_reports.js: %s\n" % e)
mforms.Utilities.show_error("Error Loading Report Definitions",
"An error occurred loading file %s\n%s" % (os.path.join(mforms.App.get().get_resource_path("sys"), "sys_reports.js"), e),
"OK", "", "")
return
category_labels = report_data["categories"]
reports = report_data["reports"]
prev = None
parent = None
for report in reports:
view = report["view"]
if view not in known_views:
log_debug("View `%s` not in sys, skipping report\n" % view)
continue
else:
known_views.remove(view)
try:
tab = JSSourceHelperViewTab(self, report)
except Exception as e:
log_error("Error processing PS report definition %s:\n%s\n" % (e, report))
continue
setattr(self, "tab_"+tab.caption, tab)
self.pages.append(tab)
self.tabview.add_page(tab, str(tab.caption))
if tab.category != prev:
if parent:
parent.expand()
prev = tab.category
parent = self.tree.add_node()
parent.set_string(0, category_labels.get(tab.category, tab.category))
node = parent.add_child()
node.set_string(0, tab.caption)
node.set_tag(tab.caption)
if parent:
parent.expand()
print("The following views are not handled", set([v for v in known_views if not v[0]=='-' and not v.endswith("_raw")]) - set(["wbversion", "version"]))
return self.content
def refresh(self):
self._report_selected()
def _report_selected(self):
node = self.tree.get_selected_node()
if node:
tag = node.get_tag()
if self._selected_report == tag:
return
self._selected_report = tag
if tag:
i = 0
for tab in self.pages:
if tab.caption == tag:
self.tabview.set_active_tab(i)
tab.refresh()
break
i += 1