Shulou(Shulou.com)06/01 Report--

# change disk scheduling algorithm

Echo 'deadline' > / sys/block/xvdb/queue/scheduler

# turn off the NUMA feature

# numactl-interleave=all

Here's my.cnf.

[client]

Port = 3306

Socket = / tmp/mysql.sock

[mysqld_safe]

# google optimized memory allocation module, which makes MySQL's memory footprint more stable under high concurrency. The system does not come with it and needs to be installed by itself. It is recommended to use it. There is no installation module to set this parameter will report an error.

# malloc-lib=tcmalloc

[mysqld]

Port = 3306

Socket = / tmp/mysql.sock

Log-error = / data/mysql/data/mysql.err

# case sensitive option for table names. Default is 0, that is, case sensitivity is enabled, and 1 means case insensitivity.

# lower_case_table_names = 1

# change the time zone of mysql. The default is SYSTEM, that is, synchronize with the system, view it with show variables like'% time_zone%', and change it as needed.

Default-time-zone ='+ 8VR 00'

# change the log display time. The default is UTC, which is 8 hours different from the Chinese time zone. It looks strange. Just change it to the system SYSTEM.

Log_timestamps = SYSTEM

# default storage engine. Innodb is the default after 5.5.

Default-storage-engine=INNODB

# default storage engine for tables. Default is innodb after 5.5.If you don't set it, follow default-storage-engine configuration.

# default_table_type = InnoDB

# path of MySQL program, code call path

Basedir=/usr/local/mysql

# the location of global data files and structures; table data (including innodb engine), indexes, logs (unless set separately) and other files will be stored here

Datadir=/data/mysql/data

# the root directory of the shared tablespace data file of the innodb engine. If not set, the directory of the datadir parameter is used by default

# innodb_data_home_dir = / data/mysql/data

# Global default character set type, set on demand, utf8,utf8mb4,gb2312, etc.

Character-set-server = utf8mb4

# Global default character proofreading rule, utf8 default proofreading rule is utf8_general_ci (case-insensitive), utf8_bin is case-sensitive, set as required.

# collation_server = utf8_bin

# when the connection is in the password entry stage, connection requests beyond connect_timeout will be rejected. The default is 10 seconds, which is generally not set, and it is not long or short. It is best not to set it too long. It is also necessary to spend some resources when the connection is suspended.

# connect_timeout=5

# the number of seconds to wait before closing a non-interactive connection (pure sleep state), which ranges from 1 to 31536000 (linux), with a default value of 28800. Too long is not conducive to efficient use of resources, but some time-consuming sql and some php/python/ruby script calls can not be set too short, otherwise it will be interrupted in advance. If there is a connection pool, it is best to default or set it larger, and tell the developer that the timeout of the connection pool should not exceed this time, otherwise something strange will happen.

Wait_timeout=86400

# the number of seconds to wait before closing an interactive connection (sleep and other states). The default value is 28800. Reducing it is beneficial to resource recovery. If you need a persistent connection, you can turn it up, because the timeout will force sql to be closed. If there is a connection pool, it is best to default or set it larger, and tell the developer that the timeout period of the connection pool should not exceed this time, otherwise something strange will happen.

Interactive_timeout = 86400

# during the busy phase of connection (non-sleep), it is valid for TCP/IP links. Only for threads in Activity state, the read timeout for clients to send, receive, or process packets is 30 by default, which is generally not set, unless your network is in a poor state.

# net_read_timeout=300

# during the busy phase of connection (non-sleep), it is valid for TCP/IP links. Only for threads in Activity state, the write timeout for clients to send, receive, or process packets is 60 by default, which is generally not set, unless your network is in a poor state.

# net_write_timeout=300

# disable domain name DNS lookup. Hostname or domain name cannot be used in the authorization table of mysql, only IP or localhost can be used

# skip_name_resolve = 1

# Skip external locking to prevent file directories from being unavailable

Skip-external-locking

# use InnoDB engine independent tablespaces, each table independent tablespace, do not use shared tablespace ibdata, but table metadata, undo log, insert buffers, etc., are still stored in shared tablespaces. The new version is on by default

Innodb_file_per_table = 1

# how many requests can be stored in the stack within a short period of time before MySQL temporarily stops responding to new requests? the default is 50. Concurrent high needs to be increased, but too high will take up memory and cannot exceed the system setting.

Back_log = 103

# Security parameters, which have nothing to do with performance, prevent clients from logging in after failed attempts to prevent brute force cracking passwords. Those with low security requirements in the intranet can be increased appropriately. You can use the flush hosts command to refresh the login failed host record.

Max_connect_errors = 100000

# controls innodb self-growing lock mode parameters, global variables, read-only variables and integer values. There are three values (representing traditional ("traditional"), consecutive ("continuous") and interleaved ("crossover"). The default is 1, which means that the self-increasing ID is continuous and generally does not need to be modified.

# innodb_autoinc_lock_mode = 2

# Thread mode, no-threads (single thread processing), one-thread-per-connection (one thread per request), dynamically-loaded/pool-of-threads (thread pool mode, official mysql and Percona parameter names are different) official version needs to be enabled by purchasing service

# thread_handling = pool-of-threads

# the connection timeout in the thread pool. Default is 500ms, which can be lowered to improve the utilization of the connection pool.

# thread_pool_stall_limit = 200

# the size of the index buffer only works on the MyISAM table, but both the system table and the temporary disk table are still MyISAM tables. In order to speed up the running speed of these tables, this value cannot be too low.

