Talk shop and learn about MySQL and occasionally some other stores!

Topics and RecordingsLearn From Previous Discussions!

How to JoinSome simple setup help!

Follow Us On


#DBHangOps 04/02/15 -- Getting Started with PERFORMANCE_SCHEMA and more!

Hello everybody!

Join in #DBHangOps this Thursday, April, 02, 2015 at 11:00am pacific (19:00 GMT), to participate in the discussion about:

  • Getting started with PERFORMANCE_SCHEMA
    • What are issues P_S can help you find and fix faster?
    • How does P_S start to look under load?
  • What's the hardest issue you've had to debug?
    • How did it initially manifest?
    • How did you discover it?
    • How did you resolve it?
    • Any bug reports come out of it?
  • Percona Live 2015 is coming up! Are you presenting? What are you excited for?

You can check out the event page at https://plus.google.com/events/chc1fg244bbts8jp7r84eg1i3hg on Thursday to participate.

As always, you can still watch the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!

See all of you on Thursday!

You can catch a livestream at:

Show Notes

Getting started with PERFORMANCE_SCHEMA

  • PERFORMANCE_SCHEMA is a new database that was introduced in MySQL 5.5
    • added to allow developers to add instrumentation points in the MySQL server
    • most of the tables are a way to expose the instrumentation points
    • Any tables prefixed with setup_ are used to enable/disable different instruments
    • setup_instruments will determine what's enabled/disabled for monitoring in the server
    • In MySQL 5.5, a lot of global mutex tracking instruments were on by default which added a pretty steep performance impact
    • In MySQL 5.6+, a lot of these instruments are disabled by default to allow lighter weight impact from P_S
    • Some instrument categories:
    • Global locks
    • Stages -- the state of threads that you may see in SHOW PROCESSLIST -- e.g. preparing tmp table, etc.
    • Statement tracking -- you can watch for specific types of statements -- e.g. SHOW... statements, DELETE... statements, etc.
      • statement/sql/... instruments actually track the text of queries while statement/com/... tracks counts/latencies of those statement types
      • statement/sql/... instruments actually track the text of queries while statement/com/... tracks counts/latencies of those statement types
    • Table IO and locking in the storage engine
    • In MySQL 5.6 there's events_waits_ and events_statements_ tables. For most of these prefixes, there's a summary table and a per-event/per-statement table
    • For the per-statement tables, you'll see timing information (start/end of a statemtn in time), a digest, the full SQLTEXT, which schema it was on, any errors, row changes counts, etc.
      • Also a NO_INDEX_USED and NO_GOOD_INDEX_USED field which indicates if a index wasn't used to satisfy the query vs. not having an index available to satisfy some joins in a query
    • You can execute queries against events_statements_current to see performance inforamtion about queries that are actively running on a server (specifically, last statement from active threads)
    • If you want to look at digested/aggregated information about queries on you server, you can look at events_statements_summary_by_digest
    • The structure is similar to events_statements_current, but the records are rolled up by the DIGEST_TEXT field
    • SELECT * FROM events_statements_summary_by_digest ORDER BY sum_timer_wait LIMIT 5\G could be used to find statements that are spending a lot of wait time for example.
    • table_io_waits_summary_by_table will provide table-level information about IO waits
    • SELECT object_schema, object_name, count_star, sum_time_wait FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC LIMIT 10 would show an ordering of top table IO usage.
      • You can find your heaviest used tables and track down why they're so heavily used by querying the other fields in the table_io_waits_summary_by_table
    • file_summary_by_instance -- you could see which physical datafiles are observing the most latency
    • You can typically issue a TRUNCATE statement against any P_S tables in order to "reset" the data in them.
    • These statements will not replicate to downstream replicas, so you'll need to do this on all machines in a replication topology
    • All of the P_S data is collected in memory and doesn't required a file mutex lock (like the slow query log does).
  • SYS is another schema that is provided to help simplify interactions with PERFORMANCE_SCHEMA
    • SYS has one table (sys_config), the rest of the objects in SYS are views
    • Configuration for monitoring is done via the SYS.sys_config table
    • Be aware that changes to this table will replicate to your downstream replicas
    • views in the SYS schema will make things more human readable by leveraging data in PERFORMANCE_SCHEMA
    • e.g SELECT * FROM sys.statements_with_temp_tables LIMIT 5\G would give you a list of statements on the server using temporary tables, including statistics about how many temporary tables happen, how often they happen, and so on.
    • schema_table_statistics collects P_S data about table usage and I/O and presents it in an easily consumable view. You can gauge how much time is spent inserting/updating/reading from a table.

Some new PERFORMANCE_SCHEMA tables in MySQL 5.7+

  • events_transactions_current -- information about in flight transactions, their isolation levels, and so forth
    • You can link statements to records in this table using the NESTING_EVENT_* fields
  • global_status and global_variables -- this is data that
  • status_by_ tables show status counters by a given aggregate
    • e.g. status_by_user allows you to see all these counters on a per-user basis
    • e.g. status_by_thread allows you to see all these counters on a per-connected thread
  • metadata_locks -- displays all current metadata locks on the server at a given moment
  • memory_summary_ tables report information about types of memory interactions, allocations, and high/low watermarks
    • The SYS.memory_global_by_current_bytes table is an excellent view for looking at this
    • these memory instrument tables wrap mallocs in the server to increment counters so that querying this data is pretty cheap
    • Seriously...this is pretty cheap and awesome to see!
  • replication_ tables collect output from something like SHOW SLAVE STATUS and stores it in a normalized set of tables
    • with the addition of multi-threaded slaves and programmatic interactions with them, the SHOW SLAVE STATUS command couldn't serve as well
    • E.g. replication_applier_status_by_worker displays information about each worker thread for replication
    • E.g. replication_connection_status provides information about each IO thread connected for replication (to support multi-master replication)
    • Since all replication threads are simply threads on the server, you can query other P_S tables to see information about why those threads may be slowing down or having trouble
    • There's plans to add a busy_timer type field in the replication_applier_status_by_worker so it'll be easier to get information about how busy replication threads are.

Awesome links

  • pstop - https://github.com/sjmudd/pstop
    • Top-like tool that queries PERFORMANCE_SCHEMA and shows a live status of what's happening in a server
    • Seriously...this is an amazing tool!