I. performance impact analysis and optimization of MySQL Databases

I. performance impact analysis and optimization of MySQL Databases
Impact Analysis of MySQL database performance and its impact on MySQL database performance optimization I. server hardware restrictions II. the operating system used by the server. the parameter settings of the server are different. database storage engine selection 5. database parameter configuration is different 6. (key) database structure design and SQL statements

1). server configuration and settings (cpu and available memory size)

1. network and I/O resources 2. cpu clock speed and number of cores (cpu with high clock speed should be prioritized for intensive applications) (cpu with multiple cores with high concurrency preferred) 3. disk configuration and selection (using a traditional mechanical hard disk: features: Slow reading and writing, large storage space, the most common, the most used, low price; Working Process: move the head to the correct position on the disk surface. Wait for the disk to rotate so that the required data is under the head. Wait for the disk to rotate, and all required data is read by the head. selection factor: storage capacity, transmission speed, access time, spindle speed, physical size) (use RAID to enhance the performance of traditional machine hard disks: features: the level of RAID used in the technical database that utilizes a small disk group to form a large disk and provides data redundancy to ensure data integrity: RAID0 level, RAID1 level, RAID5 level [distributed parity disk array], RAID10 [sharded image (best way for databases)] RAID Level Selection: such) (SSD and PCI-E card using solid state storage: features: Compared with the mechanical disk solid state disk has better random read/write performance; compared with the mechanical solid state disk can better support concurrency; SSD is more vulnerable to damage than a mechanical Solid State Disk: the SATA interface can be used to replace a traditional disk without any changes [restricted by the speed of the interface]; SATA interface SSD also supports RAID technology PCI-E card (Fusion-iocard): unable to use on SATA interface [requires a unique driver and configuration]; expensive, scenarios where cpu resources and memory usage are used: Suitable for scenarios where a large number of random I/O exist, and suitable for solving single-thread load I/O bottlenecks) (using network storage NAS and SAN: SAN [optical fiber access server]: A large number of sequential read/write operations, read/write I/O, cache, I/O merge, random read/write slow (less than local RAID) NAS devices use network connections, file-based protocols such as NFS or SMB are suitable for accessing scenarios: database backup ,)

Use RAID to enhance the performance of traditional machine hard disks-> use RAID to enhance the performance of traditional machine hard disks at RAID 0-> use RAID to enhance the performance of traditional machine hard disks at RAID 1-> RAID 5

Comparison of Different REAID levels:

Note:

The version of the 1.64-bit database uses the 32-bit server version. 2. Select the maximum memory frequency supported by the motherboard for the memory clock speed.

Summary:

For cpu: The 1.64-bit cpu must work in a 64-bit system. for scenarios with high concurrency, the number of CPUs is more important than the frequency. for cpu-intensive scenarios and complex SQL statements, the higher the frequency, the better for memory: 1. select the maximum memory frequency that the motherboard can use. 2. the memory size is very important for performance, so the maximum I/O subsystem is as large as possible: 1. PCIe-> SSD-> RAID10-> disk-> SAN

2) Impact of the operating system on Performance

Parameter Optimization settings for Windows, FreeBSD, Solaris, and Linuxcentos: (1) kernel-related parameters (/etc/sysctl. conf) net. core. somaxconn = 65535 net. core. netdev_max_backlog = 65535. net. ipv4.tcp _ max_syn_backlog = 65535 net. ipv4.tcp _ fin_timeout = 10 net. ipv4.tcp _ tw_reuse = 1 net. ipv4.tcp _ tw_recycle = 1 net. core. wmem_defaullt = 87380 net. core. wmem_max = 16777216. net. core. rmem_defaullt = 87380 net. core. rmem_max = 16777216. net. ipv4.tcp _ keepalive_time = 120. net. ipv4.tcp _ keepalive_intvl = 30 net. ipv4.tcp _ keepalive_probes = 3 kernel. shmmax = 4294967295 vm. swappiness = 0 (2) added resource restrictions (/etc/security/limit. conf) * soft nofile 65535 * hard nofile 65535 * indicates that it is valid for all users. soft indicates that the current system takes effect. hard indicates the maximum value that can be set in the system. nofile indicates that the limited resources are the maximum number of opened files is 65535 (3 ). disk scheduling policy (/sys/block/devname/queue/scheduler) noop (ELEVATOR scheduling policy), deadline (deadline scheduling policy), and anticipatory (expectation I/O Scheduling Policy) cat/sys/block/sda/queue/scheduler noop anticipatory deadline [cfq] echo deadline>/sys/block/sda/queue/schedue

3). MySQl Database System

MySQl Database System

4). MySQl database storage engine

