A Hard Look Into Replication

For some time now I've been struggling with a slave that invariably stays behind its master. I have been looking at every detail I can possibly think and in the process discovered a number of replication details I wasn't aware until now. I haven't too much information about them in the documentation, but they can affect the way you look at your slaves.

Seconds Behind Master

This is the first value that to look at when evaluating replication, most of the monitoring systems I know of rely on it. According to the manual:

When the slave SQL thread is actively running
(processing updates), this field is the number of
seconds that have elapsed since the timestamp of the
most recent event on the master executed by that thread.

In fast networks, most of the time, this is an accurate estimate of replication status, but many times you'll see this value to be in the ten of thousands of seconds and not a minute later it falls back to 0. In a chain of master and slaves, the number on the last slave measures how far behind it is from the master at the top of the chain. Under heavy load on the top master, it can even go back and forth wildly. Because of this, I've learned not to trust this value alone. It is a good idea then to compare other variables as well. For example: Master_Log_File / Exec_Master_Log_Pos vs. Relay_Master_Log_File / Read_Master_Log_Pos. The 2nd pair will point to the last statement executed on the slave in relation to the master's binary log file (keep in mind that the statements are actually being executed from the Relay Log file). The first one, will point to the latest statement read from the master and being copied into the Relay Log. Checking all these variables in context will tell you the real status of the slaves.

Sidenote: These are the variables in the slave snapshot in sar-sql, let me know which ones do you monitor to make your slaves are healthy.

Binary Log Format

This item is important and encompasses which format you choose for replication. In the case I am working on, it was set to STATEMENT. An initial look, revealed that the master had bursts of very high traffic, after which the slaves started lagging behind significantly. Most likely (still trying to prove this), because a number of big INSERTs and UPDATEs are being processed at the same time on the master, and inevitably are serialized on the slaves. Without going into the details, switching to ROW solved most of the delays.

Although binlog_format is a dynamic variable, the change will not take place right away. It will be applied to newly created threads/connections. Which means that if you have connection pooling in place  (very common with web applications) , it might take a while until the change actually happens. If you want to force the change as soon as possible, you will have to find a mechanism friendly to your particular environment to regenerate the connections.

Another issue that came up is that, in a replication tree, no matter what the binlog_format variables establishes for the slaves in the middle of the chain. The binary log format of the top master will be used across the chain.

Status Variables and Logs

As you may know, SHOW GLOBAL STATUS includes a number of counters that count how many times a command type was issued. So Com_Insert will tell you how many INSERTs were issued since the server is up. That is, without counting the replication thread. So you may issue thousands of INSERTs on the master, and while Com_Insert will be updated accordingly, it won't change in the slave. Very frustrating when I tried to evaluate if the INSERT rate in the slave matched the rate on the master. The general log has a similar issue, it won't record any statement executed by the slave threads.

Conclusion

Although I understand where these limitations may originate from the way MySQL replication works, it does frustrate me since it really limits the type of tests and diagnostics that can be set up to find what's causing the issues on these servers.

I have to point out that MySQL Sandbox is an invaluable tool to test the different replication scenarios with minimum preparation work.

sar-sql New Alpha Release

I just uploaded a new tarball for sar-sql containing a few bug fixes, overall code improvements. I also added options to get a partial snapshot of SHOW SLAVE STATUS and SHOW MASTER STATUS. I chose only a few columns to avoid over complicating the project.

I plan one more round of heavy code changes, but no new features until I can stabilize the code enough to release it as beta.

Feel free to visit the project page in Launchpad to comment on the Blueprints, report new bugs and participate through the Answers section.

Thank you very much to Patrick Galbraith who provided some ideas on the best way to solve some of the coding issues.

Enjoy the download.

About CSV Tables

As most of MySQL users, I have often ignored what I'd like to call the minor storage engines. MYISAM, InnoDB and NDB (aka MySQL Cluster) are well covered in several articles; but what about engines like CSV or ARCHIVE? As part of some internal projects, we have been playing around with these 2 with some interesting results. On this article I'll concentrate on CSV.

Scenario

Currently we have a few servers that are storing historical data that will eventually be migrated into Oracle. Two things need to happen until we can finally decommission them: 1) export the data to CSV so it can be imported in bulk into Oracle and 2) keep the data online so it can be queried as needed until the migration is finalized. I thought it would be interesting if we could solve both issues simultaneously and decided to try the CSV engine. Here's a description of the process.

Understanding CSV Engine

The CSV engine, as far as I can tell, was and example storage engine that was included with earlier MySQL versions to illustrate the storage engine API. Since the data is stored in plain text files there are some limitations that need to be considered before using it:

1. No support for indexes
2. No NULL columns are allowed

Exporting Data To CSV

So, my first step was to determine what would it take to export data from a regular table to CSV. These are the basic steps:

1. Create the seed for the CSV table based on an existing table

CREATE TABLE test LIKE test_csv;

If you feel adventurous, use CREATE TABLE LIKE ... SELECT ... in which case you may be able to skip the next 2 steps. The engine for the new table will be redefined at the end of the process.

2. Get rid of the indexes

ALTER TABLE test_csv DROP PRIMARY KEY, DROP KEY test_idx;

Modify this statement to include all existing indexes.

3. Get rid of NULL columns

ALTER TABLE test_csv MODIFY test_char VARCHAR(10) NOT NULL DEFAULT '', MODIFY test_date TIMESTAMP NOT NULL DEFAULT '0000-00-00';

The DEFAULT values need to be reviewed so the application makes no mistake that these should be NULL. Numeric values could be tricky since you may not find a suitable replacement for NULL. Adapt to your particular case.

4. Convert to CSV

ALTER TABLE test_csv ENGINE CSV;

This step will create an empty CSV file in the schema data directory.

5. Export the data

INSERT INTO test_csv SELECT * FROM test WHERE ...

This would allow you to export the portion of the data from an existing table into the CSV table/file.

At this point your data is all stored in a CSV file called test_csv.CSV under the data subdirectory that corresponds to the schema and the table can be queried as any other regular MySQL table, which is what I was looking for at the beginning of the project.

You could even update the table. If you need to load this file to any other application, just copy the file.

Keep in mind that we are talking about regular text files, so they are not adequate for big number of rows and frequent write operations.

Importing Data From CSV

If you have to import some CSV data from another application, as long as the data formatted properly, you can just create an empty table with the right columns and then copy the CSV file with the proper table name. Example:

In MySQL:

use test
CREATE TABLE test_import LIKE test_csv;

In the OS shell:

cp data.csv /var/lib/mysql/test/test_import.CSV

Now if you do: SELECT * FROM test_import LIMIT 2; it should show you the data on the first 2 lines of the CSV file.

Import Data Example

Many banks allow you to export the list of transactions from your online statement as a CSV file. You could easily use this file as explained above to consult and/or manipulate the data using regular SQL statements.

Conclusion

The CSV engine provides a very flexible mechanism to move data in and out of regular text files as long as you have proper access the data directories. You can easily generate these tables from existing data. You can also easily manipulate the data with any editor and (re) import it at will. I see it as an invaluable tool to move around information, especially in development and testing environments.

Do you have interesting use cases? I'd like to hear about them.