MySQL sys 1.3.0 released – The Facepalm Release

With a last minute change to the host_summary_by_stages view for the 1.2.0 release of the sys schema, I managed to break the views without noticing it.

So, in the shortest release cycle for the sys schema ever (less than 24 hours), 1.3.0 has now been released.

This comes with a bonus though, there was also a new view that was contributed by Jesper Wisborg Krogh waiting to be merged as well, so I’ve added that to the 1.3.0 version (hence the 1.3.0 instead of 1.2.1).

This is the innodb_lock_waits view, which shows all sessions that are waiting for a lock within InnoDB, as well as the details of who is blocking them, here’s an example output:

mysql> SELECT * FROM innodb_lock_waits\G
*************************** 1. row ***************************
     waiting_trx_id: 805505
     waiting_thread: 78
      waiting_query: UPDATE t1 SET val = 'c2' WHERE id = 3
    waiting_lock_id: 805505:132:3:28
  waiting_lock_mode: X
  waiting_lock_type: RECORD
 waiting_lock_table: `db1`.`t1`
 waiting_lock_index: PRIMARY
    blocking_trx_id: 805504
    blocking_thread: 77
     blocking_query: UPDATE t1 SET val = CONCAT('c1', SLEEP(10)) WHERE id = 3
   blocking_lock_id: 805504:132:3:28
 blocking_lock_mode: X
 blocking_lock_type: RECORD
blocking_lock_table: `db1`.`t1`
blocking_lock_index: PRIMARY

Sorry for the breakage, but enjoy the new view!

Facepalm

MySQL sys version 1.2.0 released

MySQL sys version 1.2.0 has just been released.

All views now work with MySQL 5.7.5, and the ONLY_FULL_GROUP_BY changes.

There is also a new script available (generate_sql_file.sh) that will allow RDS users to easily generate a single SQL file, using a specified user, that can be loaded in to an RDS instance. See the README for details on how to use that.

Here’s a full summary of the other changes:

Backwards Incompatible Changes

  • The host_summary_by_stages and user_summary_by_stages wait_sum and wait_avg columns were renamed to total_latency and avg_latency respectively, for consistency.
  • The host_summary_by_file_io_type and user_summary_by_file_io_type latency column was renamed to total_latency, for consistency.

Improvements

  • Made the truncation length for the format_statement view configurable. This includes adding a new persistent sys_config table to store the new variable – statement_truncate_len – see the README for usage
  • Added total_latency to the schema_tables_with_full_table_scans view, and added an x$ counterpart
  • Added innodb_buffer_free to the schema_table_statistics_with_buffer view, to summarize how much free space is allocated per table in the buffer pool
  • The schema_unused_indexes view now ignores indexes named PRIMARY (primary keys)
  • Added rows_affected and rows_affected_avg stats to the statement_analysis views
  • The statements_with_full_table_scans view now ignores any SQL that starts with SHOW
  • Added a script, generate_sql_file.sh, that can be used to generate a single SQL file, also allowing substitution of the MySQL user to use, and/or whether the SET sql_log_bin … statements should be omitted. This is useful for those using RDS, where the root@localhost user is not accessible, and sql_log_bin is disabled (Issue #5)
  • Added a set of memory_by_thread_by_current_bytes views, that summarize memory usage per thread with MySQL 5.7’s memory instrumentation
  • Improved each of the host specific views to return aggregate values for background threads, instead of ignoring them, in the same way as the user summary views

Bug Fixes

  • Added the missing memory_by_host view for MySQL 5.7
  • Added missing space for hour notation within the format_time function
  • Fixed views affected by MySQL 5.7 ONLY_FULL_GROUP_BY and functional dependency changes