One: Analysis and optimization of the performance impact of MySQL database

Analysis of the performance of MySQL database and its optimization effect on the performance of MySQL database one. Hardware limitations of the server two. The operating system used by the server is three. The configured parameter settings for the server are different four. Selection of the database storage engine five. The parameters of the database are configured differently six. (focus) Design of the database structure and SQL statements 1). Server Configuration and settings (size of CPU and available memory)

  1. Network and I/O resources 2.cpu of the frequency and core number of choice (for intensive applications should give priority to high-frequency CPU) (    For multi-core CPUs with large concurrent application Priorities 3. Disk Configuration and selection (using traditional mechanical HDD: Features: Read and write slower, large storage space, most common, most used, low price;             Working process: Moving the head to the correct position on the disk surface;             Wait for the disk to rotate so that the resulting data is below the head; Wait for the disk to spin in the past, all the required data are read out by the head selection factors: storage capacity, transmission speed, access time, spindle speed, physical size) (using RAID to enhance the performance of traditional machine drives: Features: A technical database that uses small disks to form large disks and provides redundancy of data to ensure data integrity Level of RAID used in: RAID0 level, RAID1 level, RAID5 level [distributed parity Disk array], raid10[Shard Mirror (Database best way)] RAID level selection: (SSD and PCI-E cards using solid state storage: Features         : Better random Read and write performance compared to mechanical disk solid-state disk;         Compared with the mechanical solid-state disk can be better support concurrency;        Easier to damage SSDs than mechanical solid-state disks: You can replace traditional disks with SATA interfaces without any changes [limited by the speed of the interface];                         SATA interface SSD also supports RAID technology PCI-E card (Fusion-io card): cannot be used on SATA interface [need to use unique driver and configuration];              Expensive, use of CPU resources and memory usage scenarios: suitable for the presence of a large number of random I/O scenarios; For I/O bottlenecks for single-threaded workloads) (using networked storage NAS and san:san[fibre access servers]: Large sequential read and write operations, read/write I/O, cache, I/O merge, slow random read/write (not as local raid) NAS devices use network connectivity, File-based protocols such as NFS or SMB to access the appropriate scenario: database backup,  

Using RAID to enhance the performance of traditional machine hard drives->raid0 level using RAID to enhance the performance of traditional machine hard drives->RAID1 level using RAID to enhance the performance->RAID5 level of traditional machine hard drives

Comparison of different reaid levels:

Precautions:

 1.64位数据库的版本使用32位的服务器的版本 2.内存的主频的选择主板所能支持的最大内存的频率

Summarize:

    对于cpu：        1.64位的cpu一定能够要工作在64位的系统下        2.对于并发比较高的场景cpu的数量比频率重要        3.对于cpu密集型的场景和复杂SQL则频率越高越好    对于内存：        1.选择主板所能使用的最高频率的内存        2.内存的大小对性能很重要，所以尽可能的大    I/O子系统：        1.PCIe -> SSD -> RAID10 -> 磁盘 -> SAN

2). Impact of operating system on performance

parameter-optimized settings for Windows, FreeBSD, Solaris, 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 = Ten NE T.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 =        NET.IPV4.TCP_KEEPALIVE_INTVL = Net.ipv4.tcp_keepalive_probes = 3 Kernel.shmmax = 4294967295            vm.swappiness = 0 (2) increased resource limit (/etc/security/limit.conf) * Soft nofile 65535 * hard nofile 65535            * Indicates a valid soft for all users refers to the setting of the current system hard to indicate the maximum value that can be set in the system nofile indicates that the limited resource is the maximum number of open files 65535 is the number of restrictions (3). Disk scheduling Policy (/sys/block/devname/queue/scheduler) noop (Elevator scheduling policy), deadline (deadline scheduling policy), anticipatory (pre- I/O scheduling policy) Cat/syS/block/sda/queue/scheduler NoOp anticipatory deadline [CFQ]             echo deadline >/sys/block/sda/queue/scheduler

3). MySQL's database system

MySQL's database system

4). The storage engine for the MySQL database

