MySQL Admin: Configuring InnoDB and Installing MySQL as a Windows Service

Exclusive offer: get 50% off this eBook here
MySQL Admin Cookbook

MySQL Admin Cookbook — Save 50%

99 great recipes for mastering MySQL configuration and administration

$29.99    $15.00
by Daniel Schneller Udo Schwedt | September 2010 | Cookbooks MySQL Open Source

In this article, by Daniel Schneller & Udo Schwedt, authors of MySQL Admin Cookbook, we will cover:

  • Configuring the InnoDB redo log
  • Understanding and configuring important MySQL and InnoDB timeout options
  • Adjusting table and database name letter case handling for better platform independence
  • Installing MySQL as a Windows service with custom options

 

MySQL Admin Cookbook

MySQL Admin Cookbook

99 great recipes for mastering MySQL configuration and administration

  • Set up MySQL to perform administrative tasks such as efficiently managing data and database schema, improving the performance of MySQL servers, and managing user credentials
  • Deal with typical performance bottlenecks and lock-contention problems
  • Restrict access sensibly and regain access to your database in case of loss of administrative user credentials
  • Part of Packt's Cookbook series: Each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

Read more about this book

(For more resources on MySQL, see here.)

Configuring the InnoDB redo log

In order to prevent the transactional nature of InnoDB from completely thwarting its performance, it implements what is called the redo log.

In this recipe, we will present the relevant settings to (re-)configure a database server's redo log.

Getting ready

As the redo log setup is a part of the server configuration, you will need an operating system user account and sufficient rights to modify the server's configuration file. You will also need rights to restart the MySQL service because the redo log cannot be reconfigured on the fly.

Moreover, an administrative MySQL user account is required to prepare the server for the shutdown, necessary as part of the procedure.

Caution:
As this recipe will modify the configuration of parameters critical to data integrity, you should make a backup copy of the configuration file before editing it!

How to do it...

  1. Connect to the server using your administrative account.
  2. Issue the following command:

    mysql> SET GLOBAL innodb_fast_shutdown=0;
    Query OK, 0 rows affected (0.00 sec)

  3. Verify the setting like this:

    mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';

  4. Log off from MySQL and stop the MySQL server.
  5. Locate the MySQL configuration file, usually called my.cnf or my.ini (on Windows) and open it in a text editor.
  6. Locate the following parameters in the [mysqld] section (you values will vary, of course):

    [mysqld]
    ...
    innodb_log_group_home_dir=/var/lib/mysql/redolog
    innodb_log_file_size=32M
    innodb_log_buffer_size=64M
    innodb_log_files_in_group=2
    ...

  7. Edit the above configuration settings to their new values. If you require help on how to find suitable values, see the There's more... section of this recipe.
  8. Save the configuration file.
  9. Navigate to the directory configured for innodb_log_group_home_dir. If there is no such setting in your configuration file, navigate to MySQL's data directory that is then taken as the default.
  10. Move the files whose names start with ib_logfile to a backup location. Do not copy them; they must be removed from their original location.
  11. Restart the MySQL server.
  12. Verify that new files are created as you configured them:

    $ ls -l /var/lib/mysqld/redolog

If you do not see the new files appear and the server does not start up correctly, check the MySQL error log for messages. Usually, the only thing that can go wrong here is that you either mistyped the directory name or did not actually remove the previous ib_logfile files. To restore everything back to the original configuration, restore your configuration file from the backup and restore the ib_logfile files you moved out to the backup to their original location.

What just happened...

By setting innodb_fast_shutdown to 0, you told the server to finish writing any pending changes to the disk before actually exiting. This makes sure there are no remaining transactions in the current redo logs that could get lost when these files are replaced. After that you could change the configuration to new values, possibly using a different number of files and different sizes.

Then, before restarting, you could move the old redo log files out of the way. This is important because otherwise MySQL would complain about a mismatch between the settings file and the actual situation on disk. When it comes up finding no redo log files, it will create new ones with the settings just configured.

MySQL Admin Cookbook 99 great recipes for mastering MySQL configuration and administration
Published: March 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

Read more about this book

(For more resources on MySQL, see here.)

There's more...

Often when talking about transactions, the word rollback comes up. It means that if something goes wrong in the middle of a possibly complex data manipulation operation and it has to be aborted, the database server will safely restore everything back to the state it was in when that operation began, not leaving any data only partially deleted or modified.

The opposite term—rollforward or redoing—is less commonly used. It means that whenever the complex operation completes successfully, you are guaranteed that nothing short of actual hardware failure could lead to these changes being lost again.

This might appear obvious because one would expect the database server not to report anything as successful unless it was actually completely done. However, if that were so, write operations would become painfully slow, as the underlying I/O subsystem (generally meaning hard disks) is very often the bottleneck component.