Key_buffer_size = 32m

Bulk insert cache size, which only works on MyISAM table, is suitable for improving efficiency when inserting 100-1000 + records at a time. The default value is 8m

# bulk_insert_buffer_size = 64m

# Network packet capacity limit, larger insert / update / export / import will be limited by this parameter, exceeding the limit will cause sql execution to fail. The default is 4m (too small), and the maximum limit is 1GB (too large). Now the bandwidth of network devices is relatively large, so it is recommended to set it to at least 32m, and 64M/128M is recommended for networks with gigabit or above. The value should be a multiple of 1024; otherwise, rounding to the nearest multiple, cluster architectures such as mha had better be set to more than 64m.

Max_allowed_packet = 128m

# the global maximum number of open files cannot exceed the maximum number of files set by the system, otherwise it will be invalid

Open_files_limit = 65535

The # innodb engine limits the number of file descriptors for opening tablespace files .ibd at a time, which is only useful when using independent tablespaces. The range is 10cm 4294967295, and cannot exceed the global maximum number of files. The new version defaults to-1, which means it is automatically set according to the actual number of openings (automatic calculation rule: if innodb_file_per_table is not open, the default value is 300. if it is opened, the larger values in innodb_open_files and table_open_cache will prevail). It is only used to limit the number of descriptors in innodb tablespace .ibd files and does not affect the number of table cache. To increase this variable, you need to pay attention to modifying the variable open_files_limit=num (read-only variable) of the server layer at the same time, which is a file handle restriction parameter at the system level. Because each session manipulates the database table, it also takes up the file descriptor, and the database connection itself also uses the file descriptor, so if you specify the innodb_open_files parameter manually, you also need to pay attention to the size setting of the number of concurrent connections open_files_limit.

Innodb_open_files = 65530

# allows the number of table handles stored in the cache. Increasing can improve the operation of frequently opening and closing tables. The default for the new version is 2000. The reference value is the number of max_connections* tables. If opening table is often found in show processlist, then the setting of these two variables may be small, and it is also affected by the setting of open_files_limit and the innodb_open_files variable.

Table_open_cache = 65530

# is similar to table_open_cache, but caches the number of table definition files .frm. The default value is-1, which means automatic calculation and calculation formula (table_open_cache / 2). If the number of open table instances exceeds this parameter setting, the LRU mechanism marks the table instances and eventually removes them from the data dictionary cache, which helps to solve the situation where a large amount of memory is used to cache rarely used table instances.

# table_definition_cache = 4096

In the new version # 5.6.6, each thread can use the number of concurrency (number of partitions) of the handle of the cache table, which cannot exceed table_open_cache. The default is 1, and the maximum value is 64, which is generally sufficient, unless the concurrency of the library is too much and the table is opened frequently.

Table_open_cache_instances = 16

# sort cache, session-level memory cache. Generally speaking, the performance of OLTP over 512K does not increase significantly. The recommended value is 56K-1M. If the data warehouse (OLAP) is set to more than 8m, the effect can be further improved. If you see a large value of Sort_merge_passes through show global status, you can consider properly adjusting the value of this parameter to increase the sort cache, but it is best not to set it globally, but only to set a large value for the current session.

Sort_buffer_size = 512K

# TCP/IP and socket communication buffer size, create rows up to net_buffer_length, which mainly affect import and export

Net_buffer_length = 8K

# use buffer size when scanning table data by sequential read scan. Session-level memory cache is 128K by default, and the maximum is 2G. It only works on MyISAM tables. Note that the temporary table of the system is also myisam.

Read_buffer_size = 1m

# session-level memory cache. The buffer size is used when scanning table data by random read scan. The default is 256K, and the maximum is 2G. Originally, it only works on MyISAM tables, but the new version will also be used as the buffer of the new feature mrr.

Read_rnd_buffer_size = 16m

# the buffer required for reordering when MyISAM tables change. Temporary disk tables are all MyISAM tables. To speed up the sorting of temporary tables, this value cannot be low, especially in data warehouse (OLAP).

Myisam_sort_buffer_size = 128m

# maximum size of temporary files allowed for MySQL re-indexing

Myisam_max_sort_file_size = 10G

# automatically check and repair MyISAM tables that are not properly closed

Myisam_repair_threads = 1

The MyISAM table is checked automatically when it is opened and repaired if it is improperly closed.

# myisam_recover

# enable binlog and specify the storage location, which is stored in the data directory by default. The master library must be opened, and the slave library can be closed appropriately.

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

# the default is 1. Enable the record sql statement to binlog, and change it to 0 to prohibit the sql statement from being recorded in the binary log file binlog. Of course, it will not be updated to the slave database.

# sql_log_bin = 0

# binlog type format, it is recommended to use mixed or row, but now in most cases mixed format will be converted to row format (especially RC isolation level), while STATEMENT is only used in a few cases.

Binlog_format=mixed

# binlog verification rules are generally not required. When binlog event sends back and finally gets the event content, it will add 4 extra bytes for verification. After 5.6.5, the default is CRC32, and the lower version is NONE. Need to be set to NONE when using MGR cluster

# binlog_checksum=NONE

# Database ID number, which is mainly used for master-slave replication, and is set to a unique value for the whole network as far as possible

Server-id = 18650

# shared tablespace capacity is recommended to be at least 1G, and the real size will not be scaled until 5.7 (previously it will only become larger)

