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

I. Server hardware

1. Cpu (frequency and quantity)

(1) cpu frequency

Cpu-intensive: mainly for sql execution efficiency. Currently, mysql does not support concurrent processing of the same sql by multiple cpu.

(2) number of cpu (web application)

Mainly improve throughput and concurrent processing

Example:

2. Memory size

The more memory, the better, but increase according to the actual situation

Choice of memory:

Suggestion: the main frequency of memory is similar to that of cpu. Choose the maximum memory supported by the motherboard.

Note: (1) make up the purchase upgrade

(2) memory of each channel: same brand, particle, frequency, voltage, inspection technology and model

(3) single memory is as large as possible.

3. Disk configuration and selection

Disk performance limitations: latency and throughput

(1) traditional machine hard disk

Factors for consideration: storage capacity; transmission speed; access time; spindle speed; physical size

(2) use RAID to enhance the performance of traditional machine hard disk.

Factors to consider: raid level: such as raid0 (fastest), raid1 (reliability), raid5 (best read efficiency), raid10 (relatively good read and write performance)

Grade

Characteristics

Whether it is redundant

Number of plates

Read

Write

RAID0

Cheap, fast, dangerous

No

N

Come on!

Come on!

RAID1

High-speed reading, simple and safe

Yes

two

Come on!

Slow

RAID5

Safety, cost compromise

Yes

Numb1

Come on!

Depends on the slowest disk.

RAID10

Expensive, high-speed and safe

Yes

2N

Come on!

Come on!

Note: the raid card had better choose the one with cache function.

(3) using solid-state storage SSD and PCI cards

Factors for consideration: relatively better random read and write performance; relatively better support for concurrency; comparison more likely to be damaged; relatively high price

SSD usage scenarios:

1) it is suitable for scenarios with a large number of random Igamot Os.

2) it is suitable for solving the bottleneck of single-thread load.

(4) use network storage NAS and SAN

1) SAN-- Storage area Network

The server is connected through optical fiber, and the device is accessed through the block interface, which can be used as a hard disk.

Features: a large number of sequential reading and writing fast, random reading and writing slow

2) NAS-- network attached storage

Access through a network connection, a file-based protocol such as NFS or SMB

Scenarios applicable to network storage:

Database backup

4. The impact of network on performance: latency and bandwidth

(1) the effect of network bandwidth on performance

(2) the influence of network quality on performance.

Recommendation: use high-performance and high-bandwidth network interface devices and switches

Bind multiple network cards to enhance availability and bandwidth

Isolate the network as much as possible

5. Server BIOS adjustment:

Improve CPU efficiency reference settings:

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

(2) the purpose of opening options such as CIE and C States is to improve the efficiency of CPU.

(3) Memory Frequency (memory frequency) Select Maximum Performance (best performance)

(4) in the memory settings menu, start Node Interleaving to avoid NUMA problems

6. Array card adjustment:

(1) purchase array cards with both CACHE and BBU modules (mechanical disk)

(2) 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))

(3) the use of WT policy is strictly prohibited, and the array read-ahead policy is turned off.

II. Server system

1. Windows system-mysql case problem

2. FreeBSD system-choose the latest one

3. Solaris system

4. Linux system-Redhat/Centos

Optimization of Centos system parameters:

View command: sysctl-a

Effective command: sysctl-p

1. System kernel related parameters (/ etc/sysctl.conf)

/ / Network parameters

Net.core.somaxconn = 32768

The backlog of the listen function in the # web application limits the net.core.somaxconn of our kernel parameters to 128by default, while the NGX_LISTEN_BACKLOG defined by nginx defaults to 511, so it is necessary to adjust this value.

Net.core.netdev_max_backlog = 65535

# the maximum number of packets allowed to be sent to the queue when each network interface receives packets faster than the kernel processes them.

Net.ipv4.tcp_max_syn_backlog = 65535

# the maximum value of a connection request for which the client acknowledgement is not received

/ / Control the parameters of tcp waiting time to speed up tcp recovery and achieve high load

Net.ipv4.tcp_tw_reuse = 1

# means to enable reuse. Allow TIME-WAIT sockets to be reused for new TCP connections. Default is 0, which means off.

Net.ipv4.tcp_tw_recycle = 1

#: enable fast recycling of TIME-WAIT sockets in TCP connection. Default is 0, which means disabled.

Net.ipv4.tcp_fin_timeout = 10

# modify the default TIMEOUT time of the system

/ / the following four parameters represent the default and maximum values of the tpc socket accept and send buffer

