Pythian Group

Today’s contradiction:

MySQL has server variables named new and old.

The new variable can be set per-session and globally, and is dynamic. The old variable is not dynamic, and only global in scope. Both default to FALSE in MySQL 5.1.

According to the manual, the new variable:

was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility.

That same page notes the following about the old variable:

when old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17. That is, index hints with no FOR clause apply only to how indexes are used for row retrieval and not to resolution of ORDER BY or GROUP BY clauses.

That’s right — the old variable changes some index hint behavior to before 5.1.17, and the new variable is provided for backwards compatibility of a 4.0 function.

Perhaps now that they have already used the vague terms, MySQL will stick to descriptive names, such as old_index_hints and new_behavior (or, even better, specifying what that new behavior is. If not in the variable itself, at least in the manual!)

Do you have_community_features? I do!

SHOW GLOBAL VARIABLES LIKE 'have_community_features';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| have_community_features | YES   |
+-------------------------+-------+
1 row in set (0.00 sec)

I am pretty sure this is one of those variables that MySQL has put in as an unused placeholder, but for now, it is not even documented as unused (as are table_lock_wait_timeout, http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html, and a Google search restricted to the site dev.mysql.com turns up only one match, which is not useful: http://www.google.com/search?q=site%3Adev.mysql.com+have_community_features

I wonder if Read the rest of this entry . . .

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

That is the basic functionality of the InnoDB log files. Given this, let’s look at some of the different parameters and their ramifications.

innodb_log_files_in_group is set with a default of 2. The logfiles are written in a circular manner — ib_logfile0 is written first, and when it has reached its maximum size, then ib_logfile1 will be written to.

innodb_log_file_size is the size of each log file in the log group. The total, combined size of all the log files has to be less than 4 Gb (according to the MySQL manual). Because the logfiles contain changes in the buffer pool that have not been written to disk, the total, combined size of all the log files should not be more than the innodb_buffer_pool_size.

If all the log files in the group are full of changes that have not been written to disk, MySQL will start to flush dirty pages from the InnoDB buffer pool, writing the changes to disk. If the log files are small, changes will be written to disk more often, which can cause more disk I/O.

When InnoDB does a crash recovery, it reads the log files. If the log files are large, it will take longer to recover from a crash. If innodb_fast_shutdown is set to 0, the log files are purged when MySQL shuts down — larger files mean a longer shutdown time. The default for innodb_fast_shutdown is 1, which means that the log files are not purged before a shutdown. Starting in MySQL 5.0.5, you can set it to 2, which simulates a crash, and at the next startup InnoDB will do a crash recovery.

innodb_flush_log_at_trx_commit controls how often the log files are written to. A value of 0 causes the log files to be written and flushed to disk once per second. The default is 1, which causes the log buffer to be written and flushed to disk after every transaction commit. The value can also be set to 2, which causes the log buffer to be written after every transaction commit and flushes the log files to disk once per second. A value of 2 means that MySQL might think that some changes are written to the log file, but do not persist in the log file after an operating system crash, because the log file was not flushed to disk before a crash.

Note that some filesystems are not honest about flushing to disk, so even though you may have the default value of 1, your system may be acting as if it has a value of 2. Setting this parameter to 2 means that there will be less I/O, at the cost of not being able to recover data from a crash.

innodb_flush_method changes how InnoDB opens and flushes data and log files. See the manual for details; the end result is a tradeoff in I/O performance versus whether or not an operating system crash would leave the InnoDB log files in an inconsistent state.

innodb_log_buffer_size is the write buffer for InnoDB log files. The larger the buffer is, the less often the log files are written to. This can save I/O.