Evaluating Checkpointing in PostgreSQL

栏目: IT技术 · 发布时间: 4年前

内容简介:To evaluate PostgreSQL I will use a not identical but similar scenario: using sysbench-tpcc with 1000 Warehouses, and as with sysbench you can produce tpcc-like workload for PostgreSQL:

Evaluating Checkpointing in PostgreSQL Continuing with the checkpointing topic I started a month ago with my blog post MongoDB Checkpointing Woes , this time I want to review how PostgreSQL performs in this area. After this, I will be taking a look at MySQL and MariaDB. If anything, it will be fair not only to complain about MongoDB but to review how other databases handle it, as well.

Benchmark

To evaluate PostgreSQL I will use a not identical but similar scenario: using sysbench-tpcc with 1000 Warehouses, and as with sysbench you can produce tpcc-like workload for PostgreSQL:

Sysbench-tpcc Supports PostgreSQL (No, Really This Time)

Tuning PostgreSQL for sysbench-tpcc

The hardware I use is:

System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other)
   Platform | Linux
    Release | Ubuntu 18.04.4 LTS (bionic)
     Kernel | 5.3.0-42-generic
Architecture | CPU = 64-bit, OS = 64-bit
  Threading | NPTL 2.27
    SELinux | No SELinux detected
Virtualized | No virtualization detected
# Processor ##################################################
 Processors | physical = 2, cores = 40, virtual = 80, hyperthreading = yes
     Models | 80xIntel(R) Xeon(R) Gold 6230 CPU @ 2.10GHz
     Caches | 80x28160 KB
# Memory #####################################################
      Total | 187.6G

With the storage on SATA SSD INTEL SSDSC2KB960G8 (Intel Enterprise-grade SSD D3-S4510).

The PostgreSQL config is:

shared_buffers = '140GB'
work_mem = '4MB'
random_page_cost = '1'
maintenance_work_mem = '2GB'
 
wal_level = 'replica'
max_wal_senders = '3'
 
synchronous_commit = 'on'
seq_page_cost = '1'
synchronous_commit = 'on'
 
checkpoint_completion_target = '0.9'
checkpoint_timeout = '900'
 
max_wal_size = '20GB'
min_wal_size = '12GB'
 
autovacuum_vacuum_scale_factor = '0.4'
effective_cache_size = '200GB'
bgwriter_lru_maxpages = '1000'
bgwriter_lru_multiplier = '10.0'
logging_collector = 'ON'
wal_compression = 'ON'
log_checkpoints = 'ON'
archive_mode = 'OFF'
full_page_writes = 'ON'
fsync = 'ON'

The short settings overview:

  • Data will totally fit into memory (The datasize is ~100GB, memory on the server is 188GB, and we allocate 140GB for PostgreSQL shared buffers.)
  • The workload on storage will be mostly write-intensive (reads will be done from memory), with full ACID-compliant and data safe settings on PostgreSQL.
  • I will vary log size from 1GB to 100GB, to see the effect of log sizes on checkpointing.

The benchmark command line is:

./tpcc.lua --pgsql-user=sbtest --pgsql-password=sbtest --pgsql-db=sbtest --time=3600 --threads=56 --report-interval=1 --tables=10 --scale=100 --use_fk=0 --trx_level=RC --db-driver=pgsql --report_csv=yes run 

This means that the benchmark will run for 1 hour, with reporting throughput every 1 sec.

Results

Let’s see what results I’ve got with this setup:

Evaluating Checkpointing in PostgreSQL

That’s an interesting pattern!

Although there are no drops to the floor, we see a saw-like pattern, where throughput raises to ~8000 tps and then drops to ~3000tps (that’s 2.6 times drop!).

It was suggested to check how PostgreSQL would perform with full_page_writes = 'OFF' (this is not a data-safe setting and I would not recommend to use it in production!)

Results with full_page_writes = ‘OFF’

Evaluating Checkpointing in PostgreSQL

This seems to improve the saw-like pattern, but there are micro-drops that are concerning.

If we zoom in only to 50GB WAL size, we can see it in detail:

Evaluating Checkpointing in PostgreSQL

I would be interested to hear ideas on how PostgreSQL results in 1-sec resolution can be improved! If you are interested in the raw results and notebooks, it is available here in GitHub .


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

无懈可击的Web设计

无懈可击的Web设计

【美】Dan Cederholm / 马跃 / 清华大学出版社 / 2012-5 / 39.00元

本书将指导您采用标准设计策略来满足以各种方式浏览网页的各类用户的需要。每章首先列举一个沿用传统HTML技术的实例,然后指出该实例的局限性,并利用XHTML和CSS对其进行重构。从中您将学会如何用简洁高效的HTML标记和CSS来取代臃肿的代码,从而创建加载速度极快、能供所有用户使用的网站。本书最后将前面各章讨论的所有页面组件珠联璧合地结合在一起,制作了一个页面模板。这一版全面润色和更新了上一版本,介......一起来看看 《无懈可击的Web设计》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

在线进制转换器
在线进制转换器

各进制数互转换器

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具