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

Chapter 1 hardware level optimization 1.1 purchase of database physical machines (*)

CPU: 64-bit CPU, 2-16 CPU per machine, at least 2-4. The bigger the L2, the better.

Memory: 96G-256G (Baidu), 3-4 instances. 32-64G, run 1-2 instances (Sina).

Disk (disk IO): mechanical disk: choose SAS, the more the better.

Performance: SSD (high concurrency) > SAS (general business online) > SATA (offline)

Choose SSD: using SSD or PCIe SSD devices can improve IOPS efficiency by thousands of times.

Random IO:SAS single disk capacity 300IOPS SSD random IO: single disk capacity can reach 35000IOPS Flashcache HBA card

Raid disk array: 4 disks: RAID0 > RAID1 (recommended) > RAID5 (less used) > RAID1

Select raid10 for master library and raid5/raid0/raid10 for slave library. Slave library configuration is equal to or greater than master library.

Network cards: use multiple network cards bond, and buffer,tcp optimization

Gigabit network card and gigabit and 10 gigabit switches

Tip:

Databases are IO-intensive services, so try not to use virtualization in hardware.

Slave hardware should be equal to or greater than the performance of Master

1.2 Enterprise case:

Baidu: a department's IBM server consists of 48 cores of CPU and memory 96GB. One server runs 3 to 4 instances:

Sina: the server is DELL R510, the CPU is E5210 48GB memory, the hard disk is 12cm 300g SAS, do RAID10

1.3 Server hardware configuration adjustment

(1) Server BIOS adjustment: (buffer and cache adjustment are larger. )

Improve CPU efficiency reference settings:

a. Open Perfirmance Per Watt Optimeized (DAPC) mode to maximize the performance of CPU. Databases usually require a high amount of computation.

b. The purpose of opening options such as CIE and C States is also to improve CPU efficiency.

C. MemoryFrequency (memory frequency) Select MaximumPerformance (best performance)

d. From the memory settings menu, start Node Interleaving to avoid NUMA problems

(2) Array card adjustment:

a. Purchase array cards with both CACHE and BBU modules (mechanical disk)

b. Set the array write policy to WEB, or even OFRCE WB (high data security requirements) (wb refers to the write policy of raid card: can write (write back))

c. The use of WT policy is strictly prohibited and array read-ahead policy is turned off

Chapter 2 operating system level Optimization 2.1 operating system and MySQL instance selection

1. Be sure to choose x86x64 system. CentOS6.8 linux is recommended and NUMA feature is turned off.

two。 Separate the operating system from the data, not only logically, but also physically

3. Avoid using Swap swap partitions

4. Avoid using software disk arrays

5. Avoid using LVM logical volumes

6. Remove unused installation packages and daemons on the server

2.2 File system layer Optimization (* *) 2.2.1 tuning disk Cache mode

Enable WCE=1 (Write Cache Enable), RCD=0 (Read Cache Disable) mode

Command: sdparm-s WCE=1,RCD=0-S / dev/sdb

2.2.2 using the Linux I-scheduler O scheduler algorithm deadline

Deadline scheduling parameters

It is recommended that read_expire = 1 write_expire 2 write_expire for Centos Linux

Echo 500 > / sys/block/sdb/queue/iosched/read_expire

Echo 1000 > / sys/block/sdb/queue/iosched/write_expire

Linux I Dot O scheduling method Linux deadline io scheduling algorithm.

2.2.3 using the xfs file system

Ext4 can also be used if the volume of business is not very large, and it is recommended to use xfs: adjust the XFS file system log and buffer variables.

XFS high performance settings.

2.2.4 mount mounts the file system

Add: async,noatime,nodiratime,nobarrier, etc.

Noatime

The timestamp of inode is not updated when accessing the file. In a high concurrency environment, it is recommended to show that this option can be used to improve the performance of the system.

Async

When writing, the data will be written to the memory buffer first, and will only be written to the disk when there is a gap on the hard disk, which can improve the writing efficiency! The risk is that if the server is down or abnormal, the data not written to disk in the buffer will be lost.

Solution: server motherboard battery or add UPS uninterruptible power supply

Nodiratime

Do not update the directory inode timestamp on the system, high concurrency environment. It is recommended to show this application, which can improve the performance of the system.

Nobarrier

Do not use raid card on the battery

2.2.5 Linux kernel parameter optimization

1. Set vm,swappiness to 0-10

two。 Set vm,dirty_background_ratio to 5-10 and set vm,dirty_ratio to about twice as much to ensure that dirty data can be continuously flushed to disk, avoiding instant write and serious waiting.

2.3 optimize the TCP protocol stack

# reduce TIME_WAIT and improve TCP efficiency

Net.ipv4.tcp_tw_recyle=1

Net.ipv4.tcp_tw_reuse=1

# reduce the time spent in the FIN-WAIT-2 connection state, so that the system can handle more connections

Net.ipv4.tcp_fin_timeout=2

# reduce the time for TCP KeepAlived connection detection so that the system can handle more connections.

