PostgreSQL zedstore在CentOS 7.4进行大批量数据的测试分析

这篇文章主要介绍"PostgreSQL zedstore在CentOS 7.4进行大批量数据的测试分析"，在日常操作中，相信很多人在PostgreSQL zedstore在CentOS 7.4进行大批量数据的测试分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"PostgreSQL zedstore在CentOS 7.4进行大批量数据的测试分析"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

zedstore在执行avg时直接coredump.

testdb=# create table t_ap_heap (id1 int,id2 int,id3 int,id4 int,id5 int ,id6 int,id7 int,id8 int,id9 int);CREATE TABLEtestdb=# testdb=# insert into t_ap_heap testdb-#   select x,x,x,x,x,x,x,x,xtestdb-#   from generate_series(1,10000000) as x;INSERT 0 10000000testdb=# testdb=# drop table if exists t_ap_zedstore;DROP TABLEtestdb=# create table t_ap_zedstore testdb-#   (id1 int,id2 int,id3 int,id4 int,id5 int ,id6 int,id7 int,id8 int,id9 int) using zedstore;CREATE TABLEtestdb=# testdb=# insert into t_ap_zedstore testdb-#   select x,x,x,x,x,x,x,x,xtestdb-#   from generate_series(1,10000000) as x;INSERT 0 10000000testdb=# testdb=# select pg_size_pretty(pg_table_size('t_ap_heap')); pg_size_pretty ---------------- 651 MB(1 row)testdb=# select pg_size_pretty(pg_table_size('t_ap_zedstore')); pg_size_pretty ---------------- 501 MB(1 row)testdb=# explain analyze select avg(id1) from t_ap_heap;                                                                    QUERY PLAN                                               -------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate  (cost=136417.97..136417.98 rows=1 width=32) (actual time=2432.238..2432.240 rows=1 loops=1)   ->  Gather  (cost=136417.75..136417.96 rows=2 width=32) (actual time=2432.015..2433.781 rows=3 loops=1)         Workers Planned: 2         Workers Launched: 2         ->  Partial Aggregate  (cost=135417.75..135417.76 rows=1 width=32) (actual time=2363.185..2363.185 rows=1 loops=3)               ->  Parallel Seq Scan on t_ap_heap  (cost=0.00..125001.00 rows=4166700 width=4) (actual time=0.348..1843.592 rows=3333333 loops=3) Planning Time: 28.360 ms Execution Time: 2434.173 ms(8 rows)testdb=# explain analyze select avg(id1) from t_ap_zedstore;psql: WARNING:  terminating connection because of crash of another server processDETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.HINT:  In a moment you should be able to reconnect to the database and repeat your command.psql: server closed the connection unexpectedly  This probably means the server terminated abnormally  before or while processing the request.The connection to the server was lost. Attempting reset: Failed.!>

重新连接,执行查询:

testdb=# explain analyze select avg(id1) from t_ap_heap;                                                                   QUERY PLAN                                                ------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate  (cost=136417.97..136417.98 rows=1 width=32) (actual time=1357.266..1357.267 rows=1 loops=1)   ->  Gather  (cost=136417.75..136417.96 rows=2 width=32) (actual time=1357.068..1362.153 rows=3 loops=1)         Workers Planned: 2         Workers Launched: 2         ->  Partial Aggregate  (cost=135417.75..135417.76 rows=1 width=32) (actual time=1346.515..1346.515 rows=1 loops=3)               ->  Parallel Seq Scan on t_ap_heap  (cost=0.00..125001.00 rows=4166700 width=4) (actual time=0.488..830.427 rows=3333333 loops=3) Planning Time: 0.550 ms Execution Time: 1362.347 ms(8 rows)testdb=# explain analyze select avg(id1) from t_ap_zedstore;                                                                 QUERY PLAN                                                  -------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate  (cost=107843.55..107843.56 rows=1 width=32) (actual time=9.579..9.580 rows=1 loops=1)   ->  Gather  (cost=107843.33..107843.54 rows=2 width=32) (actual time=0.467..11.620 rows=3 loops=1)         Workers Planned: 2         Workers Launched: 2         ->  Partial Aggregate  (cost=106843.33..106843.34 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=3)               ->  Parallel Seq Scan on t_ap_zedstore  (cost=0.00..98295.87 rows=3418987 width=4) (actual time=0.007..0.008 rows=0 loops=3) Planning Time: 0.268 ms Execution Time: 11.835 ms(8 rows)testdb=#

heap vs zedstore : 1362ms vs 12ms,性能确实有大幅提升.

不过,等等

testdb=# select avg(id1) from t_ap_heap;         avg          ---------------------- 5000000.500000000000(1 row)testdb=# select avg(id1) from t_ap_zedstore; avg -----(1 row)testdb=# select count(*) from t_ap_zedstore; count -------     0(1 row)testdb=# insert into t_ap_zedstore testdb-#   select x,x,x,x,x,x,x,x,xtestdb-#   from generate_series(1,10000000) as x;psql: ERROR:  too many attributes for zedstoretestdb=#

到此，关于"PostgreSQL zedstore在CentOS 7.4进行大批量数据的测试分析"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！