def _get_blocked_hosts(session, advices, details): title = "Blocked Hosts" stmt = """select host, ip, COUNT_HOST_BLOCKED_ERRORS, @@max_connect_errors from performance_schema.host_cache where COUNT_HOST_BLOCKED_ERRORS>0; """ result = session.run_sql(stmt) if result.has_data(): rows = result.fetch_all() if len(rows) > 0: output = util.output(title, "") for row in rows: output += util.output("{} ({})".format(row[0], row[1]), row[2], 1) if advices: output += util.print_red( "You have blocked host(s), please use mysqladmin flush-hosti and maybe increase max_connect_errors ({})." .format(row[3])) if details: stmt = """select HOST, IP, COUNT_HOST_BLOCKED_ERRORS, FIRST_SEEN, LAST_ERROR_SEEN from performance_schema.host_cache where COUNT_HOST_BLOCKED_ERRORS>0 """ stmt = """SELECT HOST, IP, COUNT_HOST_BLOCKED_ERRORS, concat("'", FIRST_SEEN, "'") FIRST_SEEN, concat("'", LAST_SEEN, "'") LAST_SEEN FROM performance_schema.host_cache where COUNT_HOST_BLOCKED_ERRORS>0""" output2, nbrows = util.run_and_print("Blocked Hosts", stmt, session) output += output2 else: output = util.output(title, "none") return output
def get_tables_without_pk(session, advices=False, details=False): tbl_no_pk = 0 title = "Tables without PK" stmt = """SELECT concat(tables.table_schema, '/' , tables.table_name) as `Table Name`, tables.engine as `Engine`, tables.table_rows as `Rows` FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name WHERE puks.table_name is null AND tables.table_type = 'BASE TABLE' AND Engine='InnoDB'""" result = session.run_sql(stmt) output = util.output(title, "") for row in result.fetch_all(): tbl_no_pk += 1 output = util.output(title, tbl_no_pk) if advices: if tbl_no_pk > 0: output += util.print_red( "It's not recommended to have tables without Primary Key") if details and tbl_no_pk > 0: output2, nbrows = util.run_and_print("Details", stmt, session) output += output2 return output
def get_engines(session, advices=False, details=False): got_inno = False other = False title = "Engines Used" stmt = """select count(*) as '# TABLES', CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') INDEXES, CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') 'TOTAL SIZE', engine as ENGINE from information_schema.TABLES where TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') and data_length group by engine;""" result = session.run_sql(stmt) output = util.output(title, "") for row in result.fetch_all(): output += util.output(row[4], "{} table(s) ({})".format(row[0], row[3]), 1) if row[4] == "InnoDB": got_inno = True else: other = True if advices: if not got_inno and other: output += util.print_red("It's recommended to only use InnoDB") if details: output2, nbrows = util.run_and_print("Details".format(title), stmt, session) output += output2 return output
def get_routines(session, advices=False, details=False): title = "User Defined Routines" stmt = "select routine_schema, count(*) `amount` from information_schema.routines where definer not in ('mysql.sys@localhost') group by routine_schema" result = session.run_sql(stmt) output = util.output(title, "") for row in result.fetch_all(): output += util.output(row[0], row[1], 1) if advices and row[0] in "mysql": output += util.print_red( "Custom routines in {} schema are not supported in MDS".format( row[0])) if details: stmt = """select routine_schema, routine_name, definer from information_schema.routines where routine_schema = '{}' and definer not in ('mysql.sys@localhost')""".format( row[0]) output2, nbrows = util.run_and_print( "Routines in {}".format(row[0]), stmt, session) output += output2 return output
def get_largest_innodb_tables(session, branch, details=False, limit=10): if not details: return "" title = "Top {} largest InnoDB Tables".format(str(limit)) stmt = """SELECT NAME as `Table Name`, TABLE_ROWS as `Rows`, format_bytes(data_length) `Data Size`, format_bytes(PAGE_SIZE) `Page Size`, SPACE_TYPE `Space Type`, format_bytes(index_length) `Index Size`, format_bytes(data_length+index_length) `Total Size`, format_bytes(data_free) `Data Free`, format_bytes(FILE_SIZE) `File Size`, format_bytes((FILE_SIZE/10 - (data_length/10 + index_length/10))*10) `Wasted Size` FROM information_schema.TABLES as t JOIN information_schema.INNODB_TABLESPACES as it ON it.name = concat(table_schema,"/",table_name) ORDER BY (data_length + index_length) desc limit %s""" % str( limit) if branch == "56": stmt = """SELECT CONCAT(table_schema, '.', table_name) as `Table Name`, CONCAT(ROUND(table_rows / 1000000, 2), 'M') as `Rows`, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') `Data Size`, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') `Index Size`, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') `Total Size` FROM information_schema.TABLES as t ORDER BY (data_length + index_length) desc limit %s""" % str( limit) else: if branch == "57": stmt = """SELECT TABLE_NAME as `Table Name`, TABLE_ROWS as `Rows`, sys.format_bytes(data_length) `Data Size`, sys.format_bytes(index_length) `Index Size`, sys.format_bytes(data_length+index_length) `Total Size`, sys.format_bytes(data_free) `Data Free` FROM information_schema.TABLES as t ORDER BY (data_length + index_length) desc limit %s""" % str( limit) output, nbrows = util.run_and_print(title, stmt, session) return output
def get_configured_variables(session, details=False): title = "Global Variables Configured" stmt = """SELECT count(*), VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE != 'COMPILED' GROUP BY VARIABLE_SOURCE""" result = session.run_sql(stmt) output = util.output(title, "") for row in result.fetch_all(): output += util.output(row[1], row[0], 1) if details: stmt = """SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE != 'COMPILED'""" output2, nbrows = util.run_and_print("Details".format(title), stmt, session) output += output2 return output