MyISAM and InnoDB engine optimization analysis _mysql

Source: Internet
Author: User
Tags mysql query mysql version
The past few days in learning MySQL database optimization and set up on their own server, metaphor is mainly learned MyISAM and InnoDB two kinds of engine optimization methods, they have advantages and disadvantages, generally in the actual application of the two engines combined to use the effect will be better. The hardware configuration of the test and the software environment are as follows:

Server model: IBM S226
CPU: Quad-core Xeon
Memory: 4G
HDD: two 80G do RAID1
System: Windows Server 2003 SP1 32-bit Enterprise Edition
MySQL version: 5.5
According to the actual situation of their own servers, optimized and parameters are as follows:

first, public options
skip-external-locking//Avoid MySQL external locking, reduce error probability enhance stability.
Skip-name-resolve//prohibit MySQL from DNS resolution of external connections, use this option to eliminate MySQL DNS resolution time. However, it should be noted that if this option is turned on, all remote billion-en-tech host connection authorization to use the IP address, otherwise MySQL will not be able to handle the connection request properly!
Max_connections = 1024//Specifies the maximum number of connection processes allowed by MySQL. You need to increase the value of a too Many connections error message when you visit the forum frequently.
Query_cache_size = 16M//default is 0, is not enabled. Specifies the size of the MySQL query buffer. You can observe this by performing the following command at the MySQL console:
# > Show VARIABLES like '%query_cache% ';
# > Show STATUS like ' qcache% ';
# If the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient;
If the value of the qcache_hits is very large, then the query buffer is used very frequently, if the value is small but will affect the efficiency, then you can consider not query buffering; qcache_free_blocks, if the value is very large, then the buffer is a lot of fragments.
Sort_buffer_size = 6M//the sort cache size for each thread, which works on the sort order by and group by. Note: This parameter corresponds to the allocated memory is per connection exclusive! If there are 100 connections, the total sorted buffer size that is actually allocated is 100x6 = 600MB. Therefore, for the existence of around 4GB server recommended set to 6-8m.
record_buffer=16m//Each thread scanned by a sequential scan allocates a buffer of this size to each of its scanned tables, which can be set to more than 2M
Table_cache = 512//Opens the number of tables for all threads. Increasing this value can increase the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table.

Second, MyISAM option
Key_buffer_size = 256M//key_buffer_size Specifies the size of the buffer to use for the index, increasing it for better index processing performance. This parameter can be set to 256M or 384M for servers with around 4GB.
Note:The value of the parameter set too large will be the overall efficiency of the server down!
read_buffer_size = buffer size that can be used by the 4M//Read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!
Myisam_sort_buffer_size = 64M//default is 16M. The buffer size used when setting, restoring, and modifying tables is not set too large.
Join_buffer_size = 8M//union query operation can use the size of the buffer, like the sort_buffer_size, the corresponding allocation of memory for each connection is exclusive!

three, InnoDB option
INNODB_BUFFER_POOL_SIZE=1G//A InnoDB Most important parameter, this parameter and MyISAM's key_buffer_size have similarities, but also have the difference. This parameter mainly caches the index of the InnoDB table, the data, the buffer when inserting data, the larger the set, the less disk I/O required to access the data in the table, generally half of the memory, no more than 2G, otherwise the system will crash. Optimization of the primary parameters for InnoDB acceleration. This parameter allocates the principle of memory: This parameter is assigned only 8M, which can be said to be a very small value. If it is a dedicated DB server, then he can take up 70%-80% of the memory. This parameter cannot be changed dynamically, so the allocation needs to be considered more. Excessive allocation, will make swap too much, resulting in MySQL query very slow. If your data is relatively small, then the allocation is your data size + 10% as the value of this parameter.

For example: The data size is 50M, then assign innodb_buffer_pool_size=64m to this value
INNODB_ADDITIONAL_MEM_POOL_SIZE=16M//The internal directory used to hold InnoDB This value is not allocated too large, the system can be automatically tuned. Do not set too high. Usually the larger data set 16M is sufficient, if the table is more, you can increase the appropriate. If this value is automatically incremented, it will appear in the error log.

innodb_log_file_size=256m//The size of each log file in the log group, typically Innodb_buffer_pool_size 25%, is the official recommendation of Innodb_buffer_pool_size 40-50%. General control in several log file addition size within 2G is preferred. The specific situation also needs to look at your transaction size, data size as the basis. Description: This value allocation size and database write speed, transaction size, abnormal restart after the recovery has a great relationship.

innodb_log_files_in_group=2//Specify how many log groups you have. Allocation principle: Generally we can use 2-3 Day value group. The default is two.

INNODB_LOG_BUFFER_SIZE=3M//transaction buffering in storage. Allocation principle: Control in 2-8m. This value is not too much. The memory inside him is typically written to disk one second. The way you write is related to how your transaction is submitted. In Oracle and other databases to understand this, the general maximum designation of 3M more appropriate.

Innodb_flush_logs_at_trx_commit=0//Control Transaction Submission Method Allocation principle: This parameter has only 3 values, 0,1,2 Please confirm your own acceptable level. The default is 1, please do not change the master library. A higher performance can be set to 0 or 2, but a second transaction is lost. Note: The setting of this parameter has a great effect on the performance of InnoDB, so explain it here. When this value is 1 o'clock: InnoDB transaction Log writes the day value file after each commit and flushes the daily value to disk. This can be done without losing any one transaction. When this value is 2 o'clock: in each commit, the log buffer is written to the file, but the log file is not flushed to disk operations, and the refresh of the log files occurs once per second in the case of a value of 2. It should be noted, however, that due to the problem of process invocation, 100% per second is not guaranteed to occur. Which is the fastest in performance. However, the last second transaction is removed by the operating system crash or power off. When this value is 0 o'clock: Log buffers are written to log files once per second, and disk operations are refreshed on log files, but no action is taken on a transaction commit. The crash of the MYSQLD process deletes the last second of the transaction before the crash.

From the above analysis, when this value is not 1 o'clock, you can achieve better performance, but encounter anomalies will have losses, so need to be measured according to their own situation.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.