ylacancellera

echo "set session sql_log_bin='OFF' > table.sql

sed -n -e '/-- Table structure for table `<Table Name>`/,/UNLOCK TABLES/p' <SQL File> >> table.sql

ylacancellera

pager grep -v "\-\-+\-\-" | grep -v "Type" | sed 's/| InnoDB | //' | sed 's/\s\+|\s\+/\t/g' | sed 's/ |$//' | sed 's/os_waits=/= /' | awk 'BEGIN { FS = "=" } { locks[$1]+=$2 } END { for (l in locks) print l,locks[l] }'

show engine innodb mutex;

ylacancellera

pager grep -v "\-\-+\-\-" | grep -v "Type" | sed 's/| InnoDB | //' | sed 's/\s\+|\s\+/\t/g' | sed 's/ |$//' | sed 's/os_waits=/= /' | awk 'BEGIN { FS = "=" } { splits[$1]+=1 } END { for (l in splits) print l,splits[l] }'

show engine innodb mutex;

ylacancellera

pager sed 's/^| //g' | sed 's/\s\+|\s\+/\t/g' | sed 's/ |$//' | sed 's/^Com_//'

show global status where variable_name in ('com_select', 'com_update', 'com_delete', 'com_insert', 'com_update_multi', 'com_delete_multi', 'com_insert_select', 'com_replace', 'com_replace_select', 'com_call_procedure');

ylacancellera

# easy copy-paste for templates
pager sed 's/^| //g' | sed 's/\s\+|\s\+/\t/g' | sed 's/ |$//'

# db size
SELECT table_schema AS "Database", SUM(ROUND((data_length + index_length)/1024/1024, 2)) AS "Size (MB)", SUM(ROUND(data_free/1024/1024, 2)) as "Free (MB)" FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'sys', 'information_schema', 'performance_schema') GROUP BY table_schema; 


# top 10 table sizes per database. Everything below top 10 will be aggregated as "other"
SELECT table_schema, table_name, sum(size) FROM (SELECT table_schema, CASE WHEN rn <= 10 THEN table_name ELSE 'other' END as table_name, size FROM (SELECT table_schema, table_name, ROUND((data_length + index_length)/1024/1024, 2) as size,          @row_number:=CASE WHEN @schema = table_schema THEN @row_number + 1 ELSE 1 END AS rn,    @schema:=table_schema FROM information_schema.TABLES , (select @row_number:=0, @schema:='') as t   WHERE table_schema not in ('performance_schema', 'information_schema', 'MySQL', 'sys', 'Claranet

ylacancellera

# I_S
select round((select sum(variable_value) from information_schema.global_status where variable_name in ('com_update', 'com_delete', 'com_insert', 'com_update_multi', 'com_delete_multi', 'com_insert_select', 'com_replace', 'com_replace_select')) / (select sum(variable_value) from information_schema.global_status where variable_name in ('com_select', 'com_update', 'com_delete', 'com_insert', 'com_update_multi', 'com_delete_multi', 'com_insert_select', 'com_replace', 'com_replace_select')) * 100, 2);

ylacancellera

# non-expensive top data_free tables within database
pager grep "\(Name:\|Data_free: [0-9]\{9\}\)" | grep -B 1 Data_free 
show table status\G

# top tables with data_free
select table_schema, table_name, ROUND(data_free/1024/1024, 2) from information_schema.tables order by data_free desc limit 30;

ylacancellera

[mysqld]
max_connections = 100

default_authentication_plugin=mysql_native_password

thread_cache_size = 1024
innodb_thread_concurrency = 0

table_open_cache = 10240
table_definition_cache = 10240

ylacancellera

pager md5sum

pager echo >/dev/null

# table to TSV
pager sed 's/^| //g' | sed 's/\s\+|\s\+/\t/g' | sed 's/ |$//'

# visual explain
pager pt-visual-explain

ylacancellera

performance-schema-instrument='memory%=COUNTED'

# Top 15 events currently eating ram
mysql> SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 15;
 
         
# Top 15 events that allocated the most since restart 
mysql> SELECT EVENT_NAME, HIGH_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name ORDER BY HIGH_NUMBER_OF_BYTES_USED DESC LIMIT 15;