Offshore Software Outsourcing Company, offshore IT outsourcing services, offshore software development Russia

About ALTOROS: ALTOROS Systems is headquartered in Tampa, Florida and maintains an office near Boston, Massachusetts and technology development center in Belarus and Russia. ALTOROS specializes on providing value-added e-commerce and web-based software development and offshore software outsourcing services to emerging enterprises helping them successfully plan and implement business initiatives.

To use InnoDB tables in MySQL-Max-3.23 you MUST specify configuration parameters in the [mysqld] section of the configuration file my.cnf, or on Windows optionally in my.ini.

At the minimum, in 3.23 you must specify innodb_data_file_path where you specify the names and the sizes of datafiles. If you do not mention innodb_data_home_dir in my.cnf the default is to create these files to the datadir of MySQL. If you specify innodb_data_home_dir as an empty string, then you can give absolute paths to your data files in innodb_data_file_path. In MySQL-4.0 you do not need to specify even innodb_data_file_path: the default for it is to create an auto-extending 10 MB file ibdata1 to the datadir of MySQL. (In MySQL-4.0.0 and 4.0.1 the datafile is 64 MB and not auto-extending.)

But to get good performance you MUST explicitly set the InnoDB parameters listed in the following examples.

Starting from versions 3.23.50 and 4.0.2 InnoDB allows the last datafile on the innodb_data_file_path line to be specified as auto-extending. The syntax for innodb_data_file_path is then the following:

pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
... ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]

If you specify the last datafile with the autoextend option, InnoDB will extend the last datafile if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

instructs InnoDB to create just a single datafile whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out. If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another datafile:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Be cautious on filesystems where the maximum file-size is 2 GB! InnoDB is not aware of the OS maximum file-size. On those filesystems you might want to specify the max size for the datafile:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

A simple my.cnf example. Suppose you have a computer with 128 MB RAM and one hard disk. Below is an example of possible configuration parameters in my.cnf or my.ini for InnoDB. We assume you are running MySQL-Max-3.23.50 or later, or MySQL-4.0.2 or later. This example suits most users, both on Unix and Windows, who do not want to distribute InnoDB datafiles and log files on several disks. This creates an auto-extending data file ibdata1 and two InnoDB log files ib_logfile0 and ib_logfile1 to the datadir of MySQL (typically /mysql/data). Also the small archived InnoDB log file ib_arch_log_0000000000 ends up in the datadir.

[mysqld]
# You can write your other MySQL server options here
# ...
#                                  Data file(s) must be able to
#                                  hold your data and indexes.
#                                  Make sure you have enough
#                                  free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#                                  Set buffer pool size to
#                                  50 - 80 % of your computer's
#                                  memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#                                  Set the log file size to about
#                                  25 % of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#                                  Set ..flush_log_at_trx_commit
#                                  to 0 if you can afford losing
#                                  some last transactions
innodb_flush_log_at_trx_commit=1

Check that the MySQL server has the rights to create files in datadir.

Note that datafiles must be < 2G in some file systems! The combined size of the log files must be < 4G. The combined size of datafiles must be >= 10 MB.

When you for the first time create an InnoDB database, it is best that you start the MySQL server from the command prompt. Then InnoDB will print the information about the database creation to the screen, and you see what is happening. See below next section what the printout should look like. For example, in Windows you can start mysqld-max.exe with:

your-path-to-mysqld>mysqld-max --console

Where to put my.cnf or my.ini in Windows? The rules for Windows are the following:

bullet Only one of my.cnf or my.ini should be created.

bullet The my.cnf file should be placed in the root directory of the drive C:.

bullet The my.ini file should be placed in the WINDIR directory, e.g, C:\WINDOWS or C:\WINNT. You can use the SET command of MS-DOS to print the value of WINDIR.

bullet If your PC uses a boot loader where the C: drive is not the boot drive, then your only option is to use the my.ini file.

Where to specify options in Unix? On Unix mysqld reads options from the following files, if they exist, in the following order:

bullet /etc/my.cnf Global options.

bullet COMPILATION_DATADIR/my.cnf Server-specific options.

bullet defaults-extra-file The file specified with --defaults-extra-file=....

bullet ~/.my.cnf User-specific options.

COMPILATION_DATADIR is the MySQL data directory which was specified as a ./configure option when mysqld was compiled (typically /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation).

If you are not sure from where mysqld reads its my.cnf or my.ini, you can give the path as the first command-line option to the server: mysqld --defaults-file=your_path_to_my_cnf.

InnoDB forms the directory path to a datafile by textually catenating innodb_data_home_dir to a datafile name or path in innodb_data_file_path, adding a possible slash or backslash in between if needed. If the keyword innodb_data_home_dir is not mentioned in my.cnf at all, the default for it is the 'dot' directory ./ which means the datadir of MySQL.