Innodb_data_file_path = ibdata1:1G:autoextend

# the capacity size of a single redolog file on the hard disk is recommended to be 1-2G or more, with a default value of 48m and a minimum value of 1m, starting with version 5.6.3, supporting a maximum of 512g. The larger the setting is, the less the database Ibind O is, but the greater the cost of fault recovery is and the longer the time is.

Innodb_log_file_size = 1024m

# redolog memory parameter, 8-32m is fine. The larger the setting is, the less the database ID will be.

Innodb_log_buffer_size = 16m

# there are several sets of redolog, and setting more settings can also reduce the pressure of Imax O, especially in the case of large transactions, with a maximum value of 100 and a minimum value with a default value of 2.

Innodb_log_files_in_group = 3

# redolog storage directory is not recommended. Ib_logfile is stored in the data file directory by default. Setting it is mainly to disperse the disk pressure.

# innodb_log_group_home_dir = / tmp

# controls the opening and writing mode of innodb data files and redo log. There are three values: fdatasync (default and fastest), O_DSYNC (recommended for san storage and reading), O_DIRECT (system cache is disabled, slow but most secure, raid card + wb or atomic write FIO works well). Now, solid state drives and raid cards are used in most new environments, so it is recommended to set it to O_DIRECT. Avoid the double cache phenomenon caused by the use of solid state disk, and reduce the cost of storing data

Innodb_flush_method = O_DIRECT

Starting separate purge threads and setting the number of separate DML threads will help to improve the efficiency of multi-table operations in Linux. And reduce resource contention within innodb. The default value of the new version is 4, and the maximum value is 32

# innodb_purge_threads = 8

# indicates how many undolog page are completed at a time, but the side effect of this value is that it affects the release of undolog, because undolog page is always released after 128rounds of purge. The default is 300. you can set 1-5000. Setting less will speed up undo recycling.

# innodb_purge_batch_size = 1

# Control the number of innodb internal write threads parameters, global variables, read-only variables. The default value of the new version is 4, the minimum value is 1, and the maximum value is 64, which is limited by the aio-max-nr setting of the operating system.

# innodb_write_io_threads = 8

# Control the number of read threads in innodb, global variables, read-only variables. The default value of the new version is 4, the minimum value is 1, and the maximum value is 64.

# innodb_read_io_threads = 8

# transaction isolation levels, a total of four: READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE, the default is RR, generally speaking, there will be no big problem with RC, after all, problematic phantom reads are committed data, but reduce the lock granularity.

Transaction_isolation = READ-COMMITTED

The default is 0, that is, off, which means to prohibit the use of unsafe locks, that is, to enable gap locks. This is similar to setting the transaction isolation level of MySQL to READ-COMMITTED, but this parameter does not take effect until the mysql program is restarted to load the configuration, and it is generally not set unless there is a special need. However, it also has the following features: (1) for UPDATE or DELETE statements, InnoDB locks only rows that need to be updated or deleted, and locks imposed on rows that cannot be matched by WHERE conditions will be released after condition checking. This can effectively reduce the probability of deadlock; (2) when executing a UPDATE statement, if a row has been locked by other statements, InnoDB will initiate a "semi-consistent" read operation to get the row from the last submitted version of MySQL and determine whether it can match the WHERE condition of the current UPDATE. If there is a match, MySQL locks it again, and if there are other locks, you need to wait for them to exit.

# innodb_locks_unsafe_for_binlog = 1

# the frequency of writing to the hard disk redo file ib_logfile through fsync () after the transaction is committed, 0 is stored in memory to a certain amount of rewriting (usually 1 second), 1 is written immediately (slowest but safest, only one transaction is lost at most), 2 is saved to the operating system cache and the writing time is determined by the operating system (before 5.6.6, it is refreshed every 1 second, and the later version is set by the parameter innodb_flush_log_at_timeout. The default is 1s., but it avoids the problem of completely losing data when the mysql process fails. Because it directly affects the disk IO, it has a great impact on the performance of the database.

Innodb_flush_log_at_trx_commit = 1

Introduced in # 5.6.6, this timeout parameter does not work when the parameter innodb_flush_log_at_trx_commit=1, but only works when innodb_flush_log_at_trx_commit=0/2. Indicates the frequency of refreshing redo log every few seconds (before 5.6.6 is a regular refresh once per second, after 5.6.6, the refresh rate can be set by this parameter.) The default value is 1s. If you think the disk pressure is too high, you can increase it appropriately, but please note that the data security is reduced.

# innodb_flush_log_at_timeout = 5

# the frequency of refreshing binlog. 0 is the refresh time determined by the operating system. 1 each transaction is refreshed once (slowest but safest, only one event is lost at most). N (except for the number of 0 and 1, non-negative number) is refreshed every N transactions. If group commit is not supported, it means that n event (all events, including general transactions and implicit transactions such as alter/create, etc.) are refreshed once, if group commit is supported. Then it means that the binlog group is refreshed only once after saving n event, that is, the performance of supporting group commit is better, the number of IO is reduced, and the security is also a little higher than 0, but compared with the option of 1, the security is still slightly worse, it will lose one transaction, and group commit is basically perfectly supported after mysql5.6.

Sync_binlog = 1

