OpenWorld 2017: MySQL Automatic Diagnostics: System, Mechanism, and Usage

Shangshun Lei and Lixun Peng from Alibaba Cloud discussed in this session a system they have built called CloudDBA to automate a lot of traditional DBA roles.  The system sounds pretty cool and is what my team should be aspiring to do.  But there was not a lot of information on the how, and a lot of the what for CloudDBA.  My notes from the session are:

  • Why CloudDBA
    • Reduce Costs –
      • 80% of time spent on finding root cause, optimizing performance, scaling hardware and resources
      • 20% on database platform
    • Focus your resources on business
    • Provide best technology
  • Architecture
    • Kafka/JStorm for log collection
    • Offline Data repository
      • Error log
      • slow log
      • audit log
      • cpu/ios/status
    • Offline diagnostics
      • Top SQL Analyss
      • Trx Analysis
      • SQL Review
      • Deadlock Analysis
    • Online diagnostics
      • Knowledge base – rule engine
      • Inference Engine – matches for conditions and runs execution to resolve or provide advise to users
    • Realtime Event and advise
      • Slave delay
      • Config Tuning
      • Active Session
      • Lock and Transaction
      • Resource
  • Rule Engine
    • Immediate detection of useful changes with low cost
    • Choose correct inference model
      • Database global status is mature and easy to get
      • High frequency monitoring to make sure no useful info is missed
      • Real time state change detection algorithms
      • Importance of database experience
  • Knowledge Base and inference engine
    • Ability to accumulate DBA experts’ experience in short time
    • Accurate issue detection & corresponding advice
  • Offline diagnosis
    • Audit log does matter
    • Record full SQLs for database
    • A feature of AliSQL, no performance impact
  • Transaction analyiss
    • uncommitted transactions
    • long transactions
    • long interval between transactions statements
    • big transactions
  • SQL review
    • how many types of sql
    • how many types of transactions
    • sqls or sequence in transaction is expected or not
    • scan rows, return rows, elapsed time and sql advise
  • Top SQL
    • top sql before optimize
    • help explain questions such as why my cpu is 100%
    • different statistics dimensions and performance metrics
  • SQL Advisor
    • Not a database optimizer
    • built outside of MySQL kernel
    • query rewriter
    • follow rules to create indexes that works for the lowest cost

Advanced MySQL Query Tuning – Percona Live 2015

I attended the “Advanced MySQL Query Tuning” session by Alexander Rubin from Percona.  Alex just finished helping us out with a Galera evaluation and he is just top notch.  He also helps organize the Triangle MySQL Meetup which I attend.  The talk was specific to tuning Queries and did not cover configuration.  He went over GROUP BY, ORDER BY, DISTINCT queries and calculated fields (introduced in 5.7).

Indexes can be used to scan to leaf node on equality search.  It can also be scanned at the leaf level for range searches.  MySQL can use the leftmost part of combined indexes (pt_duplicate_index_checker can be used to look for duplicate indexes).  Alex showed how you can tell in the EXPLAIN if MySQL is only using part of the index based on key_len and the columns in the index.  MySQL cannot use a combined index if you are not using the leftmost part of the index.  A covered index covers all the columns in a query (WHERE and SELECT).  A covering index avoids row lookups.

MySQL cannot use an index if you use a calculated expression.  You will need to remove the expression to use an index.  You can also add a field to the table and create a trigger to keep it up to date and then you can use an index on that columns.  In 5.7.7 they add GATHERED field to create a derived column and they can be indexed.  The insert speed is 2x based on Alex’s example.  And he showed that the select does in fact use the index.  You can also define the derived table to be stored, but it increases the size of your table.

Temporary tables are slow and are created when you have group by, range + order by and in a few other cases.  There are both in memory and on disk temporary tables.  The tmp_table_size and max_heap_table_size configuration options are used to control spillage.  If the table is larger than either it will spill to disk.  Memory temporary table do not support BLOB/TEXT.  If you have a covering combined index it can avoid temporary table creation.

With a range scan the covering index will not help.  You can hack it by converting it to a union of multiple queries with covering indexes.  Still creates a temporary table but much much smaller.

When creating a covering index for filesort you need to start with the where condition and then the group by.

A loose index scan considers only a fraction of the key in an index.  The query cannot have joins, the group by should be the leftmost prefix of the index, only allows MIN() and MAX() aggregate functions.

Joining can cause duplicates and adding the DISTINCT can cause a slowdown.  By moving the DISTINCT to an outer query you can avoid spilling to disk if your select does not contain the columns with BLOG/TEXT.

In 5.7 Group By will no longer automatically cause an ORDER BY.  So Developers will need to add the ORDER BY if that is the desired result.