InnoDB Chinese Reference Manual---2 InnoDB startup options

Reference Manual | Chinese InnoDB chinese reference Manual---Dog dog (heart Sail) Translation 2 InnoDB startup options
In order to use the InnoDB table in mysql-max-3.23, you must specify the configuration parameters in the [mysqld] section of the configuration file ' my.cnf ' or ' My.ini ' (Windows system).

As the minimum setting, in 3.23 You must specify the data file name energy and size on the Innodb_data_file_path. If no innodb_data_home_dir is specified in ' my.cnf ', the system creates the data file in the MySQL datadir directory. If you set Innodb_data_home_dir to an empty string, you can give an absolute path in Innodb_data_file_path. In MySQL-4.0 you can set up a innodb_data_file_path:mysql-4.0 (auto-extending) file ' Ibdata1 ' by default in the DataDir directory ( The size of the data file in MySQL-4.0.0 and 4.0.1 is MB and is not self expandable (not auto-extending)).

In order to get better performance you must explicitly set the InnoDB startup parameters in the example shown.

Starting with version 3.23.50 and 4.0.2, InnoDB allows the most data file set in Innodb_data_file_path to be described as auto-extending. The Innodb_data_file_path syntax looks like this:
Pathtodatafile:sizespecification;pathtodatafile:sizespec ...; Pathtodatafile:sizespec[:autoextend[:max:sizespecification]]
If the last data file is described with the Autoextend option, the last data file is automatically expanded when the InnoDB runs out of free space on all tables, with an increment of 8 MB each time. Example:
Innodb_data_home_dir = Innodb_data_file_path =/ibdata/ibdata1:100m:autoextend
Specifies that the InnoDB only establishes a data file with an initial size of MB and an increase in 8MB per block when the table space is exhausted. If there is not enough space on your hard disk, you can add another data file and put it on another hard disk. For example: first check the size of the hard disk space, set the Ibdata1 file so that it close to the hard disk space size and 1024 * 1024 bytes (= 1 MB) multiples, ibdata1 explicitly specified in the Innodb_data_file_path. You can add another data file after this:
Innodb_data_home_dir = Innodb_data_file_path =/ibdata/ibdata1:988m;/disk2/ibdata2:50m:autoextend
Note: When setting the file size, be sure to see if your OS has the maximum file size of 2GB! InnoDB is not aware of your OS file size limitations, in some file systems you may want to set the maximum capacity limit:
Innodb_data_home_dir = Innodb_data_file_path =/ibdata/ibdata1:100m:autoextend:max:2000m

　

A simple example of my.cnf. Suppose your computer has 128 MB RAM and a hard drive. The following examples are some of the configurations that might be made in my.cnf or My.ini files in order to use InnoDB. We assume that you are running mysql-max-3.23.50 and above, or MySQL-4.0.2 and above versions.

This example is ideal for Unix and Windows users who do not need to put InnoDB data files and log files on several disks. This example creates a self-expanding (auto-extending) data file ibdata1 and two InnoDB run log files Ib_logfile0 and ib_ in MySQL's DataDir directory (typically/mysql/data) Logfile1 and ib_arch_log_0000000000 files.

[Mysqld] #在这里加入其它 MySQL Server configuration # ... # data files must be # able to hold data and index # OK there's enough # disk space Innodb_data_file_path = ibdata1:10m:autoextend # Set buffer pool size to # 50-80% set-variable = innodb_buffer_pool_size=70m% of your main memory = Set-variable = Innodb_additional_mem_pool_siz E=10M # Set the size of the log file is approximately the # buffer pool # set-variable = innodb_log_file_size=20m set-variable = Innodb_log_buffer _size=8m # If you lose the most recent transaction impact # Small, you can set the #. _flush_log_at_trx_commit = 0 Innodb_flush_log_at_trx_commit=1

InnoDB does not create a directory of its own, it must use the operating system command to establish the appropriate directory. Check that your MySQL service program has sufficient permissions to create files in the DataDir directory.

Note: The data file size must be less than 2g! in some file systems The total size of all run log files must be less than 2G or 4G, depending on the specific MySQL system version. The sum of the data files must be greater than or equal to ten MB.

When you first set up a InnoDB database, it is recommended that you start the MySQL service on a command-line basis. This prompts the InnoDB database to be displayed on the screen so that the build process can be seen. The following 3rd section shows the on-screen display of the InnoDB database when it is established. For example, use the following instructions under Windows to start Mysqld-max.exe:
Your-path-to-mysqld>mysqld-max--console

　

Where is my.cnf or My.ini placed under the Windows system? The rules are as follows: only one my.cnf or My.ini file My.cnf file must be placed in the root directory of C: My.ini file must be placed in the Windir directory, for example: C:\WINDOWS or C:\WINNT. You can use MS-DOS's set command to view windir directory values If your PC uses the boot loader boot system and C: is not the boot disk, then only use My.ini as the settings file
　