To evade this potential performance problem, most transactional databases, InnoDB being no different, uses the concept of a transaction—or redo—log that allows it to more efficiently handle write operations without risking data integrity. The redo log works as a sort of scratch pad, containing information on what remains to be done to the data files. With its help, the server can optimize disk access to improve performance.

The ideal size for the redo log depends on the size and number of transactions the server has to process. Generally speaking, the log should be large enough to store any single transaction plus about 10 percent. As a rule of thumb, the total log size (the number of log files times their individual size) need not exceed about 50% of the InnoDB buffer pool size.

For more information on redo logs and how to determine a sensible size setting, visit http://mysqldump.azundris.com/archives/78-Configuring-InnoDB-An-InnoDB-tutorial.html for a detailed description of InnoDB log configuration.

Understanding and configuring important MySQL and InnoDB timeout options

MySQL's configuration file can contain a variety of different timeout settings, each responsible for a specific kind of operation or connection. In this recipe, we present a selection of these timeout settings and a suggested value to go along with each. The How it works... section has details on each value presented.

In general, the values suggested here should be appropriate for both MySQL versions 5.0 and 5.1. However, please note that any of these options may well vary for your environment, depending on what the requirements are; so please do not simply use these values verbatim.

Getting ready

To apply timeout configuration settings, you will need access to the MySQL configuration file—typically my.cnf or my.ini (on Windows)—and the rights to restart the server to have any changes made to the configuration take effect.

How to do it...

  1. Locate the MySQL configuration file and open it in a text editor.
  2. In the [mysqld] section, set up the following values. Some of the options may already be present; others will likely have to be added. Make sure each option only appears once.

    [mysqld]
    ...
    innodb_rollback_on_timeout
    innodb_lock_wait_timeout=50

    interactive_timeout=1200
    wait_timeout=28800

    net_read_timeout=30
    net_write_timeout=120
    ...

  3. Save the file.
  4. Restart the MySQL server.

How it works...

By setting the values as described in the above section, you tell MySQL to use different values than the defaults for the options mentioned. The new settings take effect with the server restart.

Setting values for innodb_rollback_on_timeout / innodb_lock_wait_ timeout

These two settings are probably the most important as regards the locking behavior in any MySQL setup. Starting with version 5.0.13, MySQL changed the rollback behavior in case a timeout occurred because a transaction could not acquire a lock for a row. This usually happens when another transaction is still working on that row, and this is to be expected in normal database operations. The database server rolls back the entire transaction in this case. Applications should be designed to respond to such conditions by retrying the entire transaction.

Prior to 5.0.13, this was the default behavior, maintaining the rule that a transaction either succeeds or fails and is rolled back completely as an atomic entity. In 5.0.13 and newer versions, the default was modified to roll back only the very last statement of the failing transaction instead, keeping the transaction open. While there is a reason behind that change (for long transactions, it can be faster to just retry the very last statement than wait for the rollback and try again from the start), it requires special precautions taken on the application level to be able to handle this very MySQL-specific scenario.

Setting the innodb_rollback_on_timeout option in the configuration file restores the more standard way of rolling back the whole transaction in case of a lock wait timeout. We strongly recommend enabling this option unless you are perfectly sure your application is aware of the MySQL-specific behavior configured as the default.

innodb_lock_wait_timeout defines the number of seconds a transaction will wait to acquire a necessary lock when a second transaction is working concurrently on the same records. The default value is 50 seconds and if the lock could not be acquired by then, a timeout error will occur and the transaction will be rolled back. Depending on how long the transactions in your setup typically take, this value often needs to be adjusted. If you regularly have bulk data operations that affect a lot of rows, you will want to increase this value. If on the other hand your system normally uses very short transactions, reducing this value may help you find out about problems with lock contention earlier.

Setting values for interactive_timeout / wait_timeout

interactive_timeout defines how long an interactive client connection can be idle before the server closes it automatically. 'Idle' in this context refers to the time between two statements being executed with no activity in between.

We recommend reducing this from the default value of 28,800 seconds (8 hours) to a much lower value like 1,200 seconds (20 minutes). This allows the server to close idle connections and conserve some resources.

The counterpart variable for non-interactive sessions, such as those from an application server's connection pool, is called wait_timeout and has the same semantics. Depending on your application, you might want to leave this setting on a higher value as most connection pools can be configured to release connections automatically depending on current load conditions.

Setting values for net_read_timeout / net_write_timeout

The protocol MySQL uses to handle communication between server and clients is rather limited in design, allowing only one operation to be carried out at a time. A side effect of this is that once a data transfer in either direction has started, there is no way for it to be interrupted in a controlled manner.

The net_read_timeout controls how long a piece of information can be sent from the client to the server, before the connection is aborted. This is usually not a problem—the default setting is 30 seconds. Under no regular circumstances will a communication in that direction take so long.

