Tmpfs with IO_Direct

bad idea..

tmpdir..
..on tmpfs

upgrade..
..to 5.5

default engine..
..innodb

create..
..temp tables statements

optimizing my.cnf...
..io_direct sounds good..

tmpfs..
..and io_direct
do not..
..play well together

..lucky...
..just slave..
..had backup

LOAD DATA INFILE to resolve locks

In relation to this MPB post, there is a command to output data into a file and then to load it back into MySQL to resolve locks. (An example of a really big reporting query that could be quite heavy, can be found here.)

There is an issue with outputting data into a file through MySQL if the file already exists. For example:

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed 

mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec) 

mysql> select * from user into outfile '/tmp/user.sql';
ERROR 1086 (HY000): File '/tmp/user.sql' already exists

So to resolve this, I played around with the \! command (which lets you run commands on your OS):

mysql> \! rm -rf /tmp/user.sql
mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec) 

mysql> \! rm -rf /tmp/user.sql
mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec)

This seemed to have worked.

Apart from security issues and OS specific commands, anyone see anything wrong with this approach?

Perhaps I should wrap it around in a stored procedure..

Query-Digest on Windows

The best way to run pt-query-digest is by making absolutely sure it is not running on any server that is even slightly important to production.

I recently had a case where pt-query-digest took up all the memory and swap on a (linux) development server. I think the reason was that the slow log had blob data in it.
Even though it wasn't a production server, it managed to upset some people.

So I:

1. Installed strawberry perl on my windows computer
2. Downloaded the gzip'd slow log file (using WinSCP)
3. Unzip'd it
4. Saved percon.com/get/pt-query-digest to a .pl file on my laptop
5. Went to start menu
6. In the run part I typed cmd
7. In the new command window, I typed pt-query-digest.pl slow.log > digest.txt

And walla!
My laptop was a bit hot for a few hours, but nothing crashed and no one was upset.