Shulou(Shulou.com)06/01 Report--

The design specification recommends:

1)。 Index specification

Explicitly specify self-incrementing int/bigint unsigned not null as primary key

Do not use foreign keys

Make rational use of the overlay index, but try not to exceed 5 fields

Rational use of leftmost index (prefix index / partial index)

Delete redundant indexes in time

Choose the appropriate index order with high selectivity and high condition.

Fields with low cardinality (Cardinality) do not create indexes (MySQL does not yet support bitmap indexes)

Using third-party system to realize text/blob full-text search

Create indexes on commonly used sorting (ORDER BY), grouping (GROUP BY), and taking unique (DISTINCT) fields

The number of indexes per table shall not exceed 5

Index field condition does not use function

2)。 Development environment

Enable log_queries_not_using_indexes

Set long_query_time to minimum

Check and analyze slow log regularly

Authorization is consistent with production environment

Close Query Cache

Set smaller InnoDB Buffer Pool, key buffer size

The amount of data should not be too small, otherwise some performance problems can not be avoided in advance.

3)。 Code of conduct

Batch import and export data must be notified to DBA in advance and request for assistance in observation

Promotional activities or new features should be notified to DBA in advance to request stress assessment.

Connect to the database without using SUPER permissions

Multiple ALTER operations in a single table must be merged into one operation

Database DDL and important SQL should be submitted for DBA review as soon as possible.

The important business database should inform DBA of the importance level and the timeliness requirements of data backup.

Do not update or query the database in batch during the peak period of business.

Submit online DDL requirements, all SQL statements must have comments

4)。 Hardware

NUMA new architecture, CPU direct access to memory, more efficient

CPU is generally not the bottleneck, but MySQL multicore support is still not good.

Devices are getting cheaper and large memory solves a lot of problems

SSD is used more and more widely, and will be the main force in the future.

Raid cards can effectively improve IOPS and data security (RAID 10 vs RAID 5)

Raid card must be equipped with BBU, set FORCE WB

Optimization recommendations:

1)。 System

Upgrade to 64-bit

/ tmp uses tmpfs of / dev/shm

Kernel

IO scheduling: deadline,noop, no cfq anyway

VM Management: vm.swappiness=0

2)。 File system: xfs/zfs

Full B + tree with high efficiency

Assign groups to improve the degree of concurrency

Delayed allocation, reduced IO

Mount:nobarrier 、 data=ordered,writeback

3) .MySQL configuration

Memlock

Open_files_limit

Max_connections

Long_query_time

Table_open_cache

Key_buffer_size

Query_cache_size

Tmp_table_size/max_heap_table_size

Innodb buffer pool

Innodb_flush_log_at_trx_commit

Interactive_timeout/wait_timeout

Transaction_isolation

Innodb_log_file_size

Innodb_data_file_path

Innodb_max_dirty_pages_pct

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.