Showing entries 1 to 10 of 122
10 Older Entries »
Displaying posts with tag: performance_schema (reset)
TOP 10 MySQL 8.0 features for developers

MySQL 8.0 RC2 has just been released with a lot of new features compared to MySQL 5.7. In this blog post, I will list the top 10 new features that should excite developers.

These functionalities are presented in descending order of preference of our MySQL Community.

TOP 10

  1. MySQL Document Store
  2. Default to utf8mb4
  3. JSON enhancements
  4. CTEs
  5. Window Functions
  6. Descending Indexes
  7. Better Optimizer Cost Model
  8. MySQL Server Components
  9. Improvement in GIS
  10. InnoDB NO WAIT & SKIP LOCKED

MySQL Document Store

This is the most expected and liked feature in MySQL 8.0 … and …

[Read more]
Fun with Bugs #61 - On MySQL Bug Reports I am Subscribed to, Part III

Since my previous post on this topic I've subscribed to 19 more MySQL bugs, so it's time for yet another review of these reports. I am trying to pick up important, funny or hard to process reports every day, and here is the list of the most interesting ones starting from the latest:

  • Bug #89607 - "MySQL crash in debug, PFS thread not handling singals." We have a patch contributed by Robert Golebiowski.
  • Bug #89583 - "no rpm build instructions from source git tree". As Simon Mudd put it:
    "... The sources are supposed to be in the git tree …
[Read more]
Fun with Bugs #60 - On Some Memory Leaks, Replication and Other Bugs Fixed in MySQL 5.7.21

Oracle had formally released MySQL 5.7.21 yesterday. I do not bother any more to study MySQL release notes carefully and completely, but during a quick review today I've noted several interesting items I'd like you to pay attention to.

I am historically interested in InnoDB implementation details, so I could not miss Bug #87619 - "InnoDB partition table will lock into the near record as a condition in the use ". This was a regression bug in 5.7+, probably caused by new implementation of partitioning in InnoDB.

Another interesting bug is Bug #86927 - "Renaming a partitioned table does not update mysql.innodb_table_stats.", by …

[Read more]
More on Studying MySQL Hashes in gdb, and How P_S Code May Help

I have to get back to the topic of checking user variables in gdb to clarify few more details. In his comment Shane Bester kindly noted that calling functions defined in MySQL code is not going to work when core dump is studied. So, I ended up with a need to check what does the my_hash_element() function I've used really do, to be ready to repeat that step by step manually. Surely I could skip that and use Python and Shane himself did, but structures of HASH type are widely used in MySQL, so I'd better know how to investigate them manually than blindly use existing code.

Quick search with grep for …

[Read more]
How to Find Values of User Variables With gdb

In his comment to my announcement of the previous post, Shane Bester kindly suggested to consider pretty printing the information about user variables from gdb. I tried to do this tonight, after a long working day, while working with the same Percona server 5.7.x on CentOS 6.9, and found several interesting details to share even before getting to the pretty printing part (that I'd surely try to avoid doing with Python anyway, as I am lazy and not in a mood to use that programming language for a decade already). So, I decided to share them in a separate …

[Read more]
MySQL Group Replication: who is the primary master – updated!

Some time ago I wrote this article explaining how to find which node was the Primary Master in a MySQL Group Replication cluster running in Single-Primary Mode.

In the latest release of Group Replication, MySQL 8.0.2 dmr, Jaideep improved the visibility of Group Replication extending the performance_schema tables (see his article).

Thanks to these improvements, it’s now very easy to find which host is acting a Primary-Master. This is the query you can use:

mysql-sql> SELECT MEMBER_HOST as `PRIMARY` 
           FROM performance_schema.replication_group_members 
           WHERE MEMBER_ROLE='PRIMARY';
+---------+
| PRIMARY |
+---------+
| mysql3  |
+---------+
1 row in set (0.00 sec) …
[Read more]
Why Thread May Hang in "Waiting for table level lock" State - Part I

Last time I had to use gdb to check what's going on in MySQL server and found something useful with it to share in the blog post it was April 2017, and I miss this kind of experience already. So, today I decided to try it again in a hope to get some insights in cases when other tools may not apply or may not be as helpful as one could expect. Here is the long enough story, based on recent customer issue I worked on this week.
* * * Had you seen anything like this output of SHOW PROCESSLIST statement:

Id User Host db Command Time State 
Info Progress
...
28 someuser01 xx.xx.xx.xx:39644 somedb001 Sleep 247
NULL 0.000
29 someuser01 xx.xx.xx.yy:44100 somedb001 Query 276
Waiting for table level lock DELETE FROM t1 WHERE (some_id = 'NNNNN') AND ...
0.000 …
[Read more]
How To Find What Thread Had Executed FTWRL

This week one of MariaDB Support customers asked how to find what thread had executed FLUSH TABLES WITH READ LOCK (FTWRL) and thus blocked all changes to data. I decided to list several ways to find this out (as eventually customer wanted to know how to find this out not only in MariaDB 10.1, but also in MySQL 5.6 etc).

Let me start with a quick summary. I know the following ways (other that having all queries logged in general query log, slow log, by some audit plugin or at client side, and checking the log) to find the thread that executed FLUSH TABLES WITH READ LOCK successfully:

  1. In MariaDB starting from 10.0.7 you can use METADATA_LOCK_INFO plugin.
  2. In MySQL starting from 5.7 you can use …
[Read more]
InnoDB Locks Analysis: Why is Blocking Query NULL and How To Find More Information About the Blocking Transaction?

Consider the scenario that you execute a query. You expect it to be fast - typically subsecond - but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error:

mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You continue to investigate the issue using the sys.innodb_lock_waits view or the underlying Information Schema tables (INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS).

Note:

[Read more]
Fun with Bugs #52 - On Some Bugs Fixed in MySQL 5.7.18

I had not reviewed MySQL 5.7 release notes for quite a some time in this series. Last time I checked it was MySQL 5.7.15. So, as soon as I noted new release, 5.7.18, I decided to check the release notes for any interesting fixed bug (reported by Community users in public) in the areas I am interested in: InnoDB, replication, optimizer and few others.

Note that recently most of the bugs fixed are internal only, found by Oracle engineers that never cared (or are not allowed, who knows) to report them in public, so this blog post is not even remotely a full review of what's fixed in MySQL 5.7.18 and is not a replacement for reading the detailed …

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