# Row format definition, Antelope type (supports redundant, compact row format) can use redundant and compact row format, Barracuda type (supports data compression, dynamic row format, improved blob,text field storage layout) can use the old redundant and compact, but also can use compressed and dynamic four row formats, to support compression type, you must first define Barracuda. Try not to use compressed row format, because pages in innodb_buffer cannot be compressed, only page saved to disk can be compressed. ROW_FORMAT=xxx row format needs to be added to the table definition. The default value is antelope. It is recommended to use the default antelope file format and compact line format, which is suitable for most scenarios. Barracuda is better for tables that need to be compressed and do not need to be frequently modified and backed up.

# innodb_file_format = Barracuda

# dynamic compression is allowed for the index, but the row_format of the table must be compressed or dynamic. You can make the index column length greater than 767bytes, but the total length cannot be greater than 3072 bytes.

# innodb_large_prefix = 1

# enable and disable the function of refreshing adjacency pages. It is recommended that the mechanical disk be set to 1 and off for SSD. It can also be set to 2, which means that the adjacent pages are also refreshed, but the dirty pages in the same area are not required to be continuous.

# innodb_flush_neighbors = 0

# instance-level memory parameters, the total amount of innodb cache (including some other caches). The default value is 128m. The larger the setting, the higher the innodb performance. Generally, it is recommended for 50% color 70% for single instance and about 20% for multiple instances. It is a read-only variable before version 5.6, which can only be restarted and can be dynamically modified after 5.7.x.

Innodb_buffer_pool_size = 830m

# the number of concurrency of cache pool instances to reduce internal contention for cache pool data structures (the access to the cache pool is mutually exclusive at some stages) and improve concurrency performance. It only takes effect when innodb_buffer_pool_size is greater than 1G. The default value is 1 when the innodb_buffer_pool_size/innodb_buffer_pool_instances is less than 1G and 8 when it is greater than 1G. To ensure that the cache is greater than 128m, otherwise it may be counterproductive. It is recommended that the value be greater than 512m. At 5.7, the maximum is 64.

# innodb_buffer_pool_instances = 16

# set the memory pool size of data catalog information and other internal data structures stored by InnoDB. The more tables you have, the more memory you need to allocate. If the set value is exceeded, a warning message is written in the error log. The default value is 1MB.

# innodb_additional_mem_pool_size = 2m

# defaults to turning off OFF. If this parameter is enabled, when the MySQL service is started, MySQL loads the local hot data into the InnoDB buffer pool and enables it as needed.

# innodb_buffer_pool_load_at_startup = 1

# defaults to turning off OFF. When off, dump the hot data to the local disk, cooperate with the above parameters, and turn on as needed.

# innodb_buffer_pool_dump_at_shutdown = 1

# start refreshing dirty pages after the percentage of dirty pages. The more you write, the lower you should set (to reduce cache usage). According to the difference between LSN and log sequence number,Log flushed up to, the farther the difference is, the lower it should be. The default value is 75%, the minimum value is 0, and the maximum value is 99.

# innodb_max_dirty_pages_pct = 50

The maximum proportion of old_block_sublist (old data chain) in # innodb_buffer_pool is 3 by default, that is, 37%. The value range is 5: 95. If you read more and write less, you can increase it appropriately, and vice versa.

# innodb_old_blocks_pct = 50

# the number of dirty pages that flush the cache pool to disk. The default value is 2000.According to the performance of the hard disk, the mechanical hard disk is about 1000, the SSD hard disk is about 4-5 thousand, and the PCIE-SSD can be 10-20 thousand, but it is not recommended to exceed 20 thousand.

# innodb_io_capacity = 1000

# the maximum number of dirty pages flushed to disk, set according to the above parameters, but must be more than him

# innodb_io_capacity_max = 2000

# controls the switch for Innodb double write buffering (double write buffering is to write sequentially to continuous disk space, reducing random Icano). It is on by default, 0 is on, 1 is off. If the storage device supports atomic write, it can be turned off, and the write speed increases significantly. Other cases are recommended to turn on.

# innodb_doublewrite = 1

# limit the number of concurrent additions, deletions, modifications and queries. When concurrency reaches this limit, additional threads will be placed in the queue to wait for concurrent connections to be released and sleep for several microseconds. You can configure sleep time by setting the parameter innodb_thread_sleep_delay without consuming cpu and io resources. The default is 0 (unlimited), and the maximum value is 1000. In most cases, it does not need to be set, but when the concurrency is too high for the server to support the load, it can be set to 64x128. It is recommended that the number of CPU cores X disks X2. In fact, after the thread enters the lock and waits, the count of concurrent threads will be reduced by one, that is to say, the thread with equal line lock (including gap lock) is not counted in 128.

# innodb_thread_concurrency = 128

# it used to be called insert buffer, but now it is called change buffer, because the values now set are: inserts, deletes, purges, changes (inserts and deletes), all (default), none. It can only be used on non-unique indexes, nested in innodb_buffer_pool, and generally does not need to be moved.

# innodb_change_buffering = all

# set the size of innodb_buffer_pool occupied by innodb_change_buffering. The default is 25%, and the maximum is 50%. It is supported after 5.6. If there are many data modifications, it depends on the requirements.

# innodb_change_buffer_max_size = 25

# in the new version of mysql, query_cache has lost its meaning and occasionally causes query_cache locks, resulting in additional overhead. It is recommended to close it directly.

Query_cache_size=0

Query_cache_type=0

# the timeout of MDL metadata locks, and the wait time for locks similar to (not just) data structures such as ddl or dml. The default value is 31536000 seconds (one year). Setting this parameter is mainly to prevent some large table DDL or DML operations from locking the table for a long time. If the amount of data in the table is small, it can be done by default.

