Analyze MySQL Performance

Table of Contents

Analyze MySQL Performance


Slow queries and Slowlog

Brought to you by Rick James

Analyze MySQL Performance

When asked to analyze the performance of a MySQL or MariaDB server, there are two main tasks (tuning and slowlog) I like to start with. The groundwork for them can be done by the customer.

The deliverables:

    ⚈  Short list of settings (VARIABLES) to change in my.cnf.
    ⚈  Recommendations for speeding up the 'worst' queries.
    ⚈  (possibly) Schema or Architectural recommendations.

In doing these tasks, I get a feel for what the system is doing, thereby jumpstarting any further involvement with the customer's site.

The tuning is usually a one-time task, but may be rerun as the data grows and/or major changes are made to the application.

The Slowlog analysis should be rerun periodically.


Please provide

    ⚈  How much RAM in the server
    ⚈  SHOW VARIABLES; -- the tunables
    ⚈  SHOW GLOBAL STATUS; -- various metrics
    ⚈  (If sys is installed) SELECT * FROM sys.metrics;

Please take GLOBAL STATUS after the server has been running at least 24 hours. (Otherwise things like 'cold cache' invalidate some of the findings.)

The SHOWs need to be in machine readable format.

Privacy: There is nothing very sensitive in the SHOWs. However, if you are especially paranoid, you could mask out any ip addresses and host names. Nothing significant will be lost from the analysis.

With those, I will use an automated script compute about 200 formulas and check for reasonable values. Usually about 20 are flagged as 'suspect'. Then I review them the results and clean up things. The bottom line is a few concrete recommendations for

    ⚈  Changing a few variables.
    ⚈  (maybe) Converting away from MyISAM. (I have tips on the task, if you have not yet done such.)
    ⚈  (maybe) Turn off the Query cache. (Perhaps under 5% of Production systems benefit from the QC.)
    ⚈  (probably) Turning on and analyzing the slowlog (below).

If you choose to post online, see a free tool such as
    ⚈  pastebin

Slow queries and Slowlog

MySQL and MariaDB optionally create a "slowlog" file or table.


    ⚈  Set long_query_time = 1 -- Preferrable in my.cnf We may change that threshold up or down later, but this is a reasonable start.
    ⚈  Set up the slowlog to be captured to FILE.
    ⚈  Turn on (the details of this vary with the Version)
    ⚈  Wait at least 24 hours.

Writing slow_log to file -- this is preferred, since there are tools for condensing such:
    log_output = FILE
    slow_query_log = ON
    slow_query_log_file = (fullpath to some file)
    long_query_time = 1
    log_slow_admin_statements = ON
    log_queries_not_using_indexes = OFF

    ⚈  log_output can be TABLE to write to mysql.slow_log, or FILE,TABLE to write both
    ⚈  slow_query_log_file has a default; is not needed for TABLE
    ⚈  long_query_time is a float, and can be as low as 0, but that gets verbose; default is a 'useless' 10
    ⚈  log_slow_admin_statements tend to be slow but infrequent
    ⚈  log_queries_not_using_indexes is mostly clutter; simply worry about those that are slow
    ⚈  If running on a Replica, consider using log_slow_slave_statements
    ⚈  Beginning with 8.0.14, also have log_slow_extra = ON

Other options (version dependent; incomplete):

Gather results for me (preferrably using FILE):

    ⚈  Digest the results using either of these:
    mysqldumpslow -s t
    ⚈  Grab the first few (perhaps 5) queries. They will be sorted by (frequency * avg-time), which is the most useful.
    ⚈  Provide SHOW CREATE TABLE -- for each table
    ⚈  Provide EXPLAIN SELECT ... -- for each query

Analyze Results: Usually (not always), I can then provide a concrete suggestion on speeding up each query:

    ⚈  Add a particular index (often 'composite')
    ⚈  Reformulate the query (a simple case is not hiding an indexed column in a function; more complex involves adding/removing subqueries)
    ⚈  Recommend schema change
    ⚈  Possibly even an architectural change (EAV is a common problem)

