大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

包含postgresql优化的词条

SQL语句优化,使用postgresql数据库,查询下面sql,需要20多分钟:

postgresql(8.2)的配置文件中有一个参数log_min_duration_statement,意思是只log执行时间大于设定值的语句,如果设为0,表示log所有语句;如果设为-1,表示不log任何语句。

创新互联公司是一家专注于成都网站制作、网站建设与策划设计,桦甸网站建设哪家好?创新互联公司做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:桦甸等地区。桦甸做网站价格咨询:18980820575

看起来,这个配置选项对性能的调整是很有用的,比如可以设置:

log_min_duration_statement = 1000

则只log执行时间大于1s的语句,重点优化这些sql语句就好了。

然而,奇怪的,这个选项不太容易生效!经过反复试验,原来需要如下配置:

#debug_print_parse = off

#debug_print_rewritten = off

#debug_print_plan = off

#debug_pretty_print = off

log_connections = off

#log_disconnections = off

log_duration = off

log_line_prefix = '%t [%p]: [%l-1] ' # Special values:

# %u = user name

# %d = database name

# %r = remote host and port

# %h = remote host

# %p = PID

# %t = timestamp (no milliseconds)

# %m = timestamp with milliseconds

# %i = command tag

# %c = session id

# %l = session line number

# %s = session start timestamp

# %x = transaction id

# %q = stop here in non-session

# processes

# %% = '%'

# e.g. '%u%%%d '

log_statement = 'none' # none, mod, ddl, all

#log_statement = 'all' # none, mod, ddl, all

#log_hostname = off

注意看上面的其中两个选项的设置:

log_duration = off

log_statement = 'none'

这两个选项的意思是不log任何sql语句和执行时间,但是恰恰是关闭了这两个,log_min_duration_statement才会生效!可能postgresql内部 对这两个选项做了“互斥”处理吧。

PostgreSQL 源码解读(28)- 查询语句#13(查询优化-上拉子链接#3)

本节简单介绍了PG查询逻辑优化中的子查询链接(subLink),以EXISTS子链接为例介绍了子查询链接上拉主函数处理逻辑以及使用gdb跟踪分析。

上一节介绍了ANY子链接,本节介绍了EXISTS子链接.

为便于方便解析,根据日志分析,得出查询树如下图所示:

convert_EXISTS_sublink_to_join函数源码:

相关数据结构

1、Var

XX_one_pos

依赖的函数

simplify_EXISTS_query

OffsetVarNodes

IncrementVarSublevelsUp

IncrementVarSublevelsUp

pull_varnos

contain_vars_of_level

query_tree_walker

OffsetVarNodes_walker

query_or_expression_tree_walker

pull_varnos_walker

contain_vars_of_level_walker

expression_tree_walker

技术文档 - PostgreSQL 性能优化之 fsync 参数

目 录

总 结

PostgreSQL 通过调用系统 fsync() 或者其他使得事务内容写入到物理磁盘,这样可以保证操作系统或者数据库出现宕机后,仍然可以恢复到某一个一致性的状态。理论上讲 PostgreSQL 的 fsync 功能关闭,可以实现性能的提升,但是带来的影响就是需要承担数据的丢失,因为出现系统宕机或者数据库崩溃的时候有一些数据是没有落盘的。

本文将验证 fsync 参数的性能影响,以及参数关闭时数据库宕机后的影响。

数据量:1000W

fsync 参数:on

初始化表:user_info

pgbench 压测

pgbench 结果

pgbench 压测

pgbench 结果

数据量:1000W

fsync 参数:off

初始化表:user_info

pgbench 压测

pgbench 结果

pgbench 压测

pgbench 结果

通过对比发现,将 fsync 改为 off,对于读 TPS,参数 fsync 的影响不大,对于写 TPS,性能有一定提升。

现在验证参数关闭时数据库宕机后的影响

首先,使用将数据库性能跑起来

然后,模拟服务器断电

之后,启动数据库

提示信息:比致命错误还过分的错误。

结果:数据库无法启动,原因就是因为无法找到一个有效的 checkpoint 记录,这就是因为 fsync 设置为 off,由于数据库异常宕机导致。可以通过使用 pg_resetxlog 恢复数据库,但是会造成部分数据无法找回,数据丢失;也可以通过备份恢复,同样也会丢失部分数据。

fsync 参数对于读 TPS 的性能影响不大,对于写 TPS 的性能有一些影响,设置为 off,写 TPS 性能有一定提升,但是存在数据库宕机后无法正常启动,即使恢复后启动数据库,也会有数据丢失的很大风险。因此生产环境非必要时,不要将此参数设置为 off,还是使用默认的 on 比较稳妥。

Postgresql 建索引性能优化

客户新上线了一套监控系统,可以监控到所有的执行慢的SQL,监控到有个批量任务导入大量数据后,进行索引创建。耗时需要几分钟,虽然不影响业务,但是需要整改。

这种问题的处理思路,都是大拆小,搞并发。

在测试环境,创建一个大表进行测试,创建大量的假数据。

创建表

随机字符串生成函数

生成大量的数据

经测试发现这种方法创建数据太慢了,改成使用COPY的方式创建数据。

排查发现random_string效率太低,生成一条数据接近1ms

重新创建表

写程序创建8600万条数据放在test.csv中

导入大量数据

测试基准数据

\timing 开启计时

耗时532824.434 ms

耗时385838.893 ms,提升 38%的性能,非常不错,但是远远不够。

仍然会出发告警。

重新创建表

创建分区

并发创建INDEX,并记录每个分区索引创建的开始时间和结束时间;

耗时 = 最大结束时间 - 最小开始时间 = 137 s,速度提升接近4倍。

顺序创建INDEX,并记录每个分区索引创建的开始时间和结束时间;

耗时 = 每个索引的耗时相加 = 457358.14 ms,速度提升 16.5%

顺序创建INDEX,优化并发

耗时 = 每个索引的耗时相加 = 292027.642 ms, 速度提升接近两倍。

在开启了并发参数的情况下,如果再叠加并发分区INDEX创建,会不会有惊喜呢?

并发创建INDEX,并记录每个分区索引创建的开始时间和结束时间;

耗时 = 最大结束时间 - 最小开始时间 = 141 s,速度还不如默认并发参数下的表现。应该是资源发生争抢导致的,通过系统监控发现CPU已经打满了。

分区并发是目前能想到的最优化手段了。

还需要结合查询的情况进行分析,分区会带来一点点的性能下降是否影响也需要考虑一下。

分区时目前能避开监控报警的唯一手段了,另外还钻了监控报警的空子。

客户的监控是基于单条语句的,单个分区的最大创建时间为47s,控制在分钟以内了。


分享名称:包含postgresql优化的词条
当前URL:http://dzwzjz.com/article/dscppdd.html
在线咨询
服务热线
服务热线:028-86922220
TOP