Lock_wait_timeout = 600,

# innodb transaction request row lock timeout limit parameter, too many lock waiting is very bad, while innodb has a spin lock, the waiting time is too long will also consume resources, in seconds, the minimum can be set to 1 second, the maximum can be set to 1073741824 seconds (34 years), the default is 50 seconds.

Innodb_lock_wait_timeout = 300

The default value is OFF. If the transaction times out due to locking, the operation performed by the last statement will be rolled back. If ON is set, the entire transaction is rolled back. The need to focus on transaction consistency

Innodb_rollback_on_timeout = 1

# when the innodb thread fails to obtain mutex resources, the number of spin lock attempts is 30. 0 by default. Because spin locks take up cpu and memory resources, appropriate reduction can reduce the load, but also increase the number of query failures. Increasing this value is the opposite, and it is best to consider modifying it after testing.

# innodb_sync_spin_loops = 30

# defines the number of idle cycles for InnoDB spin operations, which defaults to 6 revolutions. As long as it is not 0, it has little effect on the performance, and it is generally used with innodb_sync_spin_loops.

# innodb_spin_wait_delay = 6

# is used to configure whether updates from the server are written to the binary log. If there is a level of slave library, it must be opened (note server-id). If not, it is recommended to turn it off, which can effectively improve performance. In GTID replication mode, the log_slave_updates parameter is enabled for all databases to facilitate fast data synchronization, and if switching is involved, binary logs can be quickly synchronized (because all servers have fully recorded binlog)

Log_slave_updates = 1

# when the slave is down from the library, if the relay-log is damaged, causing part of the relay log not to be processed, then automatically discard all outstanding relay-log and retrieve the log from the master, thus ensuring the integrity of the relay-log. This function is off by default and is recommended to be turned on to effectively ensure data consistency.

Relay_log_recovery = 1

# set the database to read-only, ordinary users cannot write / change data (administrators can still write), turn it off by default (that is, 0), it is generally best to set from the database to avoid unnecessary misoperation, of course, do not touch him

# read_only=1

The new parameter introduced in # 5.7, the previous read_only cannot restrict super users (such as root), but this parameter can.

# super_read_only=1

# 5.7 the new parameters introduced. When enabled, the SQL executed can only be read-only.

# tx_read_only=1

# the automatic purge switch after the execution of the slave relay log is enabled by default (i.e. 1). In some cases, it can be turned off (i.e. 0). It only works on the slave library.

# relay_log_purge=0

# temporary table capacity cache, session-level memory cache. After that, temporary tables will be transferred to disk. The temporary table engine on disk defaults to myisam, and the temporary table engine in memory is memory, which cannot be read or written. Because complex queries are often useful to temporary tables and this parameter is session-level, it is easy to cause OOM if you set it too large. If you have a large query, you can set a larger value for the current session. If you see a large value of Created_tmp_disk_tables through show global status, you can apply this formula: Created_tmp_disk_tables/ (Created_tmp_disk_tables+Created_tmp_tables) * 100% > 10% Confirm whether it needs to be added.

Tmp_table_size=64M

# temporary table row cache, session-level memory cache, limit temporary table size together with tmp_table_size, and the maximum number of rows that can be created in memory table (memory table) is generally sufficient by default of 16m. If there is a large query, you can set a larger value for the current session. If the amount of data is large, it is best to increase it.

# max_heap_table_size=96M

# if the temporary table is too large and the disk space pressure increases, you need to consider setting the temporary table storage location. The default is / tmp

# tmpdir=/tmp

# the maximum number of connections allowed in the database is recommended to be set to 80% of the maximum number of loadable connections, otherwise it may be stuck. The default is 151. If the status variable Connection_errors_max_connections is not zero and keeps growing, it means that many users have failed to connect by this parameter. You should consider increasing the value of this parameter. In addition, this parameter is also limited by the open-files-limit setting of the operating system, and the maximum number of connections for mysql is actually: max_connections+1.

Max_connections=1024

# the maximum number of connections for each independent database user. Default is 0, which means there is no limit. Setting this can prevent the maximum number of connections max_connections from being full, resulting in administrative users (root, etc.) not being connected.

Max_user_connections=256

# the number of threads saved in the cache can be reused. When the thread is disconnected, it will not be closed immediately, but will be saved in the cache waiting to be reused. The default value is-1, which means automatic calculation: 8 + (max_connections/100). The effect of concurrent low this parameter is not obvious, but the effect of concurrent high profile is obvious. When the ratio of Threads_created/Connections state variables in the formula is closer to 1, it means that the lower the hit rate of the thread is, the more consideration should be given to increasing the value of this parameter.

# thread_cache_size = 51

# session-level memory cache, join query cache. The default value is 256K, and the minimum value is 128bytes. If you have more join, you should also pay attention to oom. If you have a large query, you can set a larger value for the current session.

Join_buffer_size = 128m

# enable slow query parameters, 0 off, 1 on, off by default, can be modified dynamically.

# slow_query_log = 1

# set slow query storage path

Slow_query_log_file = / tmp/slow_querys.log

# slow query recording time. It can be set to less than 1 second after 5.5. It is recommended to set it to 0.1 second.

Long_query_time = 0.1

# used to set whether to record queries that do not use indexes to slow query records. Turn it off by default. If the demand is turned on, a lot of logs will be generated and can be modified dynamically.

# log_queries_not_using_indexes = 1

