MySQL Hardware optimization

Causes of Downtime:

1. Operating environment: 35%, the operating environment can be seen as supporting the database server running system and resource collection, including operating system, hard disk and network

2. Performance: 35%

3. Replication 20%

4. Various types of database loss or corruption and other issues. 10%

Ranking of events leading to downtime

1. In the operational environment, the most common problem is the exhaustion of space

2. On performance issues, the most common downtime is due to poorly run SQL, as well as server bugs and bad behavior.

3. Index design is also the reason for the impact of downtime

4. Replication problems are usually due to inconsistent master and standby data

5. Data loss is usually caused by accidental deletion.

If you have an interview question: How to optimize MySQL database, you should answer from five aspects:

1. Hardware optimization 2. Software optimization 3.SQL optimization 4. Architecture Optimization 5. Process institutionalization

Hardware-Optimized:

One, server-level optimization

Baidu's optimization has been done very well, if they are now to optimize the time, it can only be optimized from the hardware

1. Server Selection

Sun minicomputer, dell730xd, HPDL380, IBM3850, cloud services, etc.

DELLR710

2. Number of CPUs, memory size

Large memory, high IO, is a must for modern web-based database (Baidu server memory: 96G---128, 2 instances, CPU

8 to 16 PCs)

3.

Disk: SAS, SSD, FIO card

Reduces seek time, rotation time, transmission time

Ssds

(High concurrency) >sas (on-line General Service) >sata (offline business)

4. Raid card battery, RAID level

WriteBack, Readaheadnone,direct,nowrite Cache

If Bad BBU

RAID0 >RAID10

>raid5>raid1

5. Other: Network card and so on (multi-block network card)

Second, operating system level optimization

1. I/O scheduling policy

NOOP, CFQ, Deadline, anticipatory

Temporary effect: echo "Dadline" >/sys/block/sda/queue/scheduler

Permanent:/etc/grub.conf in kernel plus elevator=deadline (requires restart)

2. Swap usage Strategies

echo "vm.swappiness=10" >>/etc/sysctl.conf

https://www.percona.com/blog/2014/04/28/oom-relation-vm-swappiness0-new-kernel/

3.

File system

Ext3, EXT4 or using XFS

To be precise, XFS is better than ext series

4. Avoid NUMA issues

Numactl--interleave=all that allows all processors to cross-access all memory

5./tmp partition

TMPFS/DEV/SHM Tmpfs defaults 00

After setting up tmpdir=/tmp, some people who habitually write files to TMP will change their habits because the files Occupy memory and are not disks, and are always occupied if not restarted.

6. CPU

Turn off the power-saving mode of the server

To view the Kondemand process running:

Ps-ef |grepkondemand

This article is from the "xinsz08の parallel space-time" blog, be sure to keep this source http://xinsz08.blog.51cto.com/10565212/1915967

MySQL Hardware optimization