Why You Want to Switch to MySQL 5.1

Posted in: Technical Track

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

    • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
    • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.

    • InnoDB AUTO_INCREMENT lock handling. Simplistically, you can change the locking behavior of AUTO_INCREMENT with InnoDB tables to make your application more scalable. There are a lot of details in how this is implemented and when you would want to change the locking mode versus when you would not, so I will leave it to the reader to check out the very thorough documentation.

These last two items do not have anything to do with online operations, but “Two words: online operations” makes a much better soundbyte than if I had added these last two into the mix.

  • Prepared statements now work with the query cache. Mostly. Check the documentation for the limitations, but I will answer the big question everyone has; “Are the statements cached only with the expansion in place? Or is the prepared statement cached with the unknown variable parameter?” The answer is “textual calls of prepared statements are cached without query expansion, but binary calls of prepared statements are cached with query expansion.”
    If that does not make sense, I assure you, the documentation will.
  • INFORMATION_SCHEMA.PROCESSLIST. This one is also not an online operation, but gone are the days of using
    for $i in `mysql -e "show full processlist" | grep [something] | cut -f1`
    do
    mysql -e "kill $pid"
    done

    in order to kill a certain class of queries (ie, queries in Sleep status, queries on a database, queries from a user). And of course seeing a distinct list of who is connected is easier.

    Here is how it works; just as you would expect. Here is a SHOW FULL PROCESSLIST, followed by a read of the PROCESSLIST table, followed by showing the schema — note that the “Info” column is longtext, so the full query will be shown — followed by an actual use case of selecting some columns based on a filter:

    mysql> SHOW FULL PROCESSLIST\G
    *************************** 1. row ***************************
         Id: 4
       User: root
       Host: localhost:4213
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: SHOW FULL PROCESSLIST
    1 row in set (0.02 sec)
    
    mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST\G
    *************************** 1. row ***************************
         ID: 4
       USER: root
       HOST: localhost:4213
         DB: NULL
    COMMAND: Query
       TIME: 0
      STATE: preparing
       INFO: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
    1 row in set (0.02 sec)
    
    mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.PROCESSLIST\G
    *************************** 1. row ***************************
           Table: PROCESSLIST
    Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
      `ID` bigint(4) NOT NULL DEFAULT '0',
      `USER` varchar(16) NOT NULL DEFAULT '',
      `HOST` varchar(64) NOT NULL DEFAULT '',
      `DB` varchar(64) DEFAULT NULL,
      `COMMAND` varchar(16) NOT NULL DEFAULT '',
      `TIME` bigint(7) NOT NULL DEFAULT '0',
      `STATE` varchar(64) DEFAULT NULL,
      `INFO` longtext
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.01 sec)
    
    mysql> SELECT id,host,time FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user='root';
    +----+----------------+------+
    | id | host           | time |
    +----+----------------+------+
    |  4 | localhost:4213 |    0 |
    +----+----------------+------+
    1 row in set (0.02 sec)

I must have missed any hype about the MySQL 5.1 Use Case Competition, because when I was on the MySQL website today I saw a banner for it and clicked it, wondering what it was.

In case you are wondering too, it is a contest where you submit your use case for MySQL 5.1 — your review can be positive or negative. You have a chance to win a free pass to the 2009 MySQL User Conference.

At the end of that article they have a list of some of the features they are working on, taken directly from Jay Pipes’s summary of 5.1 features, MySQL 5.1 Article Recap:

  • Partitioning
  • Row-Based Replication
  • The Plugin interface
  • Event Scheduler
  • Logs on demand/table logging
  • XML functions
  • Other improvements

Only the logging issue is on my list (although I believe the PROCESSLIST table came about because of the plugin interface, so maybe that’s two). The online ALTER TABLE is a feature that has been so often considered the Achilles’ heel of MySQL that I am very surprised it is not on the list.

Interested in working with Sheeri? Schedule a tech call.

13 Comments. Leave new

Online Logging FTW « MySQL-HA
August 10, 2008 12:19 pm

[…] Posted in mysql by mtaylor on the August 10, 2008 I was just reading Sheeri’s post Why You Want to Switch to MySQL 5.1, and my favorite 5.1 feature jumped out at me. Online, table-based […]

Reply
Mark Callaghan
August 10, 2008 1:24 pm

For the cases I have tested, changing the default value of a column is online in 5.0, but it is not documented.

For the slow query log, I also want to rotate it and 5.1 does not do that when FLUSH LOGS is run. If you want it, update the feature request — http://bugs.mysql.com/bug.php?id=38702

Reply

Except table-based logging to the slow log reverts to one-second granularity instead of microsecond-precision.

Useless.

Reply

Baron,

That’s only useless for people who depend on microseconds. In reality there are not many organizations who use it, though it is very handy for those who do. Perhaps it’s useless to you, but it’s not useless to 95% of MySQL users who are not depending on microsecond precision.

Reply
Matthew Montgomery
August 11, 2008 12:10 pm

I wrote a stored procedure to find and kill all queries of non-SUPER or system users running longer than N seconds using the information_schema’s PROCESSLIST and USER_PRIVILEGES tables. The technique can be adapted to find queries matching almost any criteria.

See: http://forge.mysql.com/tools/tool.php?id=106

Reply
Log Buffer #110: A Carnival of the Vanities for DBAs
August 15, 2008 11:47 am

[…] here at home, Sheeri Cabral’s item on why you want to switch to MySQL 5.1. She argues that additions such as online ALTER TABLE, online table-based logging, and […]

Reply
End of the Series ?Using Feature XXX in MySQL? 5.1 | Integribase.com
September 21, 2008 8:07 pm

[…] Why you want to switch to MySQL 5.1 (Sheeri Cabral) […]

Reply

Online alter table is still going to be a problem, this only fixes a couple of cases but doesn’t do anything for the more common need of adding/removing columns.

Reply
How to Stop Hating MySQL
November 12, 2008 9:01 pm

[…] MySQL on Debian or Ubuntu? Recent Commentsshow / hide Davy on Why You Want to Switch to MySQL 5.1Keith Murphy on Overview of Transaction Logging in MySQLResult_cache blocking « OraStory on […]

Reply
Propiedad Privada » Blog Archive » Enlaces variados de la semana - PHP Y MySQL
November 14, 2008 5:36 pm

[…] Un listado de buenos motivos para cambiar a MySQL 5.1 […]

Reply
Olaf van der Spek
November 15, 2008 6:12 pm

> Perhaps it’s useless to you, but it’s not useless to 95% of MySQL users who are not depending on microsecond precision.

Eh, who’s still counting query times in seconds nowadays?
Higher resolution is definately needed.

Reply

Olaf — I agree that higher resolution is needed. But as I said, 95% of MySQL users have their slow query log set to 4 or greater (ie, saving only queries that took 5+ seconds).

Also, I forgot the best reason to switch to 5.1 — SHOW CONTRIBUTORS!!

Reply
If you can’t say something nice… » Karl Katzke | PHP, Puppies, and other Geekery
December 19, 2008 12:24 pm

[…] article by Sheeri Cabral: Why you Want to Switch to MySQL 5.1. The 5.1 branch has gotten a bad rap, but there’s a few things in there like the table change […]

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *