酷酷娱乐网

站内广告

PostgreSQL DBA(61) - zedstore:想说爱你不容易

时间:2020-09-16 14:30:03   作者:酷酷娱乐网   来源:www.kukuyl.com  
内容摘要:


本节继续介绍zedstore,在CentOS 7.......

本节继续介绍zedstore,在CentOS 7.4进行”大批量”(不过1000w行)数据的测试,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 TABLE
testdb=# 
testdb=# insert into t_ap_heap 
testdb-#   select x,x,x,x,x,x,x,x,x
testdb-#   from generate_series(1,10000000) as x;
INSERT 0 10000000
testdb=# 
testdb=# drop table if exists t_ap_zedstore;
DROP TABLE
testdb=# 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 TABLE
testdb=# 
testdb=# insert into t_ap_zedstore 
testdb-#   select x,x,x,x,x,x,x,x,x
testdb-#   from generate_series(1,10000000) as x;
INSERT 0 10000000
testdb=# 
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 r
ows=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 process
DETAIL:  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 ro
ws=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 r
ows=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,x
testdb-#   from generate_series(1,10000000) as x;
psql: ERROR:  too many attributes for zedstore
testdb=#

数据竟然都丢了?┌(。Д。)┐

参考资料
PostgreSQL 基于access method api的列存zedstore

本文章url:https://www.kukuyl.com/new/30000608.html

相关文章

本栏最新更新

本栏推荐

阅读排行

CopyRight 2018 - 2020 http://www.kukuyl.com 酷酷娱乐网 All Rights Reserved .