Net.core.wmem_default = 87380

Net.core.rmem_max = 16777216 # maximum socket read buffer. Optimized value for reference: 873200

Net.core.rmem_default = 8388608

Net.core.wmem_max = 16777216 # maximum socket writes buffer. Optimized value for reference: 873200

Optimize TCP receive / send buffer

# Increase Linux autotuning TCP buffer limits

Net.ipv4.tcp_rmem=4096 87380 16777216

Net.ipv4.tcp_wmem=4096 65536 16777216

Net.ipv4.tcp_mem = 94500000 915000000 927000000

/ / the following three parameters are used to reduce the tcp system resources occupied by failed links and accelerate the efficiency of resource recovery.

View command (sysctl-a | grep tcp_keepalive)

Net.ipv4.tcp_keepalive_time = 600,

# indicates the frequency of TCP sending keepalive messages when keepalive is used; reduce the time for TCP KeepAlived connection detection so that the system can handle more connections. The default is 2 hours, changed to 10 minutes.

Net.ipv4.tcp_keepalive_intvl = 30

# when the probe is not confirmed, the frequency of the probe is retransmitted. The default is 75 seconds.

Net.ipv4.tcp_keepalive_probes = 3

# how many keepalive probe packets for TCP should be sent before determining that the connection is invalid. The default value is 9. Multiplying this value by tcp_keepalive_intvl determines how much time a connection can have without responding after sending a keepalive.

Net.ipv4.tcp_syncookies = 1

# means to enable SYN Cookies. When a SYN waiting queue overflow occurs, enable cookies to handle it to prevent a small amount of SYN***, from defaulting to 0, which means that it is turned off.

Net,ipv4.tcp_synack_retries = 1

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

Net.ipv4.tcp_sync_retries = 1

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

Net.ipv4.ip_local_prot_range = 4500 65535

# range of ports allowed to be opened by the system

Net.ipv4.tcp_max_tw_buckets = 4096

# the system maintains the maximum number of TIME_WAIT socket. If this number is exceeded, some TIME_WAIT will be randomly cleared and warnings will be printed.

Net.ipv4.tcp_max_syn_backlog = 4096

# enter the maximum length of the SYN queue, and increase the queue length to accommodate more waiting connections (default 1024)

/ / memory parameters

# set the maximum memory shared segment size bytes

Kernel.shmmax = 68719476736

One of the important parameters that defines the maximum value of a single shared memory segment.

Note:

1) it is recommended that the setting be large enough for a shared memory segment to accommodate the size of the entire Innodb cache pool

2) the maximum recommended value is physical memory-1byte. The recommended value is more than half of the physical memory. Generally, the value is larger than the size of the innodb buffer pool.

Kernel.shmall = 4294967296

# the amount of shared memory that can be used by the system at one time, in pages; the page size of Linux shared memory is 4KB

Vm.swappiness=0

# memory swap partition; use memory swap partition when physical memory is used to 100%

Note:

If you disable the memory swap partition, it will degrade the performance of the operating system; it is easy to cause memory overflow, crash, and the system kill will be dropped.

It is best to set vm.swappiness=1 or 0 on the server of MySQL

/ / File caching

Vm.dirty_background_ratio = 10

Vm.dirty_background_bytes = 0

Vm.dirty_ratio = 20

Vm.dirty_bytes = 0

Vm.dirty_writeback_centisecs = 500,

Vm.dirty_expire_centisecs = 3000

Vm.dirty_background_ratio is the percentage of memory that can be filled with "dirty data". These "dirty data" will be written to disk later, and the background processes such as pdflush/flush/kdmflush will clean up the dirty data later.

Vm.dirty_ratio is an absolute dirty data limit, and the percentage of dirty data in memory cannot exceed this value. If the dirty data exceeds this amount, new IO requests will be blocked until the dirty data is written to disk. This is an important cause of IO stutter, but it is also a protection mechanism to ensure that there is no excessive dirty data in memory.

Vm.dirty_expire_centisecs specifies how long dirty data can survive. In this case, its value is 30 seconds. When pdflush/flush/kdmflush starts, it checks to see if any data exceeds this time limit, and if so, writes it asynchronously to disk. After all, there is a risk that data will be lost if you stay in memory for too long.

Vm.dirty_writeback_centisecs specifies how often pdflush/flush/kdmflush these processes will rise.

Note:

Set vm.dirty_background_ratio to 5-10

Set vm.dirty_ratio to about twice its size to ensure that dirty data can be continuously flushed to disk, avoiding instant Icano writes and serious waiting.

