数据库优化笔记

一、优化准则
1） 去除无用步骤；
2） 做得更快；
优化指标
1） 响应时间：与数据库交互多久能够相应。
2） 吞吐量：单位时间完成数据库任务。
优化目标
1） 性能目标：CPU利用率，IOPS降低；每秒处理SQL数，QPS提高。
2） 相应时间：降低多少毫秒时间。
3） 吞吐量：每秒出来SQL数,QPS提高。
二、优化方法
测试工具：memtest86+(内存测试工具)，STREAM(内存测试工具)，sysbench(综合测试工具，CPU/IO/数据库等)，fio(IO测试工具)，orion(oracle的IO测试工具)

硬件知识

文件系统及I/O调优
文件系统：Ext2文件系统、Ext3文件系统、Ext4文件系统、XFS文件系统、Barrier I/O
I/O调优方法
1) 打开noatime(包含ctime:改变世界，mtime:修改时间，atime:访问时间)
通常pg并不用，禁止没用的atime，mtime和ctime有点作用（判断相应的数据文件最后修改时间）
/etc/fstab的配置项上，设置noatime: /dev/sdd1/xfs noatime,errors=remount-ro 0 1
2) 调整预读
3) 调整虚拟内存参数
4) 写缓存优化
三个参数控制写缓存的过程
vm.dirty_background_ratio
vm.dirty_ratio
vm.dirty_writeback_centisecs
5) 调整I/O调度器
性能监控
数据库性能试图
track_counts:是否收集表和索引上的统计信息，默认为on；
track_functions:‘pl’只收集PL/PGSQL写的函数的统计信息，‘all’收集所有类型的函数，包括C,SQL，默认为‘none’；
track_activities:是否收集当前正在执行的SQL，默认为on；
track_io_timing:是否收集I/O的时间信息，默认为off,收集可能会导致某些平台的性能瓶颈；
pg_stat_activity:查询当前正在运行的SQL

pg_stat_database:

pg_stat_all_tables,pg_stat_sys_tables,pg_stat_user_tables分别显示所有表的统计信息，显示系统表的统计信息，显示用户表的统计信息。

pg_stat_user_functions:对数据库内函数的调用次数及其他信息进行统计的试图。

Linux监控工具
top:查看全局信息（CPU占用率，load情况，内存使用情况，topN的进程等）；
iostat：查看磁盘的读写IOPS，读写吞吐量，读写相应时间，I/O利用率；
vmstat：查看内存的情况；
sar：查看网络的流量等；

数据库配置优化
内存配置优化
shared_buffers:共享存储区的大小
work_mem:为每个进程单独分配的内存，主要用于排序，hash等
maintence_work_mem:为每个进程单独分配的内存，用于维护操作，如vacuum

双缓存优化
1） 设置较小的shared buffer,大多数内存给文件系统缓存使用。
2） 设置较大的shared buffer,只留较小的内存给系统缓存使用。

Vacuum中的优化

预写式日志（wal）写优化
checkpoint_segments
checkpoint_timeout