Diamond Notes

When doing large imports into a table it can be very helpful to do different optimizations to speed up the import.  Sometimes this can make a dramatic difference.  For example, just recently I imported 106 million rows into a MyISAM table.  Initially I just took the data and imported it into the server.  This took about 60 hours.  Then, because a mistake was made in the setup of the data, we had to re-import.  This time the data was sorted before importing.  While this operation took several hours, the import time dropped to one hour.  Sorting the data might not always be practical, but in this case it worked — and worked dramatically.

There are other things to do that can speed up data imports.  Sounds like good topics for future tips!!

In preparation for disaster recovery and for training purposes I asked our Junior DBA to document the innodb_force_recovery options in our internal Wiki.  I am modestly familiar with the command having used it a few times because of data corruption.  However, somehow, I completely missed something that I discovered when discussing his findings with the other DBA.  I am passing it along since some of you might not know of it.

If you are doing a large operation that is modifying a transactional table and something goes wrong and you end up aborting the operation mysql will normally roll back the operation.  Because this is a disk-bound operation it will be many times slower than the original transaction.

Solution is this: if you abort a large operation and it begins to roll back you can kill the entire server process, add the  innodb_force_recovery=3 line to your my.cnf file (the mysqld section) and restart the mysql server.  You can then do any cleanup necessary because the server doesn’t begin transaction rollback as it normally would.  If you were doing a large import that you cancled you could drop the table for example.   Then just remove the innodb_force_recovery line from my.cnf and restart the mysql server.  You should tail the error log as it there will be lots of complaining by the server because of what you did, but in ten or fifteen minutes you should be back online.  I would restart the server again just to make sure everything begins as normal but at that point you can consider yourself in business.  If you can restart the server (which might not always be possible) this is much faster than waiting for a long transaction to roll back.

You should test this in a controlled test environment before having to use it “in the field”.

Resource:   http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html

You can copy the three files  (table_name.frm, table_name.MYI, and table_name.MYD) that form a MyISAM table from one server to another and just add them to the appropriate database directory and they will “appear” in the new database.  However, with Innodb this is not possible.  To copy Innodb databases you either need to dump the database/tables involved and re-import or copy the entire dataset and tablespace files from one server to another.

One caveat about the MyISAM table copy, you should lock the table while copying it to keep the copy from being corrupt due to changes to the file during the copy operation.  Also, check and make sure your owner/permissions are the same after the copy.

Here is somethingthat you might be interested in.  If you are using Linux on your servers and you ssh to the server and then run the mysql client to access the server you can use a .my.cnf file to speed up access.  This is put in your home directory and is automatically parsed by the mysql client when it is invoked.  For example here is a really simple one:

[client]
user =username

password =x&r%@ldc

host = localhost

Now you can just type ‘mysql’ and it connnects you up automatically.  There are many other options you can put in here.  Not sure how this works on Windows — I don’t think it does.  Of course if you are using Windows as a desktop OS you can ssh to the server and then go from there.  The reference page for this is: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

There are several ways to count rows in a table.

SELECT count(*) from table_name;

or

SHOW TABLE STATUS  LIKE ‘table_name%’;

or

SELECT table_name, table_rows FROM information_schema.tables where table_name=’table_name’ and table_schema=’database_name’;

However, the last two methods of getting a row count are only approximate row counts for Innodb tables.  Keep that in mind.

I came up with an orginal *wink* idea of doing a small tip each day.  The post I just did about pagination gave me the idea.  I will do one tip a day as long as I can come up with something original.  Something short.  And I am soliciting input from the readership.  If YOU have an idea for a Tip of the Day shoot me an email at bmurphy AT paragon-cs.com.  If you have a previous post I can “reprint” with appropriate kudos and links I would love that too.