OurSQL Episode 139: Starting to Perform

This week we discuss the basics of using the performance schema in MySQL 5.5 and 5.6. Ear Candy is about a temporal gotcha when using dates and times that do not exist, and At the Movies is David Stokes giving some useful for System Administrators who also are in a DBA role.

News
Continuent's Tungsten Replicator is now completely open source
TokuDB is now open source

Performance Schema Basics
Marc Alff's blog on configuring the PERFORMANCE_SCHEMA
Marc Alff's blog on an overview of performance schema

Finding out what instruments are enabled:

SELECT *
FROM PERFORMANCE_SCHEMA.SETUP_INSTRUMENTS
WHERE ENABLED='YES';

Find out whether the performance schema is enabled:

SHOW GLOBAL VARIABLES LIKE 'performance_schema';

setup_instruments table - what instruments are monitored
setup_consumers table - what information is stored about instruments
setup_timers table - information about event timing

events_statements_summary_by_digest table - has a digest of queries run

Sample digest entry:

                SCHEMA_NAME: performance_schema
                     DIGEST: f2d57cea9e78e7b37c4509c0564dd1cc
                DIGEST_TEXT: SELECT ? + ?
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 1930473000
             MIN_TIMER_WAIT: 78122000
             AVG_TIMER_WAIT: 965236000
             MAX_TIMER_WAIT: 1852351000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 2
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2013-05-08 08:17:20
                  LAST_SEEN: 2013-05-08 08:25:12

Performance Schema timing and picoseconds

Timing table examples:

mysql> select * From setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+
4 rows in set (0.00 sec)

mysql> select * From performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2492686567 |                1 |             21 |
| NANOSECOND  |      1000000000 |                1 |             62 |
| MICROSECOND |         1000000 |                1 |             68 |
| MILLISECOND |            1037 |                1 |             82 |
| TICK        |             107 |                1 |           1774 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

MySQL 5.5 PERFORMANCE_SCHEMA quick start
MySQL 5.6 PERFORMANCE_SCHEMA quick start

event_waits_current table
Example event_waits_current entry:

            THREAD_ID: 713
             EVENT_ID: 180
           EVENT_NAME: wait/synch/mutex/sql/THD::LOCK_thd_data
               SOURCE: sql_class.cc:4277
          TIMER_START: 572053933012016
            TIMER_END: 572053933147268
           TIMER_WAIT: 135252
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 591564288
     NESTING_EVENT_ID: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: 0

There's also a historical record of this information in the events_waits_history table.

Ear Candy
This week in ear candy we’ll talk about a date gotcha that is difficult to diagnose. It is described in bug 34214. If you try to use an invalid date, for example '2012-03-11 02:30:00', MySQL gives the error:
"ERROR 1292 (22007): Incorrect datetime value"
The date is invalid because March 11th was when Daylight Saving Time began in 2012, and so times between 2 am and 2:59 am on that day do not exist.

At the Movies
This week in at the movies we present MySQL Community Manager David Stokes talking about Congratulations! You're the New Linux Admin and the MySQL DBA Too!, presented at Open Database Camp two months ago.

Where you can see us
Gerry will be at the June Seattle MySQL Meetup on Monday June 3rd when Rob Smith is going to talk and show us 2 of his projects: Diamond (http://www.kormoc.com/projects/diamond/), a flexible light monitoring tool similar to collectd and his query digest UI (http://www.kormoc.com/projects/query-digest-ui/), a tool to create reports from the slow query log.

Sheeri will be at the June Boston MySQL User Group meeting on Monday June 10th at MIT.

Sheeri will be speaking and assisting at Wise Bootcamp for women in science and engineering, June 23-24 in Boston.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast