Linux에서 MySQL을 최적화하기 위한 팁-MySQL 튜토리얼-php.cn

요즘은 어떤 개발을 하든 고효율에 주목해야 합니다. 개발할 때 기술에만 주의를 기울이는 것이 아니라 때로는 일부 기능을 최적화하면 작업 효율성도 향상됩니다. 요즘 MySQL이 실행되는 대부분의 환경은 Linux에 있습니다. 다음은 Linux 운영 체제에서 MySQL을 최적화하는 방법에 대한 몇 가지 일반적이고 간단한 전략입니다. 이러한 방법은 모두 MySQL 성능을 향상시키는 데 도움이 됩니다.

1. CPU

CPU부터 시작해 보겠습니다.
주의 깊게 살펴보면 일부 서버에 흥미로운 현상이 있습니다. /proc/cpuinfo를 cat하면 CPU 주파수가 공칭 주파수와 다르다는 것을 알 수 있습니다.

#cat /proc/cpuinfo      processor : 5     model name : Intel(R) Xeon(R) CPU E5-2620 0 @2.00GHz ...     cpu MHz : 1200.000

로그인 후 복사

이것은 Intel E5-2620 CPU입니다. 2.00G * 24 CPU이지만 다섯 번째 CPU의 주파수는 1.2G인 것으로 나타났습니다.
이유는 무엇인가요?
이 기능은 실제로 CPU의 최신 기술인 에너지 절약 모드에서 파생되었습니다. 운영 체제는 CPU 하드웨어와 협력하여 시스템이 사용 중이 아닐 때 전력을 절약하고 온도를 낮추기 위해 CPU의 주파수를 줄입니다. 이는 환경 보호론자들과 지구 온난화에 맞서 싸우는 데 도움이 되는 일이지만 MySQL에게는 재앙이 될 수 있습니다.
MySQL이 CPU 리소스를 최대한 활용할 수 있도록 하려면 CPU를 최대 성능 모드로 설정하는 것이 좋습니다. 이 설정은 BIOS 및 운영 체제에서 설정할 수 있습니다. 물론 BIOS에서 이 옵션을 설정하는 것이 더 좋고 철저합니다. 다양한 BIOS 유형의 차이로 인해 CPU를 최대 성능 모드로 설정하는 방법이 크게 다르므로 여기서는 설정 방법을 보여주지 않습니다.

2. 메모리

그럼 메모리를 살펴보고 무엇을 최적화할 수 있는지 살펴보겠습니다.

i) 먼저 Numa를 살펴보겠습니다.
Non-Uniform Memory Access Architecture(NUMA: Non-Uniform Memory Access)도 최신 메모리 관리 기술입니다. 대칭형 다중 프로세서 아키텍처(SMP: Symmetric Multi-Processor)에 해당합니다. 간단한 팀 분류는 다음과 같습니다.

Linux에서 MySQL을 최적화하기 위한 팁

사진과 같이 여기서는 자세한 NUMA 정보를 소개하지 않습니다. 그러나 SMP의 메모리 액세스 비용은 동일하지만 NUMA 아키텍처에서는 로컬 메모리 액세스 비용과 비로컬 메모리 액세스 비용이 다르다는 것을 직관적으로 알 수 있습니다. 이에 따라 이 기능에 따라 운영 체제에서 프로세스의 메모리 할당 방법을 설정할 수 있습니다. 현재 지원되는 방법은 다음과 같습니다.

--interleave=nodes   --membind=nodes   --cpunodebind=nodes   --physcpubind=cpus   --localalloc   --preferred=node

로그인 후 복사