See how much dirty data is in memory:

Cat / proc/vmstat | egrep "dirty | writeback"

Remarks: adjust the relevant parameters online according to the actual situation. For more information, please refer to the official

2. File system layer optimization

(1) adjust 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) use Linux I _ scheduler O scheduler algorithm deadline (refer to Linux column for detailed configuration)

Disk scheduling policy:

# cat / sys/block/sda/queue/scheduler

Noop (No Operation)-suitable for flash devices, RAM and embedded systems

Cfq (Completely Fair Scheduler)-full Fair Scheduler

Deadline-suitable for database applications

(3) deadline scheduling parameters

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

The command is as follows:

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

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

(4) File system, xfs is recommended (default for centos7)

Windows:FAT and NTFS

Linux:EXT3, EXT4 and XFS

(5) mount mounts the file system (if it is EXT3 and EXT4, note the following options)

Data=writeback, ordered,journal

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

Noatime: the timestamp of inode is not updated when accessing files. In a high concurrency environment, this option can be applied to push line display 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, you will lose the data not written to disk in the buffer: the 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

Example: / dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

(VI) Resource restrictions (/ etc/security/limits.conf)-limits on the number of files opened.

View command: ulimit-a (adjust according to actual situation)

Note: limits.conf is Linux PAM (plug-in authentication module)

* soft nofile 65535

* hard nofile 65535

* soft noproc 65535

* hard noproc 65535

# the above two parameters control the limit on the number of open files

Parameter description:

*-indicates that it is valid for all users

Soft---- refers to the setting that the current system takes effect.

Hard--- represents the maximum value that can be set in the system.

Nofile-- means that the limited resources are the maximum number of open files.

Noproc-- represents the maximum number of processes that can be set in the system

Note: restart the system before it takes effect.

3. Disable seliunx (for more information on security mechanism, please refer to other articles)

# vim / etc/selinux/config

SELINUX=disabled

4. Close numa (it is recommended to close bios)

Can be temporarily shut down from BIOS, operating system, when starting the process.

III. Database architecture

Client-Link Manager (mysql Service layer)-Storage engine

Note: the storage engine is for tables, not for libraries (different tables in a library can use different storage engines)

1. MyISAM of storage engine

The MYISAM storage engine table consists of MYD (data) and MYI (index).

Properties:

1) concurrency and lock level-modify lock level table; read plus shared lock; poor read-write interaction, high single read efficiency

2) repair of table damage-easy to cause data loss

# check to see if the table is damaged:

Mysql > check table tablename

# repair the table:

Mysql > repair table tablename

3) types of indexes supported by the MYISAM table

4) MYISAM tables support data compression-compressed tables are only allowed to read, not write

Command: myisampack

Example: myisampack-b tablename.MYI

Limitation: storage size varies from version to version

Scene:

1) non-transactional applications

2) read-only applications

3) Space application

2. Innodb of storage engine

2.1.Using tablespaces for data storage in innodb

State storage via innodb_file_per_table

Mysql > show variables like 'innodb_file_per_table'

ON: independent tablespace: tablename.ibd

OFF: system tablespace: ibdataX

Contrast:

1) the system tablespace cannot simply shrink the file size

Independent tablespaces can shrink system files through the optimize table command

2) the system tablespace will cause IO bottleneck

Independent space can refresh data from multiple files at the same time.

Table transfer step: (system space to independent tablespace)

1) use MySQLdump to export all database table data

2) stop the mysql service, modify the parameters, and delete the innodb related files

3) restart mysql service and rebuild the tablespace of innodb system

4) re-import data

Two important keywords: innodb data dictionary information and Undo rollback segment

Two special logs (Redi Log and Undo Log)

1) the Redo log file contains a set of log files, usually physical logs, that record physical changes to the data page, which are recycled.

Innodb_log_file_size and innodb_log_files_in_group

Generate two files: ib_logfile0 and ib_logfile1

2) Undo Log (random read)-record to a version by scrolling back and forth. Undo log is generally a logical log, which is recorded according to each line of record

2.3.The status of innodb check

Mysql > show engine innodb status\ G

2.4, scene

1) used in most OLTP applications

3. Other storage engines

(1) CSV storage engine-stored in a file as text

Features:

1) data storage in CSV format

2) all columns must not be UNLL

3) indexing is not supported (not suitable for large tables, not suitable for online processing)

4) data files can be edited directly

Scenario: the most suitable intermediate table for data exchange

(2) Archive storage engine

Features:

1) only insert and select operations are supported

2) indexing is only allowed on self-incrementing id columns