(1 ). the structure of MyISAM, a storage engine of Mysql: file composition characteristics of tables MYD, MYI, and frm: concurrency and lock level MyISAM tables support data compression of index type MyISAM tables (command line: myisampack) myisampack-B-f myIsam. MYI; the compressed table cannot be written, but can only be read. Fixed: check and fix the tables in the database: check table mytable; repair table mytable; myisamchk tool, during restoration, the database service must be stopped. When MySQL5.0 is used, the default table size is 4 GB (MAX_Rows and AVG_ROW_LENGTH are modified for large tables). versions after MySQL5.0 support 256 TB by default. Applicable scenarios: non-transactional applications read-only application space applications (GPS data) (2 ). the default structure of the storage engine used by Mysql versions after InnoDB mysql5.5.8: The storage location of innodb_file_per_table is set to different ON: Independent tablespace: tablename. ibd OFF: System tablespace: ibdataX suggestion: InnoDB standalone tablespace feature is recommended for mysql: the transaction storage engine fully supports the transaction storage engine Redo log (stores committed transactions) and Undo log (store uncommitted transactions) InnoDB supports maximum row-Level Lock program support concurrent row-level locks are implemented by the storage engine layer: Shared locks (read locks) exclusive lock (write lock) Table-Level Lock and row-Level Lock blocking: ensure normal execution deadlock of transaction concurrency: an abnormal InnoDB status check generated when two or more transactions wait for each other's resources during execution: show engine innodb status; applicable scenarios: inooDB is applicable to most OLTP applications (3 ). mysql storage engine CSV features: data is stored in files as text. CSV file to store table content. the CSM file stores the table metadata, such as the table status and data volume. the structure information of the frm file storage table is stored in CSV format. All columns must not be NULL and indexes are not supported (not suitable for large tables and not suitable for online processing) applicable scenarios for direct editing of data files: it is suitable for data exchange between mysql Data Directories-> csv files-> other web applications excel workbooks-> csv files-> mysql Data Directories (4 ). mysql storage engine Archive features: zlib is used to compress table data, disk I/O less data is stored in files suffixed with ARZ. Only insert and select operations are supported. Only auto-increment ID columns can be indexed. Applicable scenarios: log and Data Collection Applications (4 ). features of Mysql storage Engine Memory: data is only stored in Memory. Memory storage engine is particularly efficient in I/O. It supports HASH indexes and BTree indexes. All fields are fixed in length. Memory storage engine is not supported for large fields such as BLOG and TEXT. the maximum value of data stored in a table-Level Lock table is determined by the max_heap_table_size parameter. Applicable scenarios: used to search for or map tables. For example, zip code and region are used to save the intermediate table generated by data analysis and are used to cache the result table of periodically aggregated data.

5). MySQl database server Parameters

(1 ). mysql configuration parameter scope global parameter set global parameter name = parameter value; set @ global. parameter Name: = parameter value; session parameter set [session] parameter name = parameter value; set @ session. parameter Name: = parameter value; (2 ). memory configuration-related parameters determine the upper limit of memory available to determine the memory used by each MySQL connection sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size determine how much memory needs to be reserved for the operating system and how to allocate the total memory quota memory-(memory * connections required for each thread lock) -system reserved memory key_buffer_size (3 ). i/O-related configuration parameters: Innodb_log_file_size depends on Innodb_flush_method = O_DIRECT Innodb_file_per_table = 1 Innodb_doublewrite = 1 MySIAM storage engine's I/O parameter settings: delay_key_write OFF: after each operation, refresh the dirty blocks in the key buffer to the disk ON: only use the delayed refresh for tables with the delay_key_write option specified in the key table ALL: use the latency key to write data to all MYSIAM tables (4 ). security-related configuration parameters expire_logs_days specify the number of days for automatic binlog cleanup max_allowed_packet control the size of packages acceptable to MySQL (32 M) skip_name_resolve disable DNS lookup sysdate_is_now to ensure sysdate () return the deterministic date read_only. Disable write permissions for non-super Users. skip_slave_start: Disable Slave from automatically restoring SQL _mode. Set the SQL mode strict_trans_tables using no_zero_date no_zero_in_date only_full_group_by in

(5). Other related configuration parameters sync_binlog = 1 control how MySQL refreshes binlog tmp_table_size and max_heap_table_size to the disk to control the size (Consistent setting) of the temporary memory table max_connections = 2000 control the maximum number of connections allowed

5). MySQl database structure design and SQL Optimization

(1 ). too many columns are created for tables (2 ). excessive normalization leads to too many table Associations (3 ). use an inappropriate partition table in the OLTP environment (4 ). use foreign keys to ensure data integrity

Performance Optimization Sequence

• Database Structure Design and SQL statement Optimization
• Database storage engine selection and parameter configuration
• System Selection and Optimization
• Hardware upgrade