즉, 메모리가 로컬로 할당되거나, 특정 CPU 노드에 할당되거나, 폴링에서 할당되도록 지정할 수 있습니다. --interleave=nodes 폴링 할당 모드가 설정되지 않은 경우 모든 NUMA 노드에 메모리를 할당할 수 있습니다. 다른 방법으로는 다른 NUMA 노드에 메모리가 남아 있더라도 Linux는 남은 메모리를 이 프로세스에 할당하지 않고 SWAP를 사용하여 메모리를 얻습니다. 숙련된 시스템 관리자나 DBA는 모두 SWAP로 인해 데이터베이스 성능 저하가 얼마나 파괴적인지 알고 있습니다.
그러므로 가장 쉬운 방법은 이 기능을 끄는 것입니다.
기능을 끄는 방법은 다음과 같습니다. BIOS, 운영 체제에서 또는 프로세스를 시작할 때 이 기능을 일시적으로 끌 수 있습니다.
a) 다양한 BIOS 유형의 차이로 인해 NUMA를 끄는 방법이 크게 다르기 때문에 여기서는 설정 방법을 보여주지 않겠습니다.
b) 운영 체제에서 이를 끄려면 아래와 같이 /etc/grub.conf의 커널 줄 끝에 numa=off를 직접 추가하면 됩니다.

kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-root rd_NO_LUKS.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet
SYSFONT=latarcyrheb-sun16 
rhgb crashkernel=auto rd_LVM_LV=VolGroup/swap rhgb crashkernel=auto quiet KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM  numa=off

로그인 후 복사

또한 vm.zone_reclaim_mode를 설정할 수 있습니다. =0은 메모리 회수를 시도합니다.
c) MySQL을 시작할 때 NUMA 기능을 끄세요:
numactl --interleave=all mysqld &

물론 가장 좋은 방법은 BIOS에서 끄는 것입니다.

ii) vm.swappiness를 다시 살펴보겠습니다.

vm.swappiness는 물리적 메모리 스와핑을 제어하기 위한 운영 체제의 전략입니다. 허용되는 값은 백분율이며 최소 0, 최대 100입니다. 기본값은 60입니다. vm.swappiness를 0으로 설정하면 가능한 한 적게 스왑하는 것을 의미하고, 100은 비활성 메모리 페이지를 최대한 많이 스왑하는 것을 의미합니다.
구체적으로: 메모리가 기본적으로 가득 차면 시스템은 이 매개변수를 사용하여 메모리에서 거의 사용되지 않는 비활성 메모리를 교체할지, 아니면 데이터 캐시를 해제할지 결정합니다. 캐시는 디스크에서 읽은 데이터를 캐시합니다. 프로그램의 지역성 원칙에 따라 이러한 데이터는 이름에서 알 수 있듯이 나중에 다시 읽을 수 있습니다. "오랜만" 기억.
vmstat를 사용하여 비활성 메모리의 양을 확인할 수 있습니다:

#vmstat -an 1   procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
   r b swpd free  inact  active si so bi bo in cs us sy id wa st   1 0 0 27522384 326928 1704644 0 0 0 153 11 10 0 0 100 0 0  
    0 0 0 27523300 326936 1704164 0 0 0 74 784 590 0 0 100 0 0   0 0 0 27523656 326936 1704692 0 0 8 8 439 1686 0 0 100 0 0  
     0 0 0 27524300 326916 1703412 0 0 4 52 198 262 0 0 100 0 0

로그인 후 복사

/proc/meminfo를 통해 더 자세한 정보를 볼 수 있습니다:

#cat /proc/meminfo | grep -i inact   Inactive: 326972 kB   Inactive(anon): 248 kB   Inactive(file): 326724 kB

로그인 후 복사

这里我们对不活跃inactive内存进一步深入讨论。Linux中，内存可能处于三种状态：free，active和inactive。众所周知，Linux Kernel在内部维护了很多LRU列表用来管理内存，比如LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE , LRU_ACTIVE_FILE, LRU_UNEVICTABLE。其中LRU_INACTIVE_ANON, LRU_ACTIVE_ANON用来管理匿名页，LRU_INACTIVE_FILE , LRU_ACTIVE_FILE用来管理page caches页缓存。系统内核会根据内存页的访问情况，不定时的将活跃active内存被移到inactive列表中，这些inactive的内存可以被交换到swap中去。
一般来说，MySQL，特别是InnoDB管理内存缓存，它占用的内存比较多，不经常访问的内存也会不少，这些内存如果被Linux错误的交换出去了，将浪费很多CPU和IO资源。 InnoDB自己管理缓存，cache的文件数据来说占用了内存，对InnoDB几乎没有任何好处。
所以，我们在MySQL的服务器上最好设置vm.swappiness=0。

