PostgreSQL 中 UniProt 的生物序列

Question

pietrop

Asked: 2015-03-28 04:01:35 +0800 CST2015-03-28 04:01:35 +0800 CST 2015-03-28 04:01:35 +0800 CST

无法使 Dell PE T420 (Perc H710) 的性能优于带有 PostgreSQL 的 MacMini

772

几个月来我一直在尝试解决 PostgreSQL 的性能问题。

系统配置

我们的部署机器是 Dell PowerEdge T420，配备 Perc H710 RAID 控制器，配置如下：

VD0：两块15k SAS磁盘（ext4，OS分区，WAL分区，RAID1）
VD1：10个10k SAS磁盘（XFS，Postgres数据分区，RAID5）

该系统具有以下配置：

Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-48-generic x86_64)
128GB 内存（DDR3、8x16GB @1600Mhz）
两个 Intel Xeon E5-2640 v2 @2Ghz
具有 512MB RAM 的 Dell Perc H710（写入缓存：“WriteBack”，读取缓存：“ReadAhead”，磁盘缓存：“已禁用”）：
- VD0（OS 和 WAL 分区）：两个 15k SAS 磁盘（ext4，RAID1）
- VD1（Postgres 数据分区）：十个 10k SAS 磁盘（XFS，RAID5）
PostgreSQL 9.4（已更新至最新可用版本）
将 pg_stat_tmp 移动到 RAM 磁盘

我个人的低成本低配置开发机器是这样配置的 MacMini：

OS X 服务器 10.7.5
8GB 内存（DDR3、2x4GB @1333Mhz）
一个英特尔 i7 @2.2Ghz
两个用于操作系统分区的内部 500GB 7.2k SAS HDD（非 RAID）
外部 Promise Pegasus R1 与 Thunderbolt v1 连接（512MB RAM，四个 1TB 7.2k SAS HDD 32MB 缓存，RAID5，写入缓存：“WriteBack”，读取缓存：“ReadAhead”，磁盘缓存：“启用”，NCQ：“启用”）
PostgreSQL 9.0.13（OS X Server 附带的原始内置）
将 pg_stat_tmp 移动到 RAM 磁盘

到目前为止，我已经对两台机器进行了大量调优调整，包括官方 Postgres 文档站点上的内核推荐调整。

应用

部署机器运行一个 web 平台，该平台指示 Postgres 进行超过十亿条记录的大交易。这是一个为一个用户设计的平台，因为由于数据大小，系统资源必须尽可能多地专用于一个单一的工作（我不喜欢称它为大数据，因为大数据是十亿的数量级）。

问题

我发现部署机器比开发机器慢很多。这是矛盾的，因为两台机器在很多方面确实不同。我运行了很多查询来调查这种奇怪的行为，并做了很多调优调整。

在过去的两个月里，我准备并执行了两种类型的查询集：

答：这些套装使用SELECT ... INTO、CREATE INDEX和。CLUSTERVACUUM ANALYZE
B：这些集合来自我们的应用程序生成的事务，并利用SELECT集合 A 创建的表。

A 和 B 在 T420 上总是比较慢。唯一更快的操作类型是VACUUM ANALYZE.

结果

A型套装：

T420：从 311 秒（默认postgresql.conf）到 195 秒，对 RAID、内核和进行调优调整postgresql.conf；
MacMini：40 秒。

B型套装：

T420：141秒；
MacMini：101 秒。

我不得不提一下，我们还调整了 T420 上的 BIOS，将所有可能的参数设置为“性能”并禁用低能耗配置文件。这将 A 类集的执行时间从 240 秒减少到 211 秒。

我们还将所有固件和 BIOS 升级到最新可用版本。

以下是使用生成的两个基准pg_test_fsync：

T420pg_test_fsync

