Every so often I run into situation when I need to kill a lot of connections on MySQL server - for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:

PLAIN TEXT SQL:

1. mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';
2. +------------------------+
3. | concat('KILL ',id,';') |
4. +------------------------+
5. | KILL 3101;             |
6. | KILL 2946;             |
7. +------------------------+
8. 2 rows IN SET (0.00 sec) …

Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate to limit rate of IO per second. But it really works when you do local copy.
What about stream backup ? Even you copy just to remote box with
innobackupex --stream=tar | ssh remotebox "tar xfi -"
, read may be so intensive so your mysqld feels impact, slave getting behind, etc...

For this there is a nice small utility - pv .

With pv you run:
innobackupex --stream=tar | pv -q -L10m | ssh remotebox "tar xfi -"

and it will limit channel rate to 10 M per second.

Entry posted by Vadim | No comment

Add to: …

Sometimes when you do profiling you can see number like this in timestamps. Periodically in our patches there was Query_time: 18446744073709550.000 in slow.log file (well, it was fixed recently, but still appears in other places).

I faced this problem several years ago when only 2-core AMD Opteron systems appeared and I noticed sysbench getting crazy showing query execution time 18446744073709550.000 or like this.

Obviously this is unsigned integer which was received by subtraction bigger number from smaller.
But how it can be if we use:

start_time = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp);
...
query_execution
...
end_time = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp);

total_time = end_time - start_time;

How we can get query executed in negative time ? It would be too good to be real :).

After some research I found the reason, and it is …

I see a lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there's a safer alternative.

The danger is simple: they don't work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn't replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form.)

Now you do the following:

PLAIN TEXT CODE:

1. $ mysql
2. mysql> delete from garbage.junk;
3. mysql> use garbage;
4. mysql> update production.users set disabled = 1 where user …

Recently we received a letter from our customer who was unsatisfied with the amount of Internet traffic that dbForge Studio for MySQL produced when he was working with the remote MySQL server via VPN connection.

Here are two tips on how you can reduce your traffic in version 3.50.

First tip: Turn off code completion

SQL editor code completion feature requires all object names from all databases to be queried from server. This is no problem when working with MySQL server on local network but it takes time on remote connection. To turn off loading completions from database go to Tools->Options->Text Editor->Code Completion option tab and turn off option ‘Load completions from database’.

Second tip: Use compression

Version 3.50 of dbForge Studio for MySQL contains database connection option named ‘Use …

It is obvious thing for me, but I just figured out it may be not common understanding. Xtrabackup is also can be used (not only can, but we actually use it this way) to clone one slave to another, or just setup new slave from the master. And it is done in almost non-blocking way ( true for InnoDB setups) for cloned server. Here is command

PLAIN TEXT CODE:

1. innobackupex-1.5.1 --stream=tar /tmp/--slave-info | ssh user@DESTSERVER "tar xfzi - -C /DESTDIR"

When it finished on destination server you run

PLAIN TEXT CODE:

1. innobackupex-1.5.1 --apply-log --use-memory=2G  /DESTDIR

And you have ready database directory, just copy my.cnf from original …

• Peeping into memcached.

Really interesting read about how to examine what’s stored in memcached.

Peep uses ptrace to freeze a running memcached server, dump the internal key metadata, and return the server to a running state. If you have a good host ejection mechanism in your client, such as in the Twitter libmemcached builds, you won’t even have to change the production server pool. The instance is not restarted, and no data is lost.

Highlights include scalability improvements, subquery optimizations and join improvements, improved stored procedure management, out parameters in prepared statements, and new information schema additions.

Sometimes a MySQL server running InnoDB takes a long time to shut down. The usual culprit is flushing dirty pages from the buffer pool. These are pages that have been modified in memory, but not on disk.

If you kill the server before it finishes this process, it will just go through the recovery phase on startup, which can be even slower in stock InnoDB than the shutdown process, for a variety of reasons.

One way to decrease the shutdown time is to pre-flush the dirty pages, like this:

PLAIN TEXT CODE:

1. mysql> set global innodb_max_dirty_pages_pct = 0;

Now run the following command:

…

Quite common task during schema review is to find the optimal data type for the column value - for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain any NULLs or it can be defined NOT NULL which reduces space needed and speeds up processing in most cases ?

These and similar tasks are often done by bunch of SQL queries while really MySQL has a native feature to perform this task - PROCEDURE ANALYSE

Here is sample run of PROCEDURE ANALYSE on drupal schema of Percona web site. The output contains row for each column in the table but I've omitted everything by few rows.

PLAIN TEXT SQL:

1. mysql> SELECT * FROM node  …

I feel like I've been seeing this a lot lately.

occasionally, seemingly innocuous selects take unacceptably long.

Or

Over the past few weeks, we've been having bizarre outages during which everything seems to grind to a halt... and then fixes itself within 5 minutes. We've got plenty of memory, we're not running into swap, and we can't find any queries that would seem to trigger outages: just tons of simple session requests all hung up for no obvious reason.

Problems like this are always hard to debug. If it happens twice a week for 5 minutes at a time, your chance of getting someone logged onto the machine to watch it in action are pretty slim. And of course, when they do look at it, they see nothing wrong on the surface; it takes some very clever, very fast work with OS-level debugging and tracing utilities to really prove what's happening.

The two cases mentioned above were caused by …