1. Hardware layer related optimization

1.1, cpu related

In the bios settings of the server, the following configurations can be adjusted,The purpose is to maximize the performance of the cpu,Or avoid the classic numa problem:

(1) Select the performance per watt optimized (dapc) mode to maximize the performance of the CPU.Do not consider power saving when running db, a service that usually requires a high amount of computation;

(2) Turn off options such as c1e and c states, the purpose is also to improve cpu efficiency;

(3) Select the maximum performance for the memory frequency;

(4) In the memory setting menu,Enable node interleaving to avoid numa issues;

1.2, disk i/o related

The following are sorted according to the magnitude of iops performance improvement.Some measures that can be optimized for disk i/o:

(1) Use ssd or pcie ssd equipment to get at least hundreds of times or even 10,000 times the iops improvement;

(2) The purchase of an array card equipped with both cache and bbu modules can significantly increase iops (mainly refers to mechanical disks,Except ssd or pcie ssd. At the same time, the health status of the cache and bbu modules needs to be checked regularly.To ensure that no data is lost in case of an accident);

(3) When there is an array card,Set the array write strategy to wb, or even force wb (if there is dual power protection,Or if the data security requirements are not particularly high), the use of wt strategy is strictly prohibited. And closed array read-ahead strategy,Basically chicken ribs,Not very useful

(4) Choose raid-10 instead of raid-5 if possible;

(5) When using a mechanical disk,Choose as high as possible,For example, choose a disk of 15krpm instead of 7.2krpm, which is not bad.

2. System layer related optimization

2.1, file system layer optimization

At the file system level,The following measures can significantly improve iops performance:

(1) Use two kinds of i/o schedulers:deadline/noop. Do not use cfq (it is not suitable for running db services)

(2) Use xfs file system,Never use ext3;ext4 is barely available,But if there is a lot of business,Be sure to use xfs;

(3) Added file system mount parameters:noatime, nodiratime, nobarrier options (nobarrier is unique to the xfs file system);

2.2. Optimization of other kernel parameters

Set appropriate values ​​for key kernel parameters,The purpose is to reduce the tendency of swap and keep the memory and disk i/o from fluctuating greatly.Causes instantaneous peak load:

(1), set vm.swappiness to about 5-10,It is even set to 0 (rhel 7 and above is carefully set to 0 unless you allow oom kill to occur) to reduce the chance of using swap;

(2) Set vm.dirty_background_ratio to 5-10 and vm.dirty_ratio to about twice,To ensure that dirty data is continuously flushed to disk,Avoid instant i/o writes and generate serious waits (similar to innodb_max_dirty_pages_pct in mysql);

(3) Set net.ipv4.tcp_tw_recycle and net.ipv4.tcp_tw_reuse to 1, reduce time_wait and improve tcp efficiency;

(4) As for the two parameters of read_ahead_kb and nr_requests transmitted by the Internet,After I tested,I found that the oltp environment with mixed reading and writing is not significant (it should be more effective for reading-sensitive scenes), but maybe it is a problem with my test method.Can decide at their own discretion whether to adjust;

3, MySQL layer related optimization

3.1 About version selection

The official version is called oracle mysql. There is nothing to say about this.I believe most people will choose it.

I personally strongly recommend choosing the percona branch version,It is a relatively mature and excellent mysql branch version.Many improvements have been made in performance improvement, reliability, and management.It is basically completely compatible with the official oracle mysql version,And the performance has been improved by about 20%,So I recommend it first,I myself have been focusing on it since 2008.

Another important branch version is mariadb. It is actually not appropriate to say that mariadb is a branch version.Because its goal is to replace oracle mysql. It mainly makes a lot of source-level improvements in the original mysql server layer.It is also a very reliable and excellent branch version.But it also resulted in new features represented by gtid that are incompatible with the official version (since mysql 5.7, it also supports gtid mode to be dynamically turned on or off online), considering that most people will still follow the official versionTherefore no priority is given to mariadb.

3.2. Suggestions on the adjustment of the most important parameter options

It is recommended to adjust the following key parameters for better performance:

(1) If you choose percona or mariadb version,It is strongly recommended to enable the thread pool feature, so that in the case of high concurrency,No significant performance degradation occurs.In addition, there is an extra_port function, which is very practical. Can save lives at critical moments.Another important feature is the query_response_time function, which also allows us to have an intuitive experience of the overall SQL response time distribution;

(2) Set default-storage-engine=innodb, that is, use the innodb engine by default, it is strongly recommended not to use the myisam engine anymore, the innodb engine can definitely meet more than 99%of business scenarios;

(3) Adjust the size of innodb_buffer_pool_size, if it is a single instance and most of them are innodb engine tablesConsider setting it to about 50%~ 70%of physical memory;

