Mysql5.7 is a hundred times better performance than that.

Preamble:A common 76 parameters (in fact, if you spend one or two weeks reading, understanding and setting yourself up) are used throughout the text. The author has condensed the 300 million-degree project database over the last 10 years to a potential reader with only 2 weeks to get it, in accordance with the following:

  1. What’s with every parameter?
  2. What should be the parameters on db in some typical hardware configurations?
  3. What’s going to happen to the set?
  4. What happens if you don’t?
  5. What’s the hole to fill?
  6. What about the parameters and algorithms?

This type of style is written in the belief that this article will help some people who use mysql, especially those who are or will be facing 10,000-grade co-issue projects and websites. Read the document! One thousand DBAs have 1,000 configurations!Suggests use ssd on mysql serverUnless the number of rounds is always within 500-1,000, there will be no need to use ssd, just a normal high-speed disk. You’ll see that this article is a treasure, and all these parameters are capable of experimenting with an interview officer who can basically hang mesql outside.

clint domain:

1.character_set_client

Recommended settings:

utf8mb4

Role:

character set, open utf8mb4 if the front desk has a network mobile application category that includes uschat and allows for emoji expression

Consequences of incompatibility:

Mysql does not support frontend emoticons or other characters

_Other Organiser

character_set_client=utf8mb4

mysqld域:

1)server-id

Recommended settings:

If you do not make any master copying, this value can be set.

Role:

In the case of a copy from the master, there must be that value and each participant cannot repeat the server-id from the example of Mysql; it must be an Arabic numeral.

Consequences of incompatibility:

If you’re using a master copy, this id doesn’t set the whole Mysql master loses a few copies.

_Other Organiser

server-id=1

2)port

Recommended settings:

3306

Role:

Mysql instance port

Consequences of incompatibility:

Default is 3306

_Other Organiser

port=3306

3)bind_address

Recommended settings:

0.0.0.0

Role:

Unless there is a special need, we limit the possibility that only mysql cases will be asked by one of the pip parties and not supported in many ways, with production at 0.0.0.0 and then using firewall strategies to control them.

Consequences of incompatibility:

Default does not allow remote login

_Other Organiser

bind_address=0.0.0.0

4) self-commit

Recommended settings:

1

Role:

Production is open to one, and if you start with zero there’s a situation: a runs an insert statement without doing the task; b does not look for it at this time. This operation is usually used when writing a story.

Consequences of incompatibility:

If it is set to zero at the system level of my.cnf, if you want to use root as set autocompmit = one when you run it in production (99% is used 1). And if you don’t have it set to one from the start, then you can hand-held a session on a temporary basis when you need to set it to zero when you encounter some special scenes, especially writing store process.

_Other Organiser

autocommit = 1

5)character_set_server

Recommended settings:

utf8mb4

Role:

character set, open utf8mb4 if the front desk has a network mobile application category that includes uschat and allows for emoji expression

Consequences of incompatibility:

Mysql does not support frontend emoticons or other characters

_Other Organiser

character_set_server=utf8mb4

6)skip_name_resolve

Recommended settings:

1

Role:

The production proposal is to open in 1 so that mysql servicer does not use back-dns resolution of client connections, otherwise the customer connection sometimes occurs at a high speed when it runs directly timeout, and if set up at 1 the problem is that you cannot use host names in Mysql to divide client privileges, but instead use ip. If you are going to do this by allowing mesql to allow the host name to assign access to the client interface, then let’s make sure that I don’t have mysql dns resolution, write on the host/etc/hosts file of mysql, because when the client connects, mysql does not go backwards to search for client links: first find / etc/hosts files, search domain names and IP correspondence. But there is also a question about whether you want to make a dinsqort.

Consequences of incompatibility:

Mysql servicer often uses dns to interpret client connections in reverse, and there is a back-to-back timeout.

_Other Organiser

skip_name_resolve=1

7)max_connections

Recommended settings:

20,000

The maximum number of connections, with microlinks: 30,000 tps at the front end, assuming 50 per cent failure in the redis strike (this is a disaster), then the single or open connection for backendsmysql is 20,000, where our company had over 60,000 at the front end, 80 per cent was blocked by waf, vanish, cache, up to 20,000 qps on db, and according to mesql official, max_connactions is subject to the system ‘ s maximum opening connection limit, so we need two steps: 1) to add 2 lines at the bottom:/ etc/security/libits.conf

mysql hard nofile 65535mysql soft nofile 65535复制代码

2) In /usr/lib/system/system/mysqld.service (subject to how mysql is installed, a coding installation and yum installation will result in different path paths).

LimitNOFILE=65535LimitNPROC=65535$ systemctl daemon-reload$ systemctl restart mysqld.service复制代码

If the heavy server is not valid, the consequences of non-coalition:

Default only 150

_Other Organiser

max_connections = 20,000

8)max_connect_errors

Recommended settings:

Production 10 Use default-100 for development tests

Impact: 10 times on production and no defaults are used for development tests. max_connect_errors is a security-related counter value in MySQL that prevents failed clients from trying too many times to break their passwords. If you need to set this value, add it manually. When this value is set at 10. This means that if a client tries to connect the MySQL server, but fails 10 times (such as a password error), MySQL will force the client to be stopped without condition. The relevant login error information will be recorded in the table of preformance_schema.host_cache. If you want to reset the number of the countr, you must restart the MySQL server or execute the value.

Mysql> FLUSH HOSTS;

1 Mysql> FLUSH HOSTS;

When this client is successfully connected to MySQL server once, the max_connect_errors for this client will be emptied. You can use a firewall strategy to limit someip’s remote connection. If you do not match:

Default is 100

_Other Organiser

max_connect_errors =10

9)innodb_flush_log_at_trx_commit

Recommended settings:

