Category Archives: MySQL Connect 2013

MySQL Connect 2013: ps_tools

During my talk and hands on labs at MySQL Connect 2013 I used several views, functions, and procedures from ps_helper and ps_tools.

The slides from the two talks can be downloaded from Thanks For Attending MySQL Connect or from the MySQL Connect 2013 Content Catalogue.

You can read more about ps_helper on Mark Leith’s blog and you can download ps_helper from github.

To get ps_tools, download ps_tools.tgz from here. Once unpacked there is a ps_tools_5x.sql for each of the versions supported. The tools presented at MySQL Connect were all based on MySQL 5.6 and 5.7. Note that several of the included tools are not particularly useful on their own but are more meant as utility functions for some of the other tools. The actual implementations are organised so they are in the subdirectory corresponding to the earliest version it applies to. For a few tools, such as ps_reset_setup(), there are multiple versions depending on the MySQL version it applies to.

Several of the tools have a help text at the top of the source file.

The main tools are:

  • Function ps_thread_id(): returns the thread id for a given connection. Specify NULL to get the thread id for the current connection. For example:
  •  View ps_setup_consumers: similar to performance_schema.setup_consumers, but includes a column to display whether the consumer is effectively enabled. See also slide 10 from CON 5282.
  • Function substr_count(): See also A Couple of Substring Functions: substr_count() and substr_by_delim().
  • Function substr_by_delim(): See also A Couple of Substring Functions: substr_count() and substr_by_delim().
  • Function ps_account_enabled(): Check whether a given account is enabled according to performance_schema.setup_actors. For example:
  • View ps_accounts_enabled: Lists each account and whether it is enabled according to performance_schema.setup_actors.
  • Procedure ps_setup_tree_instruments(): Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.
  • Procedure ps_setup_tree_actors_by_host(): Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.
  • Procedure ps_setup_tree_actors_by_user(): Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.
  • Procedure ps_setup_tree_consumers(): Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting and slide 11 from CON 5282 for more details.
  • Procedure ps_dump_thread_stack(): This is the same as in ps_helper with one bug fix. See also slides 30-31 from CON 5282.
  • Procedure ps_enable_all(): Enable all consumers and instruments.
  • Procedure ps_reset_setup(): Reset consumers, instruments, actors, and threads to the defaults (without taking my.cnf into consideration).
  • View is_innodb_lock_waits: show information about waiting locks for InnoDB.
  • View is_engine_data: summary of the amount of data and indexes grouped by storage engine.
  • View ps_memory_by_thread_by_event_raw: the amount of memory used grouped by thread and event without any formatting and ordering.
  • View ps_memory_current_by_thread_by_event: The same as above but formatted output and ordered by current usage.
  • View ps_memory_high_by_thread_by_event: The same as above but formatted output and ordered by the high watermark.
  • Procedure schema_unused_routines(): Lists unused stored procedures and functions.

Thanks For Attending MySQL Connect

MySQL Connect 2013 was held this past Saturday through Monday, and I would like to extend a big thank you to everyone who attended my sessions, I talked to or otherwise took part in the conference.

I had two sessions as well as participated in a Birds of the Feather session with the Community and Support teams. The slides have been uploaded the the Content Catalog but they are not available for download from there yet. Until then you can download them from the the links below:

The ps_helper views, procedures, and functions used in the above presentations can be downloaded from https://github.com/MarkLeith/dbahelper:

git clone https://github.com/MarkLeith/dbahelper

For ps_tools, I will follow up on this site with more information although some of the tools can be found in Easier Overview of Current Performance Schema Setting. Note: the presentation uses the naming convention that Performance Schema tools are prefixed ps_ – that was not the case in the above blog, so e.g. ps_setup_tree_consumers is in the blog call setup_tree_consumers.

And again: thanks for attending MySQL Connect 2013 – hope to see you again next year.

Speaking at MySQL Connect 2013

183037-mysql-tk-imspeaking-250x250-1951648It is hard to believe it is already closing in on a year since the last MySQL Connect, but it is true, it is time to start preparing again.

This year MySQL Connect will take place in the weekend of 21-23 September with the Monday being dedicated to tutorials. As last year MySQL Connect is part of Oracle OpenWorld and is hosted at Hilton, San Francisco Union Square.

I am fortunate enough this year to be taking part in three sessions:

  • Meet the MySQL Community and Support Teams [BOF2480]
    Join MySQL community and support team members in this BOF to ask all your questions as well as provide feedback, suggestions, and ideas related to the MySQL community, handling of bugs, and overall technical support at Oracle.

    This Birds-Of-a-Feather session will take place Saturday at 4:30 PM.

  • Making the Performance Schema Easier to Use [CON5282]
    The Performance Schema can seem daunting at first, with the vast amount of data available. This session focuses on tools, such as ps_helper views and stored programs, that make it easier to get started with the Performance Schema and perform common tasks. The presentation includes examples of how ps_helper can be used to simplify checking the current configuration, changing the configuration, and investigating the usage of the server.

    This talk will take place Sunday at 11:00 AM.

  • Improving Performance with MySQL Performance Schema [HOL9733]
    The Performance Schema feature of MySQL is MySQL’s gateway for looking into the engine room. It enables you not only to discover what is going on in the internals but also to get detailed information about the current connections and some historical data. MySQL 5.6, which is now GA, introduces significant enhancements to Performance Schema. This hands-on lab gives you the opportunity to use Performance Schema, going through the steps from initial configuration and high-level summaries to detailed wait events.

    This Hands-On-Labs session will take place Sunday at 2:00 PM.

However those three sessions is just a small part of the agenda for MySQL Connect. There are a total of more than 80 sessions from both Oracle developers, engineer, and staff as well as users, third party developers, and more. See also Bertrand Matthelié’s Top 10 Reasons to Attend MySQL Connect.

See you all there.

183037-mysql-tk-joinme-250x250-1951669