![]()
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:
- What’s with every parameter?
- What should be the parameters on db in some typical hardware configurations?
- What’s going to happen to the set?
- What happens if you don’t?
- What’s the hole to fill?
- 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:
- 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.
- 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.
- 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.
- 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).
- Read Unsubmitted (READ-UNCOMMITTED) – It is the lowest level of isolation, although most performing but not recommended
- 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.
- 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:
- 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.
- 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);
- 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!
- 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:
- 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
- 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:
- If it’s a 128 gb memory server, I suggest 64 m in my.cnf.
- 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.
- show global status like ‘created_tmp%’;
- 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:
- 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:
- You can use it if you’ve met a lot of applications: Got a packt bigger than’max_allowed_packet’ bytes
- 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.
- 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:
- 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
- Interactive_timeout: Interactive connection timeout (mysql tool, mesqldump etc.)
- wait_timeout: non-interactive connection timeout, default connection Mysql api program, jdbc connection database etc.
- 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:
- 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.
- 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:
- Innodb_io_capactiy configures 800 under sas 15000 and over 2000 under ssd.
- 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:
- This value is important, it’s invalid and decisive for writing.
- 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
- 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.
- 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:
- For smaller innodb_buffer_pool_size, we’ll make it the same as innodb_buffer_pol_size.
- 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
- 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:
- 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.
- 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:
- The master uses it for copying, and recommends Tablee.
- 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:
- 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.
- 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:
- The main is from copy, with a recommended value of 1, and it is suggested to open.
- 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:
- It’s not necessary to add flowers. Here are some best practices:
- 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!
- 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.
- 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:
- Suggests to open as 1
- 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:
- If you have to be mysql,>5.7.6 Otherwise it shall be closed.
- This parameter controls how mysql uses binlog files in an iterative manner when it starts or restarts.
- 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:
- Recommended for use: system
- 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.
MySQL User Management: Add User, Authorize, Delete User
Learn html_How websites work
WordPress Summary of Moving Methods: Migration of Hosts and Replacement of Domain Names