2

The effect: (the core trading system is set to 1, the default is 1, or 2 or 0), 0 for: log buffer will write in log file every second, and log file flash (swipe to disk) operations will be performed simultaneously. This mode does not automatically trigger the writing on disk at the time of submission of the transaction. 1 means that each transaction is submitted by MySQL will write log Buffer data into log file, and flush will do it once a second (paint to disk). This model is faster (thus retaining every redo log) 2 for: Every time a transaction is submitted, MySQL may lose all data on business if you have a log file, but only if you do so on disk without having any small or large-scale arrays which would otherwise be required in order to leave this option behind.

Default is 1, affecting system writing performance.

_Other Organiser

innodb_flush_log_at_trx_commit=2

10)transaction_insulation

Recommended settings:

READ-COMMITTED

Role:

  1. This parameter directly determines the performance of mysql, oracle’s default level for matters is read-committeeed, while minesql’s default level is: recapable-read, which uses its own Gap Lock to solve “fiction.” But also because of Gap Lock, there are significant limitations on the ability of record Lock at READ-COMMITED, REEATABLE-READ to insert features.
  2. Recapable-read: This is the default isolation level of MySQL’s InnoDB engine, which prevents any line of query from being changed by other services. Therefore, blocking cannot be read over and over again, not over. This means that there may be a vision reading in repetitively. Repeating a medium-strict locking strategy so that all queries in a service can see data from the same snapshot (i.e. data at startup). Other matters may need to wait when having this level of business execute UPDATE… WHEREREREE, SELETE … FOR UPDATE and LONKIN SHARE MODE operations.
  3. Read-Commissioned – Recommendation: The service cannot see data not submitted from other services, but it can see the data submitted by another service after the current transaction has been started. Other matters may need to wait when a service of this level is run by UPDATE WHERE or DELETE WHERE. But the service can be executed by SELECT FOR UPDATE, and LONK IN SHARE MODE, and others do not have to wait.
  4. Serialization (SERIALIZABLE) – Strongly dissuasive. Serialization is the highest level of segregation, which uses the most conservative lock-down strategy. It prevents any other matter from inserting or changing data that it reads until the service is complete. In simple terms, a matter is one of execution and clearly has low performance. Under this class of isolation, the same query can be repeated in one case, with the same result per query. From the current event onwards, any attempt to change data submitted by another transaction will cause the current matter to wait (blocking). This is the default level of segregation assigned by the SQL standard (not to MySQL).
  5. Read Unsubmitted (READ-UNCOMMITTED) – It is the lowest level of isolation, although most performing but not recommended
  6. It will read changes to data not submitted by other services, and the use of this level of segregation requires great care, recognizing that search results under such levels may be inconsistent or unreplicable, depending on what other services are doing at the same time. Usually, matters with this level of isolation perform only queries without inserting, updating or deleting operations.
  7. In the actual environment, the corresponding level of isolation should be selected on the basis of whether or not dirty readings, non-readable reads and phantom reads are allowed. For example, in big data, a small amount of data inconsistency does not affect final decision-making, where lower levels of segregation can be used to submit performance and confluence.

Consequences of incompatibility:

Default is recapable-read

_Other Organiser

transaction_isolation = READ-COMMITTED

11)explicit_defaults_for_timestamp

Recommended settings:

1

Role:

  1. Mysql5.7 defaults that the ” current date of the system ” will be shown for the timestamp field, even if you leave this timestamp field empty when plug-in. It shows the date of the system as it comes out of self-check. Therefore, the range of impact of this value is caused by you during tab construction.
  2. The system default value is 0, in case of zero, you have to let the table’s timestamp field not show the system default time for Null, and your form must be: file table order (o_id int, updated_timetimestamp nuld);
  3. Explicit_defaults_for_timestamp will directly affect the table structure, which means that the time of use of extrplicit_defaults_for_timestamp is when it is defined; it is too late for yourupdate ⁇ insert to change behavior!
  4. So, I recommend that this value be set at 1.

Consequences of incompatibility:

Default is 0

_Other Organiser

explicit_defaults_for_timestamp = 1

12)join_buffer_size

Recommended settings:

16M

Role:

  1. The default size of the system is: 512k, the default size for mac is 256k and I recommend 8-16M for 128GB, 10,000 simultaneous mesql
  2. For JOIN KEY, there are indexes and secondary indices, and JoIN KEY without an index mesql will use join_buffer_size. It is generally recommended that a small GLObal value be set, but not that many of the values become larger to make an appropriate adjustment. If you slap your head at another 4g, we have 1,000 co-heads, which means we’ve lost 4T memory. We assume that our mesql is located in a vm 128gb, and that one of the join (if used) is 4M and 10,000 is not just 40G, while according to official accounts, the join_buffer_size produced together is not more than 50% smaller than your system. Default 512k is certainly small, and we can relax appropriately, for example: 2M, when actually using the scene, we find such high frequency operations (subject to meaningful sql implementation plan of high-frequency presence and confirm that it was implemented: “query_cost”: “1003179606.87”, which resulted in: 0.93 G; if it really high frequency appears at an excellent level, we would do asset session join_buffersize = 1024 * 1024 * ; this was not done from my.cnf’s first assignment of a large value based on 128 gb, and if it is present at least 16 M(16 does not mean that I did not refer to the original M8+16).

Consequences of incompatibility:

Default is 256k

_Other Organiser

join_buffer_size = 16M

13)tmp_table_size

Recommended settings:

67108864