Where do I specify a configuration file under Unix? Under Unix mysqld search for profiles in the following order:/ETC/MY.CNF Global Options COMPILATION_DATADIR/MY.CNF Server-wide options defaults-extra-file using--defaults-extra -file= ..... Set the default file ~/.MY.CNF user-specified file Compilation_datadir is the MySQL data file directory, which is specified in the./configure settings when MYSQLD is compiled (typically/usr/local/mysql/data Binary installation or/usr/local/var for source installation).
　

If you are not sure where mysqld reads my.cnf or My.ini, you can specify its directory in detail on the first command line: Mysqld--defaults-file=your_path_to_my_cnf.

The InnoDB data file directory is a combination of the Innodb_data_home_dir and Innodb_data_file_path data file names or directories, and adds a "/" or "\" between them if necessary. If the keyword Innodb_data_home_dir is not explicitly specified in MY.CNF, its default value is ".", that is, directory "./", which means MySQL's datadir of MySQL.

An advanced my.cnf example. Let's say you have a 2 GB RAM and 3 GB hard drives (The paths are "/", "/DR2" and "/DR3") are installed with Linux. The following examples are some of the configurations that might be made in the My.cnf file to use InnoDB.

Note: InnoDB does not create the file directory on its own: you must create them yourself. Use the Unix or MS-dos mkdir command to establish the appropriate data and log file directory.

[Mysqld] #在这里加入其它 MySQL Server configuration # ... # If you don't use the InnoDB table to remove a row of comments # Skip-innodb # # The data file must be # able to hold the data and index # OK there is enough # disk space innodb_d Ata_file_path =/ibdata/ibdata1:2000m;/dr2/ibdata/ibdata2:2000m:autoextend # Set buffer pool size to # 50-80 of your main memory size, but # in Linux x86 total Memory # Use must be less than 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 and #. _log_group_home_dir the same; # Starting with 4.0.6, you can omit it innodb_log_arch_dir =/dr3/iblogs set-variable = innodb_log_files_in_group=3 # Set The size of the log file is about # buffer pool # set-variable = innodb_log_file_size=150m set-variable = innodb_log_buffer_size=8m # If you lose the most recent transaction impact # Small, you can set the #. _flush_log_at_trx_commit = 0 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: We have placed two data files on different hard drives, and InnoDB will populate the table space from the bottom of the data file. In some cases all of the data is assigned to a different physical hard disk, which improves the performance of the database. It is often useful to improve performance by placing log files on separate physical hard disks with data files. You can also use a raw disk partition (raw diskette partitions (raw devices)) as a data file, which will improve I/O capability in some unixe systems. How to specify them in MY.CNF, see section 12.1.

Warning: On Linux x86 You must be careful not to set the memory usage too high, glibc will grow the process heap on top of the thread stack, which will crash the server. The following close to or exceeding 2G will be very dangerous:
Innodb_buffer_pool_size + Key_buffer + max_connections * (Sort_buffer + record_buffer) + max_connections * 2 MB
Each thread will use the 2MB (MySQL AB binary version of 256 KB) stack, in the worst-case scenario will also use Sort_buffer + record_buffer additional memory.
　

How do I adjust other mysqld server parameters? See the MySQL User manual for more detailed information. Typical parameters that are appropriate for most users are as follows:
skip-locking set-variable = max_connections=200 set-variable = record_buffer=1m set-variable = Sort_buffer=1M # set index buffering (ke Y_buffer) size is # your RAM 5-50%, which relies heavily on the number of MyISAM tables used in the # system. # But you must ensure that the sum of the buffer pools (buffer pool) size of the index buffer (key_buffer) and InnoDB # is less than 80% of RAM. Set-variable = key_buffer= ...

　

Note: Some of the parameters in the My.cnf file are set to a number, and they are formatted as: set-variable = InnoDB ... = 123, while others (string and logical) are formatted in another format: Innodb_ ...

