MySQL General optimization

MySQL optimization

--Xudong He

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/82/F8/wKioL1dnnZ2BRTIHAABANBXUs0E228.jpg "title=" 123. JPG "alt=" wkiol1dnnz2brtihaabanbxus0e228.jpg "/>

MySQL uses memory

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/82/F8/wKiom1dnnXvwWlwdAAC-RhqEAwc760.jpg "title=" 122. JPG "alt=" wkiom1dnnxvwwlwdaac-rhqeawc760.jpg "/>

Features of the engine

Comment:supports transactions, Row-level locking, and foreign keys

Supports Transactions : Transaction

Row-level Locking : Row lock

foreign keys: foreign key.

Transactions : Indicates whether transactional processing is supported, yes is supported, no indicates no support.

XA : Indicates whether the Distributed transaction Processing XA specification, yes is supported, no indicates no support.

savepoints : Indicates whether the savepoint is supported so that the transaction is rolled back to the savepoint, yes is supported, and no indicates no support.

Currently, InnoDB uses dual-track authorization, one is GPL-licensed and the other is proprietary software licensing.

InnoDB The storage engine gives the MySQL database a transaction security table with transactional, rollback, and crash-repair capabilities, and multiple versions of concurrency control. The InnoDB storage engine also provides row-level locks.

Advantages: Good transaction management, crash repair capability and concurrency control.

Disadvantage: Reading and writing efficiency is slightly poor, occupy data space is relatively large.

3 aspects introduce the characteristics of InnoDB:

1 InnoDB There are two ways in which tables and indexes are stored in the storage engine: Shared tablespace storage and multi-table space storage.

Shared tablespace storage. The table structure that is created is stored in a file with a suffix of. frm, and the data and indexes exist in the table spaces defined by Innodb_data_home_dir and Innodb_data_file_path.

Multi-table Space storage. The table structure you create is stored in a file with a. frm suffix, but the data and indexes for each table are saved separately in the. ibd file. If the table is partitioned, each partition table corresponds to a separate. ibd file, and the file name indicates the + partition name. If you are a partitioned table, each partition table corresponds to a separate. ibd file, and the file name is the table name + partition name. Using multi-tablespace storage requires setting the parameter innodb_file_per_table and restarting the service to take effect only for new tables.

2 InnoDB The storage engine supports foreign keys, the table where the foreign key resides is a child table, and the table that the foreign key depends on is the husband table. Husband table quilt The field associated with the foreign key must be the primary key. If you delete, modify a piece of information in the table, the child table must also have a corresponding change.

3 InnoDB The storage engine supports autogrow column auto_increment, the value of the autogrow column cannot be empty, and the value must be unique. In addition, in MySQL database to specify that the self-increment column must be the primary key, and then insert the value, automatic growth is divided into 3 cases.

First, if the autogrow column does not enter a value, the inserted column automatically grows after the value.

Second, if you enter a value of 0 or null (NULL), the inserted value is also the auto-grow value.

If you insert a certain value and the value is not present in the previous data, you can insert it directly.

Threading Features

# The 4 file_io threads in 5.1 include:
Insert Buffer Thread
Log Thread
Read thread
Write thread

#而在5.5 with a total of 10 file_io threads
(insert buffer thread) * 1
(log thread) * 1
(Read thread) * 4
(Write thread) * 4

10 parameters for general optimization

(1) , Max_connections:
(2), Record_buffer:
(3), Key_buffer_size:
4), Back_log:
(5), Interactive_timeout:
(6), Sort_buffer:
(7), Table_cache:
(8), Thread_cache_size:
(9), MySQL search function
(10), Wait_timeout:

System Layer IO scheduling algorithm

NoOp anticipatory deadline [CFQ]
The Deadline:deadline algorithm guarantees a minimum delay time for a given IO request.
Anticipatory: After an IO occurs, if another process requests Io, a default 6ms guessing time is generated, guessing what the next process request Io is doing. This can cause a large delay for random reads.
Bad for database applications, and for Web servers.
CFQ: An IO queue is maintained for each process, and IO requests from each process are handled by CFQ in a round-robin manner, which is fair to every IO request. Suitable for discrete-time reading applications.
NOOP: All IO requests are processed in FIFO queue format. There is no performance issue with default IO.

Optimizing Swap Partitions

By default , when the swap space is used to reach 60%, the cache/buffers in physical memory begins to be released.

Brush disk mechanism

MySQL log operation steps: Log_buffer---mysql write (write)---> log_file---os refresh (flush)---> Disk

0 : The data in log buffer is written to log file at a frequency of one second, and the file system-to-disk synchronization is performed, but the commit of each transaction does not trigger any log buffer to log File Refresh or filesystem-to-disk refresh operation;

1 : The data in log buffer will be written to log file each time the transaction commits, and the file system to disk synchronization will also be triggered;

2 : Transaction commit triggers a flush of log buffer to log file, but does not trigger a disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.

Specific display

0 (Delayed Write): Log_buffer--Every 1 seconds---log_file-real-time disk

1 (real-time write, real-time brush): log_buffer-Real-time, log_file-real -time disk

2 (real-time write, delay brush): log_buffer-Real-time Log_file--Every 1 seconds-->disk

Process Management Ideas

Zombie Process: A child process exits without calling wait () or waitpid () on its parent process. This child process is the zombie process. If the parent process persists and does not call wait, the zombie process cannot be reclaimed until the process is retired by Init after its parent process exits.
Orphan process: One parent process exits, and one or more of its child processes are still running, then those child processes will become orphans. The orphan process will be adopted by the INIT process (process number 1) and the Init process completes the state collection for them.
The zombie process will lead to a waste of resources, while the orphans will not.

The maximum number of processes that can exist simultaneously

[Email protected] ~]$ Cat/proc/sys/kernel/pid_max
32768

Linux load, which is 3 times times the number of cores

Linux The maximum allowed number of processes can be viewed by ulimit-u

This article from the "Clear Sky" blog, declined reprint!

MySQL General optimization