Role:

  1. If it’s a 128 gb memory server, I suggest 64 m in my.cnf.
  2. Add a temporary table size by setting the tmp_table_size option, such as that generated by doing advanced GROUP BY. The default system is 32M. If you add up more and more of your provisional tables to exceed this value, mysql will create it on a system disk, which is not as good as it is, nor does it have any appropriate value.>64M, and then we run through the following formula for ad hoc promotion.
  3. show global status like ‘created_tmp%’;
  4. Among the results obtained: (Created_tmp_disk_tables /Created_tmp_tables)* 100% if<= 25% is the best value. Note that a similar algorithm must be used when setting heat at production:
  5. Set global tmp_table_size=64*1024*1024 instead of set global tmp_table_size=64M.

Consequences of incompatibility:

Default is 32M

_Other Organiser

tmp_table_size = 67108864

14)tmpdir

It’s a parameter that allows for delivery, just put it in the big space. There’s nothing too sensitive.

15)max_allowed_packet

Recommended settings:

134217728

Role:

  1. You can use it if you’ve met a lot of applications: Got a packt bigger than’max_allowed_packet’ bytes
  2. Showables like ‘%max_allowed_packet%’; this value is not suitable to view, as typically when importing data from a client, error codes are encountered: 1153 – Got a packt bigger than ‘max_allowed_packet’ bytes. In such settings, when MySQL clients or mysqld servers receive information packages larger than max_allowed_packet bytes, they send “overpack” errors and close connections. For some customers, if the message package is too large, there may be an “missing connection with MySQL server” error during the query.
  3. Clients and servers have their own max_allowed_packet variables, so if you want to handle large information packages, this variable must be added to the client and server. In general, the server defaults that max-allowed-packet is 1MB, which can be judged by capturing the package on a switch or graphically analysing it. It is generally recommended to set the setting at 128M. It can also adjust dynamically while running: set global max_allowed_pact = 128*1024*1024

Consequences of incompatibility:

1M

_Other Organiser

max_allowed_packet = 134217728

16)sql_mode

There’s no need to set up, use default. This piece has nothing to do with performance. If we have a sql in the center, because it’s already been reported in the test environment, it’s used to limit your sql writing, and if it’s an application developed from scratch, we say that restraints are all ansi sql writing, not for a product, but for a painting snake.

17)interactive_timeout

Recommended settings:

600

Role:

  1. If the two are too large, you will notice that there is a lot of sleep connections in mysql, and these links are called: zombie connections, which we throw when you really need them for one more time: too error, so we usually use “tick-out mechanisms” for long-term unused connections. How long do I kick an activity? The conservative point, I think, can be set to 10 centimetres, and good applications at the application end of which it connects to a database through jdbc will have an autoconect parameter in jdbc that matches the water_timeout in mysql. At the application end, there is also an operation like validate sql to keep alive.<These two values in mysql will be the best, while idleConnectTestTestPeriod uses a different step to make timecheck. For example:idleConnetionTestPeriod and testConnectOnCheckin are reliable
  2. Interactive_timeout: Interactive connection timeout (mysql tool, mesqldump etc.)
  3. wait_timeout: non-interactive connection timeout, default connection Mysql api program, jdbc connection database etc.
  4. The so-called interactive connection, i.e. the CLIENT_INTERACTIVE, is used in mysql_real_content() function.
show global variables like 'wait_timeout';复制代码

1 timeout has an impact on idle sessions only. 2 session level wait_timeout inherits value of global level inactive_timeout. 3 interactive session time is influenced by Global level interactivity_timeout. Therefore, changes to timeout under non-interactive mode must be made simultaneously with the value of intraactive_timeout. 4 In non-interactive mode, Wait_timeout parameters inherit water_timeout at global level.

System default is 28800

_Other Organiser

interactive_timeout = 600

18)wait_timeout

Both values are set the same as inactive_timeout.

19)read_buffer_size

Recommended settings:

4194304

Impact: This value is simply not available because it affects only two scenarios and not all of them, without the ability to read multiple blocks from mysql5.6 onwards, regardless of whether or not a read_buffer_size parameter is set. Application: 1) SELECT INTO…

Default is 64k

_Other Organiser

read_buffer_size = 4194304

20)read_rnd_buffer_size

Recommended settings:

8388608

Impact: MySQL, when a data block needs to be read in order to satisfy certain sequences, will need to generate random reading and then use the memory buffer set by the read_rnd_buffer_size parameter. Its default is 256k, maximum can be set at 2G, which works with order by keywords, when the planned cost of orderby exceeds sord_buffer_size, mesql produces random reading and consumes additional content, and many outside bloggers say that it works only for the Myisam engine, but it does not actually cover all engines, generally its recommended settings are 8-16M, and I recommend 8M, and if sql analyses involve high-frequency queries and orderby’s return package is large, they will be released at the level of the Ssession.

Default is 256k

_Other Organiser

read_rnd_buffer_size = 8388608

21)sort_buffer_size

Recommended settings:

4194304

The size of the memory assigned to each session in order to execute a sorting operation. To increase max_sort_length parameters, it is necessary to expand the sort_buffer_size parameter. If you see the SHOW GLOBAL STATUS output with large parameters per second, then you can consider increasing the value soor_buffer_size to improve the processing speed of ORDER BY and GRUP BY. It is recommended that 1-4MB be set. When individual sessions require significant sequencing operations, this parameter is increased at the level of the conversation. So-called session levels, I take an example of what you have to say about 32M, which you have on 10,000 requests, which is much larger. And if mymsql are stored, what effect would be 30% of the mass?

Default value is 1M

_Other Organiser

sort_buffer_size =4194304

22)innodb_page_size

Recommended settings:

8192

