MySQL Database optimization Summary

MySQL Database optimization Summary

-----du

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 bytes

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 data rows (all data rows share a header section, which holds pointers to each relevant data column). Therefore, using the char type is not necessarily better than using a 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 line.

2. Explicitly define a numeric type to increment the primary key of the field, this field can be used only for the master key, do not do other purposes.

3. The MySQL primary key should be single-column to improve 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 be automatically generated by the computer.

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 procedure is more complicated.

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 The fields that often require sorting, grouping, and union operations.

3. Index The fields that are frequently used as query criteria.

4. Limit the indexed data, not the more the better.

5. Try to use a small amount of data index, for large fields can be considered prefix index.

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 execution 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, regularly do 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 or use functions in indexed columns.

5. Do not query the data in the 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. Where clause uses functions on 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 used ', digital type must not use '.

9. All SQL keywords capitalize, develop good habits, avoid the duplication of SQL statements resulting in the waste of system resources.

10. When querying the 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 Summary