In this blog, I will provide answers to the Q & A for the InnoDB Troubleshooting webinar.

First, I want to thank everybody for attending the August 11 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: What’s a good speed for buffer pool speed/size for maximum query performance?

A: I am sorry, I don’t quite understand the question. InnoDB buffer pool is an in-memory buffer. In an ideal case, your whole active dataset (rows that are accessed by application regularly) should be in the buffer pool. There is a good …

Join Sveta on Thursday, May 26, 2016, at 10 am PDT (UTC-7) for her webinar Troubleshooting MySQL hardware resource usage.

MySQL does not just run on its own. It stores data on disk, and stores data and temporarily results in memory. It uses CPU resources to perform operations, and a network to communicate with its clients.

In this webinar, we’ll discuss common resource usage issues, how they affect MySQL Server performance, and methods to find out how resources are being used. We will employ both OS-level tools, and new features in Performance Schema that provide detailed information on what exactly is happening inside MySQL Server.

…

In this blog, I will provide answers to the Q & A for the Troubleshooting Slow Queries webinar.

First, I want to thank you for attending the April 28 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: I’ve heard that is a bad idea to use

select *

; what do you recommend?

A: When I used

SELECT *

 in my slides, I wanted to underline the idea that sometimes you need to select all columns from the …

In this blog, I will provide answers to the Webinar Q & A for Introduction to Troubleshooting Performance: What Affects Query Execution?

First, I want to thank you for attending the April, 7 webinar. This webinar is the third in the “MySQL Troubleshooting” webinar series and last introductory webinar in the series. The recording and slides for the webinar are available here. Here is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: If we had some MyISAM tables, could we use …

You may sometimes hear complaints about MySQL not providing good enough tools for profiling and execution analysis. A few years ago I would have agreed with such opinions, thankfully MySQL developers have made huge efforts to improve the situation in recent major versions. MySQL DBAs now have some great native diagnostic tools at their disposal... which is totally not what this article is about :)
Native MySQL tooling (whatever it might be) is just the tip of the iceberg and if you want to be a better troubleshooter, SysAdmins are the first people you should talk to. Their toolboxes are full of awesomeness and the tools they use have one significant advantage over MySQL tools: they can analyze server execution holistically, regardless of the MySQL version you may be using.
In this article, we will have a look at three OS-level tools: pstack, perf and callgrind (Valgrind tool).

Introduction Tools …

High memory usage scenarios may sometimes be trivial to troubleshoot e.g. when memory parameters are explicitly set too high. Investigations into such issues may also prove to be very difficult when memory pressure is a result of specific workload patterns or better yet, engine bugs.
Advanced memory troubleshooting in MySQL was never easy but thanks to performance_schema memory instrumentation in MySQL 5.7, we finally have some tools to work with. There are still situations when performance schema will not be sufficient:

1. On servers running MyQL <5.7 (well... vast majority),
2. When the component you're interested in is not instrumented,
3. When you don't fancy reading cryptic names of performance schema instruments. While I do love performance schema, this is no joke: OS-level heap profiles are much …

I lost count of the number of times I had customers come to me for a regular MySQL performance check-up saying “no, we don’t have any performance problems right now”, just to find they do actually have them, they’re just not aware of it. It’s no big surprise this happens when you’re not using your system as actively as your users are.

So let me share with you one trick I’m using to quickly check if the system I’m on has any problems that need troubleshooting. Actually, it’s dead simple. And no, it’s not a slow query log, it’s Threads_running. Let me show you.

Wait, what’s wrong with the slow query log?

Nothing at all. MySQL’s slow query log is a great tool when you’re looking for slow queries. Or when you’re fishing for things to optimize – either to reduce the resource consumption, or to increase speed for queries you didn’t know are slow.

And it’s …

In the life of a professional MySQL DBA there comes a moment when issues are no longer trivial enough to be diagnosed using simple repros and built-in diagnostic commands. While trying to understand complex problems, you may be forced (challenged?) to look for answers at the lowest level, by analyzing the server's source code.
If you're a seasoned database engineer who killed several keyboards reporting MySQL bugs, you can stop reading now. If you're just about to begin your journey into the source, you could probably use all the help you can get so keep reading. In this short article I'll describe the MySQL server tracing feature I've been using as an aid during MySQL investigations. When investigating issues at code level, one of the first questions you will ask yourself is "where do I start?". Let's say you're attempting to debug a SHOW CREATE …

Performance Schema has been with us for a while now. Over the years, it has gone a long way from being a curiosity disabled by default to becoming a sophisticated diagnostic tool you may want to enable permanently in your production database.
MySQL 5.7 introduced some exciting Performance Schema features and the first one I'm going to look at is the instrumentation for server memory usage. Have I Got Data For You MySQL 5.7.9 (GA) supports the following memory summary tables (per documentation) that let you look at server memory usage from different angles:

• "memory_summary_by_account_by_event_name" summarizes events for a given account.
• "memory_summary_by_host_by_event_name" summarizes events for a given host.
• "memory_summary_by_thread_by_event_name" summarizes events for a given thread and event name.
…

Today Oracle released MySQL 5.7.6 milestone 16. With this, MySQL 5.7 has been in development for over 2 years.
Compared to MySQL 5.6, the changes are quite extensive. The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases.
A full list of what is new would take too much space here, but I would like to mention some key points:

• Oracle has spent a considerable amount of energy in the improvement of MySQL security and safety. You will see many new features, but even more old features that were deprecated and more that were removed after deprecation in 5.6.
• The installation process has been changing in every …