Q&A on MySQL 5.1

Listening to Sheeri's presentation on MySQL 5.1, I saw that there are a few questions left unanswered. I am listing here some of the questions that I found interesting, plus a few from an early webinar on the same topic.

Q: does Partitioning physically split data?

A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user.

Q: Can you set partitions to different servers?

A: No. Partitions are logical parts of one table within one server. Partitioning through the Federated engine is not supported.

Q: How efficient are Row-Based Replication operations compared to Statement based ones?

A: RBR is faster when the insert or update is the result of an expensive operation. Otherwise, the efficiency for insertion and deletion is roughly equivalent. Updates on multiple records are usually more expensive with Row-Based Replication.

Q: Is the event scheduler polluting the Error Log?

A: yes, unfortunately. But it has been fixed in 5.1.31 (See also Bug#38066. As you can see from the discussion in the bug report page, it was object of a long and intense discussion.

Q: Can you send email through the Event Scheduler?

A: No. But you can integrate it with a hack using MySQL Proxy through Federated tables.

Q: Is there an equivalent to SHOW FULL PROCESSLIST from the INFORMATION SCHEMA?

A: Yes. Actually, SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST is equivalent to SHOW FULL PROCESSLIST. See the manual

Q: Are partitions supported in replication?

A: Yes. Partitions are fully supported in replication. The only problem you may have is when using the "DATA DIRECTORY" and "INDEX DIRECTORY" clauses, if the slave does not have the same directory structure and OS privileges as the master.

Q: Is the event scheduler supported in replication?

A: Yes. The event definition is replicated but left inactive, and the effects of the event scheduler are replicated as any other statement. When promoting a slave to master, it is necessary to manually activate the events.

Q: What happened to RENAME DATABASE? Why is not available anymore?

A: RENAME DATABASE was a command implemented in the early stages of MySQL 5.1, for the specific purpose of helping the upgrade script to set the database name with the appropriate charset. It had a deadly side effect, though. It removed all the objects associated with the database. Thus, it was removed and renamed in such a way that nobody would use it for simply renaming a database.

Q: How does the slow query log work with microseconds?

A: To catch slow queries with duration measured in less than 1 second, you can set the long_query_time global variable to a fractional value. E.g. set global long_query_time=0.5; will enable queries taking more than 1/2 second to be logged to the slow queries log.