Shulou(Shulou.com)05/31 Report--

This article mainly introduces "PostgreSQL zedstore carries on the test analysis of mass data in CentOS 7.4". In the daily operation, I believe that many people have doubts on the test and analysis of mass data in PostgreSQL zedstore in CentOS 7.4. the editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "PostgreSQL zedstore testing and analysis of mass data in CentOS 7.4". Next, please follow the editor to study!

Zedstore directly coredump when executing avg.

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 xrexrexrexrexrexrexrexrexrexrexrexrexrexrextemxtestdblichue # from generate_series (1mement10000000) as xtterterinsert 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 xrexrexrexrexrexrexrexrexrexrexrexrexrewkyxtestdblichue # from generate_series (1meme 10000000) as xtincter insert 0 10000000testdb=# testdb=# select pg_size_pretty (pg_table_size ('tweeapapheap')) Pg_size_pretty-651 MB (1 row) testdb=# select pg_size_pretty (pg_table_size ('tweeapped storey')); 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. >

Reconnect and execute the query:

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, the performance has indeed been greatly improved.

But wait.

Testdb=# select avg (id1) from tasking apprenticesheap; avg-5000000.5000000000 (1 row) testdb=# select avg (id1) from tasking apprenticeships; avg-(1 row) testdb=# select count (*) from t_ap_zedstore At this point, count-0 (1 row) testdb=# insert into t_ap_zedstore testdb-# select xrexrexrexrexrexreforxrexrexrexrexrexrextestdb # from generate_series (1meme10000000) as xdexpsql: ERROR: too many attributes for zedstoretestdb=#, the study on "PostgreSQL zedstore testing and analyzing large quantities of data in CentOS 7.4" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.