Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge about "what optimizations need to be done before and after MySQL server installation". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

I. Hardware

1. It is recommended to use ssd disk for running MySQL server alone to improve the io of disk.

2. It is recommended to choose RAID 10 instead of RAID 5. Although RAID 10 wastes some disk space, it has a lot of IO capacity than RAID 5.

II. OS part

1. It is recommended to use deadline/noop, two I/O schedulers. The system defaults to cfq.

2. It is recommended to use the xfs file system to highlight its performance advantages under high concurrency conditions.

III. System kernel optimization

1. Set net.ipv4.tcp_tw_recycle to 1

Set net.ipv4.tcp_tw_reuse to 1.

3. Turn on the above two parameters to quickly reduce the number of TCP connections in TIME-WAIT state and improve TCP efficiency.

Set vm.dirty_background_ratio to 5.

Set vm.dirty_ratio to 10.

vm.dirty_background_ratio: This parameter specifies what percentage of system memory (e.g. 5%) will trigger background writeback processes such as pdflush/flush/kdmflush to asynchronously flush certain cached dirty pages into external memory.

vm.dirty_ratio This parameter specifies what percentage of system memory (e.g., 10%) the file system cache will have to process dirty pages (because the number of dirty pages is already high, and some dirty pages need to be flushed to external memory to avoid data loss); during this process, many application processes may block because the system is instead processing file IO.

6. Set vm.Swappiness to 0 to reduce the latency of swapping (provided that the machine does not run out of memory, if it does, it may be oom)

MySQL server parameter optimization

1. It is recommended to use innodb storage engine. The default storage engine after MySQL 5.5 is innodb engine, which can meet more than 95% of user needs.

2. It is recommended to adjust the size of innodb_buffer_pool_size. The main function is to cache the index of innodb table and data. The buffer when inserting data defaults to 128M. If it is a separate MySQL server, it is recommended to set it to about 70%--80% of physical memory.

It's not that the bigger the better. If the setting is too large, it will cause the swap space of the system to be occupied, causing the operating system to slow down, thus reducing the efficiency of sql queries. innodb logfile is the transaction log, used to recover after mysqlcrash. So setting a reasonable size is very important for mysql performance

3. It is recommended to set innodb_log_files_in_group = 2

4. It is recommended to set innodb_file_per_table = 1 to use a separate table space for easy maintenance.

1. Easy to manage data files if you use soft links to distribute large tables to different partitions 3. Easy repair and recovery of corrupted data 3.2 Export import only for individual tables, not entire shared tablespaces 5. It is easier to reclaim disk space for a large number of delete operations 7. Easy to audit There are three values: fdatasyc (default), O_DSYNC, O_DIRECT When O_DSYNC is used, innodb will use O_SYNC to open and brush redo log, and use fsync() to brush data files In unix-like operating systems, the file opening method of O_DIRECT minimizes the impact of buffering on io, which is operated directly on the buffer in user space.

And io operations are synchronous, so whether it's a read() system call or a write() system call, the data is guaranteed to be read from disk

5. It is recommended to set binlog_format=row to default to statement. It is strongly recommended to set row mode to record the modification of each record, which is conducive to master-slave synchronization.

"MySQL server before and after installation need to do what optimization" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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