我们可以通过在sysctl.conf中添加一行：

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

로그인 후 복사

并使用sysctl -p来使得该参数生效。

三、文件系统

最后，我们看一下文件系统的优化
i)我们建议在文件系统的mount参数上加上noatime，nobarrier两个选项。

用noatime mount的话，文件系统在程序访问对应的文件或者文件夹时，不会更新对应的access time。一般来说，Linux会给文件记录了三个时间，change time, modify time和access time。
我们可以通过stat来查看文件的三个时间：

stat libnids-1.16.tar.gz   File: `libnids-1.16.tar.gz&#39;   Size: 72309 Blocks: 152 IO Block: 4096 regular file 
  Device: 302h/770d Inode: 4113144 Links: 1   Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)  
  Access  : 2008-05-27 15:13:03.000000000 +0800   Modify: 2004-03-10 12:25:09.000000000 +0800  
   Change: 2008-05-27 14:18:18.000000000 +0800

로그인 후 복사

其中access time指文件最后一次被读取的时间，modify time指的是文件的文本内容最后发生变化的时间，change time指的是文件的inode最后发生变化(比如位置、用户属性、组属性等)的时间。一般来说，文件都是读多写少，而且我们也很少关心某一个文件最近什么时间被访问了。
所以，我们建议采用noatime选项，这样文件系统不记录access time，避免浪费资源。
现在的很多文件系统会在数据提交时强制底层设备刷新cache，避免数据丢失，称之为write barriers。但是，其实我们数据库服务器底层存储设备要么采用RAID卡，RAID卡本身的电池可以掉电保护；要么采用Flash卡，它也有自我保护机制，保证数据不会丢失。所以我们可以安全的使用nobarrier挂载文件系统。设置方法如下：
对于ext3, ext4和 reiserfs文件系统可以在mount时指定barrier=0；对于xfs可以指定nobarrier选项。

ii)文件系统上还有一个提高IO的优化*，那就是deadline。

在Flash技术之前，我们都是使用机械磁盘存储数据的，机械磁盘的寻道时间是影响它速度的最重要因素，直接导致它的每秒可做的IO(IOPS)非常有限，为了尽量排序和合并多个请求，以达到一次寻道能够满足多次IO请求的目的，Linux文件系统设计了多种IO调度策略，已适用各种场景和存储设备。
Linux的IO调度策略包括：Deadline scheduler，Anticipatory scheduler，Completely Fair Queuing(CFQ)，NOOP。每种调度策略的详细调度方式我们这里不详细描述，这里我们主要介绍CFQ和Deadline，CFQ是Linux内核2.6.18之后的默认调度策略，它声称对每一个 IO 请求都是公平的，这种调度策略对大部分应用都是适用的。但是如果数据库有两个请求，一个请求3次IO，一个请求10000次IO，由于绝对公平，3次IO 的这个请求都需要跟其他10000个IO请求竞争，可能要等待上千个IO完成才能返回，导致它的响应时间非常慢。并且如果在处理的过程中，又有很多IO请求陆续发送过来，部分IO请求甚至可能一直无法得到调度被“饿死”。而deadline兼顾到一个请求不会在队列中等待太久导致饿死，对数据库这种应用来说更加适用。
实时设置，我们可以通过

echo deadline >/sys/block/sda/queue/scheduler

来将sda的调度策略设置为deadline。

我们也可以直接在/etc/grub.conf的kernel行最后添加elevator=deadline来永久生效。

总结

CPU方面
关闭电源保护模式

内存：
vm.swappiness = 0
关闭numa

文件系统：
用noatime，nobarrier挂载系统
IO调度策略修改为deadline。

MySQL优化的方式很多，优化之后你的工作效率也会大大的提升，希望本文对你有用。