The latest from the MySQL community

There are few easy ticks to see what is stuck inside MySQL instance. All these techniques will not give you whole picture, but might help to find queries that block performance. Let’s start from what exactly doing your MySQL server right now.

Which queries are running now?

This will give you an idea what’s running right now so you can find long running queries which slowing down MySQL and/or causing replication lag:

mysql -e "SHOW PROCESSLIST" | grep -v -i "sleep"

It is more convenient than just run “SHOW PROCESSLIST” as it’s hiding all connected threads in “Sleep” state so you’ll get a clean output. Also you can get same output but updating each second:

watch -n1 'mysql -e "SHOW FULL PROCESSLIST" | grep -v -i "Sleep"'

What to look for? This is complex output but you can start with Time and State columns. When you see a query running for more …

It's easy to load multiple rows of data from a file into a MySQL table with a single LOAD DATA INFILE command, but if the file is over a certain size you may want to load it in multiple chunks. When I have a file with millions of lines I typically use pt-fifo-split to separate the file into multiple chunks in order to limit the size of each transaction. Unlike most tools in Percona Toolkit, pt-fifo-split doesn't actually interact with your database, but this is one MySQL use case where it's very helpful.

Here's a quick example in bash that illustrates how I can take a large tab-delimited file, break it into multiple 100,000 line chunks, and load the chunks into a table:

``` FLAT_FILE="/tmp/big_file.txt" …

This Log Buffer Edition goes right through the fields of salient database blog posts and comes out with something worth reading.

Oracle:

Extract SQL full text from SQL Monitor html.

Disruption: Are Hot Brands Breaking the Rules?

Understanding Flash: Unpredictable Write Performance.

The caveats of running .sql scripts with GUI tools.

…

Dear Perl and MySQL community,

I'm pleased to announce the release of DBD::mysql 4.029

From the changelog:

• Added fix to tests to create test database if not exists (contstant failure on Travis) (CaptTofu)
• Support the fraction of (Oracle) MySQL Fabric that is supported by the most recent Connector/C (Steffen Mueller smuellercpanorg
• Statistics Info Milan Šorm is4ucz> for work on statistics_info
• Fix for RT 97625, use after free(), Reini Urban rurbancpanorg and Giovanni Bechis bigiosnbit>

Thanks to all who contributed!

Patrick "CaptTofu" Galbraith

Please feel free to visit:

CPAN DBD-mysql-4.029

And as always:

Github repo

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets create a user account with minimal privileges and login.

$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution

…

Here is a list of my most useful tools that I use when doing performance audits.
Please note, I am writing this mainly for myself, because I sometimes end up trying to find them in my other blog post about mastering indexing and this may save me time as well as a few changes that have happened over the years.
Regular Slow Log Report pt-query-digest slow_query.log  >slow.txt
All Queries (that use indexes) for a certain table pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select|update|delete)/) &&  ($event->{arg} =~ m/mytable /) ' --limit=100% >mytable.txt
Longest Running Select Queries - most painful queries with response time % right next to them. pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ …

In the wrong context, almost anything can be funny, and MySQL’s error codes are no exception. A few weeks ago, things got a little loopy at VividCortex headquarters as we discovered some definitions that could be taken more than one way.

How It Began

It started with ER_M_BIGGER_THAN_D, which doesn’t sound like an error at all. Anyone who knows alphabetical order knows that M is bigger than D! Of course, the error is really about an attempt to define a decimal or float column wrongly.

Next was ER_TOO_BIG_SCALE, about which I tweeted “MongoDB doesn’t have this error, as far as I know, but MySQL does.”

MongoDB doesn't have this error, as far as I know, but MySQL does.

ER_TOO_BIG_SCALE

…

Virtual Hosting With PureFTPd And MySQL (Incl. Quota And Bandwidth Management) On CentOS 7.0 This document describes how to install a PureFTPd server that uses virtual users from a MySQL database instead of real system users. This is much more performant and allows to have thousands of ftp users on a single machine. In addition to that I will show the use of quota and upload/download bandwidth limits with this setup. Passwords will be stored encrypted as MD5 strings in the database.

Virtual Hosting With PureFTPd And MySQL (Incl. Quota And Bandwidth Management) On CentOS 7.0 This document describes how to install a PureFTPd server that uses virtual users from a MySQL database instead of real system users. This is much more performant and allows to have thousands of ftp users on a single machine. In addition to that I will show the use of quota and upload/download bandwidth limits with this setup. Passwords will be stored encrypted as MD5 strings in the database.

I just posted on the Server team's blog, an account on my recent only_full_group_by work. We have made significant improvements in 5.7.5, worth a look!