(1).             MySQL Storage engine MYISAM structure: Table for myd and myi, frm file composition features: Concurrency and lock level MyISAM Table support index type MYISAM table supports compression of data (command line: myisampack) Myisampack-b-F myisam.myi;        The compressed table cannot be written and can only be repaired by reading: Check the table in the database and fix it: Check table mytable;        Repair table mytable; Myisamchk tool, repair when the database service must stop throttling: The default table size before using MySQL5.0 4G (Store large table modifications Max_Rows and avg_row_length) use MySQL5.0 after the version default support 256TB applicable Scenario: Applications of non-transactional applications with read-only classes (GPS data) (2). The storage engine used by the MySQL storage engine InnoDB mysql5.5.8 is composed of the following: the location is stored by setting the innodb_file_per_table parameter on: Separate table space: tablename.i BD OFF: System tablespace: Ibdatax Recommendation: For MySQL, we recommend using InnoDB's standalone tablespace attribute: The transactional storage engine fully supports the transaction's storage engine RedoLog (stores the committed transaction) and UndoLog (store uncommitted transactions) InnoDB support for row-level lock maximum programs and release-level locks are locks implemented by the storage engine layer: Shared locks (read locks), exclusive locks (write locks), table-level locks, row-level lock resistance        Plug: A normal execution deadlock that ensures that transactions are concurrent: two or more than two transactions that are waiting for each other's resources during the execution of an exception InnoDB status check: Show engine InnoDB status; Applicable scenario: The INOODB is suitable for most OLTP applications (3). MySQL Storage engine CSV feature: Data is stored as text in the file. The CSV file stores the contents of the table.        The CSM file stores the table's metadata such as the state of the table and the amount of data. frm file stores the structure of the table information in CSV format data storage All columns must not be NULL for unsupported indexes (not suitable for large tables, not suitable for online processing)  You can edit the data file directly for the applicable scenario: Intermediate table for data exchange MySQL Data Catalog->csv file-Other web programs Excel spreadsheet, CSV file MySQL Data Catalog (4).    MySQL Storage Engine archive features: Compression of table data with zlib, less disk I/O data stored in files with arz suffix only the INSERT and select Operations Support indexing on the self-Increment ID column only Application scenario: Logging and Data acquisition class applications (4). MySQL Storage engine memory features: The data is only saved in the Memories Storage engine I/O efficiency is particularly high support hash index and Btree index all fields for fixed length do not support blog and T Ext and other large-scale memory storage engine use table-level lock table to store the maximum value of data by the Max_heap_table_size parameter to determine the applicable scenario: for the lookup or mapping table, such as postal code and the region for the preservation of data analysis production Used for the intermediate table of the rawThe result table for caching periodic aggregated data 

5). Server parameters for MySQL database

(1). MySQL config parameter scope global parameterSet global Parameter name = value of the parameter;SET @ @global. Parameter name: = parameter value; session parameterSet[session] Parameter name = value of the parameter;SET @ @session. Parameter name: = argument value, (2). Memory configuration-related parameters determine the upper limit of memory that can be used to determine the memory used by each connection of MySQL sort_buffer_size join_buffer_ Size Read_buffer_size read_rnd_buffer_size determines how much memory needs to be reserved for the operating system how to allocate memory for the cache pool Innodb_buffer_po Ol_size Total Memory-(number of memory * connections required per thread lock)-the system's reserved Memory Key_buffer_size (3). I/O-related configuration parameters Innodb Storage Engine I/o parameter settings: innodb_log_file_size innodb_log_file_in_group innodb_log_buffer_ Size Innodb_flush_log_at_trx_commit Innodb_flush_method = o_direct innodb_file_per_table = 1 I Nnodb_doublewrite = 1 Mysiam I/o parameter settings for the storage Engine: Delay_key_write OFF: Refreshes the dirty block in the key buffer to disk on after each operation: only on the key table        When you specify a table with the Delay_key_write option, use the Lazy flush all: Use the delay key write (4) for all Mysiam tables. Security-related configuration parameters expire_logs_days Specify the number of days to automatically clean Binlog        Max_allowed_packet control the size of the packet that MySQL can accept (32M) skip_name_resolve Disable DNS lookup sysdate_is_now ensure that sysdate () returns a deterministic date   Read_Only prohibit write permissions for non-super-privileged users skip_slave_start disable slave automatic recovery     Sql_mode set the SQL mode used by MySQL strict_trans_tables no_engine_subtitutoion no_zero_date No_zero_in_date only_full_group_by

(5).其他相关配置参数        sync_binlog = 1控制MySQL如何向磁盘刷新binlog        tmp_table_size和max_heap_table_size 控制内存临时表的大小(设置一致)        max_connections = 2000 控制允许的最大连接数

5). MySQL database structure design and SQL optimization

(1).过分的反范式化为表的建立太多的列(2).过分的范式化造成太多的表关联(3).在OLTP环境中使用不恰当的分区表(4).使用外键保证数据的完整性

Order of performance optimization

• Database structure design and SQL statement optimization
• Selection of the storage engine for the database and configuration of the parameters
• The selection and optimization of the system
• Hardware upgrades

One: Analysis and optimization of the performance impact of MySQL database