Net.ipv4.tcp_keepalived_time=600

# increase the maximum number of SYN semi-connections supported by the system (default 1024)

Net.ipv4.tcp_max_syn_backlog = 16384

# reduce the number of system SYN connection retries (default 5)

Net,ipv4.tcp_synack_retries = 1

Net.ipv4.tcp_sync_retries = 1

# number of SYN packets sent before the kernel abandons the established connection

Net.ipv4.ip_local_prot_range = 450065535

# range of ports allowed to be opened by the system

2.4 Network optimization

# optimize system socket buffer

# Increase TCP max buffer size

Net.core.rmem_max=16777216 # Max socket read buffer

Net.core.wmem_max=16777216 # maximum socket writes buffer

Net.core.wmem_default = 8388608 # this file specifies the default value (in bytes) for the buffer size of the receive socket

Net.core.rmem_default = 8388608

# optimize TCP receive / send buffer

# Increase Linux autotuning TCPbuffer limits

Net.ipv4.tcp_rmem=4096 87380 16777216

Net.ipv4.tcp_wmem=4096 65536 16777216

Net.ipv4.tcp_mem = 94500000 915000000927000000

# optimize the receiving queue of network devices

Net.core.netdev_max_backlog=3000

2.5 other optimizations

Net.ipv4.tcp_timestamps = 0

Net.ipv4.tcp_max_orphans = 3276800

Net.ipv4.tcp_max_tw_buckets = 360000

Chapter 3 MySQL Database level Optimization (*) 3.1 my.cnf Parameter Optimization

This optimization is mainly aimed at the innodb engine

If you use the MyISAM engine, you need to increase key_buffer_size.

Key_buffer_size = 256m

# specifies the size of the buffer used for the index, which can be increased for better index processing performance. For servers with memory around 4GB, this parameter can be set to 256MB or 384MB.

Innodb engine, default-storage-engine=Innodb, is highly recommended.

Adjust the innodb_buffer_pool_size size and consider setting it to about 50% of physical memory.

Innodb_buffer_pool_size = 64m

# InnoDB uses a buffer pool to hold the index and raw data. The larger the setting, the fewer disks needed to access the data in the table. It is strongly recommended not to arbitrarily configure the Buffer Pool value of InnoDB to 50%-80% of physical memory, depending on the specific environment.

Set the value of inno_flush_log_at_trx_commit,sync_binlog according to your actual needs. If you want data not to be lost, set both to 1. 0. If larger data loss is allowed, it can be set to 2 and 0, respectively, and to 0 on slave

Set innodb_file_per_table = 1 to use independent tablespaces

Set innodb_data_file_path = ibdata1:1G:autoextend, do not use the default 10%

Setting innodb_log_file_size=256M and innodb_log_files_in_group=2 can basically meet more than 90% of the scenes.

Do not set the innodb_log_file_size parameter too large, which can make it faster and have more disk space at the same time. It is usually good to lose more logs, and can reduce the event of recovering the database after the database crash.

Set long_query_time = 1 to record those slow-performing SQL for subsequent analysis and troubleshooting

According to the actual needs of the business, properly adjust the max_connection (maximum number of connections) max_connection_error (the maximum number of errors is recommended to be set to more than 100000, and the parameters open_files_limit, innodb_open_files, table_open_cache, table_definition_cache can be set to about 10 times the size of max_connection;) do not set too large, it will burst the database

It is recommended that you turn off the query cache function or lower the setting no more than 512m.

Query_cache_size = 64m

# specify the size of the MySQL query buffer. As you can observe in the MySQL console, if the value of Qcache_lowmem_prunes is very large, there is often insufficient buffering; if the value of Qcache_hits is very large, query buffering is used very frequently. In addition, if the change value is small but will affect the efficiency, then you can consider not query buffering. For Qcache_free_blocks, if the value is very large, there is a lot of fragmentation in the buffer.

Tmp_table_size = 64m

# set the maximum value of memory temporary table. If this value is exceeded, the temporary table is written to disk, ranging from 1KB to 4GB.

Max_heap_table_size = 64m

# maximum capacity allowed for stand-alone memory tables.

Table_cache = 614

# memory allocated to frequently accessed tables, the larger the physical memory, the larger the setting. Increasing this value generally reduces the disk IO, but correspondingly takes up more memory, which is set here to 614.

3.2 specifications for the design of library tables

1. Utf-8 character set is recommended, although some people say that talking is not as fast as latin1.

two。 Fixed string columns use as much fixed length char as possible and less varchar

Using VARCHAR instead of CAHR--- to store variable-length strings saves space because of fixed-length CHAR and non-fixed VARCHAR length (UTF8 is not worried about this effect)

3. All InnoDB tables design a self-incrementing column with no business use as the primary key.

4. Under the premise that the length of the field meets the requirements, choose the one with small length as far as possible.

5. Field properties should be constrained by NOT NULL as far as possible (empty fields cannot be indexed, so the query speed is slow)

For some text fields, such as "province" or "gender", we can define them as ENUM types.