Role:

  1. This value can be carefully updated, generally not easily after setting. It is generally considered that the larger the value, the better it is, the higher the size of the memory division particles, and the higher its usage. The other problem is to limit the size of the index fields and the entire rows. Innodb engines read and update their contents, which are updated by a page, and this innodb_page_size determines the size of a basic page. The B+Tree index, the B+tree tree, is designed as a balanced search tree for diskettes and other storage aids (not two-knock trees). If all the records are stored in half the size order of the same leaf node, then each leaf is connected with a small point of reference. MySQL sets the size of each leaf node into one page, but it’s probably too much more than double the size of the data sheet. But if I go over one page, I can only take up a little bit more than the size of the space. Mysql’s page is tick point too often.
  2. This is a test tool that we use to observe throughputs, or tps, measured in the four-k, eight-k, 16-k scenario under full-scale production environments. I can give you a recommended value for using single tables of over 1,000 w data based on the database results from medium 1.1 (over 1000 w per table), which are best at 8K.

Consequences of incompatibility:

Defaults for 32-bit and 8192, 64-bit and 16384.

_Other Organiser

innodb_page_size = 8192

23)innodb_buffer_pool_size

Recommended settings:

72G

This value and innodb_buffer_power_instances complement each other. Under 32 machines, innodb_buffer_pol_instances is typically 1 and on 64 machines the value of which is 8-64. Pool_instances is actually cpu core, its effect is: 1) for a system within thousands of megabytes of buffers, by reducing the competition for reading and writing from different lines to narrow pages, it can be improved and produced by dividing the buffer into several separate examples. 2) Using hash function will assign randomly one example of a buffer pool to each page that is read from the buffer. Once the swap space is squeezed, your backside of mysql is severely slow to read and write. It is strongly recommended that there be 20-65% memory (independent Mysql server), why should one be 20%?<Mysql of 4gb is stored in 20% of the system on a server. As we are memory for 128gb, I suggest 72G here, and if the memory exceeds 128gb, we usually set up a load approach of 16, each with about 10 g of buffer_pool_size, which can be done for 256gb memory servers.

Default is 64

_Other Organiser

innodb_buffer_pool_size = 72G

24)innodb_buffer_pool_instances = 8

This parameter was shared with Innodb_buffer_pool_size.

25)innodb_buffer_pool_load_at_startup

Recommended settings:

0

Mysql was empty for the first time (restarting), and as mysql ran for 1-2 hours, its buffer_pol_size began to be stuffed into something, divided between old block and new block, when mysql performance started a little bit of reading-writing efficiency, because much of mysql reads on hard drives, from hard drive to inner memory, so that it often creates a startup=1 to speed up this “preheating” process, which is used in conjunction with the parameter shutdown, which is equivalent to putting things in innot_d_b_buffer_pool at the start of the previous use, so as to accelerate mesql’s sexuality. High note: When you add the startup and shutdown = 1, mysql will be slow to start, and if there are more than 50 g of what you did with the dump’s buffer_pool, then mysql starts slowly. The effect of this is that many people use default zero (not open), which is your system reading and writing performance after Mysql restarts, at first not as good as it was when your system was running for two or four hours (depending on db reading and writing).

The default system is 0 if it doesn’t fit

_Other Organiser

innodb_buffer_pool_load_at_startup = 0

26)innodb_buffer_pool_dump_at_shutdown

Parameters for the startup and explanation of

27)innodb_lru_scan_depth

Recommended settings:

2000

Role:

  1. Innodb_io_capactiy configures 800 under sas 15000 and over 2000 under ssd.
  2. You can use the default configuration. It is not set.

Consequences of incompatibility:

Default is 200, db swallows no more.

_Other Organiser

innodb_lru_scan_depth = 2000

28)innodb_lock_wait_timeout

Recommended settings:

60

Impact: We usually encounter the mistake of mysql innod_lock_wait_timeout, which is caused by slow sql. It represents a transaction lock that slows sql has exceeded the time frame set for mesql locking. The default value is 50s, this value can be changed dynamically, and I do not recommend changing it because one sql can reach the limit of 50s? Dynamic change orders are as follows: SHOW GLOBAL VARIAABLES LIKE ‘INKNOD_bit_meanout; make 60s sufficient if not assigned:

Default is 50s

_Other Organiser

innodb_lock_wait_timeout = 60

29)innodb_io_capacity_max

Recommended settings:

8000

Role:

  1. This value is important, it’s invalid and decisive for writing.
  2. It directly determines mysql’s tps, for which reference is given: sata/sas hard drive at 200. sas rad10:2000, ssd hard drive: 8,000, fusion-io (clause): 25,000-50,000
  3. This is based on ssd, which is set at 8,000. The last company, Internet Finance, threw a whole piece of mysql into the flash bank, so it’s set at 50,000.
  4. It’s based on the hard drive number of a pas or an ias vm.

Consequences of incompatibility:

Default is 200, the system won’t swallow up.

_Other Organiser

innodb_io_capacity_max = 8000

30)innodb_io_capacity

It’s half of the io_capacity_max, and it’s also decisive for writing when reading invalid.

innodb_io_capacity_max = 4000

31)innodb_flush_method

Recommended settings:

O_DIRECT

Impact: Recommended to use O_DIRECT. Together, let’s understand that there are three models: 1) fdatasync (int fd) function, most frequently mentioned above, which is used by flush to write to disks with a buffer associated with the document described in the fd file, and to slow down within the DIREECT mode of writing data information (e.g. modification date, creation date, etc.) for flush to count flush success because it is done directly from Mysql Innodb Buffer to disk, without having to read or write too often 2) O_DIRECT indicates that our write operation is written directly on the disk from mysql innodb but also through the real completion of the operating system, whether it will go through the OS buffer or O_DERT ‘ s more than SQL if it does not have any effect.

Its default value is fdatasync.

_Other Organiser

innodb_flush_method = O_DIRECT

32)innodb_file_format

Recommended settings:

Barracuda

