11. Build a standalone running environment -- Performance Optimization of mysql5.6.10 in centos6.4

Run the following command to test the configuration script:

rm -rf  /var/log/mysqld.log

/usr/local/mysql/bin/mysqld_safe --log-error=/var/log/mysqld.log

vi /var/log/mysqld.log

Check/var/log/mysqld. log to see why MySQL is not started properly.

 

 

VI/etc/My. CNF

In the [mysqld] configuration section, add:

[mysqld]

# Port number when myslqd service is running
Port = 3306

# The socket file is exclusive to the Linux environment. The user's client software connection can be directly connected to MySQL using a UNIX socket without using a TCP/IP network.
Socket =/tmp/MySQL. Sock

# Avoid external locks of MySQL, reduce the chance of errors, and enhance stability.
Skip-external-locking

# Prohibit MySQL from performing DNS resolution on external connections. Using this option can eliminate the NDS resolution time of MySQL. However, you must note that if this option is enabled, all remote hosts are connected.
You must use IP addresses for Access authorization. Otherwise, MySQL cannot process connection requests normally.
Skip-name-resolve

# The value of the back_log parameter indicates how many requests can be stored in the stack before MySQL temporarily stops responding to a new request. If the system has many connections in a short time, you need to> increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the size of this queue. If you try to set back_log to a value higher than the operating system, the default value is 50. For Linux systems, we recommend that you set it to an integer smaller than 512.
Back_log = 384

# Increase the index buffer size to improve the index processing performance. For servers with around 4 GB of data, this parameter can be set to 384 Mb or MB. If the value of this parameter is too large, the overall efficiency of the server will be reduced.
Key_buffer_size = 384 m

# Set the maximum number of messages transmitted during network transmission. The default value is 1 MB. The maximum value is 1 GB. It must be set to a multiple of 1024, in bytes.
Max_allowed_packet = 4 m

# Set the stack size of each MySQL thread. The default value is large enough to meet normal operations. The value range is 192 KB to 4 GB. The default value is kb.
Thread_stack = 256 K

# Set the buffer size that can be used in query sorting. The default size is 2 MB. Starting from 5.1.23, it can be 4 GB on 64-bit platforms except Windows. This parameter
The allocation is exclusive to each connection. If there are 100 connections, the total size of the actually allocated sort buffer is 100*6 = 600 mb, for servers with a memory of about 4 GB, we recommend that you set it to 6 MB-8 Mb.
Sort_buffer_size = 6 m

# The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocation corresponding to this parameter is exclusive to each connection.
Read_buffer_size = 4 m

# Set the maximum number of connection pool threads that can be cached in the thread cache pool. It can be set to 0-16384. The default value is 0. 1 GB memory we configured to 8, 2 GB memory we configured to 16, 4 GB or above we configured to 64.
Thread_cache_size = 64
# Specify the size of the MySQL Query Buffer. You can observe it on the MySQL console. If the qcache_lowmem_prunes value is very large, it indicates that the buffer is insufficient frequently. If
The qcache_hits value is very large, indicating that the query buffer is used very frequently.
Query_cache_size = 64 m

# Set the maximum value of the internal temporary table. If this value is exceeded, the temporary table will be written to the disk. The value range is 1 kb to 4 GB.
Tmp_table_size = 256 m

# Specify the maximum number of connection processes allowed by MySQL. If the too connector connections error is frequently prompted during program access, you need to increase the value of this parameter.
Max_connections = 5000

# Specify the maximum connection time of a request. For servers with around 4 GB of data, you can set it to 5-10.
Wait_timeout = 120

# The value of this parameter is the number of logical CPUs of the server x 2. For example, the server has two physical CPUs, and each physical CPU supports HT hyper-threading. Therefore, the actual value is 4x2 = 8, this is also the mainstream dual-quad-core
Server configuration.
Thread_concurrency = 8

# Enabling this option can completely disable the MySQL TCP/IP connection mode. If the Web server accesses the MySQL database server remotely, do not enable this option, otherwise, the connection fails.
Skip-networking

Innodb_flush_log_at_trx_commit
# Complain that InnoDB is 100 times slower than MyISAM? You probably forgot to adjust this value. The default value 1 indicates that logs must be written to (flush) for each transaction commit or non-transaction instruction)
Hard disk, which 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 are flushed to hard disks per second, so you will not lose updates that exceed 1-2 seconds. Setting it to 0 is faster, but Ann> is less comprehensive. Even if MySQL crashes, transaction data may be lost. Value 2 can only lose data when the entire operating system is down.
Innodb_flush_log_at_trx_commit = 2

# This is the buffer used by transaction logs of the InnoDB Storage engine. Similar to BINLOG buffer, when InnoDB writes transaction logs, in order to improve performance, it first writes information> into innofb log buffer, when the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log will be written to the File> (or synchronized to the disk. You can use the innodb_log_buffer_size parameter to set the maximum memory space that can be used.
Innodb_log_buffer_size = 2 m

# This number should be set according to the actual situation, but it is a suitable setting in most cases.
Innodb_thread_concurrency = 8

# The default size of tmp_table_size is 32 MB. If a temporary table exceeds this size, MySQL generates an error in the table tbl_name is full format.
For advanced group by queries, add the tmp_table_size value.
Tmp_table_size = 64 m

# Memory (read_rnd_buffer_size) used for random read of Data Buffer: corresponds to sequential read. when MySQL reads data blocks in an unordered manner (random read, the> buffer zone is used to temporarily store read data. For example, read Table Data Based on index information, and join the table based on the sorted result set. In general, when the data block reading needs to meet> a certain order, MySQL needs to generate random reads and then use the memory buffer set by the read_rnd_buffer_size parameter.
Read_rnd_buffer_size = 16 m

# When defining database naming rules, you 'd better use a combination of lowercase letters and underscores instead of any uppercase letters.
Lower_case_table_names = 1

# Setting the Verification Mode
SQL _mode = no_engine_substitution, strict_trans_tables

# No cache query is enabled by default.
Explicit_defaults_for_timestamp

Function:

# Disabling MySQL logs

Run:/usr/local/MySQL/bin/MySQL-u root-P, enter the password, log on to MySQL, and then run reset master. After exiting, run VI/etc/My. CNF searches for log-bin = mysql-bin binlog_format = mixed and adds # to the front of the two rows to comment it out, run the restart MySQL command to disable the MySQL Log/etc/init. d/MySQL restart

For batch MySQL operations, add the rewritebatchedstatements parameter.

http://elf8848.iteye.com/blog/770032

MySQL insert performance test

http://elf8848.iteye.com/blog/1547398