View Post

Advanced MySQL Slow Query Logging

In MySQL by Aurimas Mikalauskas1 Comment

Proper MySQL Query Optimization starts with a proper Slow Query Logging session. And MySQL Query Optimization is where I spend 70-80% of my time when doing MySQL performance optimization. In this short series I will show you how to do Slow Query Logging the right way. Read More

View Post

How To Speed Up MySQL Restart (hint: just like before, but this time for real)

In MySQL by Aurimas Mikalauskas2 Comments

Restating MySQL can be really annoying. You just want to disable the goddamn query cache and it takes forever (read 5-10 minutes) to shutdown, not to mention the warm-up time. Yes, with MySQL 5.7 you can do may changes online, so you won’t necessarily be restarting that often, but you still need to do upgrades, occasionally increase redo log size and, admit it, enable skip-grant-tables. Here’s how you can make this process way less painful. Read More

View Post

Troubleshooting (Web) Application Performance Issues with Strace

In Apache by Aurimas Mikalauskas2 Comments

What do you do when your Linux web app starts acting out? You troubleshoot it, of course! Me too. And today I would like to talk to you about one tool that works really well with all sorts of applications and application servers – ranging from apache http server running modules (mod_php, mod_perl, etc.), to applications running in fast cgi setting or even simple single-threaded programs. Read More

View Post

MySQL 5.7 default configuration: what’s new? (and what’s not)

In MySQL by Aurimas Mikalauskas0 Comments

Inspired by Peter Zaitsev’s post for MySQL 5.6, I decided to do a comparison between default configuration of MySQL 5.6 and 5.7. And I gotta tell you, some of the changes I found will have a huge impact on performance so make sure to get yourself familiar with them before you upgrade, even if you’re not otherwise running with a default configuration. Read More

View Post

MySQL Performance Troubleshooting Crash Course. A Story Of One Sneaky MySQL Performance Bug

In MySQL by Aurimas Mikalauskas0 Comments

What better way to learn the basics of MySQL Performance Troubleshooting, than by observing a MySQL expert troubleshoot a nasty MySQL 5.7 performance bug, that a team of DBAs were chasing for weeks before giving up and calling in an external consultant for help.

The bug was causing roughly 2 minute long lock-ups for all MySQL write queries at seemingly random 20-30 hour intervals and it took me (the external consultant) only 4 hours to uncover the issue (and a couple more to find and showcase a solution). Read More

View Post

Troubleshooting MySQL Performance. Step Zero: Check If You Have a Performance Problem to Troubleshoot

In MySQL by Aurimas Mikalauskas1 Comment

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.
Read More

View Post

17 Key MySQL Config File Settings (MySQL 5.7 proof)

In MySQL by Aurimas Mikalauskas4 Comments

When MySQL becomes too slow (or too unstable), temptation usually is to tweak the MySQL configuration file. Indeed, it’s a good place to start. But if you ever looked at the available configuration options, you know things can get messy – MySQL now has over 450 configuration variables for your consideration, that are not classified in any way, and neither of them are included in the stock my.cnf. It’s hard to know where to start! Read More