# administrative instructions will also be recorded in slow queries. For example, OPTIMEZE TABLE, ALTER TABLE, off by default, see the demand on, will generate a lot of logs, can be dynamically modified.

# log_slow_admin_statements = 1

# whether the slow execution statements from the library are also included in the slow query log of the library. See if the demand is turned on, there will be a lot of logs that can be modified dynamically.

# log_slow_slave_statements = 1

# binlog session-level memory cache, when the transaction is not committed, the resulting log is recorded in Cache; when the transaction needs to be committed, the log is persisted to disk.

Binlog_cache_size = 4m

# the cache size of the maximum binlog log. The total number of binlog_cache_size for all sessions cannot exceed this value. The default is the maximum value. In a 32-bit system, it is 4G binlog_cache_size and 64-bit is 16p. It is set to prevent mysql memory overflow.

# max_binlog_cache_size = 4G

# binlog binary log is written to the given value. The default value is 1GB, and the maximum setting cannot be greater than 1G. According to the actual situation, the file is too large to open, but if you are using a large transaction, the binary log will still exceed the limit.

Max_binlog_size = 500m

# limit the size of binlog space. The default is 0 (unlimited). If you are using large transactions, the binary log will still exceed the max_binlog_size limit, and the maximum value is limited by this parameter to prevent the disk from exploding.

# relay_log_space_limit=16G

# the number of days saved in binlog, which is automatically deleted after expiration. The default value is 0 (which means the binlog will not be cleaned automatically)

Expire_logs_days=7

# controls whether to record the parameters of the user's native SQL to binlog file when binlog_format=row. It is disabled by default.

# binlog_rows_query_log_events = 1

# enable and disable the ordinary query log feature. If it is set to 1 or ON, all SQL of the database will be recorded. Generally, it will grow rapidly, and 10% of the database performance will be wasted.

# general_log = 1

# set the query log path and file name. If not, it will be named hostname.log by default in the datadir directory and used in conjunction with the above parameters.

# general_log_file = / tmp/general_querys.log

# specify the query log general _ log and slow _ log to be stored in the database table, mysql.general_log and mysql.slow_log, respectively. The default is FILE, that is, in the file, set according to the actual situation.

# log_output=TABLE

# 5.6 New feature, which separates undo files from ibdata1, opens and specifies how many undo log files there are. Default is 0, that is, this feature is not enabled. Mysql8.0 is enabled by default and set to 2.

Innodb_undo_tablespaces = 4

# specify the directory where the undo is stored. The default is the current data directory (the directory specified by the datadir parameter). For the sake of reducing disk pressure, you can consider leaving it independently.

# innodb_undo_directory = / tmp/

# specify the size of the undo rollback segment, which is at least 35 or greater. The default is 128, which is generally enough. Mysql8.0 cancels this parameter.

# innodb_undo_logs = 256

# enable online recycling (shrinking) undo log log files to support dynamic settings.

Innodb_undo_log_truncate = 1

# when undo exceeds this threshold (default is 1G), truncate reclaim (shrink) action will be triggered, and the space will be reduced to 10m after truncate, depending on the size of your hard drive.

# innodb_max_undo_log_size = 2G

# controls the frequency of reclaiming (shrinking) undo log. The undo log space does not shrink until its rollback segment is released. To increase the frequency of releasing the rollback interval, you have to lower this parameter. The default is 128.

# innodb_purge_rseg_truncate_frequency = 64

# parameters after mysql5.6. When enabled, all deadlock will be recorded in error_log. As dba, you should know how much deadlock information there is.

Innodb_print_all_deadlocks = 1

Before 5.6.2, the master information recorded by slave and the information of slave application binlog were stored in files, namely master.info and relay-log.info. After version 5.6.2, it is allowed to record to table, multi-source replication, this must be table

Master_info_repository = TABLE

# ditto information, multi-source replication, this must be table

Relay_log_info_repository = TABLE

# GTID mode, 5.6new features, new replication mode, open it if necessary. It is recommended to change binlog to row.

Gtid_mode = on

# enforce GTID consistency, generally used with the above parameters, but only allow transaction security when enabled, and SQL statements that can be logged can be executed, such as create table. Select and create temporarytable statements, and SQL statements or transactions that update both transactional and non-transactional tables are not allowed to execute.

Enforce_gtid_consistency = 1

# disable the numa function. The new parameter 5.6.27 is 0 by default, that is, it is enabled. We need to set it to 1, that is, to disable this function. Numa is a function of the linux system to reasonably allocate memory space in a multi-core environment, but it causes performance bottlenecks for large memory applications, so it should be disabled for database category applications.

# innodb_numa_interleave = 1

# timeout for fetching transactions from the flush queue, which is mainly used to prevent concurrent transactions from being too high, causing the RT of some transactions to rise, in microseconds. The default is 0.

# binlog_max_flush_queue_time = 0

# on is enabled by default (i.e. 1), and transactions will be written in the same order as binary logs. When set to disable off (i.e. 0), transactions may be committed in a different order from binlog, and there will be some performance improvement in high concurrency

# binlog_order_commits = 1

# innodb storage engine recovery level. Optional 0-6. Default is 0. It may be needed when the database or table cannot be used properly.

# innodb_force_recovery = 0

# 5.7.15 New function, turn off deadlock monitoring, normally deadlock monitoring only takes up a small part of the resources, so you don't need to turn it on by default. In the case of high concurrency, on the contrary, it will cause extra overhead. If you turn it off, you can improve concurrency, but if there is a deadlock, you will not roll back and wait for the lock all the time. So after closing, you need to reduce the innodb_lock_wait_timeout value to reduce the lock waiting time and avoid the lock waiting for too long. The code level also needs to make a good return rule for this error report.

