OurSQL Episode 194: Common Schema, part 4

This week we continue our series on Common Schema and talk about different ways to look at the processlist, help with the query profiler, and some internal Common Schema tables. Ear Candy is about RHEL 7.0 using MariaDB, and At the Movies is The Human Postmortem.

News
In the news, the OurSQL podcast will change to a monthly format after episode 200. We love doing the podcast, but it is very time consuming and production is expensive. We will still bring quality episodes, they will just be spaced further apart.

Events
DB Hangops - every other Wednesday at noon Pacific time.
Upcoming SkySQL events
Upcoming MySQL events
FrOSCon 2014 will be held Saturday 23 August - Sunday 24 August at the University of Applied Sciences Bonn-Rhein-Sieg in Germany.

Training
SkySQL Trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings

Common Schema

  • Process Views

    • processlist_grantees

      Kill all queries and connections being run by app_user@192.168.56.101:
      SELECT eval(sql_kill_connection) FROM common_schema.processlist_grantees WHERE user = 'app_user' and host = '192.168.56.101';
      Kill all connections that are not replication and that are idle:
      SELECT eval(sql_kill_connection) FROM common_schema.processlist_grantees WHERE is_repl=0 and is_current=0;

    • processlist_per_userhost

    • processlist_top
    • processlist_states

    • processlist_summary

    • processlist_repl

    • Example:
      mysql> SELECT * FROM common_schema.processlist_repl\G
      *************************** 1. row ***************************
                 ID: 1
               USER: system user
               HOST:
                 DB: NULL
            COMMAND: Connect
               TIME: 42687
              STATE: Slave has read all relay log; waiting for the slave I/O thread t
               INFO: NULL
          is_system: 1
      is_io_thread: 0
      is_sql_thread: 1
           is_slave: 0
      *************************** 2. row ***************************
                 ID: 2
               USER: system user
               HOST:
                 DB: NULL
            COMMAND: Connect
               TIME: 682865
              STATE: Waiting for master to send event
               INFO: NULL
          is_system: 1
      is_io_thread: 1
      is_sql_thread: 0
           is_slave: 0
      *************************** 3. row ***************************
                 ID: 1193736
               USER: repl
               HOST: 192.168.1.152:50241
                 DB: NULL
            COMMAND: Binlog Dump
               TIME: 83207
              STATE: Master has sent all binlog to slave; waiting for binlog to be up
               INFO: NULL
          is_system: 0
      is_io_thread: 0
      is_sql_thread: 0
           is_slave: 1
      *************************** 4. row ***************************
                 ID: 12
               USER: repl
               HOST: 192.168.1.92:45062
                 DB: NULL
            COMMAND: Binlog Dump
               TIME: 682642
              STATE: Master has sent all binlog to slave; waiting for binlog to be up
               INFO: NULL
          is_system: 0
      is_io_thread: 0
      is_sql_thread: 0
           is_slave: 1
      4 rows in set (0.00 sec)

    • slave_hosts
      SHOW SLAVE HOSTS documentation

    • slave_status
      SHOW SLAVE STATUS

    Example:
    mysql> SELECT * FROM common_schema.slave_status\G
    *************************** 1. row ***************************
    Slave_Connected_time: 683428
         Slave_IO_Running: 1
        Slave_SQL_Running: 1
            Slave_Running: 1
    Seconds_Behind_Master: 50
    1 row in set (0.00 sec)

  • Query Profiling

  • Specialized Views


  • Common Schema Internal Tables

Ear Candy
MariaDB 5.5 becomes the 'default MySQL' in RHEL 7, and what this means for you
RHEL 7.0 docs

At the movies
This week in at the movies, we present The Human Postmortem, by David Zwieback at last year's Surge conference. It's not strictly a technical talk, but anyone who has ever been involved in a postmortem will enjoy this different, more human take on the process.

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