When trying to understand queries in the slow log, an interesting metric to look at is rows examined. Also, when trying to understand CPU spikes on a MySQL instance, the InnoDB Metric dml_reads and the global status Innodb_rows_read are interesting to check. In a similar way, when trying to gather information about which queries are loading the system, SUM_ROWS_EXAMINED from the Performance
10 Older Entries »
When Index Condition Pushdown is used, you cannot trust rows examined as reported by the slow query log (nor in some Performance Schema tables) and you cannot trust the global status Innodb_rows_read (nor the InnoDB Metrics dml_reads). These can lead to an incorrect query complexity analysis, to a mistaken estimation of the total load of a MySQL server, and to a lot of wasted time. As we go
Locking is an important concept in databases. They help regulate
access to the data, so your
SELECT queries return
consistent results and DML and DDL statements leave the data and
schema in a consistent state. For the data, there are four
different transaction isolation levels that influence which locks
are taken. The most two commonly used isolation levels are
REPEATABLE READ (the default in InnoDB) and
READ COMMITTED (the default in some other
databases). Both of those are said to provide non-locking reads,
but there is a little more to it than that.
Selecting into a user variable causing a lock wait timeout.
One case where reads are always locking is when you explicitly requests …[Read more]
Introduction In this article, I’m going to explain how to do query profiling using the MySQL Performance Schema. The Performance Schema has been available since MySQL 5.5.3 and allows MySQL to instrument SQL queries and store their trace information in various tables that you can later inspect in order to determine why a given SQL statement is slow. On older versions of MySQL, you might have used the SHOW PROFILE command, but since this feature has been deprecated, you should now use the Performance Schema for SQL query profiling. Enabling the MySQL... Read More
Undo Tablespaces can be truncated either implicitly or explicitly in MySQL 8.0. Both methods use the same mechanism. This mechanism could cause periodic stalls on very busy systems while an undo tablespace truncate completes. This problem has been fixed in MySQL 8.0.21.…
Facebook Twitter LinkedIn
On a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post.
The binary log compression feature is controlled by two variables, one for enabling the feature and …[Read more]
sys schema was created to make it easier to use
the Performance Schema. This included several functions, for
example to convert the picoseconds used by the Performance Schema
into human readable strings. In MySQL 8.0.16, three of these
functions have been implemented as native functions in MySQL
Why do away with the
sys schema functions? There are
two reasons: performance and ease of use. The native functions
are written in C++ like the rest of the server whereas the
sys schema functions were written as stored
functions. Function written in C++ are inherently faster than
stored functions. Additionally, that the functions are native
means you no longer need to prefix them with
tell MySQL where …
MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.
In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.
The built-in MySQL Connector/Python connection attributesConnection Attributes in MySQL Server
The …[Read more]
Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents MySQL Performance Schema in 1 hour on Thursday, March 21st, 2019, at 10:00 am PDT (UTC-7) / 1:00 pm EDT (UTC-4).
MySQL 8.0 Performance Schema is a mature tool, used by humans and monitoring products. It was born in 2010 as “a feature for monitoring server execution at a low level.” The tool has grown over the years with performance fixes and DBA-faced features. In this webinar, I will give …[Read more]
Version 8.0.12 added many great new features to MySQL. One of the new features included is the memory instrumentation of the XCom cache, which allows users to view and monitor the memory utilization of the cache by querying Performance Schema (PS).…
10 Older Entries »