Where to get pt-query-digest

Download the "Percona toolkit".

If you are using a cloud service, you may not be able to get to the slowlog file. See if there is a UI that provides a way to download it. It may also provide a way to set up the slowlog paramterers.


The Percona toolkit does "not" work on Windows. But it seems that the only limitation with the slowlog is "end-of-line". Change CRLF to LF throughout the slowlog; then pt-query-digest should work.

Some common recommendations:

Many-to-Many mapping schema
Speeding up wp_postmeta
explode-implode problem of JOIN + GROUP BY
Pagination via OFFSET - instead "remember where you left off"

More tips are found in the links below.

MariaDB 10.6

Posted June, 2017;   Slowlog: Sep, 2017

MySQL Documents by Rick James

HowTo Techniques for Optimizing Tough Tasks:

Partition Maintenance (DROP+REORG) for time series (includes list of PARTITION uses)

Big DELETEs - how to optimize -- and other chunking advice, plus a use for PARTITIONing
    Chunking lengthy DELETE/UPDATE/etc.

Data Warehouse techniques:
    Data Warehouse Overview   Summary Tables   High speed ingestion   Bulk Normalization  

Schema and code design for large Sensor database

Entity-Attribute-Value (EAV) -- a common, poorly performing, design pattern; plus an alternative

5 methods for 'Find Nearest'

Lat/Lng search to Find the nearest 10 pizza parlors
    Lat/Long representation choices

Z-Order 'find nearest'

Pagination, not with OFFSET, LIMIT

Techniques on efficiently finding a random row (On beyond ORDER BY RAND())

GUID/UUID Performance (type 1 only)

IP Range Table Performance -- or other disjoint ranges

Rollup Unique User Counts

Alter of a Huge table -- Mostly obviated by 5.6

Efficient List of Latest 10 news articles

Build and execute a Pivot SELECT (showing rows as columns)

(Groupwise Max): Efficiently find largest row(s) for each group

Other Tips, Tuning, Debugging, Optimizations, etc...

Rick's RoTs (Rules of Thumb -- lots of tips)

Datatypes and building a good schema

Memory Allocation (caching, etc)

Character Set and Collation problem solver
    Trouble with UTF-8   If you want case folding, but accent sensitivity, please file a request at .
    Python tips,   PHP tips,   other language tips
    utf8 Collations   utf8mb4 Collations on 8.0

Converting from MyISAM to InnoDB -- includes differences between them

Compound INDEXes plus other insights into the mysteries of INDEXing

Cookbook for Creating Indexes
    Many-to-many mapping table   Handler counts   wp_postmeta   UNION+OFFSET

MySQL Limits -- built-in hard limits
    767-byte INDEX limit

Galera, tips on converting to (Percona XtraDB Cluster, MariaDB 10, or manually installed)

5.7's Query Rewrite -- perhaps 5.7's best perf gain, at least for this forum's users

Analyze MySQL Performance
    Analyze VARIABLEs and GLOBAL STATUS     Analyze SlowLog

My slides from conferences
MiniFest 2021 - Rick James & Daniel Black - Answering on Stack Overflow(+comments) - MariaDB Frontlines
Percona Live 4/2017 - Rick's RoTs (Rules of Thumb) - MySQL/MariaDB
Percona Live 4/2017 - Index Cookbook - MySQL/MariaDB
Percona Live 9/2015 - PARTITIONing - MySQL/MariaDB

Contact me via LinkedIn; be sure to include a brief teaser in the Invite request:   View Rick James's profile on LinkedIn

Did my articles help you out? Like what you see? Consider donating:

☕️ Buy me a Banana Latte and bagel ($10) There is no obligation but it would put a utf8mb4 smiley 🙂 on my face, instead of the Mojibake "🙂"