MySQL Database optimization

To be add ...

--------------------------------------------------------------------------------------------------------------- ------

Predecessor articles

Http://blog.chinaunix.net/uid-20639775-id-3154234.html

Description: The environment for this article is CentOS 5.5 bit/mysql 5.1.50

Introduction: The use of MySQL for some time, during the period did a lot of MySQL optimization, design, maintenance work, the two days have time to do a simple summary, to facilitate their own memories, but also hope to be a little help to everyone.

I Hardware configuration Optimization

• CPU Selection: Multi-core CPU, High frequency CPU
• Memory: Larger memory
• Disk selection: Faster speed, RAID, array cards,
• Network environment choice: As far as possible to deploy in the LAN, SCI, optical cable, gigabit network, dual network cable to provide redundancy, 0.0.0.0 multi-port binding monitoring

II OS-level optimization

• Use a 64-bit operating system for better use of large memory.
• Set Noatime,nodiratime

[Email protected]_server1 ~]$ Cat/etc/fstab

label=//ext3 defaults,noatime,nodiratime 1 1

/dev/sda5/data XFS defaults,noatime,nodiratime 1 2

• Optimizing Kernel Parameters

net.ipv4.tcp_keepalive_time=7200

net.ipv4.tcp_max_syn_backlog=1024

Net.ipv4.tcp_syncookies=1

Net.ipv4.tcp_tw_reuse = 1

Net.ipv4.tcp_tw_recycle = 1

NET.IPV4.NEIGH.DEFAULT.GC_THRESH3 = 2048

NET.IPV4.NEIGH.DEFAULT.GC_THRESH2 = 1024

NET.IPV4.NEIGH.DEFAULT.GC_THRESH1 = 256

Net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.forwarding = 1

Net.ipv4.conf.default.proxy_arp = 0

Net.ipv4.tcp_syncookies = 1

Net.core.netdev_max_backlog = 2048

Net.core.dev_weight = 64

Net.ipv4.tcp_rmem = 4096 87380 16777216

Net.ipv4.tcp_wmem = 4096 65536 16777216

net.ipv4.tcp_rfc1337 = 1

Net.ipv4.tcp_sack = 0

Net.ipv4.tcp_fin_timeout = 20

Net.ipv4.tcp_keepalive_probes = 5

Net.ipv4.tcp_max_orphans = 32768

Net.core.optmem_max = 20480

Net.core.rmem_default = 16777216

Net.core.rmem_max = 16777216

Net.core.wmem_default = 16777216

Net.core.wmem_max = 16777216

Net.core.somaxconn = 500

Net.ipv4.tcp_orphan_retries = 1

Net.ipv4.tcp_max_tw_buckets = 18000

Net.ipv4.ip_forward = 0

Net.ipv4.conf.default.proxy_arp = 0

Net.ipv4.conf.all.rp_filter = 1

KERNEL.SYSRQ = 1

Net.ipv4.conf.default.send_redirects = 1

net.ipv4.conf.all.send_redirects = 0

Net.ipv4.ip_local_port_range = 5000 65000

Kernel.shmmax = 167108864

Vm.swappiness=0

• Increase file descriptor limits

Vim/etc/security/limits.conf

Plus

* Soft Nofile 65535

* Hard Nofile 65535

• File System Selection XFS

/dev/sda5/data XFS defaults,noatime,nodiratime 1 2

III MySQL Design optimization
III.1 Storage Engine Selection

• Myisam: Database concurrency is small, read and write less, and can be very good use of indexes, SQL statements relatively simple application, TB Data Warehouse
• INNODB: Large concurrent access, write operations more, there are foreign keys, transactions and other requirements of the application, the system memory is large.

III.2 naming rules

• Most development language naming conventions: such as Myadress
• Most open source idea naming rules: my_address
• Avoid random naming

III.3 field type selection

General principles for the selection of field types:

• Select the appropriate field type according to your needs, and the field type as small as possible when you meet your needs.
• Allocate only the minimum number of characters that meet your needs, not too generously.

Cause: Smaller field types smaller characters take up less memory, consume less disk space, consume less disk IO, and consume less bandwidth.

iii.3.1 Integral type:

See as:

