MySQL database Optimization

MySQL database Optimization

How should we optimize the MySQL database? Next I will explain this problem from MySQL's hardware selection, MySQL installation, my. cnf optimization, MySQL architecture design, and data splitting.

Optimization of server physical hardware

When selecting a hardware server, we should optimize the hardware configuration of the MySQL server from the following aspects:

1. Disk seek capability (disk I/O). We now use SAS15000 to convert the hard disk, and use 6 fast such hard disk as RAID 1 + 0. MySQL performs a large number of complex query operations every second, And the read/write volume of the disk can be imagined. Therefore, disk I/O is usually considered to be one of the biggest factors in the performance of MySQL. For forums (Discuz) and blogs (Wordpress) with an average daily access volume of more than 1 million PVS, if the performance is poor, the direct consequence is that MySQL performance will be very low! To solve this problem, you can consider using a RAID1 + 0 disk array. Do not try RAID5. The efficiency of MySQL on the PAID5 disk array will not be as fast as you expected, if the funds permit, you can choose SSD to replace the SAS hard disk with Raid 1 + 0.

2. The impact of CPU on MySQL cannot be ignored. We recommend that you select a CPU with powerful computing power. We recommend that you use DELL R710 (dual quad-core), which is also a selling point of its powerful virtualization and database capabilities.

3. For a Database Server using MySQL, we recommend that the memory of the Server be less than 2 GB. We recommend that you use more than 4 GB physical memory, however, memory is a negligible problem for the current server. If it is a high-end server, the memory basically exceeds 32 GB, and our data stream server uses 32 GB memory.

I have used a lot of database servers in my work, such as DELL R710/R720, with good stability and performance. At the same time, I also found that many of my peers use it as the database server, so I would like to recommend it to you here.

MySQL should be installed by compiling

For the online environment installation of MySQL databases, we recommend that you compile and install the MySQL database to greatly improve the performance. For the server system, CentOS6.7 X86_64 is recommended. The compilation parameters of the source code package generate binary code in Debug mode by default, while the Debug mode causes a high performance loss for MySQL, therefore, when compiling the product code to be installed, do not forget to use the-without-debug parameter to disable the Debug mode. If you set the compilation parameters-with-mysqld-ldflags and-with-client-ld-flags to-all-static, you can tell the compiler to compile in static mode, compile the results to obtain the highest performance. Compared with code that uses static compilation and dynamic compilation, the performance gap may reach 5% to 10%. Later, I will share with you the compilation parameters of our online MySQL database. You can refer to them and modify the content based on your online environment.

MySQL configuration file Optimization

[Client]

Port = 3306 # The client port number is 3306.

Socket =/data/3306/mysql. sock #

Default-character-set = utf8 # client character set (control character_set_client, character_set_connection, character_set_results)

[Mysql]

No-auto-rehash # Only key-value updates and deletes are allowed

[Mysqld] # The group includes the mysqld service startup parameters. It involves many aspects, including MySQL directories and files, communication, network, and information security, memory Management, optimization, query cache, and MySQL Log Settings.

User = mysql # mysql_safe script uses MySQL to run the user (specified by compile-user = mysql). mysql users are recommended.

Port = 3306 # the port number when the MySQL service is running. We recommend that you change the default port to be vulnerable to attacks.

Socket =/data/3306/mysql. sock # socket files are unique in Linux/Unix environments. in Linux/Unix environments, you can directly use unix socket to connect to MySQL without using a TCP/IP network.

Basedir =/application/mysql # The path where the mysql program is stored. It is often used to store mysql startup, configuration files, and logs.

Datadir =/data/3306/data # MySQL data storage file (extremely important)

Character-set-server = utf8 # default character set for databases and database tables. (Utf8 is recommended to avoid garbled characters)

Log-error =/data/3306/mysql_xuliangwei.err # mysql error log storage path and name (when an error occurs, you must check the error log. Of all errors can be solved through the error log plug-in .)

Pid-file =/data/3306/mysql_xuliangwei.pid # The MySQL_pid file records the pid of the current mysqld process, that is, ProcessID.

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

Skip-name-resolv # This option prevents MySQL from performing DNS resolution on external connections. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!

Skip-networking # 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 will fail!

Open_files_limit = 1024 # maximum number of files that MySQLd can open. If too mant open files is displayed, you need to adjust this value.

Back_log = 384 # The back_log parameter indicates the number of requests that can be stored in the stack in a short period of time 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 greater than the operating system, the default value is 50. For Linux systems, it is recommended to set the value to an integer smaller than 512.