60 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                   23358.758 ops/sec      43 usecs/op
        fdatasync                       21417.018 ops/sec      47 usecs/op
        fsync                           21112.662 ops/sec      47 usecs/op
        fsync_writethrough                            n/a
        open_sync                       23082.764 ops/sec      43 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                   11737.746 ops/sec      85 usecs/op
        fdatasync                       19222.074 ops/sec      52 usecs/op
        fsync                           18608.405 ops/sec      54 usecs/op
        fsync_writethrough                            n/a
        open_sync                       11510.074 ops/sec      87 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
         1 * 16kB open_sync write       21484.546 ops/sec      47 usecs/op
         2 *  8kB open_sync writes      11478.119 ops/sec      87 usecs/op
         4 *  4kB open_sync writes       5885.149 ops/sec     170 usecs/op
         8 *  2kB open_sync writes       3027.676 ops/sec     330 usecs/op
        16 *  1kB open_sync writes       1512.922 ops/sec     661 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        write, fsync, close             17946.690 ops/sec      56 usecs/op
        write, close, fsync             17976.202 ops/sec      56 usecs/op

Non-Sync'ed 8kB writes:
        write                           343202.937 ops/sec       3 usecs/op

苹果机pg_test_fsync

60 seconds per test
Direct I/O is not supported on this platform.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                      3780.341 ops/sec     265 usecs/op
        fdatasync                          3117.094 ops/sec     321 usecs/op
        fsync                              3156.298 ops/sec     317 usecs/op
        fsync_writethrough                  110.300 ops/sec    9066 usecs/op
        open_sync                          3077.932 ops/sec     325 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                      1522.400 ops/sec     657 usecs/op
        fdatasync                          2700.055 ops/sec     370 usecs/op
        fsync                              2670.652 ops/sec     374 usecs/op
        fsync_writethrough                   98.462 ops/sec   10156 usecs/op
        open_sync                          1532.235 ops/sec     653 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
         1 * 16kB open_sync write          2634.754 ops/sec     380 usecs/op
         2 *  8kB open_sync writes         1547.801 ops/sec     646 usecs/op
         4 *  4kB open_sync writes          801.542 ops/sec    1248 usecs/op
         8 *  2kB open_sync writes          405.515 ops/sec    2466 usecs/op
        16 *  1kB open_sync writes          204.095 ops/sec    4900 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        write, fsync, close                2747.345 ops/sec     364 usecs/op
        write, close, fsync                3070.877 ops/sec     326 usecs/op

Non-Sync'ed 8kB writes:
        write                              3275.716 ops/sec     305 usecs/op

这证实了 T420 的硬件 IO 功能，但不能解释为什么 MacMini 更快。

有任何想法吗？

更新 1

B 型集是交易，所以我不可能发布EXPLAIN ANALYZE结果。我从单个事务中提取了两个查询，并在两个系统上执行了两个查询。以下是结果：

T420

查询 B_1 [55999.649 毫秒 + 0.639 毫秒] http://explain.depesz.com/s/LbM

查询 B_2 [95664.832 毫秒 + 0.523 毫秒] http://explain.depesz.com/s/v06

苹果机

查询 B_1 [56315.614 毫秒] http://explain.depesz.com/s/uZTx

查询 B_2 [44890.813 毫秒] http://explain.depesz.com/s/y7Dk

更新 2

我使用 gcc-4.9.1 和 Postgres 的不同参数组合编译了原始的 Postgres 9.4.1 源代码。我关注了这篇文章，但-flto由于make. 经过两天的测试，我在 T420 上从 195 秒下降到 189 秒，而 MacMini 是 40 秒（一套）；从 141 到 129 秒，而 MacMini 是 101 秒（B 组）。我使用了以下编译选项：

./configure CFLAGS="-O3 -fno-inline-functions -march=native" --with-openssl --with-libxml --with-libxslt --with-wal-blocksize=64 --with-blocksize=32 --with-wal-segsize=64 --with-segsize=1

我还尝试使用第 N 个逻辑 CPU禁用超线程，echo 0 > /sys/devices/system/cpu/cpuN/online但B 集查询没有任何改变。cpuN我们有 2 个 8 核 CPU，总共 16 个物理核和 16 个逻辑核。

更新 3

这是之前的查询计划加上使用与 MacMini 相同配置的 T420 计划（但不同的 Postgres 版本）；这样计划是相同的，除了性能。

T420 与 MacMini postgresql.conf

查询 B_1 [51280.208ms + 0.699ms] http://explain.depesz.com/s/wlb

