JOIN and comma precedence
Here’s a little something that might trip you up occasionally. Have a look at this test scenario:
USE test; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; CREATE TABLE a ( a INT ); CREATE TABLE b ( b INT ); CREATE TABLE c ( c INT ); SELECT a.a FROM a LEFT JOIN c ON c.c = a.a; -- Q1 SELECT a.a FROM a, b LEFT JOIN c ON c.c = a.a; -- Q2
Q1 and Q2 will produce the same result, right? Wrong! As of MySQL 5.0.12, per the SQL standard, JOIN has had higher precedence than comma ‘,’.
So, you get the following:
mysql> SELECT a.a FROM a LEFT JOIN c ON c.c = a.a; Empty set (0.00 sec) mysql> SELECT a.a FROM a, b LEFT JOIN c ON c.c = a.a; ERROR 1054 (42S22): Unknown column 'a.a' in 'on clause'
This is because, in earlier versions, MySQL interpreted it as ( ( a, b ) LEFT JOIN c ). Now, it interprets this syntax as ( a, ( b LEFT JOIN c ) ). If you run into this problem, the fix is easy. You simply need to add brackets around the table list:
mysql> SELECT a.a FROM (a, b) LEFT JOIN c ON c.c = a.a; Empty set (0.00 sec)
You can read more about this in the MySQL Documentation, from the section starting with “Previously, the comma”.
Tools of a Support Engineer
So, you’ve emailed MySQL Support, they’re working on the problem you’re having. How are they working? What tools do they use? Well, here’s my list:
- IRC – All MySQL Support Engineers work on IRC, it’s our main communication medium. While you’re only getting emails from one Engineer, it’s quite likely they’re consulting with several others at the same time. Many pairs of eyes catch all of the details.
- MySQL Docs, Changelogs, Knowledge Base, Google, etc – There’s a lot of information out there, far too much for any one person to keep in their head at once. So, we have extensive documentation that everyone can access, plus the Knowledge Base available to customers. Also, given that MySQL is a very open project, we have plenty of community members who write about their experiences.
- MySQL Sandbox (Link) – If you’re having a problem with a specific version of MySQL, we need to be able to reproduce it. Sandbox is by far the easiest way to create, modify and maintain test environments.
- Virtual Machines – Similarly, if you’re having a problem with a specific OS, we can usually reproduce it in a VM. I prefer VirtualBox for this, though there are many options.
- gdb – Crashes often mean core files, and gdb makes them easy to debug. Remember that if you do want to do your own debugging in gdb, you will need a copy of the mysqld binary that generated the core file.
- Test Servers – We also have a big ol’ pile of servers we use for testing various setups, if it is required.
And that’s about it. Personally, I like to keep my environment simple but flexible.
Backing up permissions for individual databases
Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:
shell> mysqldump -u root -p --databases db1 db2 ... > backup.sql
The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.
-- Grab the users with global permissions, -- with permissions to the databases you want, -- and tables/stored procedures in it. mysql> SELECT u.* INTO OUTFILE '/tmp/user.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.USER u WHERE u.Select_priv='Y' UNION SELECT u.* FROM mysql.USER u, mysql.db d WHERE d.Db IN('db1', 'db2', ...) AND d.USER = u.USER UNION SELECT u.* FROM mysql.USER u, mysql.tables_priv t WHERE t.Db IN('db1', 'db2', ...) AND t.USER = u.USER UNION SELECT u.* FROM mysql.USER u, mysql.procs_priv p WHERE p.Db IN('db1', 'db2', ...) AND p.USER = u.USER; -- Now, grab the database permissions, and those of objects in the database. mysql> SELECT * INTO OUTFILE '/tmp/db.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.db WHERE Db IN('db1', 'db2', ...); mysql> SELECT * INTO OUTFILE '/tmp/tables_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.tables_priv WHERE Db IN('db1', 'db2', ...); mysql> SELECT * INTO OUTFILE '/tmp/procs_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM mysql.procs_priv WHERE Db IN('db1', 'db2', ...);
Then, re-loading the permissions onto the new server is simple:
mysql> LOAD DATA INFILE '/tmp/user.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.USER; mysql> LOAD DATA INFILE '/tmp/db.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.db; mysql> LOAD DATA INFILE '/tmp/tables_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.tables_priv; mysql> LOAD DATA INFILE '/tmp/procs_priv.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' INTO TABLE mysql.procs_priv;
All up, a few queries to account for everything, but pretty easy to include in your backup/restore process. For further development, you could put the database list in a variable, so that you only need to change it on one line, rather than 6.
A Brief Introduction to MySQL Performance Tuning
Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.
Server Variables
For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.
For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.
Other variables that are worth investigating for performance tuning are:
query_cache_size – This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.
innodb_log_file_size – Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.
sort_buffer_size – Another one that shouldn’t be set too large. Peter Zaitsev did some testing a while back showing that increasing sort_buffer_size can in fact reduce the speed of the query.
Server Hardware
There are a few solid recommendations for improving the performance of MySQL by upgrading your hardware:
- Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM. At this point in time, InnoDB does have issues scaling past 8 cores, so you don’t need to go out of your way to have lots of processors.
- Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.
- If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can seperate your data files, OS files and log files onto different physical disks.
Query Tuning
Finally, though probably the most important, we look at tuning queries. In particular, we make sure that they’re using indexes, and they’re running quickly. To do so, turn on the Slow Query Log for a day, with log_queries_not_using_indexes enabled as well. Run the resulting log through mysqldumpslow, which will produce a summary of the log. This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.
Have fun!
Don’t Quote Your Numbers
It’s a fairly simple rule, and something that should be obeyed for your health and sanity.
There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.
Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:
mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G *************************** 1. ROW *************************** id: 1 select_type: SIMPLE TABLE: NULL TYPE: NULL possible_keys: NULL KEY: NULL key_len: NULL REF: NULL ROWS: NULL Extra: Impossible WHERE noticed after reading const TABLES 1 ROW IN SET (0.00 sec) mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999', '9999999999999999999999')\G *************************** 1. ROW *************************** id: 1 select_type: SIMPLE TABLE: a TYPE: ALL possible_keys: PRIMARY KEY: NULL key_len: NULL REF: NULL ROWS: 655360 Extra: USING WHERE 1 ROW IN SET (0.00 sec)
Note that you only run into it when you quote multiple large numbers.
Anyway, the long and the short of this post is: if at all possible, don’t quote numbers. MySQL will love you for it.
The \G modifier in the MySQL command line client
A little publicized, but exceedingly useful feature of the MySQL command line client is the \G modifier. It formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’.
For example, checking the master status:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000193 | 7061 | | | +------------------+----------+--------------+------------------+ 1 ROW IN SET (0.00 sec) mysql> SHOW MASTER STATUS\G *************************** 1. ROW *************************** File: mysql-bin.000193 POSITION: 7061 Binlog_Do_DB: Binlog_Ignore_DB: 1 ROW IN SET (0.00 sec)
Now try this for the much larger SHOW SLAVE STATUS. Or for the enormous SHOW ENGINE INNODB STATUS.
As you can see, this is a handy option to make your console output much easier to read.
Upgrading MySQL with minimal downtime through Replication
Problem
With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:
- Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
- mysql_upgrade: A much faster method, but it can still be slow for very large data sets.
I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.
Preparation
First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ‘stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:
- If you haven’t already, enable Binary Logging on S1. We will need it to act as a replication Master.
- Add an extra bit of functionality to your backup procedure. You will need to store the Binary Log position from when the backup was taken.
- If you’re using mysqldump, simply add the –master-data option to your mysqldump call.
- If you’re using InnoDB Hot Backup, there’s no need to make a change. The Binary Log position is shown when you restore the backup.
- For other backup methods, you will probably need to get the Binary Log position manually:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; (Perform backup now...) mysql> UNLOCK TABLES;
Once you have a backup with the corresponding Binary Log position, you can setup S2:
- Install MySQL 5.1 on S2.
- Restore the backup from S1 to S2.
- Create the Slave user on S1.
- Enter the Slave settings on S2. You should familiarise yourself with the Replication documentation.
- Enable Binary Logging on S2. We’ll need this during the upgrade process.
- Setup S2 as a Slave of S1:
- If you used mysqldump for the backup, you will need to run the following query:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password';
- For any other method, you’ll need to specify the Binary Log position as well:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.nnnnnnn', MASTER_LOG_POS=mmmmmmmm;
- If you used mysqldump for the backup, you will need to run the following query:
- Start the Slave on S2:
mysql> START SLAVE;
The major pre-upgrade work is now complete.
Upgrade
Just before beginning the upgrade, take a backup of S2. For speed, I’d recommend running the following queries, then shutting down the MySQL server and copying the data files for the backup.
mysql> STOP SLAVE; mysql> SHOW MASTER STATUS;
Once the backup is complete, restart S2 and let it catch up with S1 again.
When you’re ready to begin the upgrade, you will need a minor outage. Stop your application, and let S2 catch up with S1. Once it has caught up, they will have identical data. So, switch your application to using S2 instead of S1. Your application can continue running unaffected while you upgrade S1 server.
- Stop the Slave process on S2:
mysql> STOP SLAVE; - Stop S1.
- Upgrade S1 to MySQL 5.1.
- Move the S1 data files to a backup location.
- Move the backup from S2 into S1′s data directory.
- Start S2.
- Setup S1 as a Slave to S2, same as when we made S2 a Slave of S1.
- Let S1 catch up with S2. When it has caught up, stop your application, and make sure S1 is still caught up with S2.
- Switch your application back to using S1.
Complete! Hooray! You just need to run a couple of queries on S1 to clean up the Slave settings:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='';
Conclusion
You can keep the outage to only a few minutes while performing this upgrade, removing the need for potentially expensive downtime. If you need the downtime to be zero, you probably want to be looking at a Circular Replication system, though that’s getting a little outside of this blog post.
Permissions by interface on the local server
I had an issue come up recently that involved some confusion over permissions for the same user, connecting through different interfaces. For example, say you have a server with the public IP address of 192.168.0.1. You could connect to it from the local machine using the following commands:
shell> mysql -h localhost # Connects through the socket file shell> mysql -h 127.0.0.1 # Connects through the loopback interface shell> mysql -h 192.168.0.1 # Connects through the network interface
They all connect to the local server, but they can all have different permissions. Here are a couple of rules to make your life easier:
- Don’t use @127.0.0.1, unless you absolutely can’t use the socket file for some reason. Connecting through @localhost is usually faster than the loopback device, and it’s easier to type.
- Only connect through the network interface if you’re planning on moving the application to a different server later on.
That’s all.
Replication Checksumming Through Encryption
February 16, 2009 · Posted in MySQL · 9 CommentsProblem
A problem we occasionally see is Relay Log corruption, which is most frequently caused by network errors. At this point in time, the replication IO thread does not perform checksumming on incoming data (currently scheduled for MySQL 6.x). In the mean time, we have a relatively easy workaround: encrypt the replication connection. Because of the nature of encrypted connections, they have to checksum each packet.
Solution 1: Replication over SSH Tunnel
This is the easiest to setup. You simply need to do the following on the Slave:
shell> ssh -f user@master.server -L 4306:master.server:3306 -N
This sets up the tunnel. slave.server:4306 is now a tunnelled link to master.server:3306. So now, you just need to alter the Slave to go through the tunnel:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO master_host='localhost', master_port=4306; mysql> START SLAVE;
Everything else stays the same. Your Slave is still connecting to the same Master, just in a different manner.
This solution does have a couple of downsides, however:
- If the SSH tunnel goes down, it won’t automatically reconnect. This can be fixed with a small script that restarts the connection if it fails. The script can be added to your init.d setup, so it automatically opens on server startup.
- If you use MySQL Enterprise Monitor, it won’t be able to recognize that the Master/Slave pair go together.
Solution 2: Replication with SSL
Replication with SSL can be trickier to setup, but it removes the two downsides of the previous solution. Luckily, the MySQL Documentation Team have done all the hard work for you.
- Step 1: Create the certificates
- Step 2: Setup the servers to recognize the certificates
- Step 3: Change the Slave to use SSL
Conclusion
If you’re seeing corruption problems in your Relay Log, but not in your Master Binary Log, try Solution 1. It’s quick to setup and will determine if encryption is the solution to your problem. If it works, setup Solution 2. It will take a little bit of fiddling around, but is certainly worth the effort.
Case Insensitive REPLACE() for MySQL
February 15, 2009 · Posted in MySQL · 2 CommentsOne request I occasionally see is for a case insensitive version of REPLACE() for MySQL. I wrote this a while back, but here it is now for all of you to play around with. It uses a basic naïve string search algorithm, so can be slow under some circumstances.
DELIMITER $$ DROP FUNCTION IF EXISTS `replace_ci`$$ CREATE FUNCTION `replace_ci` ( str TEXT,needle CHAR(255),str_rep CHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN DECLARE return_str TEXT DEFAULT ''; DECLARE lower_str TEXT; DECLARE lower_needle TEXT; DECLARE pos INT DEFAULT 1; DECLARE old_pos INT DEFAULT 1; SELECT LOWER(str) INTO lower_str; SELECT LOWER(needle) INTO lower_needle; SELECT locate(lower_needle, lower_str, pos) INTO pos; WHILE pos > 0 DO SELECT concat(return_str, substr(str, old_pos, pos-old_pos), str_rep) INTO return_str; SELECT pos + CHAR_LENGTH(needle) INTO pos; SELECT pos INTO old_pos; SELECT locate(lower_needle, lower_str, pos) INTO pos; END WHILE; SELECT concat(return_str, substr(str, old_pos, CHAR_LENGTH(str))) INTO return_str; RETURN return_str; END$$ DELIMITER ; SELECT replace_ci( 'mySQL', 'M', 'M' );
It’s also available on MySQL Forge.
Gary Pendergast, noun: Gamer and developer. MySQL, WordPress, web and music geek. Makes interesting things at Automattic. Can be observed in his natural habit at pento.net, or usually in Melbourne, Australia.
My Photo Blog