Effects: Recommended Barracuda mode. It is used to enable table compression, such as: CREATE TABLE `test_1` (

`x` Innodb_file_format has several models: Antelope-Fairset =utf8mb4 ROW_FORMAT = COMPRESSED KEY_BLONK_SIZE=8; upon completion, two new forms of data tables can be supported by showtable status like ‘test_1’. Innodb_file_format is therefore recommended to use the Barracude model because it is compatible with other data patterns. It can also change dynamically if SETLOEL_Add_orf_barra is not available:

By default, it uses what is called a “joint model”, i.e. not a leprechaun or antelope.

_Other Organiser

innodb_file_format = Barracuda

33)innodb_file_format_max

This parameter must be consistent with the innodb_file_format parameters, bearing in mind that otherwise it is not effective.

34)innodb_log_group_home_dir = /redolog/

That doesn’t have to be explained. It’s stupid. It’s a path that can work, remember hanging under the big disk.

35)innodb_undo_directory = /undolog/

That doesn’t have to be explained. It’s stupid. It’s a path that can work, remember hanging under the big disk.

36)innodb_undo_logs = 128

Recommended settings:

128

Effect: Specifies the number of rollback segments (an early version of this parameter is named innodb_rollback_securitys), with 128 default. Each spinback segment supports 1,024 online services simultaneously. These scrollback sections are distributed equally across undo table spaces. The variable can be dynamically adjusted, but the physical rollback section will not decrease, and it will control only the number of rollback segments that are used. SSD is now very popular. Innodb_undo_logs can be accepted as being 128 unchanged if you do not match:

Default is 128.

_Other Organiser

innodb_undo_logs = 128

37)innodb_undo_tablespaces

Recommended settings:

3

Impact: 3. Default to set a separate number of undo table spaces for 3; e.g., if set to 3, the undo chart space is undo001, undo002, undo003, with the initial size of each file defaulting 10M. This parameter is recommended to be set at > 3, and more debris files will affect the ou properties of disks, but not enough debris will also affect mysql ‘ s rate of throughput, which would normally best be configured on sd in 3. If there was only 1 undo table space, then the entire system would not be available in this process. In order to minimize the impact of truncate on the system, it is suggested that the parameter should be set at least 3;

Default is: 3

_Other Organiser

innodb_undo_tablespaces = 3

38)innodb_flush_neighbors

Recommended settings:

Recommended: 0

This parameter is very important, and we have now set it to zero (not open) in the prevailing ssd situation. If you set it up as one-opening (default status), InnoDB will refresh all pages of an extent because SSD does not need to activate this feature because there is no additional load on a random IO, so it opens more than that sentence. Here’s a quote from the readme read by mysql5.7 code compiler:

This new default changes MySQL to cater for SSDs and fast storage devices by default. We expect that for the majority of users, this will result in a small performance gain. Users who are using slower hard drives may see a performance loss, and are encouraged to revert to the previous defaults by setting innodb_flush_neighbors=1.

Consequences of incompatibility:

Its default is one, not zero, which works well for machine hard drives. It reduces randomio and increases performance. If it’s a ssd disk, it is suggested that it be set to 0, so you can refresh the dirty pages faster. If you set it as one while being ssd, then it doesn’t seem necessary.

_Other Organiser

innodb_flush_neighbors = 0

39)innodb_log_file_size

Recommended settings:

Step 1: Show engine innodb status; Received: Log number 294412884 Log flushed up to 2941182883 Last checkpoint at 2744318261 Step 2: set-up innodb_log_file_size$=log Log security number-last checkpoint at=selact (29441284-27244318261)) 1024/1024; = 209M Step 3: Set-up of real innodb_log_file_size<= (innodb_log_files_in_group*innodb_log_file_ size)*0.75, innotdb_log_file_side*2(default), obtained: step 4: self209 (2*0.75); and =139.33, i.e. 139m, at which point this value can be set to 140 M

Impact: The default for this value is 5M, which is far from sufficient. If there are many write-up actions to complete the installation of Mysql, then selecting a suitable Innodb_log_file_size value is important to enhance MySQL performance. However, setting too large would increase recovery time and thus allow for a long period of time when MySQL collapses or abrupt power outages take the MySQL server back up. The larger the Redo space you have, the better it is written; However, an increase in the Redo space means a longer recovery time when there is an accident such as a crash or power outage. How do we test the methodology that follows: In general, we can estimate the recovery time for every 1GB Redo log approximately five minutes. Adding this value can be a more significant step up of the system. 2) Uncheckpointed Bytes is much smaller than Max Checkpoint Age, and innod_log_file_size will not significantly improve its performance. After adjusting log_file_size we go to pmm to see: Redo Log spatial indicators, for example, if you do not have pmm, we have 60 g data that are written into the log file within an hour, which means about 10 g data every ten minutes would be “writing the diary”, which we need to remember, the longer the day-to-day writing takes, the less frequent it occurs, the better it helps mesql’s innodb properties. So this value is not absolutely recommended. If you don’t have pmm, then I have detailed examples of human count!

The default is 5M, which certainly isn’t enough.

_Other Organiser

innodb_log_file_size = 140M

40)innodb_log_buffer_size

Recommended settings:

16777216

Role:

  1. For smaller innodb_buffer_pool_size, we’ll make it the same as innodb_buffer_pol_size.
  2. And when more than 4gb’s innodb_buffer_pol_size, our suggestion is to cut it enough, which is the feature of mysql5.7, whose default is 8m, but for a large number of things Isql, we suggest setting it on the writing operating library: 16 m
  3. This parameter determines the memory size used for some log files, in M. The buffer zone is much more efficient, but unexpected malfunctions will lose data. The official profile suggests between 1 and 8M!

Consequences of incompatibility:

Default is 8M.

_Other Organiser

innodb_log_buffer_size = 16777216

41)innodb_purge_threads

Recommended settings:

0