Max_connections = 800 # specify the maximum number of connection processes allowed by MySQL. If
Increase the value of Too connector Connections.

Max_connect_errors = 6000 # sets the maximum number of abnormal disconnection requests for each host. When this limit is exceeded, the MySQL server will disable host connection requests, until the MySQL server is restarted or the host information is cleared using the flush hosts command.

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

Table_cache = 614 K # table_cache indicates the table's high-speed buffer size. When MySQL accesses a table, if there is still space in the MySQL buffer, the table will be opened and put into the table buffer. The advantage of this is that the table content can be accessed more quickly. In general, you can view the status values Open_tables and Open_tables of the database peak time to determine whether to increase the value of table_cache. That is, if Open_tables is close to table_cache, and the value of Opened_tables is gradually increased, we need to consider increasing the size of this value.

External-locking = FALSE # MySQL option can avoid external locking. True is enabled.

Max_allowed_packet = 16 M # The maximum query package value that the server can process at a time is also the maximum query value that the server program can process

Sort_buffer_size = 1 M # Set the buffer size that can be used for sorting queries. The default size is 2 MB.

Note: The allocated memory corresponding to this parameter is exclusive to each connection. If there are 100 connections, the actually allocated total sort buffer size is 100x6 = 600 MB. Therefore, we recommend that you set this parameter to 6 MB for servers with around 4 GB of memory ~ 8 MB

Join_buffer_size = 8 M # buffer size used by the Joint query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

Thread_cache_size = 64 # set the maximum number of connection threads that can be cached in the Thread Cache pool, which can be set to 0 ~ 16384. The default value is 0. this value indicates that the number of threads stored in the cache can be reused. When the connection is disconnected, if there is space in the cache, the client thread will be placed in the cache; if the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, the thread will be re-created. If there are many threads, adding this value can improve the system performance. By comparing variables in Connections and Threads_created states, you can see the role of this variable. We can set the rules according to the physical memory as follows: 1 GB memory is 8, 2 GB memory is 16, 3 GB is 32, 4 GB or above, we give this value 64 or greater.

Thread_concurrency = 8 # Set this parameter to the number of logical CPUs of the server x 2. In this example, the server has two physical CPUs, and each physical CPU supports H.T hyper-threading, therefore, the actual value is 4x2 = 8. This is also the configuration of dual-core and quad-core mainstream servers.

Query_cache_size = 64 M # specify the size of the MySQL Query Buffer. You can observe on the MySQL console that if the Qcache_lowmem_prunes value is very large, the buffer is often insufficient. If the Qcache_hits value is very large, the query buffer is frequently used. In addition, if the modification value is small, it will affect the efficiency, so you can consider not to use the query buffer. For Qcache_free_blocks, if this value is very large, it indicates that there are many fragments in the buffer.

Query_cache_limit = 2 M # Only results smaller than the set value will be cached

Query_cache_min_res_unit = 2 k # set the minimum unit of memory allocated to the query cache. Set this parameter appropriately to reduce the number of fast memory requests and allocations, however, setting too large may increase the memory fragmentation value. The default value is 4 K. We recommend that you set it to 1 K ~ 16 K.

Default_table_type = InnoDB # the default table type is InnoDB.

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

# Transaction_isolation = Level # Database isolation Level (read uncommitted content) read committed (READ submitted content) repeatable read (repeable) SERIALIZABLE (SERIALIZABLE ))

Tmp_table_size = 64 M # set the maximum value of a temporary memory table. If this value is exceeded, the temporary table is written to the disk in the range of 1 kb to 4 GB.

Max_heap_table_size = 64 M # maximum capacity allowed by an independent memory table.

Table_cache = 614 # allocate memory to frequently accessed tables. The larger the physical memory, the larger the setting. If you increase the value, the disk I/O can be reduced, but the memory will be occupied. Set this parameter to 614.

Table_open_cache = 512 # set the number of high-speed table caches. Each connection will open at least one table cache. Therefore, the size of table_cache should be related to the settings of max_connections. For example, for 200 concurrent connections, the table cache should be at least 200 × N, where N is the maximum number of tables in a join where the application can execute the query. In addition, additional file descriptors must be reserved for temporary tables and files.

Long_query_time = 1 # maximum time used to execute slow queries. The default value is 10 s. Recommended (1 s ~ 2 s)

Log_long_format # queries that do not use an index are also recorded. (Recommended, adjusted based on business)

Log-slow-queries =/data/3306/slow. log # slow query log file path (If you enable slow query, we recommend that you enable this log)

Log-bin =/data/3306/mysql-bin # operation logs of the logbin database, such as update, delete, and create, are stored in binlog logs. You can use logbin to perform incremental recovery.