The net_write_timeout is more problematic because for large result sets, the default value of 60 seconds might be too short. This is especially true for clients that fetch the result in streaming mode, potentially performing time-consuming operations on each row retrieved, thereby making the overall operation take longer than the timeout.

The exact value required for your setup depends on how clients fetch data and work with it; so you will need to experiment and find a suitable value.

One caveat to consider with net_write_timeout is that it may lead to seemingly random failures of mysqldump like this:

mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table `tablename` at row: 935578

This can happen if the following conditions apply:

  • net_write_timeout is set to a low value
  • max_allowed_packet is set to a large value

Depending on the speed of the network over which mysqldump has connected and the size of the rows being dumped, it may be necessary to increase net_write_timeout much higher, at least to as long as it takes to transmit max_allowed_packet bytes over the network and write it to the output.

mysqldump is a regular client program and subject to the net_write_timeout setting. When the server sends rows to be dumped to mysqldump in chunks of up to max_allowed_packet bytes, depending on the network connection in between, this might take longer than net_write_timeout allows, making the server cut the connection even though nothing is really wrong. Increasing net_write_timeout for the mysqldump tool's session would remedy this, but unfortunately as of the time of writing there is no such setting for mysqldump. A workaround, if you encounter this problem, is to temporarily increase the global server net_write_timeout value:

$ mysql -uroot -e "SELECT @@GLOBAL.net_write_timeout AS oldvalue;
SET GLOBAL net_write_timeout=600;"
$ mysqldump ...
$ mysql -e "SET GLOBAL net_write_timeout=oldvalue;"

The first command will display the current value for net_write_timeout and then set it to 10 minutes. After that the mysqldump can take place. Finally, the old value is reset (just make sure you fill in the correct old value).

See MySQL Bug #46103 at http://bugs.mysql.com/bug.php?id=46103 for more details.

MySQL Admin Cookbook 99 great recipes for mastering MySQL configuration and administration
Published: March 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

Read more about this book

(For more resources on MySQL, see here.)

Adjusting table and database name letter case handling for better platform independence

MySQL is available for a variety of platforms—the major ones being Windows and Linux. Even though data files are compatible and can be transferred between platforms, and configuration mostly follows the same general principles, there is an important caveat to know about how different operating systems handle file names.

In this recipe, we will show you how to set up MySQL in a way such that it is much less likely to run into problems when moving data files between platforms. Because MySQL databases and tables correlate to file system objects (directories and files), differences in how the operating system (or rather the file system) handles file and directory names can lead to undesired effects, especially when working in heterogenous environments.

We generally recommend setting up all your MySQL servers as described in this recipe to prevent any problems.

Getting ready

You will need an operating system user account and sufficient rights to modify the server's configuration file. You will also need rights to restart the MySQL service because the name handling cannot be reconfigured on the fly.

Please note that for best results this setting should be applied before you start creating databases and tables on that server.

How to do it...

  1. Make sure MySQL is not running.
  2. Locate the MySQL configuration file, usually called my.cnf or my.ini (on Windows) and open it in a text editor.
  3. Locate the following parameter in the [mysqld] section. If it is not there, add it, otherwise edit it to match the value shown here:

    [mysqld]
    ...
    lower_case_table_names = 1
    ...

  4. Save the configuration file.
  5. Restart the MySQL server.

How it works...

MySQL table and database names are mapped to file system names. Most Unix-like platform file systems are case sensitive, meaning that the two files TableA and tablea are different from each other. On Windows, these two names will refer to the very same file.

Setting the lower_case_table_names configuration to 1 tells MySQL to always convert any database or table names to lowercase letters, both when creating and using them in SQL statements. This will ensure that no matter what casing any SQL statements use, it will always affect the same tables.

This is especially useful in replication scenarios where you replicate between master and slave machines using different operating systems.

Manually configuring this setting is highly recommended because depending on which platform MySQL is run on, the default setting will vary!

The only downside of setting up MySQL in this way is that the output of SHOW TABLES or SHOW DATABASES commands do not preserve the casing in which databases or tables were created, but this is merely a cosmetic issue.

Installing MySQL as a Windows service with custom options

While for development purposes it can be very handy to have MySQL run as a console application on Windows, for regular operations a background service is the option to go for. It has the advantage of starting up and shutting down automatically with Windows without the need for a user to log in to the machine.

In this recipe, we will show you how to install MySQL as a Windows service manually from the ZIP distribution available from the MySQL homepage and specify a custom configuration file.

Getting started

Naturally, this is a Windows-only recipe. You will need a Windows user account with administrative privileges to register a new Windows service. Moreover, we assume you have already downloaded the MySQL distribution called "Without installer (unzip in C:\)".

