Tuning bitsCN.com using Mysqlmy. cnf
Mysql Performance Tuning (my. cnf parameters)
The reasonable optimization of MySQL directly affects the speed and carrying capacity of the database!
MySQL is also the most difficult part of optimization. it not only needs to understand some MySQL professional knowledge, but also requires a long period of observation statistics and judgment based on experience, and then set reasonable parameters.
This topic provides detailed parameter descriptions for my. cnf configuration.
The sample configuration is as follows:
# Cat my. cnf
# MySQL client library initialization.
[Client]
Port
= 3306
Socket =/tmp/mysql. sock
Character-set-server = utf8
# The MySQL server
[Mysqld]
Init_connect = 'set names utf8' // sets utf8 encoding for connecting to the mysql database to run the mysql database with utf8
> Show variables like 'character % '/G; query mysql characters
Init_connect = 'set autocommit = 0' // when the automatic commit mode is disabled, the user is always treated as a transaction.
Character-set-server = utf8
Port = 3306
Socket =/tmp/mysql. sock
Basedir =/opt/justone/mysqldM/mysql
Datadir =/opt/justone/mysqldM/mysql/data
Skip-locking // avoid MySQL external locks, reduce the chance of errors and enhance stability.
# Skip-networking // when this option is enabled, the MySQL TCP/IP connection mode is completely closed. if the WEB server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!
Skip_name_resolve // disables MySQL from performing DNS resolution on external connections. this option can eliminate the time for MySQL to perform DNS resolution. If this option is enabled, IP addresses are required for all remote host connection authorization; otherwise, MySQL cannot process connection requests normally!
# Back_log = 384 // The number of connections that can be maintained in the listener queue is stored in the connection before the MySQL connection manager thread processes the connection.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request.
If there are many connections in a short period of time, you need to increase the value of this parameter to specify the size of the listener queue for the incoming TCP/IP connection.
Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.
The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux. The system value is viewed by ulimit-.
Max_connections = 2000 // Number of Customers allowed simultaneously. Add this value to increase the number of file descriptors required by mysqld.
Note: If the value is Too small, the client will often encounter Too many ONS errors.
Wait_timeout = 10 // specify the maximum connection time of a request. for servers with around 4 GB of memory, you can set it to 5-10.
Max_connect_errors = 500 // if the same host has an interrupted error connection that exceeds the value of this parameter, the host will be disconnected.
To unban the HOST, run: flush host;
Table_open_cache = 2048 // number of tables opened by all threads
Max_allowed_packet = 16 M // maximum size of a query statement package. the allowed size of the information package is used in information exchange (for example, import table)
Max_heap_table_size = 256 M // The maximum length of the HEAP data table (memory table) (16 M by default );
HEAP data tables that exceed this length will be stored in a temporary file instead of resident in the memory.
Sort_buffer_size = 512 K // The buffer size that can be used for sorting.
The allocated memory corresponding to this parameter is exclusive to each connection. if there are 100 connections, the actually allocated sort buffer size is 100 × 512 K = 50 MB.
Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 MB.
Join_buffer_size = 1 M // The buffer size that can be used by the joint query operation. the allocated memory corresponding to this parameter is exclusive to each connection.
Thread_cache_size = 8 // number of threads that can be reused and stored in the thread cache. the memory G * 8 is set to 16 for 2 GB.
Each connection of the database must use its own thread. It takes time to create a thread. Therefore, if the thread does not need to be closed when the connection is closed, the server will save it in its own thread cache for use by the next connection.
Thread_concurrency = 8 // set this parameter to the number of server logical CPUs × 2
Bulk_insert_buffer_size = 8 M // specify that the MyISAM data table uses a special tree structure for caching.
Using the whole bulk can speed up the INSERT... SELECT, INSERT... VALUES (...), (...), ..., And load data infile. This parameter limits the cache size of the tree structure used by each thread. if it is set to 0, the cache acceleration function is disabled. Note: the cache operation corresponding to this parameter can only be performed by users into non-empty data tables! The default value is 8 MB.
Query_cache_size = 64 M // specify the size of the MySQL Query buffer.
You can run the following command on the MySQL console:
> Show variables like '% query_cache % ';
> Show status like 'qcache % ';
If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient;
If the value of Qcache_hits is very large, it indicates that the query buffer is used very frequently. if the value is small, it will affect the efficiency, so you can consider not to use the query buffer;
Qcache_free_blocks. if the value is very large, it indicates that there are many fragments in the buffer.
Default-storage-engine = InnoDB // the default data table type of the new data table. the default value is MyISAM.
Lower_case_table_names = 1 // MySQL implementation is case insensitive
Transaction_isolation = REPEATABLE-READ // sets the default transaction isolation level for all connections
Tmp_table_size = 256 M // maximum length of the temporary HEAP data table
The default value is 32 MB. temporary data tables that exceed this length are converted to MyISAM data tables and saved to a temporary file.
Slow_query_log = 1
Log =/opt/justone/mysqldM/mysql/logs/mysql. log
Long_query_time = 2
Log-slow-queries =/opt/justone/mysqldM/mysql/logs/slowquery. log
# Replication related settings
Server-id = 1 // Set to master
Log-bin = mysql-bin // The generated log starts with mysql-bin.
Binlog_cache_size = 8 M // specify the cache size used by the SQL query statement during query request processing for binary log.
If it is frequently used for processing a large number of complex SQL expressions, you should increase the value of this parameter to improve performance.
Binlog_format = mixed // log format, which can also be customized.
Differences between InnoDB and MyISAM
InnoDB and MyISAM are the two most common table types used by many people when using MySQL. the Two table types have their own advantages and disadvantages, depending on the specific application.
The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does.
MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.
Notes for InnoDB:
1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) fromtable, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) clause contains the where condition, the operations on the two tables are the same.
3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. when you DELETE FROMtable, InnoDB does not create a new table, but deletes a row.
5. the load table frommaster operation does not work for InnoDB. the solution is to change the InnoDB TABLE to the MyISAM TABLE first, and then the InnoDB TABLE after the data is imported, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.
6. the row locks of the InnoDB table are not absolute. if MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, updatetable set num = 1 where name like "a %"
# MyISAM Specific options
Key_buffer_size = 32 M // The buffer size of the index block. increase it to get better processing indexes (for all read and multi-rewrite)
The index block is buffered and shared by all threads. If you make it too large, the system will begin to change pages and it will really slow down.
The default value is 8388600 (8 MB). The MySQL host has 2 GB of memory and can be set to 402649088 (400 MB ).
Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Read_buffer_size = 256 K // The buffer size that can be used by the read query operation. the allocated memory corresponding to this parameter is also exclusive to each connection.
Read_rnd_buffer_size = 256 K // query results output in a specific order (such as the ORDERBY clause) (256 K by default)
The read data after sorting is accelerated to improve the speed of reading the classified rows.
Myisam_sort_buffer_size = 128 M // sort_buffer_size of the myisam engine
Myisam_max_sort_file_size = 10G // similar
Myisam_recover // The MyISAM table cannot be properly closed after automatic check and repair
# Skip-innodb // remove innodb support
# Skip-bdb // remove the support for bdb transaction tables
# INNODB Specific options ***
Innodb_additional_mem_pool_size = 16 M // memory pool size used by InnoDB to store data dictionaries and other internal data structures.
The more tables in the application, the more memory should be allocated. if innodb is used up, the memory will be allocated to the system memory.
And write warning logs. according to the MySQL manual, the recommended value for 2 GB memory machines is 20 MB.
The default value is 1 MB. Generally, it does not need to be too large. it only needs to be enough. it is related to the complexity of the table structure.
# Innodb_buffer_pool_size = 6 GB // specify the memory size to buffer data and indexes.
For a separate MySQL database server, you can set this value to 80% of the physical memory.
According to the MySQL manual, for 2 GB memory machines, the recommended value is 1 GB (50%)
Innodb_buffer_pool_size = 512 M
Innodb_data_file_path = ibdata1: 10 M: autoextend // the tablespace used to hold InnoDB as a data table:
It may involve more than one file. The maximum length of each tablespace file must be measured in bytes (B), megabytes (MB), or gigabytes (GB;
The names of tablespace files must be separated by semicolons. The last tablespace file can also contain an autoextend attribute and a maximum length (max: n ).
For example, ibdata1: 1G; ibdata2: 1G: autoextend: max: 2G means:
The maximum length of the tablespace file ibdata1 is 1 GB, and the maximum length of ibdata2 is also 1 GB, but it can be expanded to 2 GB.
In addition to the file name, you can also use the name of the hard disk partition to define the tablespace. at this time, you must add the newraw keyword to the maximum initial length of the tablespace for suffix, add the raw keyword to the maximum extended length of the tablespace (for example,/dev/hdb1: 20Gnewraw or/dev/hdb1: 20 Graw ); the default setting for MySQL 4.0 and later versions is ibdata1: 10 M: autoextend.
Innodb_file_io_threads = 4 // maximum number of threads for I/O operations (hard disk write operations) (4 by default ).
Innodb_file_per_table = 1 // Create a tablespace file for each new data table instead of storing the data table in the central tablespace.
If the number of tables in the system is small and there is no large table, this parameter can be used to make the maintenance between tables relatively independent.
Innodb_thread_concurrency = 16 // maximum number of threads that can be used by the InnoDB driver simultaneously (the default value is 8 ).
Innodb_flush_log_at_trx_commit = 1 // InnoDB logs.
If set to 1, MySQL writes transaction logs to the disk when each transaction is committed.
If it is set to 0 or 2, logs are written to the disk every second.
The actual test shows that this value has a significant impact on the speed of data insertion.
If it is set to 2, it takes only 2 seconds to insert 10000 records. if it is set to 0, it only takes 1 second to insert. if it is set to 1, it takes 229 seconds.
We recommend that you merge the insert operation into a transaction as much as possible, which can greatly increase the speed.
If you are at risk of losing the most recent transaction, you can set this value to 0.
Innodb_log_buffer_size = 8 M // log cache size
The default settings can meet the performance requirements of the server in the case of moderate write load and short transactions.
This value should be increased if the peak value of the update operation or the load is large. 8-16 m.
Innodb_log_file_size = 256 M // the size of each log file in the log group is very important at high write loads, especially when large datasets exist.
The larger the value, the higher the performance, but the longer the recovery time. The default value is 5 MB. Innodb_log_file_size = 64 M recommended by Javaeye
After modification, STOP the service, delete the original logs ib_logfile0 and ib_logfile1, and then start the service.
Overall performance analysis report> show engine innodbstatus/G;
Innodb_log_files_in_group = 3 // Number of log files in the log group. 3 is recommended.
Innodb_max_dirty_pages_pct = 90 // percentage of the maximum dirty page
When the percentage of dirty pages in the system exceeds this value, INNODB writes the updated data on the page to the disk file.
Innodb_flush_method = O_DIRECT // InnoDB log file synchronization method (only applicable to UNIX/Linux systems ).
O_DIRECT skips the file system DiskCache of the operating system, allowing MySQL to directly read and write data to the disk.
Innodb_lock_wait_timeout = 120 // Set the resource timeout for a transaction. the default value is 50 s.
If a transaction does not obtain the required resources after waiting for n seconds (s), use the ROLLBACK command to discard the transaction.
This setting is important for discovering and processing deadlock conditions that are not recognized by the InnoDB data table driver.
However, automatic monitoring cannot be performed on deadlocks caused by table locks.
[Mysqldump]
Quick // do not buffer the query and export it directly to stdout; use mysql_use_result () to do it.
Do not cache the entire result in the memory before writing it to the disk. this item is required when exporting a very large table.
Max_allowed_packet = 16 M
[Mysql]
No-auto-rehash // The auto-rehash command is set to auto-rehash. TAB completion by default
# Safe-updates // only allow the use of key value UPDATE and DELETE
[Myisamchk] // A very useful tool for MyISAM table maintenance.
You can use the myisamchk utility to obtain information about database tables or to check, repair, and optimize them.
Myisamchk applies to MyISAM tables (tables corresponding to. MYI and. MYD files ).
Key_buffer_size = 512 M
Sort_buffer_size = 512 M
Read_buffer = 8 M
Write_buffer = 8 M
[Mysqlhotcopy]
Interactive-timeout // sets the maximum time for data transmission to the default 28800 S
[Mysqld_safe]
Open-files-limit = 8192 // number of files that can be opened by each process.
Make sure you have set the system limit high enough # ulimit-HSn 65536
Impact of server hardware on MySQL Performance
A) disk tracing capability (disk I/O)
Currently, high-speed SCSI hard disks (7200 rpm) are searched for 7200 times per second theoretically. physical features cannot be changed.
MySQL performs a large number of complex query operations every second. you can imagine the disk read/write volume.
Disk I/O is one of the biggest factors restricting MySQL Performance. for example, the daily average access to the Forum of WPV.
Due to disk I/O constraints, MySQL performance will be very low, you can consider the following solutions:
Use a RAID-0 + 1 disk array
Be sure not to try RAID-5. the efficiency of MySQL on the RAID-5 disk array will not be as fast as you expected;
Abandon traditional hard disks and use faster flash storage devices.
After Discuz! The company's technical engineering tests show that the use of flash storage devices is about 6-10 times faster than traditional hard disks.
B) CPU for MySQL applications, we recommend using multi-channel symmetric CPUs in the S. M. P. Architecture, for example, two IntelXeon GHz CPUs.
C) the server memory is recommended not to be less than 2 GB, but more than 4 GB physical memory is recommended. BitsCN.com
