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:
- On servers running MyQL <5.7 (well... vast majority),
- When the component you're interested in is not instrumented,
- 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 …
[Read more]
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 …
Talking with Percona Live attendees last year I heard a couple of common themes. First, people told me that there is a lot of great advanced content at Percona Live but there is not much for people just starting to learn the ropes with MySQL. Second, they would like us to find a way to make such basic content less expensive.
I’m pleased to say we’re able to accommodate both of these wishes this year at Percona Live! We have created a two-day intensive track called “MySQL 101” that runs April 15-16. MySQL 101 is designed for developers, system administrators and DBAs familiar with other databases but not with MySQL. And of course it’s ideal for anyone else who would like to expand their professional experience to include MySQL. The sessions are designed to lay a solid foundation on many aspects of MySQL development, design and …
[Read more]
One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and
corresponding views in sys
schema
And as troubleshooting freak I have huge reason to greet this
feature.
Before version 5.7 we had very limited abilities to diagnose
memory issues in MySQL. We could use operating system tools, such
as vmstat, top, free, but they only showed what MySQL server uses
memory, but do not show how. In version 5.7 things
changed.
Lets examine what can we study about memory usage by MySQL
Server.
At first, this is total amount of memory, used by all internal MySQL
structures:
mysql> select * from …
September 18, 2014 By Severalnines
Galera Cluster is a popular choice for achieving high availability using synchronous replication. Though if you are planning to run huge sites with many DB objects (tables), a few tweaks are necessary.
Yes, you might have been successful in loading your 1000s of databases and 1000s of tables, but what happens if you have a node failure and Galera recovery fails?
In this blog post we will show you how to determine one common error related to the open_files_limit that MySQL imposes, and also to spot another potential pitfall.
Open_files_limit
If you are using wsrep_sst_method=xtrabackup or wsrep_sst_method=xtrabackup-v2 then you will find a log file in the data directory of the donor node. This log file is called innobackup.backup.log.
140912 19:10:15 innobackupex: Done. …[Read more]
Here are 3 recent ' oops... wish I hadn't done that :/ ' mistakes I've made since joining moz that you might as well avoid (I'm sure there will be more, but they better not be the same)
Reviewing config files for MySQL, but not all of the defaults
We recently migrated a few MySQL databases to a new datacenter, and took advantage of the migration to upgrade the MySQL version(s) at the