Scenario: log and data collection applications

(3) Memory storage engine

Features:

1) support HASH index (equivalent lookup) and BTree index (range lookup)

2) all fields are of fixed length

3) large fields such as BLOG and TEXT are not supported

4) Memory storage engine uses table-level locks

5) the maximum size is determined by the max_heap_table_size parameter (invalid already saved, rebuilt if necessary)

Scene:

1) corresponding tables used to find or map tables, such as zip codes and regions

2) used to save the intermediate tables generated in data analysis

3) result table used to cache periodically aggregated data

(4) Federated storage engine

Features:

1) provides a method to access tables on a remote mysql server

2) No data is stored locally, and all the data is placed on the remote server

3) the table structure and the link information of the remote server need to be saved locally.

Scenario: occasionally used in statistical analysis and manual queries

4. How to choose a storage engine

Reference conditions: things (innodb), backup (innodb), crash recovery (MYISAM), characteristics of storage engine

Fourth, database parameter file my.cnf configuration

1. Mysql Foundation

(1) mysql configuration path

1) Command line parameters (version does not need to be different)

Mysqld_dafe-datadir=/data/sql_data

2) configuration file

View the mysql read file order command:

# mysqld-- help-- verbose | grep-A 1 'Default options'

/ etc/my.cnf / etc/mysql/my.cnf / usr/etc/my.cnf ~ / .my.cnf

(2) mysql scope

(1) Global parameters

Set global parameter name = parameter valu

Set @ @ global. Parameter name: = parameter value

(2) session parameters

Set [session] parameter name = parameter valu

Set @ @ session. Parameter name: = parameter value

2. Memory configuration parameters

(1) memory used by each connection (thread)

Sort_buffer_size- sort buffer size

Join_buferr_size- connection buffer size

Buffer size, 4k multiples, that can be used by read_buferr_size--- read query operations

The index buffer size used by read_rnd_buferr_size- for random reads

(2) configuration of cache pool

Innodb_buferr_pool_size-innodb cache pool

Key_buffer_size-MYISAM cache pool

3. Configuration parameters of Icano

(1) innodb iCandle O configuration

Innodb_log_file_size

Innodb_log_files_in_group

Total transaction log size = innodb_log_file_size*innodb_log_files_in_group

Innodb_log_buffer_size-the size of the transaction log

Innodb_flush_log_at_trx_commit-refresh log mode, option [0-2] recommendation 2

Others

Innodb_flush_method=O_DIRECT---innodb refresh method

Innodb_file_per_table=1-Open independent tablespaces

Innodb_doublewrite=1-enable two writes to ensure data reliability

Innodb_data_file_path-mysql's ibdata1 recommends setting 1G to prevent high concurrency from being affected

Setting: innodb_data_file_path = ibdata1:1G:autoextend

(2) MyISAM iUnip O configuration

Delay_key_write

OFF: refresh the dirty blocks in the key cache to disk after each write operation (safest, poor performance)

ON: use delayed refresh only for tables where this option parameter is specified when the table is being built

ALL: use a delay key to write to all MYISAM tables; (index is fragile)

4. Security configuration

Expire_logs_days-specify the number of days to automatically clean up binlog

Max_allowed_packet-controls the size of packets that mysql can receive

Skip_name_resolve---- forbids DNS search

Sysdate_is_now--- ensures that sysdate () returns a deterministic date

Read_only--- forbids users with non-super permissions to write (master and slave are started in the slave library to ensure data consistency)

Skip_slave_start--- disables Slave automatic recovery (master-slave starts in the slave library to prevent mysql from automatically copying data after restart)

Sql_mode--- sets the SQL mode used by MYSQL (do not change it easily)

5. Other common configuration parameters

Sync_binlog-controls how MYSQL refreshes binlog to disk. It is recommended to set it to 1.

Tmp_table_size and max_heap_table_size--- control temporary memory table size

Max_connections---- controls the maximum number of connections allowed

5. Database structure design and sql statement

1. Need analysis: a comprehensive understanding of the storage requirements of product design

Storage requirement

Data processing requirements

Security and integrity of data

2. Logical design: design the logical storage structure of data.

Logical relationship between data entities to solve data redundancy

And data maintenance exception

3. Physical design: design the table structure according to the characteristics of the database used.

Relational database: oracle,SQLServer,Mysql,postgresSQL

Non-relational database: mongodb,Redis,Hadoop

Storage engine: innodb,myisan

4. Maintenance optimization: optimize the index and storage structure according to the actual situation.

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.