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