张文升--PostgreSQL基准测试与性能调优
Postgresql排序与limit组合场景性能极限优化详解
Postgresql排序与limit组合场景性能极限优化详解1 构造测试数据create table tbl(id int, num int, arr int[]);create index idx_tbl_arr on tbl using gin (arr);create or replace function gen_rand_arr() returns int[] as $$select array(select (1000*random())::int from generate_series(1,64));$$ language sql strict;insert into tbl select generate_series(1,3000000),(10000*random())::int, gen_rand_arr();insert into tbl select generate_series(1,500), (10000*random())::int, array[350,514,213,219,528,753,270,321,413,424,524,435,546,765,234,345,131,345,351];2 查询⾛GIN索引测试场景的限制GIN索引查询速度是很快的，在实际⽣产中，可能出现使⽤gin索引后，查询速度依然很⾼的情况，特点就是执⾏计划中Bitmap Heap Scan占⽤了⼤量时间，Bitmap Index Scan⼤部分标记的块都被过滤掉了。
这种情况是很常见的，⼀般的btree索引可以cluster来重组数据，但是gin索引是不⽀持cluster的，⼀般的gin索引列都是数组类型。
所以当出现数据⾮常分散的情况时，bitmap index scan会标记⼤量的块，后⾯recheck的成本⾮常⾼，导致gin索引查询慢。
我们接着来看这个例⼦explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 20;QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------Limit (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.665..57.668 rows=20 loops=1)-> Sort (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.664..57.665 rows=20 loops=1)Sort Key: numSort Method: top-N heapsort Memory: 27kB-> Bitmap Heap Scan on tbl (cost=2148.00..2152.01 rows=1 width=40) (actual time=57.308..57.581 rows=505 loops=1)Recheck Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])Heap Blocks: exact=493-> Bitmap Index Scan on idx_tbl_arr (cost=0.00..2148.00 rows=1 width=0) (actual time=57.248..57.248 rows=505 loops=1)Index Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])Planning time: 0.050 msExecution time: 57.710 ms可以看到当前执⾏计划是依赖gin索引扫描的，但gin索引出现性能问题时我们如何来优化呢？3 排序limit组合场景优化SQL中的排序与limit组合是⼀个很典型的索引优化创景。
PostgreSQL数据库调优经验
PostgreSQL数据库调优经验一、概述数据库的性能优化对于提升系统的整体性能至关重要。
本文将介绍一些PostgreSQL数据库调优的经验和技巧，旨在帮助开发人员和管理员提升数据库的性能和效率。
二、硬件调优1. 存储设备选择：选择高速且稳定的存储设备，如SSD硬盘，以提高数据库的读写性能。
2. 内存设置：合理设置shared_buffers参数，将其调整到适当的大小，以便缓存更多的数据块，提高查询的响应速度。
3. CPU设置：根据服务器的负载情况，调整max_connections参数以控制并发连接数，在高负载情况下可以考虑增加系统的CPU核心数。
三、索引优化1. 使用合适的索引：根据查询的需求和表的大小，选择合适的索引类型（B树、哈希、GiST等），并确保创建索引的列具有高选择性。
2. 删除不必要的索引：定期审查并删除不再使用或无效的索引，以减少索引维护的开销。
3. 索引覆盖：通过创建索引包含所需的查询列，减少磁盘I/O，提高查询的性能。
四、查询优化1. 避免全表扫描：使用WHERE子句和索引来过滤数据，避免全表扫描的开销。
2. 使用合适的JOIN类型：根据数据之间的关联关系，选择合适的JOIN类型（INNER JOIN、LEFT JOIN、OUTER JOIN等），以减少查询的复杂度。
3. 分解复杂查询：对于复杂的查询，可以将其分解为多个简单的查询，并使用临时表或WITH语句组合结果，以提高查询的可维护性和性能。
五、配置优化1. 文件系统设置：使用合适的文件系统（如XFS、EXT4等）以及正确的文件系统参数，提高I/O性能。
2. 日志设置：根据实际需求，合理设置日志级别和日志记录方式，避免过多的日志输出对性能造成影响。
3. 超时设置：根据业务需求和系统负载情况，调整合适的超时设置，避免长时间的等待或超时导致的性能问题。
六、并发控制1. 事务管理：合理管理事务的提交和回滚，尽量减少长事务的使用，以避免锁定资源时间过长，影响并发性能。
PostgreSQL基准测试与性能调优
pgbench的测试结果报告
transaction type: <builtin: TPC-B (sort of)> 行记录本次测试所使用的测试类型； scaling factor: 100 在初始化时设置的数据量的比例因子 query ห้องสมุดไป่ตู้ode: simple 是测试时指定的是simple查询协议、extended查询协议还是prepared查 询协议； number of clients: 1 是测试时指定的客户端数量; number of threads: 1 是测试时指定的每个客户端的线程数； number of transactions per client: 10 是测试时制定的每个客户端运行的事务数； number of transactions actually processed: 10/10 是测试结束时实际完成的事务数和计划完 成的事务数，计划完成的事务数只是客户端数量乘以每个客户端的事务数的值。如果测试成功结 束，实际完成的事务数应该和计划完成的事务数相等，如果有事务执行失败，则只会显示实际完 成的事务数。 latency average = 2.557 ms 平均响应时间 tps = 391.152261 (including connections establishing) tps = 399.368200 (excluding connections establishing) 最后两行tps的值分别是包含和不包 含建立连接开销的TPS值。
使用pgbench测试示例
使用内置脚本 [postgres@pghost ~]$ /usr/pgsql-10/bin/pgbench -b simple-
update -h pghost1 -p 1921 -U pguser mydb
PostgreSQL10分区表详解及性能测试报告
PostgreSQL10分区表详解及性能测试报告作者简介：中国⽐较早的postgresql使⽤者,2001年就开始使⽤postgresql,⾃2003年底⾄2014年⼀直担任PGSQL中国社区论坛PostgreSQL的论坛板块版主、管理员，参与Postgresql讨论和发表专题⽂章7000多贴.拥有15年的erp设计,开发和实施经验,开源mrp系统PostMRP就是我的作品,该应⽤软件是⼀套基于Postgresql专业的制造业管理软件系统.⽬前任职于--中国第⼀物流控股有限公司/运⼒宝（北京）科技有限公司,为公司的研发部经理⼀、测试环境操作系统：CentOS 6.4Postgresql版本号：10.0CPU：Intel(R) Xeon(R) CPU E5-2407 v2 @ 2.40GHz 4核⼼ 4线程内存：32G硬盘：2T SAS 7200⼆、编译安装PostgreSQL 10－－编译安装及初始化[root@ad source]# git clone git:///git/postgresql.git[root@ad source]# cd postgresql[root@ad source]# ./configure --prefix=/usr/local/pgsql10[root@ad postgresql]# gmake -j 4[root@ad postgresql]# gmake install[root@ad postgresql]# su postgres[postgres@ad postgresql]# /usr/local/pgsql10/bin/initdb --no-locale -E utf8 -D /home/postgres/data10/ -U postgres－－修改⼀些参数postgresql.conflisten_addresses = '*'port = 10000shared_buffers = 8096MBmaintenance_work_mem = 512MBeffective_cache_size = 30GBlog_destination = 'csvlog'logging_collector = onlog_directory = 'log'log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_file_mode = 0600log_checkpoints = offlog_connections = offlog_disconnections = offlog_duration = offlog_line_prefix = '%m %h %a %u %d %x [%p] 'log_statement = 'none'log_timezone = 'PRC'track_activity_query_size = 4096max_wal_size = 32GBmin_wal_size = 2GBcheckpoint_completion_target = 0.5pg_hba.conf增加许可条⽬host all all 192.168.1.0/24 trust－－启动服务[postgres@ad data10]$ /usr/local/pgsql10/bin/pg_ctl start -D /home/postgres/data10/－－连接数据库[postgres@ad data10]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgrespsql (10devel)Type "help"for help.postgres=#三、分区表介绍PostgreSQL的分区表跟先前版本⼀样，也要先建⽴主表，然后再建⽴⼦表，使⽤继承的特性，但不需要⼿⼯写规则了，这个⽐较赞阿。
PostgreSQL数据库性能调优指南
扫描索引 的代价
vacuum扫描索引并删除这些TID对应的所有索引项。如果它在扫描完整 个表之前耗尽了存放无效元组TID所用的内存，它将停止表扫描，转而扫 描索引，以丢弃堆积的TID列表，之后从它中断的位置继续扫描表。对于 一个大表，多次扫描索引的代价是非常昂贵的，特别是在表中有很多索 引的情况下。如果maintenance_work_mem设置太低，甚至可能需要两次 以上的索引扫描。
对数据实时性要求不高的场景，可以把该参数 配置成默认的on级别，比如报表统计
该参数配置等级越高，Master节点写延迟越 大，性能影响越大
PG参数synchronous_commit指定事务提交所要求的WAL记录同步等级，可以 取5个有效值：
① off：事务提交不需要等待WAL日志刷写入（flush）本地磁盘 ② local：事务等待WAL日志刷写入本地磁盘后才提交 ③ remote write：事务等待同步备节点接收到WAL日志并写入操作系统才能提
read
优化数据库配置参数
优化内存资源类参数
控制系统在构建索引时将使用的最大内存量。
为了构建一个B树索引，必须对输入的数据进行排 序，如果要排序的数据在maintenance_work_mem设 定的内存中放置不下，它将会溢出到磁盘中。
① maintenance_work_mem ②
autovac uum
内存耗尽
当使用缺省配置autovacuum_max_workers = 3，并且假设设置 maintenance_work_mem = 10GB，你将会经常消耗30GB的内存专门用 于自动空间清理，这还不包括你可能从前台发起的VACUUM或 CREATE INDEX操作所需的内存。这样，你会很容易把一个小系统的内存耗尽， 即便是一个大系统，也可能存在诸多性能问题。
PostgreSQL安装、命令和调优
PostgreSQL安装、命令和性能调优一、安装1.安装包文件yum install https:///pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm#客户端yum install postgresql11s#服务端yum install postgresql11-server2.初始化/usr/pgsql-11/bin/postgresql-11-setup initdbsystemctl enable postgresql-11systemctl start postgresql-113.设置Postgres用户权限passwd postgressu postgrespsqlALTER USER postgres WITH PASSWORD 'password';4.编辑访问配置编辑postgresql.conf，修改listen_addresseslisten_addresses = '*' # what IP address(es) to listen on;编辑pg_hba.conf# "local" is for Unix domain socket connections onlylocal all all md5 # IPv4 local connections:host all all 127.0.0.1/32 md5 host all all 192.168.1.15/20 md5 5.重启systemctl restart postgresql-11select * from pg_database;update pg_database set datcollate='zh_CN.UTF-8',datctype='zh_CN.UTF-8';6.重新装载pg_hba.conf配置首先/etc/profile加入export PGDATA=/var/lib/pgsql/11/data/usr/pgsql-11/bin/pg_ctl reload二、命令1.创建角色create role my_hub login password ‘password’2.创建DBcreate database my_db with owner= my_hub;3.连接DBpsql -U my_hub -d my_db4.\l查看数据库列表5.\d \d+ \dt \dt+ \ds \ds+查看所有表和序列6.\dnS命令也可以列出所有模式7.\c database切换数据库8.\c role切换用户9.revoke connect on database my_db from public;取消所有角色连接my_db数据库的权限10.grant connect on database my_db to my_test;赋予my_test角色连接my_db数据库的权限11.grant all privileges on database my_db to my_sheng;赋予my_sheng角色所有my_db数据库的权限12.GRANT ALL ON SCHEMA my_hub TO my_yan赋予my_yan角色操作health_hub模式的所有权限13.GRANT ALL ON SEQUENCE my_hub.base_hub_id_seq TO my_yan赋予my_yan角色操作my_hub.base_hub_id_seq序列的权限三、性能优化配置postgresql.conf以16G 4核为硬件参考superuser_reserved_connections = 15# 为超级用户保留多少个连接(change requires restart)max_connections=415# 规格内存(GB)*1000*(1/4)/10 + superuser_reserved_connectionsshared_buffers=4GB# IF use hugepage: 规格内存*(1/4) ELSE: min(32GB, 规格内存*(1/4))max_prepared_transactions=415# max_prepared_transactions=max_connections 分布式事务支持两阶段提交使用work_mem =4MB# max(min(规格内存/4096, 64MB), 4MB)maintenance_work_mem = 1GB# min( 8G, (主机内存*1/8)/max_parallel_maintenance_workers )autovacuum_work_mem = 400MB# min( 8G, (规格内存*1/8)/autovacuum_max_workers )max_worker_processes = 64max_parallel_maintenance_workers = 2# min( max(2, CPU核数/2) , 16 )max_parallel_workers_per_gather=2# min( max(2, CPU核数-4) , 24 )max_parallel_workers =8# CPU核数*2max_wal_size = 8GB# min(shared_buffers*2 , 用户存储空间/10)min_wal_size =2GB# min(shared_buffers/2 , 用户存储空间/10)max_sync_workers_per_subscription = 2# min ( 32 , max(2, CPU核数-4) )effective_cache_size = 9GB# 规格内存*0.75autovacuum_max_workers = 5# max(min( 8 , CPU核数/2 ) , 5)tcp_keepalives_idle = 60# 每60秒探测一次tcp_keepalives_interval = 10# 每个探测包等待响应为10妙tcp_keepalives_count = 5# 每次探测发送10个探测包checkpoint_timeout=15min#延长checkpoint_timeout时间，放置WAL增长，上量之后考虑放到30-60分钟max_wal_senders=10max_replication_slots=10# 建议大于等于max_wal_senders，max_replication_slots 公式：max_replication_slots=max_wal_senders wal_receiver_status_interval = 3s#多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间。
PostgreSQL中文手册
一、数值类型： ............................................................................................................................................................. 16 六、数组： ..................................................................................................................................................................... 22 PostgreSQL 学习手册(函数和操作符<一>) ..................................................................................................................
postgresql内存关键参数调优（work_mem）
postgresql内存关键参数调优（work_mem）work_mem 参数调优work_mem：在pgsql 8.0之前叫做sort_mem。
postgresql在执⾏排序操作时，会根据work_mem的⼤⼩决定是否将⼀个⼤的结果集拆分为⼏个⼩的和work_mem查不多⼤⼩的临时⽂件。
显然拆分的结果是降低了排序的速度。
因此增加work_mem有助于提⾼排序的速度。
通常设置为实际RAM的2% -4%，根据需要排序结果集的⼤⼩⽽定，⽐如81920(80M)。
备注：以上的官⽅的描述。
但在实际的业务中会有所不同，如纯粹的交易系统（oltp-交易多为⼏⾏内操作，但⽐较频繁）这样的系统⼏乎不涉及到排序操作，或者说涉及的排序操作数据也是相当的少（如⼗、百条数据排序），这样就没有必要去调整该参数。
如业务⽩天是oltp ，⽽晚间是olap（olap-分析系统）。
还有些系统只为数据分析⽽是⽤。
他们的使⽤还是有点区别。
如果排序处理的不合理，很有可能造成服务器利⽤率降低。
排序操作：在平时的sql语句中有好多sql都是有排序的操作，最典型的有group by ORDER BY，DISTINCT，有些连接操作，CREATE INDE X要⽤到排序操作测试案例：服务器：内存24G (该参数只和内存有关，如在同⼀太服务器上测试，其他指标不⽤关⼼)sql语句(该语句是业务真实语句)：insert into dw_analyse_file ( minserid ,rowcount,fname ,imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon )select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenameconfrom source_analyse_filegroup by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon说明：source_analyse_file据表⼤⼩6508 MB 、⾏数(已计数) 24031104执⾏第⼀组语句：TRUNCATE TABLE dw_analyse_file; --清空表记录set work_mem='8000MB'; --更改参数⼤⼩8Ginsert into dw_analyse_file ( minserid , rowcount, fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon )select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenameconfrom source_analyse_filegroup by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon执⾏结果：--查询成功: 共计8959657 ⾏受到影响，耗时: 202020 毫秒(ms)。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
pgbench的测试结果报告
transaction type: <builtin: TPC-B (sort of)> 行记录本次测试所使用的测试类型； scaling factor: 100 在初始化时设置的数据量的比例因子 query mode: simple 是测试时指定的是simple查询协议、extended查询协议还是prepared查 询协议； number of clients: 1 是测试时指定的客户端数量; number of threads: 1 是测试时指定的每个客户端的线程数； number of transactions per client: 10 是测试时制定的每个客户端运行的事务数； number of transactions actually processed: 10/10 是测试结束时实际完成的事务数和计划完 成的事务数，计划完成的事务数只是客户端数量乘以每个客户端的事务数的值。如果测试成功结 束，实际完成的事务数应该和计划完成的事务数相等，如果有事务执行失败，则只会显示实际完 成的事务数。 latency average = 2.557 ms 平均响应时间 tps = 391.152261 (including connections establishing) tps = 399.368200 (excluding connections establishing) 最后两行tps的值分别是包含和不包 含建立连接开销的TPS值。
- 硬件：CPU、IO、内存和网络 - 操作系统配置 - 中间件配置 - 数据库参数配置 - 运行在数据库之上的查询和命令 - ...
性能优化的通用路径
1、了解性能指标； 2、通过性能检测和系统监控工具、判断性能瓶颈； 3、操作系统配置调整 4、数据库全局参数调整 5、细节性能优化
常用Linux性能工具
- 硬件，如服务器配置、CPU，内存，存储，通常硬件越高级，系统 的性能越好； - 网络，带宽不足也会严重限制系统整体性能表现； - 负载，不同的用户数，不同的数据量对系统的性能影响也非常大； - 软件，不同的数据库在不同的操作系统、不同的应试对容量规划的关系
PostgreSQL基准测试与性能调优
张文升
为什么需要基准测试
- 可量化、可复现的、能对比的方法衡量系统的吞吐量； - 对新硬件的实际性能和可靠性进行测试； - 复现错误或异常；
衡量指标
- 吞吐量（Throughput） - 响应时间（RT）或延迟（Latency） - 并发量
基准测试的关键因素
使用pgbench测试示例
使用内置脚本 [postgres@pghost ~]$ /usr/pgsql-10/bin/pgbench -b simple-
update -h pghost1 -p 1921 -U pguser mydb
使用自定义脚本 [postgres@pghost2 ~]$ /usr/pgsql-10/bin/pgbench -b simple-
Linux操作系统提供了非常多的性能监控工具，可以全方位的监控 CPU、内存、虚拟内存、磁盘I/O、网络等各项指标，为问题排查提 供了便利，无论是研发人员还是数据库管理员都应该熟练掌握这些工 具和命令的用法。因为Linux相关命令和命令的变种很多，本章节简 单介绍一些常用的性能检测工具，例如top，free，vmstat，iostat， mpstat，sar，pidstat等。除了top和free外，其他工具均位于 sysstat包中。
数据库全局参数调整
- shared_buffers - work_mem - random_page_cost - vacuum_cost_limit/delay
关注统计信息和查询计划
在运行期间，PostgreSQL会收集大量的数据库、表、索引的统计信 息，查询优化器通过这些统计信息估计查询运行的时间,然后选择最快 的查询路径。这些统计信息都保存在PostgreSQL的系统表中，这些 系统表都以pg_stat或pg_statio开头。
- 基准测试可用于测试不同的硬件和应用场景下的数据库系统配置是 否合理。例如更换新型的磁盘对当前系统磁盘I/O能力不足的问题是 否有所帮助？升级了操作系统内核版本是否有性能升？不同的数据库 版本和不同的数据库参数配置的表现是怎样的？通过模拟比当前系统 更高的负载，可以预估随着压力增加可能带来的瓶颈，也可以对未来 的业务增长规划有所帮助。 - 问题：能否简单通过基准测试结果进行容量规划？数据库的状态一 直在改变，随着时间的推移或业务的增长，数据量、请求量、并发量 以及数据之间的关系都在发生着变化，还有可能有很多功能特性的变 化，有一些新功能的影响可能远远大于目前功能的压力总量，对容量 规划只能做大概的评估
- pg_stat_database - pg_stat_user_tables - pg_stat_user_indexes - pg_stat_statements
查看执行计划
mydb=# BEGIN; BEGIN mydb=# EXPLAIN ANALYZE UPDATE tbl SET ival = ival * 10 WHERE id = 1; QUERY PLAN --------------------------------------------Update on tbl (cost=0.15..8.17 rows=1 width=54) (actual time=0.159..0.159 rows=0 loops=1)
update@2 -b select-only@8 -b tpcb@0 -h pghost1 -p 1921 -U pguser mydb
pgbench的其它测试选项
- 模拟的客户端数量和连接方式 - 单次测试的运行时间 - 用固定速率运行测试脚本 - 超出阈值的事务的报告
影响数据库性能的主要因素