An advanced my.cnf example. Suppose you have a Linux computer with 2 GB RAM and three 60 GB hard disks (at directory paths /, /dr2 and /dr3). Below is an example of possible configuration parameters in my.cnf for InnoDB.

Note that InnoDB does not create directories: you have to create them yourself. Use the Unix or MS-DOS mkdir command to create the data and log group home directories.

[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#                                  Data files must be able to
#                                  hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#                                  Set buffer pool size to
#                                  50 - 80 % of your computer's
#                                  memory, but make sure on Linux
#                                  x86 total memory usage is
#                                  < 2 GB
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#                                  .._log_arch_dir must be the same
#                                  as .._log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=3
#                                  Set the log file size to about
#                                  15 % of the buffer pool size
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
#                                  Set ..flush_log_at_trx_commit to
#                                  0 if you can afford losing
#                                  some last transactions
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Note that we have placed the two datafiles on different disks. InnoDB will fill the tablespace formed by the datafiles from bottom up. In some cases it will improve the performance of the database if all data is not placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. You can also use raw disk partitions (raw devices) as datafiles. In some Unixes they speed up I/O. See the manual section on InnoDB file space management about how to specify them in my.cnf.

Warning: on Linux x86 you must be careful you do not set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. It is a risk if the value of

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB

is close to 2 GB or exceeds 2 GB. Each thread will use a stack (often 2 MB, but in MySQL AB binaries only 256 kB) and in the worst case also sort_buffer + read_buffer_size additional memory.

How to tune other mysqld server parameters? Typical values which suit most users are:

skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
#                                  Set key_buffer to 5 - 50%
#                                  of your RAM depending on how
#                                  much you use MyISAM tables, but
#                                  keep key_buffer + InnoDB
#                                  buffer pool size < 80% of
#                                  your RAM
set-variable = key_buffer=...

Note that some parameters are given using the numeric my.cnf parameter format: set-variable = innodb... = 123, others (string and boolean parameters) with another format: innodb_... = ... .

The meanings of the configuration parameters are the following:

Option	Description
innodb_data_home_dir	The common part of the directory path for all InnoDB datafiles. If you do not mentioned this option in my.cnf the default is the datadir of MySQL. You can specify this also as an empty string, in which case you can use absolute file paths in innodb_data_file_path.
innodb_data_file_path	Paths to individual datafiles and their sizes. The full directory path to each datafile is acquired by concatenating innodb_data_home_dir to the paths specified here. The file-sizes are specified in megabytes, hence the 'M' after the size specification above. InnoDB also understands the abbreviation 'G', 1G meaning 1024M. Starting from 3.23.44 you can set the file-size bigger than 4 GB on those operating systems which support big files. On some operating systems files must be < 2 GB. The sum of the sizes of the files must be at least 10 MB.
innodb_mirrored_log_groups	Number of identical copies of log groups we keep for the database. Currently this should be set to 1.
innodb_log_group_home_dir	Directory path to InnoDB log files.
innodb_log_files_in_group	Number of log files in the log group. InnoDB writes to the files in a circular fashion. Value 3 is recommended here.
innodb_log_file_size	Size of each log file in a log group in megabytes. Sensible values range from 1M to 1/nth of the size of the buffer pool specified below, where n is the number of log files in the group. The bigger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But bigger log files also mean that recovery will be slower in case of a crash. The combined size of log files must be < 4 GB on 32-bit computers.
innodb_log_buffer_size	The size of the buffer which InnoDB uses to write log to the log files on disk. Sensible values range from 1M to 8M. A big log buffer allows large transactions to run without a need to write the log to disk until the transaction commit. Thus, if you have big transactions, making the log buffer big will save disk I/O.
innodb_flush_log_at_trx_commit	Normally this is set to 1, meaning that at a transaction commit the log is flushed to disk, and the modifications made by the transaction become permanent, and survive a database crash. If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 to reduce disk I/O to the logs.
innodb_log_arch_dir	The directory where fully written log files would be archived if we used log archiving. The value of this parameter should currently be set the same as innodb_log_group_home_dir.
innodb_log_archive	This value should currently be set to 0. As recovery from a backup is done by MySQL using its own log files, there is currently no need to archive InnoDB log files.
innodb_buffer_pool_size	The size of the memory buffer InnoDB uses to cache data and indexes of its tables. The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system.
innodb_additional_mem_pool_size	Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will need to allocate here. If InnoDB runs out of memory in this pool, it will start to allocate memory from the operating system, and write warning messages to the MySQL error log.
innodb_file_io_threads	Number of file I/O threads in InnoDB. Normally, this should be 4, but on Windows disk I/O may benefit from a larger number.
innodb_lock_wait_timeout	Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. If you use LOCK TABLES command, or other transaction-safe table handlers than InnoDB in the same transaction, then a deadlock may arise which InnoDB cannot notice. In cases like this the timeout is useful to resolve the situation.
innodb_flush_method	(Available from 3.23.40 up.) The default value for this is fdatasync. Another option is O_DSYNC.