# innodb_deadlock_detect=off

# percona-specific features, which are not available in other versions, limit the idle time of all transactions, such as a large number of JDBC persistent connections, which is especially useful. Set the number of seconds to enable, and there is no limit if you do not set it by default.

# innodb_kill_idle_transaction = 30

# restrictions on importing / exporting data to files (load data and SELECT...into outfile) are one of the security restrictions. The new version defaults to null, that is, prohibited. You can enter a folder name to indicate that the import / export folder is restricted. The default value of the old version is empty, that is, there is no restriction on import / export at all.

# secure_file_priv = / tmp

# affects the algorithm of mysql selection sorting. Appropriate enlargement helps to optimize the execution efficiency of sql. Mysql has two file sorting algorithms (two-way sorting and one-way sorting). If the total size of the column to be sorted plus the size of the orderby column exceeds the bytes defined by this parameter, mysql will use two-way sorting, and two-way sorting will also be used when text and blob columns are included. The cost of two-way sorting may be huge. Because he reads the table twice, the second reading will cause a large number of random IO, which is especially expensive for myisam. The myisam table uses system calls to extract each row of data. One-way sorting is more efficient and avoids the second reading of data. And change the random IO into a sequential IO, but it uses more memory space because it exports all the columns needed for sorting at once and saves them in memory.

# max_length_for_sort_data = 4096

# sets the number of worker threads that replay events (transactions) in parallel from the library SQL thread. The default value is 0 and the maximum value is 1024. When this parameter is greater than 0 in version 5.6.x, the SQL thread acts as the coordinator of the worker thread, distributing library-based events among multiple worker threads, that is, library-level parallel replication. Version 5.7.x parallel replication can be set up based on group commit transactions, which is more useful.

# slave_parallel_workers = 8

The new parameter specifies the parallel replication method, but in order to be compatible with the library mode, the default parallel replication method is based on the library level, that is, the default value is: DATABASE. If we want to set it to the parallel replication mode based on group commit transaction supported by 5.7, we need to set it to: LOGICAL_CLOCK, which is faster.

# slave_parallel_type = LOGICAL_CLOCK

The default is 0, which is turned off. It is used when slave_parallel_type is set to LOGICAL_CLOCK, and 1 is enabled. The order of binlog submission on Slave is the same as that of binlog on Master, and the order of GTID is guaranteed. Because the order of transactions applied on slave is disordered, which is different from that recorded in relay log, so data consistency cannot be guaranteed. There may be problems even if it is set to 1 before 5.7.19, so please update to 5.7.19 if you need to use this parameter.

# slave_preserve_commit_order = 1

# how long does it take to wait for binlog to be submitted before synchronizing to disk (in microseconds). Default is 0 and no delay. Setting delay can make multiple transactions commit at one time, improve the concurrency and efficiency of binlog group commits, and thus improve the throughput of slave. Recommended default

# binlog_group_commit_sync_delay = 100

# stop waiting for direct commit if there are enough transactions before waiting for the above parameter to time out. The unit is the number of transactions. The default is 0. Recommended default

# binlog_group_commit_sync_no_delay_count = 100

The parameters introduced after # 5.6.13 control the execution time of stop slave. If you suddenly execute stop slave when you re-enlarge the transaction, it will execute for a long time and may even cause deadlock or blocking, seriously affecting performance. The default value is 31536000 seconds = 1 year.

# rpl_stop_slave_timeout=300

# the timeout setting to determine whether the master database has hung up. If you still don't get the response from Master within the set time, you will think that Master is dead, and then reconnect the master database according to the timeout reconnection parameter setting. Default value: 3600s

# slave_net_timeout=300

# automatically handles synchronous replication errors. The default is STRICT strict mode, and IDEMPOTENT is idempotent mode. It skips errors such as duplicate-key (1062) and no-key-found (1032). This mode takes effect only in ROW's binlog mode and is invalid in STATEMENT's binlog mode. It has the same effect as slave_skip_errors. However, slave_skip_errors does not support dynamic modification, and mysql must be restarted to take effect. Therefore, it is recommended to use slave_exec_mode. Normally, you should stop replication when you encounter an error in the library, and then deal with the data consistency problem manually, thinking about how to satisfy the replication in the first place, rather than skipping the transaction.

# slave_exec_mode=IDEMPOTENT

# semi-synchronous main library switch. You need to confirm whether the semi-synchronous plug-in is loaded before using it.

# rpl_semi_sync_master_enabled = 1

# after confirming that semi-synchronous is enabled, the master library uses the semi-synchronous replication mechanism to send binlog to slave, and then waits for the acknowledgement packet to be received from the library ACK. After this time, it automatically becomes asynchronous until the slave library is re-launched.

# rpl_semi_sync_master_timeout = 3000

# 5.7.3 with the newly added semi-synchronous parameter, at least N slave receive the log and return ack before the semi-synchronous transaction can be committed. The default is 1. When this value is set to equal the number of slave libraries, the effect is equivalent to full synchronous replication.

# rpl_semi_sync_master_wait_for_slave_count = 2