6. Do not use the TEXT/BLOB type as much as possible. if necessary, it is recommended to split it into child tables and not put them with the main table to avoid poor read performance during SELECT*.

7. When reading data, only select the required columns. Do not SELECT* every time to avoid serious random reading problems, especially when reading some TEXT/BLOB types. If it is really necessary, it is recommended to split it into child tables and not put them together with the main table to avoid poor reading performance during SELECT*.

8. When creating an index on a VARCHAR (N) column, it is usually sufficient to create a prefix index with a length of about 50% (or less) to meet more than 80% of the query requirements, and it is not necessary to create a full-length index of the whole column.

9. Use more compliant indexes and less independent indexes, especially if the Cardinality is too small (if the total number of unique values of the column is less than 255), do not create independent indexes.

3.3.3.Optimization of SQL statements 3.3.1 Index Optimization

1) whitelist mechanism 100 degrees, project development, DBA participation, reduce slow SQL data after launch

Catch slow SQL and configure my.cnf

Long_query_time = 2

Log-slow-queries=/data/3306/slow-log.log

Log_queries_not_using_indexs

Polling by day: slow-log.log

2) slow log analysis tool-mysqlsla or pt-query-digest (recommended)

Pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter

3) analyze the slow query regularly at 0: 00 every evening and send it to the mailbox of core development, DBA analysis, and senior operation and maintenance, CTO.

DBA analysis gives optimization recommendations-- > core development confirmation update-- > DBA online operation processing.

4) periodically use pt-duplicate-key-checker to check and delete duplicate indexes

Regularly use the pt-index-usage tool to check and delete indexes that are rarely used

5) use pt-online-schema-change to complete the ONLINE DDL requirements of large tables

6) sometimes MySQL uses the wrong index, for which case use USE INDEX

7) optimize SQL statements with explain and set profile

The website opens slowly query 3.3.2 large and complex SQL statements are split into several small SQL statements

Subquery, JOIN connected table query, 40 million records of a table

3.3.3 A database is a place where data is stored, but not where data is calculated

The data calculation and application class processing should be solved by the front-end application. Prohibit processing on the database

3.3.4 search function, like'% oldboy%' generally do not use MySQL database

Use joins (JOIN) instead of subqueries (Sub_Queries)

Avoid using cout (*) on the entire table, which may lock the entire table

When multi-table join queries, the associated field types are as consistent as possible, and all must have indexes.

Substituting UNION for subqueries in the WHERE clause

When a multi-table join query, use the table with a small result set (note, this refers to the filtered result set, not the whole table with a small amount of data) as the driver table.

The process of obtaining data by a crawler

Chapter 4 website Integration Architecture Optimization (*)

Optimization of website Cluster Architecture

1. There are 2-4 instances running on the server (depending on the hardware information of the server)

two。 Master-slave copy one master and five slaves, using mixed mode (mixed or row mode), try not to synchronize across computer rooms (process remote read and write locally), (data should be consistent, pull optical fiber, no network delay)

3. Periodically use pt-table-checksum and pt-table-sync to check and fix data differences between master-slave replication in mysql (refactoring)

4. Business split: search function, like'% oldboy% 'generally do not use MySQL database

5. Business split: some business applications use nosql for persistent storage, such as memcached, redis, ttserver

For example, fan attention, friend relationship and so on.

6. Cache must be added to the front end of the database, for example: memcached, user login, product query

7. Dynamic database static, whole file static, page fragment static

8. Database cluster is separated from read and write. One master, multiple slaves, and cluster read and write separation through programs or dbproxy

9. If the single table exceeds 8 million, dismantle the library and disassemble the table. Manual disassembly of tables and libraries (login, goods, orders)

10. Baidu and Ali domestic former × × companies will choose to back up from the database and divide the database into tables.

Chapter 5 MySQL database management process (*)

Any update of a man-made database record has to follow a process:

a. Human process: development-- > core development-- > operation and maintenance or DBA

b. Test process: intranet test-> IDC test-> online execution

c. Client management, phpmyadmin

Chapter 6 MySQL Database Security Optimization (*) 6.1 MySQL basic Security

1. The startup program is 700. the master and user group is MySQL.

two。 Set the password for the MySQL superuser root.

3. If the requirement is strict, you can delete the root user and create another administrative user, such as admin.

4. Try not to expose the password on the command line when logging in. If there is a password in the backup script, set it to 700. the owner and password group are mysql or root.

5. Delete test libraries that exist by default.

6. Initially delete useless users and retain them only.

| | root | 127.0.0.1 | |

| | root | localhost |

7. Do not want one user to manage all the libraries, try to use specialized users (a small number of libraries)

8. Clean up the mysql operation log file ~ / .mysql_history (permission 600, can not be deleted)

9. It is forbidden for developers to obtain passwords for connecting to web, and for developing connections to operate libraries outside production.

10.phpmyadmin security

11. Server forbids setting public network IP

twelve。 Anti-SQL injection (WEB) php.ini or web development plug-in monitoring, waf control

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.