I have been a MySQL DBA now for about 2 years. In August, I started at Friendster, one of the largest MySQL installations in the world. The amount I've learned about MySQL in the short time I've been at Friendster is astounding. I am going to share little tidbits now and then that I think are important.
Column Types Fun
This is probably a duplicate from the MySQL Gotchas page, but it seems a pretty common error, so I'm reproducing it here.
If you come from an Oracle background, you might expect this:
create table (
somenumber int(11)
);
to be able to store 4294967296 losslessly, after all, 4294967296 is less
than 11 digits, right?
However, in MySQL, (11) part of the column definition is ignored.
"int(anything)" is an alias for just "integer" which is a 32-bit signed
value.
Hence, even if I call it, eg, int(12)...
mysql> create table iptest (
-> ip_address int(12)
-> ) ;
mysql> insert into iptest values (4294967296) ;
mysql> select * from iptest ;
+------------+
| 2147483647 |
+------------+
If you really only need 32 bits of unsigned goodness, try unsigned int.
I recommend leaving off the (XXX) entirely so as not to confuse yourself
into thinking you can store more digits than you actually can:
mysql> create table iptest (
-> ip_address integer unsigned
-> );
mysql> insert into iptest values (4294967296) ;
mysql> select * from iptest ;
+------------+
| 4294967295 |
+------------+
Even here, the value I've given it is one too large, but we're closer to
the truth. In this case, I need bigint.
In 4.0.x, an InnoDB database stores everything in one large tablespace. Well, you can define multiple tablespaces, but you don't really get to choose what goes where.
In MySQL 4.1.x, there is one InnoDB tablespace per table, where all data, indexes, and overhead data are stored. Hence, in theory, you can put each table on its own spindle. In fact, I cannot find a way to do this except to create multiple DATABASEs and store each database on its own spindle, then move tables into the appropriate database.
You should avoid large PKs in InnoDB, since every index stores the VALUE of the primary key as its leaf nodes. Use ints or some other small value.
InnoDB to MyISAM Replication... Is it transaction safe?
You are running InnoDB on your master. You are running MyISAM on the slave. Thus far, there's been no corruption due to transactions being rolled back on the master, but you're worried.
Do not worry! The master only writes binlog events (ie: things replicated to the slave) at the point where the transaction COMMITs its data. If the transaction does a ROLLBACK, no logs are written for that transaction.
Furthermore, the transaction writing is atomic. If you COMMIT transaction A, all its binlog events will be written before transaction B's events even if transaction B does its COMMIT while A's events are being written.
Hence, no corruption will happen on the MyISAM slave.
It is important to note that this does NOT mean you can mix and match InnoDB and MyISAM on the master and not get corruption. All MyISAM data modifications will happen before the transaction decides to ROLLBACK, and all the InnoDB data will be rolled back in such a case. Hence your MyISAM and InnoDB tables will be out of sync. This is a Bad Thing, yes.
Dealing with Large Volumes of Data
One thing about doing DBA work on a large volume of data is, you cannot do things simply anymore. Let's take an example: Suppose you have a messages table with a "deleted" column, whose value is '1' if the row corresponds to a deleted message. After some time, you want to clean these "deleted" rows out of your database to reclaim some space. You have a billion messages, and the machine is under constant load reading and writing messages. Maybe two million new messages per day.
Do you "delete from messages where deleted=1"? You might think so, if you work in a low-volume shop. Don't do it. Do some back-of-the-napkin calculations. If you can delete 1,000 messages per second optimally, and your server is under load, maybe you can only delete 100 messages per second. That means it'll take 2e+9 / 1e+2 = 2e+7 seconds to complete your delete operation. 2e+7 seconds is 231 days. Do you want your messages table to be locked for 231 days? Probably not. (If you assume row-level locking with multiviews into the data -- no matter how this is implemented by your RDBMS, MySQL or not, you won't have the space or time to complete this query)
So to do this delete, you'd better break it up into ranges. The odds are your "messages" table has an "id" column. Do a for loop over your min(id)..max(id) range, deleting (say) 1,000 rows at a time. You might be nice to your server by sleeping some number of seconds between each delete as well.
But what if you determine that actually, on average, you can only delete 20 messages per second? (The server is really loaded with read/write traffic). No problem, right? You just start the script running, and don't worry about it.
Oops. Better start doing that back-of-the-napkin calculation again. At that rate, you're only deleting about 1.8 million rows per day. You are adding two million new messages per day. Even if there's a pretty even clip of adding/deleting messages (say people delete 100% of their messages after a couple of days), you'll never catch up with your delete. Your data is growing without bound. You need faster hardware.
Good luck with all that. :)
Signs of Performance Problems
Suppose you look at your database server, and the CPU is pegged. There are five "mysqld" processes hogging 100% of your CPU. You do a "show processlist" on your MySQL server, and you see 20 or 30 simple little "select * from table where id=213438787" sorts of queries. That should be fast, right?
Interestingly, it seems many developers (even very good ones) will forget to index their columns or define primary keys. If the developer forgot to index that "id" column, and the table is small, all your table scans will show up as in-memory loops through data. In other words, pegged CPU.
Many DBAs will tell you that bad I/O results from table scans. This is just a result of tables on which table scans are usually done being large. If the table won't fit in memory, then yes, you will get disk thrashing.
As hardware gets faster, and memory becomes more bountiful, the rules of thumb change a bit. Back in the day of 128MB RAM being a lot of memory, table scans almost never could be done in-memory, and would almost invariably cause disk thrashing (hence, table scans cause disk thrashing, and disk thrashing indicates table scans). Now that 8GB of RAM is not only a possibility, but even affordable by a regular consumer, and given most tables are in the sub-gigabyte range, table scans can often be done in-memory.
Creating Replication Slaves
Suppose you have a complicated environment, and taking down your master is an onerous thing to do, but yet you have to create slaves every single day (maybe two or three times a day). What to do?
The MySQL manual says things like this:
There are several options. If you have taken a backup of the master at some point and recorded the binary log name and offset (from the output of SHOW MASTER STATUS ) corresponding to the snapshot, use the following procedure [some procedure follows]
In fact, though, you have no backups of your master. You have a slave of your master running at all times, because that's how you back up MySQL databases (if you don't, you should consider it).
So the actual process is quite simple once you have a configured slave.
- stop the MySQL instance on the slave
- tar up all MySQL files (include relay logs, binlogs, and auxiliary files, usually everything is in /var/lib/mysql but /etc/my.cnf may say otherwise)
- start the MySQL slave instance
- detar all those files on a new machine
- sanity check relay-bin.index, db-bin.index, relay-log.info to make sure paths are correct (if filesystems are identical across slaves, should be non-issue)
- start the MySQL instance on that new machine
If you think carefully through all the consequences of your actions, you'll see that now on the new machine, you'll have a new slave of the master running in the same position as the slave you took down. Assuming the new slave is fast enough, it will eventually catch up to the master, and you'll have two slaves of the master whereas you only had one before.
This is all well and good, but what if you want a new slave with only a subset of tables from the master?
- stop the slave SQL and I/O threads ("slave stop") on the slave
- get the binlog position from master.info
- dump the tables you're interested in
- start the slave SQL and I/O threads on the old slave ("slave start")
- load the tables on the new slave
- do a "change master" statement on the new slave to the position recorded above
You will probably also need to check the /etc/my.cnf on the new slave to be positive it's only attempting to replicate the tables you're interested in (if you're reading this page, you should already have done the legwork for this).
Changing a slave from one master to another
Suppose you have a server, MasterA? replicating to MidB? and MidC?, then finally a slave down on the end of the chain called SlaveD?, which is a slave to MidB?. You want to make SlaveD? a slave of MidC? instead of MidB?.
First, you must ensure that the most recent binlogs on MidB? and MidC? are the same. Try "mysqlbinlog binlogfile.X | head -100 > /nfsmnt/place/`hostname -s`.sql" on both MidB? and MidC?, then diff the two files. If they are the same, then you're good to go.
(If they're not, you need to stop the MasterA?, wait until MidB? and MidC? catch up, then stop MidB? and MidC?, then delete all binlogs and relay logs on both machines, then start them again)
Now all you need to do is simply stop SlaveD?'s slave thread, reconfigure the replication setup to point to MidC? at the same binlog file/pos, then restart the slave thread. Voila!
MySQL/InnoDB Is the Only RDBMS that implements all four SQL standard isolation modes?
I'm at the MySQL User's conference, and Arjen Lentz has claimed that MySQL's InnoDB is the only engine that implements all four modes. That's interesting. I forget which one he said they implement that no-one else does, but he does also say it's not very useful in real life since no-one uses it.
--
PhiloVivero