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]
10 Older Entries »
I had forgotten the topic of studying metadata locks with gdb for
quite a some time. Now that I've upgraded to Percona Server 5.7 and enjoy the
remaining of my last free day as an independent MySQL Support
Engineer doing nothing but some blogging, I think it's time to
get back to gdb interactive sessions and try to answer the
question I've got from customer back in Percona but had no chance
In a bit rewritten way, it sounded as follows:
"Do ALTER TABLE ... STATS_AUTO_RECALC=1" and the reverse ALTER
TABLE...STATS_AUTO_RECALC=default block select, update, delete, and insert on the table?"There are two ways to answer this question. First, we can just try to …
In the previous post in this series I've concluded
that metadata locks are acquired in "batches" and the function
that implements this is called MDL_context::acquire_locks.
Let's check quickly what it does to confirm where wait for
metadata lock really happens. We need this to proceed finally
from studying what locks are set and when (this is a long and
complicated topic to spend time on out of general interest) to
more practical topic: how to find the session that holds the
blocking metadata lock in MySQL versions before 5.7.x.
I'll continue to use Percona Server 5.6.27 for now, just because I have it installed and have a source code at hand. So, MDL_context class is defined in sql/mdl.h file as follows:
Context of the owner of metadata locks. I.e. each server …
Some results in my initial post in this series led me to
questions that I'll try to answer here. First of all, I noted
that SELECT from a single table ended up with just one
metadata lock request:
(gdb) b MDL_request::init
Breakpoint 1 at 0x648f13: file /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc, line 1266.
Breakpoint 2 at 0x648e70: file /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc, line 1245.
warning: Multiple breakpoints were set.
Use the "delete" command to delete unwanted breakpoints.
[Switching to Thread 0x7ff224c9f700 (LWP 2017)]
Breakpoint 2, MDL_request::init (this=0x7ff1fbe425a8,
mdl_namespace=MDL_key::TABLE, db_arg=0x7ff1fbe421c8 "test",
Metadata locks are used in MySQL since
version 5.5.3 and are available in GA MySQL versions for 6 years
already. Still they are far from clearly documented (even less
their implementation is documented in details - try to find
anything about metadata locks in current MySQL
Internals manual) and often causes "unexpected" problems for
Only since MySQL 5.7.3 (and only for a few months in GA releases since 5.7.9) we have an easy, official and documented way to check metadata locks set by different sessions using the metadata_locks table in Performance Schema. I've already explained how to use it in my blog post at Percona's blog. Still, …
Welcome to MySQL QA Episode 3: “Debugging: GDB, Backtraces, Frames and Library Dependencies”
In this episode you’ll learn how to use debugging tool GDB. The following debugging topics are covered:
1. GDB Introduction
2. Backtrace, Stack trace
4. Commands & Logging
6. Library dependencies
8. Handy references
– GDB Cheat sheet (page #2): https://goo.gl/rrmB9i
– From Crash to testcase: https://goo.gl/3aSvVW
Also expands on live debugging & more. In HD quality (set your player to 720p!)
The post MySQL QA Episode 3: How to use the debugging tool GDB appeared …[Read more]
In the previous post we checked lock requests while
executing INSERT INTO t ... SELECT FROM tt where there is
an auto_increment column for which the value is generated
in the destination table with default
innodb_autoinc_lock_mode=1. Based on it I've
reported Bug #76563 that is already verified.
Let's continue to study a special case of the statement mentioned above, INSERT INTO t ... SELECT FROM t, that is, when source and destination table is the same. We again start with table t having just 4 rows:
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t;
| id | val |
There is a story behind this post. To make it short, the detailed
study presented here started when I noted old Bug
#72748 in "No feedback" status few days ago. I considered
this status totally wrong and started a discussion on Facebook about it (yes, this is what I usually do,
I discuss handling of MySQL bugs in public).
The technical topic of this discussion (blaming aside) was the way AUTO-INC locks are set by InnoDB with default innodb_autoinc_lock_mode=1. Manual defines "bulk inserts" concept:
- “Bulk inserts”
Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in …
Next Wednesday, I will present a webinar about MySQL performance profiling tools that every MySQL DBA should know.
Application performance is a key aspect of ensuring a good experience for your end users. But finding and fixing performance bottlenecks is difficult in the complex systems that define today’s web applications. Having a method and knowing how to use the tools available can significantly reduce the amount of time between problems manifesting and fixes being deployed.
In the webinar, titled “Optimizing MySQL Performance: Choosing the Right Tool for the Job,” we’ll start with the basic top, iostat, and vmstat then move onto advanced tools like GDB, Oprofile, and Strace.
I’m looking forward to this webinar and invite you to join us April 16th at 10 a.m. Pacific time. You can learn more and also …[Read more]
When investigating core files from crashes, one can quite easily
figure out which query crashed, as we've
Sometimes you want to just list all the currently executing statements, this is useful for diagnosing hangs or corruptions.
At least GDB 7 supports python macros, which can help us a lot here. I use a core file from 5.5.27, also a non-debug build but not "stripped". So it's a standard build made with -g allowing us to reference symbols.
I wrote a simplistic macro to iterate through mysqld's global "threads" variable.
This is what my …
10 Older Entries »