Make sure you choose the release matching your operating system (32 or 64 bit). In this recipe we will be using MySQL 5.1 from http://dev.mysql.com/downloads/mysql/5.1.html.

Be advised that security software on your computer might interfere with the installation of a Windows service, as some malicious software may try to hook into the system that way. If you encounter problems, you may have to disable anti-virus programs and other security products for the duration of the process. Do not forget to re-enable them when you are finished with the MySQL service setup!

How to do it...

  1. Unpack the downloaded ZIP file. Put the contents in c:\mysql\5.1.xx\service(replacing xx with the actual release number you are using).
  2. On a command prompt (cmd.exe) enter the following commands to install the service. Make sure to enter the full path, instead of changing the working directory with the cd command:

    c:\> c:\mysql\5.1.xx\service\bin\mysqld.exe --install MySQL51
    --defaults-file=c:\mysql\5.1.xx\service\my.ini

  3. Edit the my.ini configuration file specified in the command above to meet your requirements.
  4. Start the service and verify its status using these commands:

    c:\> sc start MySQL51

    c:\> sc query MySQL51

You should see STATE: 4 RUNNING in the status output of the second command.

How it works...

The MySQL server binary executable file mysqld.exe contains the necessary functionality to register itself with Windows as a background service. There are two options you should provide: --install and --defaults-file. The first one will specify the name of the new service to be created, MySQL51 in this case. The latter is used to define which configuration file the service will read its settings from.

Note that after the --install parameter, only a single parameter may follow. While this could be any parameter the MySQL server accepts, using the --default-file gives you the greatest flexibility, as you can put all other required settings there.

There's more...

Apart from being able to run without a user having to log in to the server machine, services can define dependencies; so, for example, you could make sure your application server only gets started when the database is ready. For details on how to do this, refer to Microsoft Knowledge Base article #193888 at http://support.microsoft.com/kb/193888.

As services do not have access to a console or the graphical user interface in general, any problems encountered while starting the service will not tell you anything about the cause. On Windows, MySQL will report problems to the system event log, viewable from the control panel, and to the MySQL error log file, usually located in the data directory with a name composed from the machine name and a .err extension.

Should your service fail to start, inspect that log file to get an idea of what is wrong. To make sure your configuration file is OK, we recommend you to start the MySQL daemon once from the command line like this:

c:\> c:\mysql\5.1.xx\service\bin\mysqld.exe --defaults-file=c:\
mysql\5.1.xx\service\my.ini --console

This will allow you to see any potential problems right away before installing the service.

Summary

In the above article we have covered:

  • Configuring the InnoDB redo log
  • Understanding and configuring important MySQL and InnoDB timeout options
  • Adjusting table and database name letter case handling for better platform independence
  • Installing MySQL as a Windows service with custom options

Further resources on this subject:


About the Author :


Daniel Schneller

Daniel Schneller works as a software developer, database administrator, and general IT professional for an independent software vendor in the retail sector. After successfully graduating from the University of Cooperative Education in Heidenheim, Germany with a degree in Business Computer Science, he started his career as a professional software developer, focused on the Microsoft technology stack. In 2002 he started focusing on enterprise-level Java development and has since gained extensive knowledge and experience implementing large scale systems based on Java EE and relational databases, especially MySQL since version 4.0.

Currently he is mostly involved with the ongoing development of framework-level functionality, including customization and extension of an ORM-based persistence layer. He is involved in different open source projects such as FindBugs, Eclipse, and Checkstyle and infrequently blogs about Java, MySQL, Windows, Linux and other insanities at http://www.danielschneller.com.

Udo Schwedt

Udo Schwedt is head of the Java architecture team and deputy head of the Java development department at the IT service provider for Germany's market leader in the Do-It-Yourself sector. After finishing his studies at RWTH Aachen, Germany with a degree in Computer Science, he started his career as a professional Java developer in a leading software company in the banking sector. In 2003, he joined his current company as a framework developer, subsequently taking the role of a lead architect.

Both authors are involved in a large scale international software project, which encompasses development of a Java-based merchandise management software solution. This decentralized multi-platform environment is based on more than 500 decentralized MySQL server instances with more than 5,500 replication slaves. Daniel and Udo are responsible for configuration and management of this infrastructure.

Books From Packt

WordPress 3.0 jQuery
WordPress 3.0 jQuery

Drupal 7
Drupal 7

Drupal 7 First look
Drupal 7 First look

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

MySQL for Python
MySQL for Python

ChronoForms 1.3 for Joomla! site Cookbook
ChronoForms 1.3 for Joomla! site Cookbook

High Availability MySQL Cookbook
High Availability MySQL Cookbook

Moodle 1.9 Theme Design: Beginner's Guide
Moodle 1.9 Theme Design: Beginner's Guide

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
n
L
V
T
K
a
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software