Install MySql in Windows to adjust InnoDB performance in my. ini and restart the MySQL Service

MySQL version 5.0, Installation File Link: http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.29-win32.zip

1) during installation, you can select the data location. INI location: eg: MySQL installed in: F: \ Program Files \ mysql, then my. INI is located at: F: \ programdata \ mysql server 5.5

You can also specify the location of the my. ini file. Specifically, go to CMD and enter the bin folder under the MySQL installation directory.

First remove the existing MySQL service: mysqld-Remove MySQL, and then install:Mysqld -- install MySQL -- defaults-file = "***** \ My. ini"

2) the MySQL service name is mysql. Because the restart command cannot be used in windows, one of the restart methods is:

Disable: net stop MySQL

Start: Net start MySQL

**************************************** **************************************

MySQL InnoDB engine database insertion speed improvement method:

Thank you? MoD = viewthread & tid = 1704541.

1) Adjust the relevant parameters in the my. ini file. You can directly modify these parameters in my. ini and then restart the MySQL service. For more information about each parameter setting, see the description of my_small.ini my_medium.ini my_large.ini my_huge.ini in the installation folder.

Innodb_flush_log_at_trx_commit

The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs will still be flushed to hard per second
Disk, So you generally do not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.

Innodb_buffer_pool_size
If InnoDB is used, this is an important variable. Compared with MyISAM, InnoDB is more sensitive to buffer size. Mysiam may also use the default key_buffer_size for large data volumes, but InnoDB will feel crawling when using the default value for large data volumes. The InnoDB buffer pool caches data and indexes, so you do not need to leave space for the system cache. If you only use InnoDB, you can set this value to 70%-80% of the memory. Similar to key_buffer, if the data volume is small and does not increase much, do not set this value too high or increase the memory usage.

Innodb_additional_mem_pool_size 
This effect is not very obvious, at least when the operating system can reasonably allocate memory. However, you may still need to set it to 20 MB or more to see how much memory InnoDB will allocate for other purposes.

Innodb_log_file_size
It is very important to write a lot, especially when there is a large amount of data. Note that large files provide higher performance, but it takes more time to recover the database. I usually use 64 m-512 m, depending on the server space.It is worth noting that the log file needs to be deleted after this value is modified.Ib_logfile0, ib_logfile1 ...... ib_log, etc. (log files starting with IB _); otherwise, an error occurs when MySQL is started.

Innodb_log_buffer_size 
The default value can be used for most moderate write operations and short transactions. If you often update or use a lot of BLOB data, you should increase this value. But it is a waste of memory because it will always flush in one second ?) Once, so you do not need to set it to more than 1 second. 8-16 m should be enough. A smaller application can be used.

In addition, some parameters can improve the performance, but you need to use the command to modify:

Innodb_autoextend_increment:Mysql> set global innodb_autoextend_increment = 128;