Type	Bytes	Minimum value	Maximum Value
		(Signed/unsigned)	(Signed/unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
Mediumint	3	-8388608	8388607
		0	16777215
Int	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

According to the minimum integer that satisfies the requirement, the selection principle can not be used bigint with Int.

Store the IP with an unsigned int instead of char (15).

iii.3.2 floating Point type:

Type	Bytes	Precision Type	Usage Scenarios
FLOAT (M,D)	4	Single precision	Not high precision, small value
DOUBLE (m,d) (REAL)	8	Double precision	The accuracy is not high, the value is large
DECIMAL (m,d) (NUMERIC)	M+2	Custom Precision	Scenarios with high accuracy requirements

iii.3.3 Time Type

Type	Range of values	Storage space	0 value notation
DATE	1000-01-01~9999-12-31	3 bytes	0000-00-00
Time	-838:59:59~838:59:59	3 bytes	00:00:00
Datetime	1000-01-01 00:00:00~9999-12-31 23:59:59	8 bytes	0000-00-00 00:00:00
TIMESTAMP	At some point in 19,700,101,000,000-2037 years.	4 bytes	00000000000000
Year	Year (4): 1901~2155 year (2): 1970~2069	1 bytes	0000

iii.3.4 character types

Type	Maximum length	Occupy Storage space
char[(M)]	M bytes	M-byte
varchar[(M)]	M bytes	M+1 bytes
Tinyblod,tinytext	2^8-1 bytes	l+1 bytes
Blob,text	2^16-1 bytes	l+2
Mediumblob,mediumtext	2^24-1 bytes	l+3
Longblob,longtext	2^32-1 bytes	l+4
ENUM (' value1 ', ' value2 ',...)	65,535 Members	1 or 2 bytes
SET (' value1 ', ' value2 ',...	64 Members	1,2,3,4 or 8 bytes

Note: L indicates the meaning of variable length

The choice of varchar and char depends on the engine and the specific situation, based on the following principles:

1. Use char if the column data items are of the same size or have a small difference.
2. If the size of the column data items varies considerably, varchar is used.
3. For MyISAM tables, use char as much as possible, especially for MyISAM and ISAM data tables, which often require modification and are prone to fragmentation, with the disadvantage of taking up disk space.
4. For the InnoDB table, because its data row internal storage format does not differentiate between fixed-length data rows and variable-length rows of data (all data rows share a header section that holds pointers to individual data columns), it is not necessarily better to use the char type than the varchar type. In fact, because the char type typically takes up more space than the varchar type, it is more advantageous to use the varchar type from the point of view of reducing space consumption and reducing disk I/O.
5. As long as there is a varchar type field in the table, all char fields automatically become varchar types, so it is recommended that the fixed-length and variable-length data be separated.

III.4 Encoding Selection

Single byte latin1

Multibyte UTF8 (kanji accounted for 3 bytes, English letters occupy one byte)

If it contains Chinese characters, it is best to use the UTF8 type uniformly, to avoid the occurrence of garbled characters.

III.5 Primary Key Selection principle

Note: The primary key design here is mainly for the InnoDB engine.

1. Can only represent the row.
2. Explicitly defines a primary key for a numeric type's self-increment field that can be used only for the master key and not for other purposes.
3. The MySQL primary key should be single-column to increase the efficiency of the connection and filtering operations.
4. Primary key field type as small as possible, can use smallint without int, can use int without bigint.
5. Try to ensure that the primary key field is not updated to prevent changes in the primary key fields, causing data storage fragmentation and reducing IO performance.
6. The MySQL primary key should not contain dynamically changing data such as timestamps, creation time columns, modified time columns, and so on.
7. The MySQL primary key should have a computer automatically generated.
8. The primary key field is placed in the first order of the data table.

It is recommended to use the numeric type master key and use the Auto_increment property to increase it automatically.

III.6 other areas to be aware of

• NULL OR NOT NULL

If possible, set each field as not NULL, unless there are special requirements for the following reasons:

1. Using an index with a null column will take up more disk space because the index NULL column needs to be stored in the outer space.
2. When comparing, the program is more complex.
3. Columns with NULL are special, SQL is difficult to optimize, and if it is a combined index, this null type of field can greatly affect the efficiency of the entire index.

• Index

Disadvantages of indexing: greatly accelerates queries, reducing the number of data rows scanned and locked.

Disadvantages of indexing: Taking up disk space, slowing data update speed, and increasing disk IO.

The following guidelines are available for adding indexes:

1. Select a uniqueness index.
2. Index fields that often require sorting, grouping, and union operations.
3. Index The fields that are commonly used as query criteria.
4. Limit the indexed data, not the more the better.
5. Try to use an index with a small amount of data, and you can consider a prefix index for large segments.
6. Delete indexes that are no longer used or are seldom used.
7. Overwrite the index with core SQL precedence.
8. Avoid using the string master key.

• Inverse Paradigm Design

Appropriate use of redundant inverse paradigm design, space-changing time can sometimes be very efficient.

IV MySQL software optimization

• Turn on MySQL replication for read-write separation, load balancing, and load-sharing of read loads across multiple slave servers to improve server processing power.
• Improve performance with the recommended GA version
• Data splitting of big data with new partitioning capabilities

V MySQL Configuration optimization

Note: Once the global parameters are set, the pre-occupied resources are started with the server.

• Key_buffer_size parameters

MySQL index buffer, if the use of MyISAM words to focus on setting this parameter, according to (key_reads/key_read_requests) judge

• Innodb_buffer_pool_size parameters

INNODB the most important engine parameters for data, index, and log buffering, judging by (hit Riatos and file I/O)

• Wait_time_out parameters

Thread connection Timeout time, try not to set a very large, recommended 10s

• Max_connections parameters

The maximum number of connections allowed by the server, try not to set too large, because the settings are too large to cause memory overflow, need to be determined by the following formula:

SET @k_bytes = 1024;

SET @m_bytes = @k_bytes * 1024;

SET @g_bytes = @m_bytes * 1024;

SELECT

(

@ @key_buffer_size + @ @query_cache_size + @ @tmp_table_size +

@ @innodb_buffer_pool_size + @ @innodb_additional_mem_pool_size +

@ @innodb_log_buffer_size +

@ @max_connections *

(@ @read_buffer_size + @ @read_rnd_buffer_size + @ @sort_buffer_size +

@ @join_buffer_size + @ @binlog_cache_size + @ @thread_stack

) )

/@g_bytes as MAX_MEMORY_USED_GB;

• Thread_concurrency parameters

Number of concurrent threads, (cpu+disk) * *, based on (request queue and tickets shown in OS)

• Sort_buffer_size parameters

Get faster--order by,group by,select distinct,union DISTINCT

• Read_rnd_buffer_size parameters

Get a faster--order by when sorting operations by key

• Join_buffer_size parameters

Join connection uses the buffer size of the full table scan connection, judging by Select_full_join

• Read_buffer_size parameters

Buffer size reserved for queries when full table is scanned, judging by Select_scan

• Tmp_table_size parameters

Temporary memory table settings, if more than the settings will be converted to disk table, according to the parameters (Created_tmp_disk_tables) to determine

• Innodb_log_file_size parameter (default 5M)

Logging the InnoDB engine's redo log file, setting a larger value means a longer recovery time.

• Innodb_flush_method parameter (default Fdatasync)

Linux systems can use O_direct to process data files and avoid OS-level Cache,o_direct mode to improve IO Submission performance of data files and log files

• Innodb_flush_log_at_trx_commit (default 1)

1. 0 means log writes to the cache once per second and flush log to disk.
2. 1 means that log writes to the cache after each transaction commit and flush log to disk.
3. 2 means that after each transaction commit, the log data is written to the cache and the flush log is executed once per second to disk.

VI MySQL statement-level optimization

1. Performance check of the reading statements, in the InnoDB count the number of rows, suggest another table, the use of MyISAM, regular statistics. The general statistical data does not require too precise circumstances to apply.
2. Try not to do operations in the database.
3. Avoid negative queries and% prefixes for fuzzy queries.
4. Do not perform operations on indexed columns or use functions.
5. Do not query data in a production environment program using SELECT * from. Queries only the columns that need to be used.
6. The query uses limit as much as possible to reduce the number of rows returned, reducing data transfer time and bandwidth waste.
7. The WHERE clause uses functions on the query columns as much as possible, because the use of functions on query columns is not indexed.
8. Avoid implicit type conversions, such as the character type must be ", digital type must not use '."
9. All of the SQL Keywords capitalized, develop good habits, to avoid the duplication of SQL statements resulting in system resources waste.
10. When querying a table, remember to put the small result set in front and follow the principle of small result set driving large result set.
11. Turn on slow queries and periodically use explain to optimize SQL statements in slow queries.

MySQL Database optimization