def get_queries_95_perc(limit=1, select=False, schema=None, session=None): import mysqlsh shell = mysqlsh.globals.shell if session is None: session = shell.get_session() if session is None: print("No session specified. Either pass a session object to this " "function or connect the shell to a database") return filter = "" if select: filter += "AND query_sample_text like '%select%'" if schema is not None: filter += "AND schema_name = '%s'" % schema stmt = """SELECT schema_name, sys.format_time(total_latency) tot_lat, exec_count, sys.format_time(total_latency/exec_count) latency_per_call, t2.first_seen, t2.last_seen, query_sample_text FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1 JOIN performance_schema.events_statements_summary_by_digest AS t2 ON t2.digest=t1.digest WHERE schema_name NOT in ('performance_schema', 'sys') %s ORDER BY (total_latency/exec_count) desc LIMIT %d""" % (filter, limit) run_and_show(stmt, 'vertical') if limit == 1: result = session.run_sql(stmt) row = result.fetch_one() if row: original_query = row[6] _get_full_details(session, original_query, row[0])
def get_amount_ddl(session=None): stmt = """SELECT event_name, count_star, sum_errors FROM performance_schema.events_statements_summary_global_by_event_name WHERE event_name REGEXP '.*sql/(create|drop|alter).*' AND event_name NOT REGEXP '.*user' AND count_star > 0;""" run_and_show(stmt, "table", session)
def get_noninnodb_tables(session=None): stmt = """SELECT table_schema, table_name, engine, table_rows, sys.format_bytes(index_length+data_length) AS 'SIZE' FROM information_schema.tables WHERE engine != 'innodb' AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');""" run_and_show(stmt, "table", session)
def get_cascading_fk(session=None): stmt = """SELECT CONCAT(t1.table_name, '.', column_name) AS 'foreign key', CONCAT(t1.referenced_table_name, '.', referenced_column_name) AS 'references', t1.constraint_name AS 'constraint name', UPDATE_RULE, DELETE_RULE FROM information_schema.key_column_usage as t1 JOIN information_schema.REFERENTIAL_CONSTRAINTS as t2 WHERE t2.CONSTRAINT_NAME = t1.constraint_name AND t1.referenced_table_name IS NOT NULL AND (DELETE_RULE = "CASCADE" OR UPDATE_RULE = "CASCADE");""" run_and_show(stmt, "table", session)
def _get_full_details(session, original_query, schema): import mysqlsh shell = mysqlsh.globals.shell old_schema = None if session.get_current_schema() is None: old_schema = None session.set_current_schema(schema) elif session.get_current_schema().get_name() != schema: old_schema = session.get_current_schema().get_name() session.set_current_schema(schema) answer = shell.prompt('Do you want to have EXPLAIN output? (y/N) ', {'defaultValue': 'n'}) if answer.lower() == 'y': stmt = """EXPLAIN %s""" % original_query run_and_show(stmt, 'vertical') answer = shell.prompt( 'Do you want to have EXPLAIN in JSON format output? (y/N) ', {'defaultValue': 'n'}) if answer.lower() == 'y': stmt = """EXPLAIN FORMAT=json %s""" % original_query run_and_show(stmt, 'vertical') answer = shell.prompt( 'Do you want to have EXPLAIN in TREE format output? (y/N) ', {'defaultValue': 'n'}) if answer.lower() == 'y': stmt = """EXPLAIN format=tree %s""" % original_query run_and_show(stmt, 'vertical') answer = shell.prompt('Do you want to have EXPLAIN ANALYZE output? (y/N) ', {'defaultValue': 'n'}) if answer.lower() == 'y': stmt = """EXPLAIN ANALYZE %s""" % original_query run_and_show(stmt, 'vertical') if old_schema: session.set_current_schema(old_schema) return
def get_queries_most_rows_affected(limit=1, schema=None, session=None): filter = "" if schema is not None: filter += "where db = '%s'" % schema stmt = """SELECT db, rows_affected, rows_affected_avg, query_sample_text FROM sys.statement_analysis as sa JOIN performance_schema.events_statements_summary_by_digest as ed ON ed.digest=sa.digest %s ORDER BY rows_affected_avg DESC, rows_affected DESC LIMIT %s """ % (filter, limit) run_and_show(stmt, 'vertical')
def get_innodb_with_nopk(session=None): stmt = """SELECT tables.table_schema , tables.table_name , tables.engine, table_rows, sys.format_bytes(index_length+data_length) AS 'SIZE' 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";""" run_and_show(stmt, "table", session)
def get_queries_updating_same_pk(limit=1, schema=None, session=None): filter = "" if schema is not None: filter += "where (current_schema = '%s' or object_schema = '%s')" % ( schema, schema) stmt = """SELECT current_schema, rows_examined, sql_text, ( SELECT count(*) FROM performance_schema.events_statements_history_long AS t2 WHERE t2.digest_text=t1.digest_text ) AS `count` FROM performance_schema.events_statements_history_long AS t1 WHERE rows_affected > 1 %s ORDER BY timer_wait DESC LIMIT %s """ % (filter, limit) run_and_show(stmt, 'vertical')
def get_autoinc_fill(percentage=50, schema=None, session=None): where_filter = "" having_filter = "" import mysqlsh shell = mysqlsh.globals.shell if session is None: session = shell.get_session() if session is None: print("No session specified. Either pass a session object to this " "function or connect the shell to a database") return stmt = "select @@information_schema_stats_expiry;" result = session.run_sql(stmt) stats = result.fetch_all() if len(stats) > 0: for stat in stats: if int(stat[0]) > 0: print( "Warning: information_schema_stats_expiry is set to {0}.". format(*stat)) if shell.options.interactive: answer = shell.prompt( """Do you want to change it ? (y/N) """, {'defaultValue': 'n'}) if answer.lower() == 'y': stmt = """SET information_schema_stats_expiry=0""" result = session.run_sql(stmt) else: print( "Changing information_schema_stats_expiry to 0 for this session only" ) stmt = """SET information_schema_stats_expiry=0""" result = session.run_sql(stmt) if percentage > 0: having_filter = "HAVING CAST(AUTO_INCREMENT_RATIO AS SIGNED INTEGER) >= {}".format( percentage) if schema: where_filter = "{} AND TABLE_SCHEMA='{}'".format(where_filter, schema) stmt = """SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IF( LOCATE('unsigned', COLUMN_TYPE) > 0, 1, 0 ) AS IS_UNSIGNED, ( CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) AS MAX_VALUE, AUTO_INCREMENT, CONCAT(ROUND( AUTO_INCREMENT / ( CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) )*100), '%') AS AUTO_INCREMENT_RATIO FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME) WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND EXTRA='auto_increment' {} {} ORDER BY CAST(AUTO_INCREMENT_RATIO AS SIGNED INTEGER) """.format(where_filter, having_filter) run_and_show(stmt) return