Relay-log =/data/3306/relay-bin # relay-log records the binary logs of the master server from the server I/O thread local files on the server, then the SQL thread reads the relay-log Content and applies it to the slave server.

Relay-log-info-file =/data/3306/relay-log.info # the file from the server that is used to record relay log-related information, named relay-log.info in the data directory by default.

Binlog_cache_size = 4 M # In a transaction, binlog records the cache size held by the SQL status. If you often use large, multi-statement transactions, this value can be added to obtain greater performance. All the statuses from the transaction are buffered in the binlog buffer, and then committed and written to the binlog at one time, if the transaction value is greater than this value, it will be replaced by a temporary file on the disk. This buffer is created when the transaction of each link is updated for the first time.

Max_binlog_cache_size = 8 M # maximum binary Cache log buffer size.

Max_binlog_size = 1G # Maximum length of the binary log file (1 GB by default) before the maximum length of the binary file is exceeded, the MySQL server automatically provides a new binary log file.

Expire_logs_days = 7 # binlog for more than 7 days. The mysql program is automatically deleted. (if the data is important, do not enable this option)

Key_buffer_size = 256 M # specify the size of the buffer used for the index, and increase it to get better index processing performance. This parameter can be set to 384 MB or MB for servers with around 4 GB of memory.

Note: If this parameter is set too large, the overall efficiency of the server will be reduced!

Read_buffer_size = 4 M # buffer size available for read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.

Read_rnd_buffer_size = 16 M # Set the buffer used for random read. This parameter is opposite to the Buffer set by read_buffer_size. It is used for sequential read and random read. However, both are targeted at and thread settings. Each thread can generate either of the two Buffer types. The default value is 256KB, and the maximum value is 4 GB.

Bulk_insert_buffer_size = 8 M # If you need to insert data in batches using special statements, you can adjust the parameter to 16 MB ~ 32 MB. 8 MB is recommended.

# Myisam_sort_buffer_size = 8 M # Set the buffer size allocated for sorting indexes in the REPAIR Table, Create index, or Alter table process. The value range is 4 bytes to 4 GB, the default value is 8 MB.

Lower_case_table_names = 1 # MySQL does not differentiate the size. (Open demand-we recommend that you enable it)

Slave-skip-errors =, # error numeric values that can be skipped from the slave database (mysql errors are fed back by numerical code. The full mysql error code list will be published to the blog later ).

Replicate-ignore-db = mysql # Set the databases that do not need to be synchronized when the master node is used.

Server-id = 1 # indicates that the serial number of the local machine is 1. If the master node or multiple instances are selected, the serverid must not be the same.

Myisam_sort_buffer_size = 128 M # When you need to re-index the REPAIR, OPTIMIZE, and ALTER statements, MySQL will allocate the cache and load data infile will be loaded to a new table, it will seriously allocate each thread according to the maximum configuration.

Myisam_max_sort_file_size = 10G # When re-indexing (REPAIR, ALTER, TABLE, or LOAD, DATA, TNFILE), MySQL is allowed to use the maximum value of temporary files.

Myisam_repair_threads = 1 # If a table has more than one index, MyISAM can use more than one thread to fix them through parallel sorting.

Myisam_recover # automatically checks and fixes MyISAM tables that are not properly closed.

Innodb_additional_mem_pool_size = 4 M # It is used to set the data directory information stored in InnoDB and the memory pool size of other internal data structures. The more tables there are in the application, the more memory you need to allocate. For a relatively stable application, the parameter size is also relatively stable, and there is no need to reserve a very large value. If InnoDB uses a wider pool of memory, InnoDB allocates memory from the operating system and writes warning information to the MySQL error log. The default value is 1 MB. When the error log contains related warning information, you should increase the parameter size as appropriate.

Innodb_buffer_pool_size = 64 M # InnoDB uses a buffer pool to store indexes and raw data. The larger the setting, the less disk I/O required for accessing data in the table. We strongly recommend that you do not arbitrarily set the Buffer Pool value of InnoDB to 50% ~ of the physical memory ~ 80%, depending on the specific environment.

Innodb_data_file_path = ibdata1: 128 M: autoextend # Set to configure a single file with an expandable size of MB. The file name is ibdata1, therefore, it is stored in the MySQL data directory by default.

Innodb_file_io_threads = 4 # file I/O thread in InnoDB. Usually set to 4. For windows, you can set a larger value to increase disk I/O.

Innodb_thread_concurrency = 8 # set the number of CPUs on your server. We recommend that you set the default value to 8.

