1.select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
-> can calculate the trx which exceed the indicated time(60s is from the example)
2. SELECT MODIFIED_DATABASE_PAGES, DATABASE_PAGES, FREE_BUFFERS, ROUND(100*MODIFIED_DATABASE_PAGES/(DATABASE_PAGES+FREE_BUFFERS), 2) AS 'Dirty Pct' FROM information_schema.INNODB_BUFFER_POOL_STATS;
-> You can check the modified pages, total pages, free buffer size and diry page pct in innodb buffer pool, which can give u some advise to innodb_max_dirty_pages_pct & innodb_max_dirty_pages_pct_lwm
3. select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;
-> If you want to check the if the max time of writing binlog or redo exceed 200ms, you can use this one. And you can customize your threshold. Generally speaking one IO cost 200ms should be filtered and pay attention to it.
(To Be Continued)