This parameter, which I recommend to set as follows: (SRV_FORCE_CORRUPT): ignores the check page. (SRV_FORCE_NO_BACKROUND): Prevents the operation of main circuits, such as the main route requires full purge operations that would result in grash. (SRV_FORCE_NO_TRX_UNDO): does not perform a rollback operation. (SRV_FORCE_NO_IBB_MERGE): does not implement a consolidation operation into a buffer, as is required by the main route, and will cause full purge operation. (SRV_FORCE_TRX_UNDO_UNDO)

innodb_force_recovery = 6

innodb_purge_threads = 0

Remember, once innodb_force_recovery>At 0200 hours, innodb_purge_threads will be set to 0.0 for the consequences of non-compensation:

Default not to set, it’s no good. Problems arise before recover needs them.

_Other Organiser

innodb_purge_threads = 0

42)innodb_large_prefix

Recommended settings:

1

Role:

  1. If your client and service end character set is utf8mb4, why do we need to turn this switch on? Mysql was a single index restriction of 767 until 5.6, due to 256 x 3-1. This 3 is the maximum space occupied by characters (utf8). But after 5.6, support for 4 bytes uutf8. 255 x 4.>767, and this parameter was added. The default value for this parameter is OFF. When you change to ON, the column index is allowed up to 3072.
  2. This switch is called “on, off” in mysql5.6. And it’s called zero and one in 5.7, because we have an utf8mb4 in front of us, so we have to open this parameter here.

Consequences of incompatibility:

It doesn’t deserve questions, especially if the index is not valid or best planned. If your character set is utf8mb4, then this value must be opened.

_Other Organiser

innodb_large_prefix = 1

43)innodb_thread_concurrency

Recommended settings:

The number of cpus installed on mysql server

Role: For example, 64 Ncpu, then recommended: 64<= cpu) If a task load is smaller than or equal to 64, it is recommended that the innodb_thread_concurrence=0; and actually our system is in big and large business. The recommendation is set at 128, then we keep dropping this value until we find the number of threads that will provide optimal performance. For safety starters, we’ll put it on the same size as cpu.

Default will be 8 in 64 slots.

_Other Organiser

innodb_thread_concurrency = 64

44)innodb_print_all_deadlocks

Recommended settings:

1

Impact: Recommendation 1: When a dead lock occurs in mysql database, the information on the last death lock will be recorded in Innodb status, but not in mysql error log. To record it, start with the innodb_print_all_deadlocks parameter. If you do not have consequences: this information will not be recorded.

innodb_print_all_deadlocks = 1

45)innodb_strict_mode

Recommended settings:

1

Starting with version MySQL5.5.X, you can open the InnoDB strict check mode, especially when using page compression, which is best activated. When this function is opened, when creating tables (CREATE TABLE), Alter TABLE and indexing (CREATE INDEX) words, there will be no warning information if it is written wrong, but instead throws it out, so that the problem can be suffocated directly in the cradle if the consequences are not matched:

If the old ddl statement is not matched by a development or unprofessional dba, it will take effect in 5.7. Another problem is that there are no obvious errors in the ddl language, which affects “the master from copying”, and as to why dll would affect the main reproduction, we will explain them in detail later in “slave_skip_errors = ddl_exist_errors”.

_Other Organiser

innodb_strict_mode = 1

46)log_error

The location of the error log, which is not much to say, can be placed in the same path as mysql log and file names can be distinguished from other logs.

47)slow_query_log

Suggested startup

48)slow_query_log_file

Slow sql, which is not much to talk about, can be placed in the same path as mysql log, and file names can be distinguished from others.

49)log_queries_not_using_indexes=1

It’s a strong suggestion to open it up.

50)log_slow_admin_statements = 1

It’s a strong suggestion to open it up.

51)log_slow_slave_statements = 1

It’s a strong suggestion to open it up.

52)log_throttle_queries_not_using_indexes

Recommended settings:

We’ll open 30 to 50 in the early days after we get online. As performance improves, we’ll turn this into 10.

It’s not stable for a while before we go online, and there are dozens of sqls that didn’t go in index with the consequences:

It doesn’t deserve to be opened. It’s suggested.

_Other Organiser

log_throttle_queries_not_using_indexes = 50

53)expire_logs_days

Recommended settings:

30

It’s not too much, because you’re not a dojo and you can’t let binlog take your disk space indefinitely. Remember that once the value is small, you need to have a bein log backup strategy, 30 days, if your beinlog backup is effective and doesn’t take mysql’s disk space.

Default is 0, i.e. never expire.

_Other Organiser

expire_logs_days = 30

54)long_query_time

Recommended settings:

10

Effect: Default is 10 seconds, that’s all.>=Sql will be recorded. I suggest setting it to 10 (by default) at the beginning of the lineup, and making it easier to move from sql to 1. Because the lower the seconds, the more sql records, the slower mysql records, the slower the mesql. It is not entirely dependent on mysql’s slow sql log but rather should have a druid sql real-time viewer or a pm or professional slow sql searcher.

Default is 10

_Other Organiser

long_query_time = 10

55)min_examined_row_limit

Recommended settings:

100

Effect: This value corresponds to the slow search sql log used, even if a query specified as line less than that value is not recorded as “sql log slow”. The consequences of failure are:

If you don’t open it, the rule of long_query_time of slow sql.

_Other Organiser

min_examined_row_limit = 100

56)master_info_repository

Recommended settings:

TABLE

Role:

  1. The master uses it for copying, and recommends Tablee.
  2. From the main node information to file, two files of master.info and relay-log.info are generated when you set them to file. Set this as table. The information will be in mysql.master_slave_info table. Do not move or edit the relevant files and tables regardless of the value set.

Consequences of incompatibility:

If you do not fit, save it in file format.

