MySQL ash_sampler : A simple ASH Builder

So we have seen until now different built-in ways  to easily collect diagnostics information on MySQL server at different level (Instance/Thread/Statement)  :

But what about analyzing database load ? Does MySQL has a build-in way to display Active Session History ?

MySQL already offer an easy way to display currently active session using the “sys”.”session” view for example but sadly there is no build-in active session history for now (Like for example Oracle database or the pgSentinel  extension for PostgreSQL) . For more information on how database load measured in average active sessions (AAS) can be great in analyzing and tuning MySQL performance take look at example using Amazon Performance Insights for MySQL.

So let’s build one ! A simple program called “ash_sampler”  to sample a slightly modified version of “sys”.”session” view which i named “aas_source” .

Capture 22

The collected data is based on the following performance schema tables :

  • events_waits_current
  • events_stages_current
  • events_statements_current
  • events_transactions_current
  • x$memory_by_thread_by_current_bytes
  • session_connect_attrs
  • data_lock_waits

So the amount information collected depend on how we configured our PERFORMANCE_SCHEMA (Instrumentation/Consumer).

The installation process is easy :

ash_sampler github repository.

Capture 01

To simulate an in-memory circular buffer for storing the collected data i created 4 tables using the MEMORY storage engine.

Capture 16

The size of the created tables is set using “max_heap_table_size” parameter when the procedure “ash_sampler” is called and before each table reach it’s maximum size (based on the number of rows inserted  as we are using fixed-length row-storage format) we will switch to the next table for insertion after truncating it.

1-Collection not yet started :

Capture 02

Using “mysqlslap” to generate the workload :

Capture 05

2-Collection is ongoing :

Run the collection :

Capture 21

Capture 03

NB: 1 is for infinite collection using 16*4 MB as maximum storage space (16 MB per table*4 tables) .The amount of data collected depend on the workload , the sampling time (which is 1 second here) and the available storage.

Capture 08

Capture 06

After some times :

Capture 07

When the “aas4” is almost full switch to “aas1”  :

Capture 04

The gathered data can be viewed using the “active_session_history” view (The screen capture is indicating  “active_session_current” but i renamed it)  which is a union between aas1,aas2,aas3 and aas4.

Capture 09

3-Drilling down using Active Session History (I ‘am using different mysqlslap workload here)

I used similar example as the ones used by Franck Pachot to explore the pgSentinel Active Session History. So here we go :

Average active session 5 Minute ago :

Capture 10

Capture 11

Drill down on statement (Truncated to 128 char to minimize space consumption ) and statement digest

Capture 12

Capture 13

Drill down on wait events

Capture 14

Time dimension using Excel PivotChart

Drill down on wait events

Capture 17

Drill down on statement digest

Capture 19

Drill down on states

Capture 20

This program is still in Beta of course any contribution is welcome !

That’s it 😀

4 thoughts on “MySQL ash_sampler : A simple ASH Builder

  1. This is really some great stuff.

    When i am trying to , i am getting an error
    ERROR 1146 (42S02): Table ‘performance_schema.data_lock_waits’ doesn’t exist

    What version/flavor of MySQL you have developed the “MySQL ash_sampler”

  2. super cool stuff!!
    got the basic query running but on the procedure
    on RDS and getting
    mysql> call ash_sampler(1,1,16);
    ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s