Innodb_flush_log_at_trx_commit = 1 # if it is set to 0, it is equal to innodb_log_buffer_size. After the queue is full, it is stored in a unified manner. The default value is 1, which is the safest setting.

Innodb_log_buffer_size = 2 M # The default value is 1 MB, which is usually set to 8 ~ 16 Mb is enough.

Innodb_log_file_size = 32 M # determine the size of the log file. Larger settings can improve performance, but also increase the time for database recovery.

Innodb_log_files_in_group = 3 # to improve performance, MySQL can write log files to multiple files cyclically. It is recommended to set it to 3.

Innodb_max_dirty_pages_pct = 90 # the InnoDB main thread refreshes the data in the cache pool.

Innodb_lock_wait_timeout = 120 # Before InnoDB transactions are rolled back, wait for a lock timeout period. InnoDB automatically detects the transaction deadlock and rolls back the transaction in its own locked table. InnoDB uses the locak tables statement to notice the lock settings. The default value is 50 seconds.

Innodb_file_per_table = 0 # InnoDB is an independent tablespace mode. Each table in each database generates a data space. 0: Disable; 1: Enable.

Advantages of independent tablespace:

1. Each table has its own tablespace.

2. Data and indexes of each table are stored in its own tablespace.

3. A single table can be moved across different databases.

4. space can be recycled (except for the drop table operation, empty tables cannot be recycled by themselves .)

[Mysqldump]

Quick

Max_allowed_packet = 2 M # sets the maximum number of messages transmitted during network transmission. The default value is 1 MB, and the maximum value is 1 GB. It must be set to a multiple of 1024. The Unit is byte.

[Mysqld_safe]

Note:

• We strongly recommend that you do not arbitrarily set the Buffer Pool value of InnoDB to 50% ~ of the physical memory ~ 80%, depending on the specific environment.

• If key_reads is too large, you should increase key_buffer_size in my. cnf to keep key_reads/key_read_re-quests above 1/100, the smaller the better.

• If qcache_lowmem_prunes is large, it is necessary to increase the value of query_cache_size.

However, it is often necessary to analyze the specific situation. We can adjust other parameters according to the status value after MySQL is stable for a period of time.

 

E-commerce MySQL Database Configuration File

This is the configuration file/etc/my after the MySQL database of the e-commerce website is adjusted. cnf (the server is DELL R710, 16 GB memory, RAID10). You can adjust the configuration file according to the actual MySQL Database hardware conditions as follows:

[Client]

Port = 3306

Socket =/data/3306/mysql. sock

Default-character-set = utf8

[Mysqld]

User = mysql

Port = 3306

Character-set-server = utf8

Socket =/data/3306/mysql. sock

Basedir =/application/mysql

Data =/data/3306/data

Log-error =/data/3306/mysql_err.log

Pid-file =/data/3306/mysql. pid

Log_slave_updates = 1

Log-bin =/data/3306/mysql-bin

Binlog_format = mixed

Binlog_cache_size = 4 M

Max_binlog_cache_size = 8 M

Max_binlog_size = 1G

Expire_logs_days = 90

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

Key_buffer_size = 384 M

Sort_buffer_size = 2 M

Read_buffer_size = 2 M

Read_rnd_buffer_size = 16 M

Join_buffer_size = 2 M

Thread_cache_size = 8

Query_cache_size = 32 M

Query_cache_limit = 2 M

Query_cache_min_res_unit = 2 k

Thread_concurrency = 32

Table_cache = 614

Table_open_cache = 512

Open_files_limit = 10240

Back_log = 600

Max_connections = 5000

Max_connect_errorrs = 6000

External-locking = FALSE

Max_allowed_packet = 16 M

Thread_stack = 192 K

Transaction_isolation = READ-COMMITTED

Tmp_table_size = 256 M

Max_heap_table_size = 512 M

Bulk_insert_buffer_size = 64 M

Myisam_sort_buffer_size = 64 M

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

Long_query_time = 2

Slow_query_log

Slow_query_log_file =/data/3306/slow. log

Skip-name-resolv

Skip-locking

Skip-networking

Server-id = 1

Innodb_additional_mem_pool_size = 16 M

Innodb_buffer_pool_size = 512 M

Innodb_data_file_path = ibdata1: 256 M: autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16 M

Innodb_log_file_size = 128 M

Innodb_log_files_in_group = 3

Innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

Innodb_file_per_table = 0

[Mysqldump]

Quick

Max_allowed_packet = 64 M

[Mysql]

No-auto-rehash

For more details, please continue to read the highlights on the next page:

1 2 Next Page