(4) Set the values ​​of innodb_flush_log_at_trx_commit and sync_binlog according to actual needs. If data is not to be lost,Then both are set to 1. If a little data is allowed to be lost,Can be set to 2 and 10 respectively. And if you don't care if the data is lost at all (for example, on the slave, you can't redo it anyway), you can set it to 0. These three settings cause the performance of the database to be affected:high, medium, and low, that is, the first one will slow the other database,The last one is the opposite;

(5) Set innodb_file_per_table=1, use independent table space,I really can't think of any benefit from using shared tablespaces;

(6) Set innodb_data_file_path=ibdata1:1g:autoextend, do not use the default 10m, otherwise when there are high concurrent transactions,Will be affected a lot;

(7) Set innodb_log_file_size=256m and set innodb_log_files_in_group=2, which can basically meet more than 90%of the scenarios;

(8), set long_query_time=1, and above 5.5,Can already be set to less than 1, it is recommended to set it to 0.05 (50 milliseconds), to record those slower executing SQL for subsequent analysis and troubleshooting;

(9) According to the actual needs of the business,Properly adjust max_connection (maximum number of connections), max_connection_error (maximum number of errors,The recommended setting is more than 100,000, and the parameters of open_files_limit, innodb_open_files, table_open_cache, table_definition_cache can be set to about 10 times the size of max_connection;

(10), the common misunderstanding is to set tmp_table_size and max_heap_table_size relatively large,I have seen it set to 1g, these 2 options are allocated for each connection session,So do n’t set it too large,Otherwise, it is easy to cause oom;other connection session-level options such as:sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size, etc. also need to be careful not to set it too large;

(11) Since the myisam engine is no longer recommended, you can set the key_buffer_size to about 32m, and it is strongly recommended to disable the query cache function;

3.3. Regarding schema design specifications and SQL usage recommendations

The following are a few common schema design specifications and SQL usage suggestions that can help improve MySQL efficiency:

(1) All innodb tables are designed with an auto-incrementing column as the primary key for no business purpose.This is true for most scenarios,There are not many purely read-only innodb tables.If so, it might as well be more cost-effective tokudb;

(2) On the premise that the field length meets the requirements,Choose as short a length as possible.In addition, try to add not null constraints to the field attributes as much as possible to improve performance;

(3) Do not use text/blob types as much as possible, if it is really needed,It is recommended to split into subtables,Don't put it with the main watch,Avoid poor read performance when selecting *.

(4) When reading data,Select only the rows you need.Don't choose * every time, to avoid serious random read problems,Especially read some text/blob columns;

(5) When creating an index on a varchar (n) column,Usually, taking a length of about 50%(or even smaller) to create a prefix index is sufficient to meet more than 80%of the query needsIt is not necessary to create a full-length index of the entire column;

(6) Under normal circumstances,The performance of subqueries is poor,It is recommended to transform into join writing;

(7) When multi-table join query,The associated field types should be as consistent as possible,And all have indexes;

(8) When multi-table join query,The result set is a small table (note that this refers to the filtered result set,(Not necessarily a small amount of data in the entire table) as the driving table;

(9) When multiple tables are joined and sorted,The sort field must be in the driving table.Otherwise, the sort order cannot use the index;

(10) Multi-purpose composite index,Use fewer independent indexes,In particular, do not create independent indexes for columns whose cardinality is too small (for example, the total number of unique values ​​in the column is less than 255)

(11) SQL similar to paging function, it is recommended to use primary key association first,Then return the result set,Efficiency will be much higher;

3.4 Other suggestions

Other suggestions about mysql management and maintenance are:

(1) Generally, the physical size of a single table does not exceed 10 gb, the number of rows in a single table does not exceed 100 million, and the average row length does not exceed 8 kb.The amount of data mysql is fully capable of processing over,Do n’t worry about performance issues,This suggestion is mainly to consider the higher cost of online ddl;

(2) Don't worry too much about the mysqld process taking up too much memory,As long as oom kill does not occur and a large number of swaps are used;

(3) In the past, the purpose of running multiple instances on a single machine was to maximize the use of computing resources.If a single instance can already consume most of the computing resources,There is no need to run more instances;

(4) Regularly use pt-duplicate-key-checker to check and delete duplicate indexes.Use the pt-index-usage tool regularly to check and delete indexes that are used infrequently;

(5) Collect slow query log regularly and analyze it with pt-query-digest tool.Can be combined with anemometer system for slow query management in order to analyze slow query and perform subsequent optimization work;

(6) You can use pt-kill to kill long-term SQL requests. There is an option in the percona version innodb_kill_idle_transaction to achieve this function;

(7) Use pt-online-schema-change to complete the online ddl requirements for large tables;

(8) Regularly use pt-table-checksum and pt-table-sync to check and repair the difference in data copied by mysql master and slave

  • Previous Android gravity sensor implements rolling pinball
  • Next Detailed transaction management methods in Java's Spring framework