_Other Organiser

master_info_repository = TABLE

57)relay_log_info_repository

Recommended settings:

TABLE

Role:

This parameter must be consistent with the above-mentioned master_info_repository, otherwise it will not be possible to reveal mysql examples. But it is important to note that these tables are defaulted on a MyIsAM engine and that if they are to start in TabLE mode, then make sure that the engines of both tables are changed to innodb.

alter table slave_master_info engine=innodb;alter table slave_relay_log_info engine=innodb;alter table slave_worker_info engine=innodb;复制代码

Consequences of incompatibility:

This parameter has to be consistent with the above-mentioned master_info_repository, otherwise mysql example cannot be revealed.

_Other Organiser

relay_log_info_repository = TABLE

58)log_bin = bin.log

Mainly from copying, mainly from the bin.log log where the copy is made.

59)sync_binlog

Recommended settings:

1

Impact: The value is used from the time of reproduction, depending on business. It can be 0,1, not zero in three settings. 1) – On behalf of mysql, it does not control writing binog, which is freely controlled by file system, where mesql ‘ s co-activity is at its best, but if you lose much of the data that will be written into binlog (e.g., you are deleting and updating)2 – Most safely, you lose a matter or a sentence at most, but at this point your performance is poor, with a performance between 0 and 1 being four to five times different. 3) If you use a multimblaze high-speed disk image or sddata and bin log both under one directory, you can open up to 1.

Default is 0

_Other Organiser

sync_binlog = 1

60)gtid_mode

Recommended settings:

I’m sorry.

Role:

  1. The main source is used for copying and recommends onon, which is useful in allowing you to “back up” from the library, where it can access the binlog bits of the repository while backuping.
  2. This parameter can also be set dynamically online. If you want to run online, it must be set as anon in my.cnf file. When opening this parameter, log-bin and log-slave-updates must also be opened, otherwise MySQL Server refuses to start, and when the GTID mode is activated, all MySQL Servers of a cluster must also configure gtid_mod=ON, otherwise no sync is possible.

Consequences of incompatibility:

Default is off

_Other Organiser

gtid_mode = on

61)enforce_gtid_consistency

Recommended settings:

1

Effect: Mainly from the time of reproduction, see gtid_mode, which is a reference parameter and opens with the opening of gtid_mode.

It has to be opened with gtid_mode, otherwise the Mysql example won’t come up.

_Other Organiser

enforce_gtid_consistency = 1

62)log_slave_updates

Recommended settings:

It’s just marked in my. cnf to mean that it works.

Effect: The main use from the time of reproduction is gtid_mode, which is a connecting parameter and opens with the opening of gtid_mode. It simply points to it here, represents it, or not.

It’s the connecting parameter that opens with the opening of gtid_mode.

_Other Organiser

log_slave_updates

63)binlog_format

Recommended settings:

# Row #

1. STATEMENT: Long history, technological maturity, small binlog file, which contains all database changes that can be used to verify the security of databases. Binlog can be used for real-time reduction rather than just copying the main version from a different version and higher from the server version than the main server version. The disadvantage is that not all UPDATE statements are copied, especially when they contain uncertain operations. Reproduction may also be problematic when calling UDF with uncertainty, and the following functions cannot be copied:

* LOAD_FILE()* UID()* USER()* FOUND_ROWS()* SYSDATE() (unless the –sysdate-is-now option is enabled on startup)

At the same time, INSERT … SELEENT produces more row locks than ROW. Reproduction of UPDATE requires full table scanning (which is not indexed in the WERERE statement) will require more row locks than RBR requests 3. For InnoDB tables with AUTO_INCREMENT fields, INSERT statements may be a good thing, and UEF will also need to be executed from servers, which must be almost consistent with the main server, otherwise they could lead to the replication of errors, or if there are mistakes in the execution of complex words, it would consume more resources. 2. ROW: any memory function (not storage process) can be replicated while using NOW() functions, which may be considered as bad, but also as much as most other IS systems would have been used in the case of fewer T-Crepectors. * The USEF has been identified as being implemented on servers, and data forms must be identical to the primary server, otherwise it would have led to the repetition of complicated phrases. *Insert*, which contains AUTO_INCREMENT fields without conditions or changes to many records of UPDATE or DELETE statements, executes INSERT, UPDATE, DELETE statements with fewer locks and allows for replication from servers using multiple threads. The disadvantage is that the inlog is much larger, and when you roll back complex, binlog contains a large amount of data. When you execute the UPDATE statement on your main server, all changes are written in binlog, while SBR only writes once, which leads to frequent complication problems, and the UDF produces big BLOB values that slow down reproduction and do not allow any copying to beinlog. From security and stability considerations, we choose the ROW model. 3.

5.7.6 Default for STATEMENT mode. MySQL 5.7.7 after default for ROW mode

_Other Organiser

binlog_format = row

64)relay_log

Main from copy, define the location and name of relay_log, if the value is empty, the default position in the directory (datadir) of the data file, which is called host_name-relay-bin.nnnnn (By print, play log file names have the form host_name-relay-bin.nnnnnn in the data directory)

65)relay_log_recovery

Recommended settings:

1

Role:

  1. The main is from copy, with a recommended value of 1, and it is suggested to open.
  2. When slave is disabled from the library, if relay-log damages a part of the relay log that has not been processed, then all unexecuted relay-logs are automatically abandoned and retrieved from Master, thus safeguarding the integrity of relay-log. By default, when the function is closed, it can be opened on the slave library with values set to 1.

Consequences of incompatibility:

The default is closed.

_Other Organiser

relay_log_recovery = 1

66)slav_skip_errors

Recommended settings:

ddl_exist_errors