The meanings of each setting parameter are as follows:
Innodb_data_home_dir
This is the directory-shared setting for the InnoDB table. If not set in MY.CNF, InnoDB will use the MySQL DataDir directory as the default directory. If you set an empty string, you can set an absolute path in the Innodb_data_file_path.
INNODB_DATA_FILE_PATH specifies the path and size of the data file individually. The full path of the data file is a combination of innodb_data_home_dir and the values set here. The file size is specified in MB units. Therefore, you must have an "M" after the file size is specified. InnoDB also supports the abbreviation "G", 1G = 1024M. Starting with 3.23.44, you can set the data file size greater than 4 GB on those operating systems that support large files. On other operating systems, the data file must be less than 2 GB. The total size of the data file must be at least ten MB. This parameter must be explicitly specified in the MySQL-3.23 in the my.cnf. This is not necessary in MySQL-4.0.2 and newer versions, and the system defaults to creating a MB self-expanding (auto-extending) data file ibdata1 in the MySQL datadir directory. You can also use a native partition (Raw raw disk partitions (raw devices)) as a data file and how to specify them in my.cnf in detail, see section 12.1. Innodb_mirrored_log_groups the number of copies of the log filegroup set to protect the data, the default setting is 1. Set in number format in MY.CNF. Innodb_log_group_home_dir the path to the InnoDB log file. You must set the same value as Innodb_log_arch_dir. If you do not explicitly specify that the default is to create two 5 MB ib_logfile in the MySQL datadir directory ... File. Number of log files in the Innodb_log_files_in_group log group. InnoDB is written to the file in a ring mode (circular fashion). The value 3 is recommended for use. Set in number format in MY.CNF. The size (in megabytes) of each log file in the Innodb_log_file_size log group. If n is the number of log files in the log group, the ideal value is the 1/n of the buffer pool (buffer pools) size set below 1M. A larger value reduces disk I/O by reducing the number of times the buffer pool is refreshed. But a large log file means that it takes longer to recover data when it crashes. The log file sum must be less than 2 gb,3.23.55 and 4.0.9 is less than 4 GB. Set in number format in MY.CNF. Innodb_Log_buffer_size InnoDB the buffer size before the log is written to the log disk file. The ideal value is 1M to 8M. Large log buffering allows the transaction to run without having to save the log in disk and only to the transaction being committed (commit). Therefore, if you have large transactions, setting a large log buffer can reduce disk I/O. Set in number format in MY.CNF. Innodb_flush_log_at_trx_commit is typically set to 1, meaning that the log has been written to disk before the transaction is committed, the transaction can run longer and the repair capability after the service crashes. If you are willing to weaken this security, or you are running a relatively small transaction, you can set it to 0 to reduce disk I/O to write log files. The default setting for this option is 0. Innodb_log_arch_dir the directory where fully written log files would be archived if we used log archiving. The parameters set here must be the same as Innodb_log_group_home_dir. You can ignore this parameter starting with 4.0.6. Innodb_log_archive This value is usually set to 0. Since recovery from backup (recovery) is appropriate for MySQL to use its own log files, archive InnoDB log files are often no longer needed. The default setting for this option is 0. Innodb_buffer_pool_size InnoDB is used to cache data and index memory buffers. Larger settings make it possible to reduce disk I/O when accessing data. It can be set to 80 of the physical memory on a dedicated database server. Do not set it too large, because the use of physical memory competition may affect the operating system's page calls. Set in number format in MY.CNF. Innodb_additional_mem_pool_size InnoDB storage combination for storing data dictionaries (information dictionary) and other internal data structures (internal data structures) (memory Pool) size. The ideal value is 2M, and if you have more tables you need to redistribute them here. If InnoDB runs out of all the memory in this pool, it allocates memory from the operating system and writes the error message to the MySQL error log. Set in number format in MY.CNF. Innodb_file_io_threads InnoDBThe file I/O thread in. Typically set to 4, but under Windows you can set a larger value to increase disk I/O. Set in number format in MY.CNF. Innodb_lock_wait_timeout the time that the transaction will wait for the timeout (in seconds) before the rollback (rooled back) is InnoDB. InnoDB automatically checks the transaction deadlock itself when locking the table and transaction rollback. If you use the lock Tables command, or if you use a different Transaction security table processor in the same transaction (transaction safe table handlers than InnoDB), a deadlock may occur that InnoDB cannot notice. Timeouts in this case will be used to resolve the problem. The default value for this parameter is 50 seconds. Set in number format in MY.CNF. Innodb_flush_method This parameter is only related to Unix. The default value for this parameter is Fdatasync. Another setting item is O_dsync. This only affects the dump of the log files, and the Fsync dumps the data under Unix. The InnoDB version starts at 3.23.40b and specifies fdatasync to use the Fsync method, specifying O_dsync to use O_sync under Unix. Since this is still problematic in some Unix environments, it is not used in ' data ' versions. Innodb_force_recovery WARNING: This parameter can only be used in an emergency where you want to transfer storage (dump) data from a corrupted database! The range of values that may be set is 1-6. Look at the following section ' forcing recovery ' to see what this parameter means. A value with a parameter setting greater than 0 represents the InnoDB to prevent users from modifying the data. Starting with 3.23.44, this parameter is available. Set in number format in MY.CNF. Innodb_fast_shutdown InnoDB is missing empty insert buffer before closing. This operation may take several minutes, and in extreme cases it can take a few hours. If this parameter is set to 1, InnoDB will skip this process and close it directly. Starting with 3.23.44 and 4.0.1, this parameter is available. Starting with 3.23.50, the default value for this parameter is 1. Innodb_thread_concurrency InnoDB will attempt to use the InnoDB service's operating system process less than or equal to the value set here. The default value for this parameteris 8. This value should be set to a smaller size if the computer system is low in performance or if the Innodb_monitor shows a lot of threads waiting for the waiter signal. If your computer system has my processor and disk systems, you can set this value higher to make the most of your system resources. The recommended value is the number of processors + number of disks. Starting with 3.23.44 and 4.0.1, this parameter is available. Set in number format in MY.CNF.