# 5.7 New parameter that controls how the main library commits the transaction in semi-synchronous mode before returning to the session transaction successfully. The old mode is AFTER_COMMIT, and the new mode is AFTER_SYNC. The default value is: AFTER_SYNC. Master writes each transaction to binlog, passes it to slave, and flushes it to disk. Master waits for slave feedback to receive the transaction and flushes to disk. Upon receiving feedback from slave, master commits the transaction in the main library and returns the result to the session. In AFTER_SYNC mode, all clients view submitted data at the same time. If the main library crash occurs, all transactions that have been committed on the main library have been synchronized to slave and logged to relay log. Switching to the slave library at this time can guarantee the minimum data loss.

# rpl_semi_sync_master_wait_point = AFTER_SYNC

The default is on, that is, when the slave library is reduced to 0, the master library is still waiting for the slave library to send ack confirmation packets. After changing to off, that is, after the number of libraries is reduced to 0, the master database no longer waits for slave libraries, but directly becomes asynchronous.

# rpl_semi_sync_master_wait_no_slave=OFF

# semi-synchronous slave library switch. You need to confirm whether the semi-synchronous plug-in is loaded before using it.

# rpl_semi_sync_slave_enabled = 1

# 5.7.5 New parameter that defines the engine type of disk temporary table. Default is InnoDB. Previous versions can only use MyISAM (even the new version 5.6)

# internal_tmp_disk_storage_engine = InnoDB

# 5.6.3 New parameter that controls the engine type of temporary tables created by CREATE TEMPORARY TABLE. Previously, the default is MEMORY.

# default_tmp_storage_engine = InnoDB

# controls whether the synchronization process of master-slave replication starts when the mysql program starts. The default is 0 (that is, it starts with the mysql process) and 1 is closed. After shutting down and rebooting from the library, there will be a dual no state, which is meaningful for the cluster.

# skip_slave_start=1

# only record the binlog of the database specified by the parameters, which is usually used in the master library setting of the master-slave replication environment. There are some hidden dangers, and it is not usually used in this way.

# binlog_do_db=work,test

# ignore the binlog of the database specified in the parameter. It is usually used in the master library setting of the master-slave replication environment, which has some hidden dangers and is not usually used in this way.

# binlog_ignore_db=mysql,test

# Select parameters synchronously from a library, execute only the synchronization statements of a library or a table, do not deal with other libraries and tables, and cannot be selected in a single row. If there is a need for one more library, you need to write an extra line of configuration.

# replicate_wild_do_table=test.%

# Select parameters from the library synchronization, ignore the synchronization statements of a library or a table, and other libraries and tables will be executed. You cannot select more than one row. If there is a need for one more library, you need to write one more line of configuration.

# replicate_wild_ignore_table=mysql.%

# 5.7.8 is added in ms, dynamic parameter, default is 0. Control the maximum query time, the default is 0, that is, unlimited, but for the time being can only control select, depending on your database performance requirements, setting a shorter time is equivalent to disabling complex queries.

# max_execution_time = 300000

# in multithreaded replication, the maximum memory occupied by Pending events in the queue is 16m by default. If the memory is surplus or the delay is large, it can be appropriately increased. This value is larger than the max_allowed_packet of the main library.

# slave_pending_jobs_size_max = 128m

# 5.6.6 New parameter: 0 is off by default, 1 is on. The old time format timestamp has non-standard behavior. If it is not declared as null, it will be assigned as a notnull attribute. When null is inserted into this column, it will be automatically converted to a timestamp, or data such as "0000-00-0000: 00:00". The function of this parameter is to turn off this non-standard behavior, so that he can insert null. When timestamp is completely abandoned, this parameter is used to turn off this non-standard behavior. This parameter will also be abandoned. It is recommended to use datatime more often.

# explicit_defaults_for_timestamp=1

# limits the upper limit of prepared statements in all session on mysqld at the same time. Its value range is "0-1048576", and the default is 16382. This value is usually sufficient because there are not so many such requirements, but prepare statements that exceed this value will report a Can't create more than max_prepared_stmt_count statements (current value: 16382) error. Just make it bigger.

# max_prepared_stmt_count=100000

A new parameter for # MySQL5.6. You can set the Innodb data page to 8K, 4K, 32K, 64K, to adapt to more scenarios. Generally, the default is 16K. This parameter should be added to the my.cnf at the beginning of initialization. If the table has been created and modified, an error will be reported when starting MySQL. In general, we recommend that two rows of data be stored on a page, but in some cases the data is very large or small, which will waste or overflow the data page, resulting in row migration. Changing this parameter can be avoided.

# innodb_page_size = 8k

# Authentication encryption method is generally not set. The default is: mysql_native_password. You can set sha256_password after 5.7.2 to make it more secure. After mysql8.0, it defaults to: caching_sha2_password, which will disconnect the old version of mysql. The modification will only take effect for newly authorized users.

# default_authentication_plugin=mysql_native_password

# enable performance_schema performance statistics library, which is enabled by default after 5.7and disabled by default before 5.6.Note: this parameter is read-only and needs to be set before the instance is started. If it fails to open, go to the error log for troubleshooting.

# performance_schema = ON

[mysqldump]

Quick

# same as above, mysqldump for too long SQL is affected by this parameter

Max_allowed_packet = 128m

[mysql]

No-auto-rehash

Prompt= "\ u @\ h\ R:\ m:\ s [\ d] >"

[myisamchk]

Key_buffer_size = 20m

Sort_buffer_size = 20m

Read_buffer = 2m

Write_buffer = 2m

[mysqlhotcopy]

Interactive-timeout = 28800

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.