Showing entries 1 to 10 of 106
10 Older Entries »
Displaying posts with tag: performance_schema (reset)
Fun with Bugs #46 - On Some Bugs I've Reported During the Year of 2016

It's time to summarize the year of 2016. As a kind of a weird summary, in this post I'd like to share a list of MySQL bug reports I've created in 2016 that are still remaining "Verified" today:

  • Bug #79831 - "Unexpected error message on crash-safe slave with max_relay_log_size set". According to Umesh this is not repeatable with 5.7. The fact that I've reported the bug on January 4 probably means I was working at that time. I should not repeat this mistake again next year.
  • Bug #80067 - "Index on BIT column is NOT used when column name only is used in WHERE clause". People say the same problem happens with INT and, what may be even less expected, BOOLEAN columns.
[Read more]
Quickly Troubleshoot Metadata Locks in MySQL 5.7

In a previous article, Ovais demonstrated how a DDL can render a table blocked from new queries. In another article, Valerii introduced performance_schema.metadata_locks, which is available in MySQL 5.7 and exposes metadata lock details. Given this information, here’s a quick way to troubleshoot metadata locks by creating a stored procedure that can:

  • Find out which thread(s) have the metadata lock
  • Determine which thread has been waiting for it the longest
  • Find other threads waiting for the metadata lock

Setting up …

[Read more]
MySQL Group Replication Limitations: savepoints

Today in our series of articles related to MySQL Group Replication’s limitations, let’s have a quick look at Savepoints.

The manual is clear about this: Transaction savepoints are not supported.

The first thing to check then is if the application that will use our MySQL Group Replication Cluster is currently using savepoints.

We have two ways to find this, the first is using STATUS variables:

mysql> show global status like '%save%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Com_release_savepoint      | 2     |
| Com_rollback_to_savepoint  | 0     |
| Com_savepoint              | 4     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
+----------------------------+-------+
[Read more]
MySQL Group Replication and table design

Today’s article is about the first two restrictions in the requirements page of the manual:

  • InnoDB Storage Engine: data must be stored in the InnoDB transactional storage engine.
  • Primary Keys: every table that is to be replicated by the group must have an explicit primary key defined.

So the first requirement is easy to check by a simple query that list all the non InnoDB tables:

SELECT table_schema, table_name, engine, table_rows, 
       (index_length+data_length)/1024/1024 AS sizeMB 
FROM information_schema.tables 
WHERE engine != 'innodb' 
  AND table_schema NOT IN 
    ('information_schema', 'mysql', 'performance_schema');

The second one is a bit more tricky. Let me show you first how Group Replication behaves:

Case 1: no keys

Let’s create a table with no …

[Read more]
MySQL Support Engineer's Chronicles, Issue #2

It's time to continue my new series that I've started 2 weeks ago. I'd like to start with a reminder that it's time to send your talks for "MySQL and Friends Devroom" at FOSDEM 2017 - the only MySQL-related event next year that I plan to attend in any case. It seems we have one more week to submit, but I've already filled in all the details for the "main" talk, "Understanding MyRocks locks and deadlocks". I'd like to apply my usual source code reading and gdb breakpoints approach in case if by the end of January, 2017 official documentation still misses important details. Official MySQL …

[Read more]
PS_history 2.0 was released last week with MySQL 8 support and bundled sys_history

PS_history is a tool which collects historical snapshots of the PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values over time, for example, it is possible to look at the 95 th percentile response time for a query over time.

PS_history is stored procedure and event based, and thus it resides entirely inside of the database with no external dependencies. It uses a clever technique to capture all of the P_S data in one consistent snapshot. This ensures that all of the sys_history views (bundled now with PS_history) have a consistent set of data.

By default, as long as the event_schedule is enabled, PS_history will collect data every 30 seconds. If a snapshot takes 30 seconds, there will be a 30 second delay before the next snapshot starts. This value can be changed by calling the `ps_history`.`set_collection_interval`(N) where N is the number of seconds between samples.

The `sys_history` schema is …

[Read more]
Fun with Bugs #44 - Community Bugs Fixed in MySQL 5.7.14

MySQL 5.7.14 was officially released yesterday. So, it's time to check what bugs reported by MySQL Community in public were fixed in this release. Some of these bugs are presented below.

As usual, let me start with InnoDB. The following bugs were fixed there:

  • Bug #80296 - "FTS query exceeds result cache limit". It was reported (for 5.6, but I do not see new 5.6 release notes yet) by Monty Solomon and verified by Umesh.
  • Bug #80304 - "generated columns don't work with foreign key actions". It was reported by Guilhem Bichot based on test case by Peter Gulutzan presented …
[Read more]
MySQL Workbench & Performance_Schema

Last week during the Oracle Users Group Leaders Summit in Bucharest I had the pleasure the meet the leaders for the MySQL Users Group from Azerbaijan,  Finland, Madrid and the Netherlands.

During some discussions, it appeared that some users are not aware of the Performance_Schema integration in MySQL Workbench. Indeed with WB you can enable PFS, add sys table if not present by default (<5.7).

You can use a default configuration or customize it and enable all the instruments and consumers you need.

When enabled, you can generate some useful reports

 

 

In case you are also not yet aware of it, the following dashboard is also available in MySQL Workbench

The latest version of MySQL Workbench has been released yesterday, June 13th : 6.3.7 GA

Fun with Bugs #42 - Bugs Fixed in MySQL 5.7.12

MySQL 5.7.12 was released more than 2 weeks ago. New features introduced there in a form of "rapid plugins" are widely discussed, but I am more interested in bugs reported by MySQL Community users that are fixed there. Unfortunately I do not see MySQL Community Release Notes by Morgan (like this) for quite a some time, so I have to continue describing key bug fixes and name people who reported and verified bugs in my "Fun with Bugs" series.

As usual, let's start with InnoDB bugs fixed:

  • Bug #80070 - "allocated_size and file_size differ if create general tablespace outside datadir". It was reported by my former colleague from Percona …
[Read more]
SYS Schema: Simplified Access To SSL/TLS Details

A while back, I wrote a blog post explaining how PERFORMANCE_SCHEMA improvements in MySQL Server 5.7 provides new visibility into the SSL/TLS status of each running client configuration.  An excellent recent post from Frederic Descamps at Percona covers similar territory.  Both of us use PERFORMANCE_SCHEMA tables directly – a powerful interface, but one that requires a query joining multiple tables.  Thanks to the excellent work of Mark Leith, and a contribution from Daniël van Eeden, access to this same information is made far easier via the SYS schema.

I overlooked the SYS

[Read more]
Showing entries 1 to 10 of 106
10 Older Entries »