Linux下MySQL所在磁盘,线程,内存的配置

磁盘日志策略
/etc/fstab中可以进行正面的配置(其性能按从上到下的顺序排列)
  • data=writeback
  • 只有元数据写入日志.元数据写入与数据写入并不同步.这是最快的配置,对innodb来说通常是安全的,innodb有自己的事务日志.唯一例外是当系统崩溃.frm文件损坏了.
  • data=ordered
  • 这个选项也只会记录元数据,但是会提供一些一致性保证,在写元数据之前会先写数据,使它们保持一致.这个选项只比writeback稍微慢一点,但是崩溃时更安全.
  • data=journal
  • 此选项提供了原子日志的行为,在数据写入到最终位置前,将会记录到日志中.
 
一般推荐MySQL使用XFS类型的文件系统.
首选XFS,其次是ext4
选择磁盘调度策略
通过使用正面的命令来查看系统所支持的任务调度策略
cfq(Completely Fair Queueing, 完全公平排队)
 
$cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
  • sda需要替换成想要查看的磁盘的盘符
  • noop适合没有自己的调度算法的设备,例如SAN或者RAID
  • deadline则对RAID及直接使用的磁盘都工作良好
  • 重要的是别用cfq,这可能会导致严重的性能问题
 
 
ext4默认有开启write barriers功能,该功能会在调用fsync()的时候立刻flush cache,会影响性能.
通常建议将write barriers功能关闭.
 
Write Barriers
A write barrier is a kernel mechanism used to ensure that file system metadata is correctly written and ordered on persistent storage, even when storage devices with volatile write caches lose power. File systems with write barriers enabled also ensure that data transmitted via fsync() is persistent throughout a power loss.
Enabling write barriers incurs a substantial performance penalty for some applications. Specifically, applications that use fsync() heavily or create and delete many small files will likely run much slower.
16.1. Importance of Write Barriers
File systems take great care to safely update metadata, ensuring consistency. Journalled file systems bundle metadata updates into transactions and send them to persistent storage in the following manner:
  1. First, the file system sends the body of the transaction to the storage device.
  2. Then, the file system sends a commit block.
  3. If the transaction and its corresponding commit block are written to disk, the file system assumes that the transaction will survive any power failure.
However, file system integrity during power failure becomes more complex for storage devices with extra caches. Storage target devices like local S-ATA or SAS drives may have write caches ranging from 32MB to 64MB in size (with modern drives). Hardware RAID controllers often contain internal write caches. Further, high end arrays, like those from NetApp, IBM, Hitachi and EMC (among others), also have large caches.
Storage devices with write caches report I/O as "complete" when the data is in cache; if the cache loses power, it loses its data as well. Worse, as the cache de-stages to persistent storage, it may change the original metadata ordering. When this occurs, the commit block may be present on disk without having the complete, associated transaction in place. As a result, the journal may replay these uninitialized transaction blocks into the file system during post-power-loss recovery; this will cause data inconsistency and corruption.
How Write Barriers Work
Write barriers are implemented in the Linux kernel via storage write cache flushes before and after the I/O, which isorder-critical. After the transaction is written, the storage cache is flushed, the commit block is written, and the cache is flushed again. This ensures that:
  • The disk contains all the data.
  • No re-ordering has occurred.
With barriers enabled, an fsync() call will also issue a storage cache flush. This guarantees that file data is persistent on disk even if power loss occurs shortly after fsync() returns.
 
网络配置
mysql的两个相关配置
skip_name_resolve
back_log: 
  • 这个选项控制MySQL的传入TCP连接队列大小
  • 默认为50
  • 设置不够的症状是,客户端会看到零星的"连接被拒绝"的错误,配以三秒超时规则
  • 还需要看看GNU/Linux系统的somaxconn限制,默认为128
 
线程
GUN/Linux提供两个线程库:
LinuxThreads线程库,大部分已经不使用的线程库
POSIX线程库: 新的原生线程库
 
内存交换区
使用vmstat查看内存交换的频率,查看si和so两个指标,通常喜欢<10/s的交换
操作系统中的配置:
/proc/sys/vm/swappiness:
  •  A low value causes the kernel to avoid swapping, a higher value causes the kernel to try to use swap space.
  • 默认为60,这是很糟糕的(针对服务器)
  • 通常要设置为非常小,0或者1

posted @ 2021-05-06 11:13  老玉米  阅读(314)  评论(0)  编辑  收藏  举报