HTAP database PostgreSQL scenario and performance test 39 - (OLTP+OLAP) contains index multi-form point writing

Keywords: github PostgreSQL Database SQL

Label

PostgreSQL, HTAP, OLTP, OLAP, Scenario and Performance Testing

background

PostgreSQL is a database with a long history, which can be traced back to 1973. It was first awarded by the 2014 Computer Turing Prize winner and the originator of relational database. Michael_Stonebraker PostgreSQL has similar functions, performance, architecture and stability as Oracle.

The PostgreSQL community has many contributors, from all walks of life around the world. Over the years, PostgreSQL releases a large version every year, known for its enduring vitality and stability.

In October 2017, PostgreSQL released version 10, which carries many amazing features. The goal is to meet the requirements of mixed HTAP scenarios of OLAP and OLTP:

PostgreSQL 10 features, the most popular HTAP database for developers

1. Multi-core Parallel Enhancement

2. fdw aggregation push-down

3. Logical Subscription

4, zoning

5. Multi-copies at the financial level

6. json, jsonb full-text retrieval

7. There are also some features of plug-in form, such as vector computing, JIT, SQL graph computing, SQL stream computing, distributed parallel computing, time series processing, gene sequencing, chemical analysis, image analysis, etc.

PostgreSQL can be seen in various application scenarios:

PostgreSQL has developed very rapidly in recent years. From the database scoring trend of dbranking, a well-known database evaluation website, we can see the upward trend of PostgreSQL:

From the annual community meetings held in PostgreSQL China, we can see the same trend. More and more companies are participating, more and more companies are sharing, and more and more subjects are shared. They span traditional enterprises, Internet, medical, financial, state-owned enterprises, logistics, e-commerce, social networking, vehicle networking, sharing XX, cloud, game, public transport, aviation, railway, military, training. Training, consulting services, etc.

The next series of articles will introduce various application scenarios and corresponding performance indicators of PostgreSQL.

Environmental Science

Reference to Environmental Deployment Method:

PostgreSQL 10 + PostGIS + Sharing (pg_pathman) + MySQL (fdw external table) on ECS Deployment Guide (for new users)

Aliyun ECS: 56 core, 224G, 1.5TB*2 SSD cloud disk.

Operating System: CentOS 7.4 x64

Database version: PostgreSQL 10

PS: The CPU and IO performance of ECS is discounted by physical opportunity, and can be estimated by one-fold performance decline. Running physical mainframe can be estimated by multiplying the performance tested here by 2.

Scenario - Multiple Form Point Writing with Index (OLTP+OLAP)

1, background

Including index, multiple tables (128 tables), write one record at a time. This is a very typical test of TP or AP scenarios, the ability of data to be injected into the scenario in real time.

2, design

Multiple tables (128 tables), including index, single transaction and single write (one write at a time). High concurrency.

3. Preparing test sheets

create table t_sensor(  
  id int8,  
  c1 int8 default 0,  
  c2 int8 default 0,  
  c3 int8 default 0,  
  c4 float8 default 0,  
  c5 text default 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',  
  ts timestamp default clock_timestamp()  
) with (autovacuum_enabled=off, toast.autovacuum_enabled=off);  
  
create index idx_t_sensor_ts on t_sensor using btree (ts) tablespace tbs1;

do language plpgsql $$  
declare  
begin  
  for i in 1..128 loop  
    execute format('create table t_sensor%s (like t_sensor including all) inherits (t_sensor) with (autovacuum_enabled=off, toast.autovacuum_enabled=off)', i);  
  end loop;  
end;  
$$;

4. Preparing test functions (optional)

create or replace function ins_sensor(int, int) returns void as $$  
declare  
begin  
  execute format('insert into t_sensor%s (id) values (%s)', $1, $2);  
  -- In order to stitch table names, a dynamic method is used. SQL，Hard resolution takes time.  
  -- This results in inconsistencies in test results, at least not worse than indexed single-table writing.  
  -- If written in batches, the problem of hard parsing can be masked.  
end;  
$$ language plpgsql strict;

5. Preparing test data

6. Preparing test scripts

vi test.sql  
  
\set sid random(1,128)  
\set id random(1,100000000)  
select ins_sensor(:sid, :id);

Pressure measurement

CONNECTS=56  
TIMES=300  
export PGHOST=$PGDATA  
export PGPORT=1999  
export PGUSER=postgres  
export PGPASSWORD=postgres  
export PGDATABASE=postgres  
  
pgbench -M prepared -n -r -f ./test.sql -P 5 -c $CONNECTS -j $CONNECTS -T $TIMES

7, test

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 300 s  
number of transactions actually processed: 51242910  
latency average = 0.328 ms  
latency stddev = 0.370 ms  
tps = 170796.479135 (including connections establishing)  
tps = 170812.608470 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set sid random(1,128)  
         0.000  \set id random(1,100000000)  
         0.326  select ins_sensor(:sid, :id);

TPS: 170812 (Dynamic SQL, resulting in test results that differ from actual capabilities)

Multiple tables (128 tables), including index, single transaction and single write (one write at a time). High concurrency.

  In order to stitch table names, dynamic SQL is used and hard parsing is time-consuming.  
  
  -- Causes inconsistencies in test results, at least not worse than indexed single-table writing. At least not less than 260,000 lines/s.  
  
  Hard parsing can be masked if written in batches.

Main bottlenecks: b-tree lock, xlog lock.