It’s not supposed to be in my.cnf, it should have disappeared or been expressed: #slave_skip_errors = ddl_exist_errors, but sometimes some of our tables (especially some developments that are unfamiliar with mesql) really use the old version of Mysql5.6 statement, which is hard to find under a single machine model at normal times, and once the master has a structure on top, there is a real chance (10-20% probability) that the ddl sentence will run on mysql, and this time we leave behind an unrecognised error from Masters5. So then we need to get data, so this table is already done, and it has little impact, so we can ignore it.” This is my blood experience, why not.

If the table statement and mysql5.7 run under a single instance mode because of their conflict, they will not find that if there is no skipping value when the main copying takes place, it affects the master from copying, as shown by the failure of the main copy.

_Other Organiser

slave_skip_errors = ddl_exist_errors

67)innodb_buffer_pool_dump_pct

Recommended settings:

25~40

Role:

  1. It’s not necessary to add flowers. Here are some best practices:
  2. Usually we’ll set it at 25%. For the big sum, we’re gonna use 40 values, which is the bigger and the longer mysql starts. It’s your innodb_buffer_pool_size!
  3. MySQL defaults to retain only 25% of the most frequently accessed page in the InnoDB buffer pool (rather than the entire buffer pool). Note that this variable is based on actual data in memory, not on the size of a buffer pool. For example, if there are 100 GB buffer pools, only 10 GB data, and only 25% of 10 GB data (or 2.5 GB) is by default saved in memory.
  4. In most use scenarios, the logical choice is to keep the most useful data pages faster than loading all of them (many pages may not be visited in subsequent work). You can change the value of innodb_buffer_pol_dump_pct variables.

Consequences of incompatibility:

It is not effective if it does not.

_Other Organiser

innodb_buffer_pool_dump_pct=25

68)innodb_page_cleaners=8

This value will normally be set in the event of a major delay, preferably =innodb_buffer_pol_instance, which is the cpu number.

69)innodb_undo_log_truncate

Recommended settings:

1

Role:

  1. Suggests to open as 1
  2. Set the parameter innodb_undo_log_truncate to 1, i.e. open an undo log file for online recycling (shrink) to support dynamic settings.

Consequences of incompatibility:

It is not effective if it does not.

_Other Organiser

innodb_undo_log_truncate=1

70)innodb_max_undo_log_size

Recommended settings:

Recommended twice the default value (default 1GB)

Effect: It is recommended to double the default value (default 1GB), which we do not usually easily set. This value is related to innodb_undo_tablespaces, innodb_undo_logs and innodb_purge_rseg_truncate_frequency, which are linked. 1) Innodb_undo_tablespaces must be connected.>Innodb_undo_logs must open 3) Innodb_purge_rseg_truncate_frequence must be opened:

The system is calculated at 1 GB.

_Other Organiser

innodb_max_undo_log_size=2G

71)innodb_purge_rseg_truncate_frequency

Recommended settings:

128

Effect: The default value is 128, which is not much to touch. Controls the frequency of undo log. Specify how many times a purge operation is called before the rollback releases. Undo watch space is truncate only when the rollback settings in undo watchspace are released. Thus, the smaller the parameter, the higher the undo watch space is tried.

The system defaults to: 128 for setting.

_Other Organiser

innodb_purge_rseg_truncate_frequency=128

72)binlog_gtid_simple_recovery

Recommended settings:

Suggested startup

Role:

  1. If you have to be mysql,>5.7.6 Otherwise it shall be closed.
  2. This parameter controls how mysql uses binlog files in an iterative manner when it starts or restarts.
  3. Set this option to be true, which enhances the ability of mysql to perform restoration. This way mysql-server starts and binlog log cleans faster. When this parameter is real, mesql-server only needs to open the oldest and latest two binlog files.

Consequences of incompatibility:

Default is 0

_Other Organiser

binlog_gtid_simple_recovery=1

73)log_timestamps

Recommended settings:

system

Role:

  1. Recommended for use: system
  2. This parameter is mainly a display time in logs such as error logs, slow query logs. It does not affect the display time in tables (mysql.general_log, mysql.slow_log) of query logs and slow logs, which are global and can be dynamically modified.

Consequences of incompatibility:

Default value: UTC

_Other Organiser

log_timestamps=system

74)transaction_write_set_extraction

Recommended settings:

It’s not a value, because you don’t use mysql8.0, and after version 5.7.6 the system is not very mature and will normally be used if it is to open: XXHASH64.

Effect: This value is based on group (parallel) reproduction, with a recommended value of XXHASH64. If no copy based on group (parallel) is opened, do not set this parameter and the settings are not set, so that default is maintained.

Default is off state, which is not effective.

_Other Organiser

transaction_write_set_extraction = OFFtransaction_write_set_extraction = XXHASH64transaction_write_set_extraction = MURMUR32复制代码

75)show_compatibility_56

Recommended settings:

I’m sorry.

Effect: Recommended to open. This parameter is compatible with the INFORMATION_SCHEMA.GLOBAL_STATUS function, which allows for a transition from 5.6 to 5.7 that is not part of mysql ‘ s full-time dba but understands mysql ‘ s transport.

Default is off. It’s a strict pattern.

_Other Organiser

show_compatibility_56=on

So, at the end of this paper, 75 methods and detailed explanations for mysql5.7 performance are presented. It is hoped that they will help you!

♪ I’m going to be the one who’s going to be ♪https://juejin.cn/post/6844904152166842376
Source: Excavation Gold Copyright is vested in the author. Commercial reproduction is requested to be authorized by the contact author, and non-commercial reproduction is indicated.

未经允许不得转载:萌萌Apprentissage » Mysql5.7 is a hundred times better performance than that.

Like (0) 打赏

Comments 0

Protected with IP Blacklist CloudIP Blacklist Cloud
  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

//下一个脚本特效
//下一个脚本