查询 B_2 [177278.205ms + 0.428ms] http://explain.depesz.com/s/rzr

具有最佳 postgresql.conf 的 T420

查询 B_1 [55999.649 毫秒 + 0.639 毫秒] http://explain.depesz.com/s/LbM

查询 B_2 [95664.832 毫秒 + 0.523 毫秒] http://explain.depesz.com/s/v06

苹果机

查询 B_1 [56315.614 毫秒] http://explain.depesz.com/s/uZTx

查询 B_2 [44890.813 毫秒] http://explain.depesz.com/s/y7Dk

以下是用于所有测试的 T420 和 MacMini 的 postgresql.conf。

T420postgresql.conf

正常操作

autovacuum = on
maintenance_work_mem = 512MB
work_mem = 512MB
wal_buffers = 64MB
effective_cache_size = 64GB # this helps A LOT in disk write speed when creating indexes
shared_buffers = 32GB
checkpoint_segments = 2000
checkpoint_completion_target = 1.0
effective_io_concurrency = 0 # 1 doesn’t make any substantial difference
max_connections = 10 # 20 doesn’t make any difference

数据加载（与上面相同，但有以下更改）：

autovacuum = off
maintenance_work_mem = 64GB

苹果机postgresql.conf

正常操作

autovacuum = on
maintenance_work_mem = 128MB
work_mem = 32MB
wal_buffers = 32MB
effective_cache_size = 800MB
shared_buffers = 512MB
checkpoint_segments = 32
checkpoint_completion_target = 1.0
effective_io_concurrency = 1
max_connections = 20

数据加载（与上面相同，但有以下更改）：

autovacuum = off
maintenance_work_mem = 6GB

1 个回答

Voted

pietrop · Answer 1 · 2015-04-08T13:46:25+08:00

在查询 pgsql-performance 列表后，Jeff Janes发现原因与 Postgres 使用的默认排序规则有关（有关更多信息，请参见此链接）。MacMini 使用的是性能良好的排序规则，而 Dell T420 使用的是 en/US 排序规则。

T420（Postgres 9.4.1）

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 pen       | pen      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

MacMini (Postgres 9.0.13)

                                    List of databases
       Name        |   Owner    | Encoding | Collation | Ctype |    Access privileges    
-------------------+------------+----------+-----------+-------+-------------------------
 caldav            | caldav     | UTF8     | C         | C     | 
 collab            | collab     | UTF8     | C         | C     | 
 device_management | _devicemgr | UTF8     | C         | C     | 
 pen               | pen        | UTF8     | C         | C     | 
 postgres          | _postgres  | UTF8     | C         | C     | 
 roundcubemail     | roundcube  | UTF8     | C         | C     | 
 template0         | _postgres  | UTF8     | C         | C     | =c/_postgres           +
                   |            |          |           |       | _postgres=CTc/_postgres
 template1         | _postgres  | UTF8     | C         | C     | =c/_postgres           +
                   |            |          |           |       | _postgres=CTc/_postgres
(8 rows)

在 T420“C”上设置整理后，A 事务从 195 秒减少到 33 秒，而在 Mac Mini 上为 40 秒；B 类事务从 141 秒减少到 78 秒，而 Mac Mini 为 101 秒。这是修改 BIOS 设置后最好的性能改进。许多内核调整并没有提供显着的改进。

因此，运行以下命令将使用排序规则 C 和编码 UTF8 初始化一个新数据库：

/usr/local/pgsql/bin/initdb -D /path/to/your/data --no-locale --encoding=UTF8

我希望这篇文章将来能帮助其他人。在阅读了很多关于内核、虚拟内存、RAID 控制器、磁盘缓存、WAL 和其他技术内容的题外话后，我从未发现有人在谈论排序规则。

无法使 Dell PE T420 (Perc H710) 的性能优于带有 PostgreSQL 的 MacMini

系统配置

应用

问题

结果

更新 1

更新 2

更新 3

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

无法使 Dell PE T420 (Perc H710) 的性能优于带有 PostgreSQL 的 MacMini

系统配置

应用

问题

结果

更新 1

更新 2

更新 3

1 个回答

相关问题