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

• MySQL overall server status report
• MySQL tracing session/thread activity
• MySQL statement level tracing

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” .

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.

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

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 :

Using “mysqlslap” to generate the workload :

2-Collection is ongoing :

Run the collection :

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.

After some times :

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

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.

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 :

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

Drill down on wait events

Time dimension using Excel PivotChart

Drill down on wait events

Drill down on statement digest

Drill down on states

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

That’s it 😀