PostgreSQL10-性能优化

10.性能优化

linux系统的I/O调度算法

查看支持的算法
dmesg | grep -i scheduler

查看磁盘的算法 （中括号包围的）
cat /sys/block/sda/queue/scheduler

修改（服务器重启失效）
echo noop > /sys/block/sda/queue/scheduler
永久修改需要改grub

算法
cfq：绝对公平算法，适合通用服务器
noop：电梯调度算法，适合SSD
deadline：绝对保障算法，适合I/O高的场景

预读参数调整

修改linux内核预读磁盘扇区参数进行I/O优化。

对于大量顺序读取的操作，可以有效减少I/O等待时间。

不适合读取大量小文件。

查看
/sbin/blockdev --getra /dev/sda
设置（echo也行）
/sbin/blockdev --setra 16384 /dev/sda

内存优化

关闭swap
swapoof -a

关闭透明大页(非永久关闭)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
永久关闭
/etc/grub.conf, kernel行加上transparent_hugepage-never

关闭NUMA
BIOS中的内存配置，Node Interleaving=Disabled
/etc/grub.conf,kernel行加上numa=off

---

全局参数调整

重要参数
shared_buffers
work_mem
random_page_cost

---

统计信息和信息查询

PG运行期间会产生大量统计信息，查询优化器通过这些信息选择最快的查询路径

统计信息被保存在以pg_stat或pg_statio开头的系统表中

还有的信息被保存在pg_statistics表中，该表只能超级用户查看，普通用户借助pg_stats视图查看

数据库级统计信息��

系统视图pg_stat_database

该视图中，除 numbackends 字段和 stats_reset 字段外， 其他字段的值是自从 stats_reset 字段记录的时间点执行 pg_stat_reset（）命令以来的统计信息 。

建议使用者在进行优化和参数调整之后执行 pg_stat_reset()命令,刷新统计信息

缓存命中率  接近1
select blks_hit::float/(blks_read + blks_hit) as cache_hit_ratio from pg_stat_database where datname=current_database();
低于99%，适当调大shared_buffers

事务提交率  接近1
select xact_commit::float/(xact_commit + xact_rollback) as successful_xact_ratio from pg_stat_database where datname=current_database();
过低，检查死锁，超时过多

---

表级统计信息

系统视图pg_stat_user_tables 或** pg_stat_all_tables**

查看索引扫描占数据库所有扫描的比率  接近1
select sum(idx_scan)/(sum(idx_scan)+sum(seq_scan)) as idx_scan_ratio from pg_stat_all_tables where schemaname='public';
或
select relname,idx_scan::float/(idx_scan+seq_scan+1) as idx_scan_ratio from pg_stat_all_tables where schemaname='public' order by idx_scan_ratio ASC;

查看所有模式
\dnS
或
select  nspname from pg_namespace;

---

语句级统计信息

语句级的统计信息一般通过 pg_stat_statements 、 postgres 日志 、 auto_explain 来获取

开启 pg_stat_statements��

vim postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

create extension pg_stat_statements;

查询平均执行时间最长的3条查询

select calls.total_time/calls as avg_time,left(query,80) from pg_stat_statements order by 2 desc limit 3;

---

查看SQL执行计划

begin; 
explain analyze  update product set name='NF' where id=10;   使用analyze语句时，sql会被执行，应在事务中执行
rollback;

---

session级统计信息

因消耗资源，在生产环境关闭，在session级别使用

在开启响应选项后，使用explain analyze语句会有更多输出

vim postgresql.conf
log_parser_stats 和 log_planner_stats 为一组，
log_statement_stats 为一组，两组参数不能全部同时设置为on