8000 +shard +fdw tests · postgrespro/postgres_cluster@96f465c · GitHub
[go: up one dir, main page]
Skip to content

Commit 96f465c

Browse files
kelvichkelvich
committed
+shard +fdw tests
1 parent 4c26d45 commit 96f465c

File tree

13 files changed

+95
-90
lines changed

‎contrib/pg_dtm/tests/benchmark.go

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -104,7 +104,7 @@ func init() {
104104

105105
func main() {
106106
start := time.Now()
107-
107+
108108
if (!cfg.SkipInit){
109109
prepare(cfg.ConnStrs)
110110
fmt.Printf("database prepared in %0.2f seconds\n", time.Since(start).Seconds())
@@ -144,7 +144,9 @@ func main() {
144144
}
145145

146146
wg.Wait()
147-
fmt.Printf("Perform %d updates and %d fetches\n", totalUpdates, totalFetches)
147+
fmt.Printf("Performed %d updates and %d fetches\n", totalUpdates, totalFetches)
148+
fmt.Printf("WTPS = %f \n", float64(totalUpdates)/float64(cfg.Time) )
149+
fmt.Printf("RTPS = %f \n", float64(totalFetches)/float64(cfg.Time) )
148150
}
149151

150152
var running = false
@@ -179,7 +181,6 @@ func prepare_one(connstr string, wg *sync.WaitGroup) {
179181
exec(conn, "drop table if exists t")
180182
exec(conn, "create table t(u int primary key, v int)")
181183
exec(conn, "insert into t (select generate_series(0,$1-1), $2)", cfg.Accounts.Num, cfg.Accounts.Balance)
182-
exec(conn, "commit")
183184
wg.Done()
184185
}
185186

‎contrib/pg_dtm/tests/deploy_layouts/cluster.yml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,17 @@
11
---
2-
- hosts: nodes[0]
2+
- hosts: nodes[1]
33
roles:
44
- role: postgrespro
55
deploy_dtm: true
66

7-
- hosts: all
7+
- hosts: nodes
88
roles:
99
- role: postgrespro
1010
pg_port: 15432
1111
deploy_postgres: true
1212
pg_dtm_enable: true
13-
# pg_dtm_enable: false
14-
pg_config_role:
15-
- line: "dtm.buffer_size = 65536"
13+
pg_dtm_enable: false
14+
# pg_config_role:
15+
# - line: "dtm.buffer_size = 65536"
1616
pg_dtm_host: "{{ groups['nodes'][0] }}"
1717

‎contrib/pg_dtm/tests/deploy_layouts/cluster_pg_shard.yml

Lines changed: 11 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,32 +1,32 @@
11
---
22

3-
- hosts: dtm
3+
- hosts: nodes[0]
44
roles:
55
- role: postgrespro
66
deploy_dtm: true
77

8-
- hosts: masters
8+
- hosts: nodes, !master
99
roles:
1010
- role: postgrespro
1111
pg_src: ./postgrespro_pgshard
1212
pg_version: xtm_pgshard
13-
pg_port: 15432
13+
pg_port: 25432
1414
deploy_postgres: true
1515
pg_dtm_enable: true
16-
pg_dtm_host: "{{ groups['dtm'][0] }}"
17-
deploy_pg_shard: true
16+
pg_dtm_host: "{{ groups['nodes'][0] }}"
1817

19-
- hosts: workers
18+
- hosts: master
2019
roles:
2120
- role: postgrespro
2221
pg_src: ./postgrespro_pgshard
2322
pg_version: xtm_pgshard
24-
pg_port: 15432
23+
pg_port: 25432
2524
deploy_postgres: true
2625
pg_dtm_enable: true
27-
pg_dtm_host: "{{ groups['dtm'][0] }}"
26+
pg_dtm_host: "{{ groups['nodes'][0] }}"
27+
deploy_pg_shard: true
2828

29-
- hosts: clients
30-
roles:
31-
- role: postgrespro
29+
# - hosts: clients
30+
# roles:
31+
# - role: postgrespro
3232

‎contrib/pg_dtm/tests/deploy_layouts/roles/postgrespro/tasks/main.yml

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -13,22 +13,22 @@
1313
when: ansible_os_family == "Debian"
1414
sudo: yes
1515

16-
# - name: ensure dependencies (RedHat)
17-
# yum: name="@Development tools" state=present
18-
# when: ansible_os_family == "RedHat"
19-
# sudo: yes
20-
21-
# - name: ensure dependencies (RedHat)
22-
# yum: name={{item}} state=installed
23-
# with_items:
24-
# - git
25-
# - automake
26-
# - libtool
27-
# - bison
28-
# - flex
29-
# - readline-devel
30-
# when: ansible_os_family == "RedHat"
31-
# sudo: yes
16+
- name: ensure dependencies (RedHat)
17+
yum: name="@Development tools" state=present
18+
when: ansible_os_family == "RedHat"
19+
sudo: yes
20+
21+
- name: ensure dependencies (RedHat)
22+
yum: name={{item}} state=installed
23+
with_items:
24+
- git
25+
- automake
26+
- libtool
27+
- bison
28+
- flex
29+
- readline-devel
30+
when: ansible_os_family == "RedHat"
31+
sudo: yes
3232

3333
- name: setup the private key for postgrespro git access
3434
copy: dest=.ssh/ppg-deploy src=ppg-deploy.key mode=0600

‎contrib/pg_dtm/tests/deploy_layouts/roles/postgrespro/tasks/pg_shard.yml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,6 @@
4545
when: not pg_dtm_enable
4646

4747
- name: create pg_worker_list.conf
48-
shell: echo "{{item}} 15432" >> {{pg_datadir}}/pg_worker_list.conf
48+
shell: echo "{{item}} 25432" >> {{pg_datadir}}/pg_worker_list.conf
4949
with_items: groups['nodes']
5050

‎contrib/pg_dtm/tests/deploy_layouts/roles/postgrespro/vars/main.yml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ pg_config:
1616
- line: "fsync = off"
1717
- line: "autovacuum = off"
1818
- line: "listen_addresses = '*'"
19-
- line: "max_connections = 512"
19+
- line: "max_connections = 2048"
2020
- line: "max_prepared_transactions = 400"
2121
- line: "port = {{pg_port}}"
2222
pg_config_role:

‎contrib/pg_dtm/tests/farms/sai

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,13 @@
11
[clients]
22
158.250.29.4 ansible_ssh_user=cluster offset=6001
3-
158.250.29.5 ansible_ssh_user=cluster offset=5001
4-
158.250.29.6 ansible_ssh_user=cluster offset=4001
3+
158.250.29.7 ansible_ssh_user=cluster ansible_ssh_port=2299 offset=3001
54

65
[nodes]
7-
158.250.29.7 ansible_ssh_user=cluster ansible_ssh_port=2299 offset=3001
6+
158.250.29.5 ansible_ssh_user=cluster offset=5001
7+
158.250.29.6 ansible_ssh_user=cluster offset=4001
88
158.250.29.8 ansible_ssh_user=cluster offset=2001
99
158.250.29.9 ansible_ssh_user=cluster offset=1001
1010
158.250.29.10 ansible_ssh_user=cluster offset=1
11+
12+
[master]
13+
158.250.29.10 ansible_ssh_user=cluster offset=1

‎contrib/pg_dtm/tests/pg_shard_transfers.go

Lines changed: 26 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -3,57 +3,67 @@ package main
33
import (
44
"fmt"
55
"sync"
6-
_ "github.com/jgallagher/go-libpq"
6+
_ "github.com/lib/pq"
77
"database/sql"
88
"strconv"
99
"math/rand"
1010
"time"
1111
)
1212

1313
const (
14-
TRANSFER_CONNECTIONS = 10
15-
INIT_AMOUNT = 100 E1FC 00
16-
N_ITERATIONS = 10000
17-
N_ACCOUNTS = 2*100000
14+
TRANSFER_CONNECTIONS = 150
15+
INIT_AMOUNT = 1000
16+
N_ITERATIONS = 3000
17+
N_ACCOUNTS = 10000
1818
)
1919

20-
var cfg = "host=127.0.0.1 port=5432 sslmode=disable"
21-
var cfg1 = "host=127.0.0.1 port=5433 sslmode=disable"
22-
var cfg2 = "host=127.0.0.1 port=5434 sslmode=disable"
20+
var cfg = "host=astro10 port=25432 dbname=postgres sslmode=disable"
21+
var cfg1 = "host=astro9 port=25432 dbname=postgres sslmode=disable"
22+
var cfg2 = "host=astro8 port=25432 dbname=postgres sslmode=disable"
23+
var cfg3 = "host=astro6 port=25432 dbname=postgres sslmode=disable"
24+
25+
// var cfg = "host=127.0.0.1 port=5432 sslmode=disable"
26+
// var cfg1 = "host=127.0.0.1 port=5433 sslmode=disable"
27+
// var cfg2 = "host=127.0.0.1 port=5434 sslmode=disable"
2328

2429
var running = false
2530

2631
func prepare_db() {
27-
conn1, err := sql.Open("libpq", cfg1)
32+
conn1, err := sql.Open("postgres", cfg1)
2833
checkErr(err)
2934
exec(conn1, "drop table if exists t_10000")
3035
conn1.Close()
3136

32-
conn2, err := sql.Open("libpq", cfg2)
37+
conn2, err := sql.Open("postgres", cfg2)
3338
checkErr(err)
3439
exec(conn2, "drop table if exists t_10001")
3540
conn2.Close()
3641

3742

38-
conn, err := sql.Open("libpq", cfg)
43+
conn3, err := sql.Open("postgres", cfg3)
44+
checkErr(err)
45+
exec(conn3, "drop table if exists t_10003")
46+
conn3.Close()
47+
48+
conn, err := sql.Open("postgres", cfg)
3949
checkErr(err)
4050

4151
exec(conn, "drop extension if exists pg_shard CASCADE")
4252
exec(conn, "create extension pg_shard")
4353
exec(conn, "drop table if exists t")
4454
exec(conn, "create table t(u int primary key, v int)")
4555
exec(conn, "select master_create_distributed_table(table_name := 't', partition_column := 'u')")
46-
exec(conn, "select master_create_worker_shards(table_name := 't', shard_count := 2, replication_factor := 1)")
56+
exec(conn, "select master_create_worker_shards(table_name := 't', shard_count := 3, replication_factor := 1)")
4757

4858
for i:=1; i<=N_ACCOUNTS; i++ {
4959
exec(conn, "insert into t values(" + strconv.Itoa(i) + ",10000)")
5060
}
51-
61+
fmt.Printf("Prepared!\n")
5262
conn.Close()
5363
}
5464

5565
func transfer(id int, wg *sync.WaitGroup) {
56-
conn, err := sql.Open("libpq", cfg)
66+
conn, err := sql.Open("postgres", cfg)
5767
checkErr(err)
5868
defer conn.Close()
5969

@@ -81,7 +91,7 @@ func inspect(wg *sync.WaitGroup) {
8191
var sum int64
8292
var prevSum int64 = 0
8393

84-
conn, err := sql.Open("libpq", cfg)
94+
conn, err := sql.Open("postgres", cfg)
8595
checkErr(err)
8696

8797
for running {
@@ -118,16 +128,8 @@ func main() {
118128

119129
inspectWg.Wait()
120130

121-
// conn, err := sql.Open("libpq", cfg)
122-
// checkErr(err)
123-
124-
// exec(conn, "begin")
125-
// sum := execQuery(conn, "select sum(v) from t")
126-
// exec(conn, "commit")
127-
128-
// fmt.Println(sum)
129-
130131
fmt.Printf("Elapsed time %f seconds\n", time.Since(start).Seconds())
132+
fmt.Printf("TPS = %f\n", float64(TRANSFER_CONNECTIONS*N_ITERATIONS)/time.Since(start).Seconds())
131133
}
132134

133135
func exec(conn *sql.DB, stmt string) {
@@ -149,5 +151,3 @@ func checkErr(err error) {
149151
panic(err)
150152
}
151153
}
152-
153-

‎contrib/pg_dtm/tests/transfers-fdw.go

Lines changed: 9 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -9,28 +9,27 @@ import (
99
)
1010

1111
const (
12-
TRANSFER_CONNECTIONS = 8
12+
TRANSFER_CONNECTIONS = 100
1313
INIT_AMOUNT = 10000
1414
N_ITERATIONS = 10000
1515
N_ACCOUNTS = 100000
16-
ISOLATION_LEVEL = "repeatable read"
17-
//ISOLATION_LEVEL = "read committed"
16+
// ISOLATION_LEVEL = "repeatable read"
17+
ISOLATION_LEVEL = "read committed"
1818
)
1919

2020

2121
var cfg1 = pgx.ConnConfig{
22-
Host: "127.0.0.1",
23-
Port: 5432,
22+
Host: "astro10",
23+
Port: 15432,
2424
Database: "postgres",
2525
}
2626

2727
var cfg2 = pgx.ConnConfig{
28-
Host: "127.0.0.1",
29-
Port: 5433,
28+
Host: "astro9",
29+
Port: 15432,
3030
Database: "postgres",
3131
}
3232

33-
3433
var running = false
3534

3635
func prepare_db() {
@@ -53,9 +52,9 @@ func prepare_db() {
5352
exec(conn2, "create table t(u int primary key, v int)")
5453

5554
exec(conn1, "CREATE EXTENSION postgres_fdw");
56-
exec(conn1, "CREATE SERVER dtm FOREIGN DATA WRAPPER postgres_fdw options (dbname 'postgres', host '127.0.0.1', port '5433')");
55+
exec(conn1, "CREATE SERVER dtm FOREIGN DATA WRAPPER postgres_fdw options (dbname 'postgres', host 'astro9', port '15432')");
5756
exec(conn1, "CREATE FOREIGN TABLE t_fdw() inherits (t) server dtm options(table_name 't')");
58-
exec(conn1, "CREATE USER MAPPING for knizhnik SERVER dtm options (user 'knizhnik')");
57+
exec(conn1, "CREATE USER MAPPING for cluster SERVER dtm options (user 'cluster')");
5958

6059
// start transaction
6160
exec(conn1, "begin transaction isolation level " + ISOLATION_LEVEL)

‎contrib/pg_dtm/tests/transfers.go

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -321,11 +321,14 @@ func writer(id int, cCommits chan int, cAborts chan int, wg *sync.WaitGroup) {
321321
}
322322

323323
if cfg.UseDtm {
324-
xid := execQuery(src, "select dtm_begin_transaction(); begin transaction isolation level " + cfg.Isolation)
325-
exec(dst, "select dtm_join_transaction(" + strconv.Itoa(xid) + "); begin transaction isolation level " + cfg.Isolation)
324+
xid := execQuery(src, "select dtm_begin_transaction()")
325+
exec(dst, "select dtm_join_transaction($1)", xid)
326326
}
327327

328-
// parallel_exec([]*pgx.Conn{src,dst}, []string{"begin transaction isolation level " + cfg.Isolation, "begin transaction isolation level " + cfg.Isolation})
328+
parallel_exec(
329+
[]*pgx.Conn{src,dst},
330+
[]string{"begin transaction isolation level " + cfg.Isolation,
331+
"begin transaction isolation level " + cfg.Isolation})
329332

330333
ok := true
331334

‎contrib/pg_dtm/tests/transfers.sh

Lines changed: 0 additions & 1 deletion
F438
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,6 @@
33
go run transfers.go \
44
-d 'dbname=postgres port=5432' \
55
-d 'dbname=postgres port=5433' \
6-
-v \
76
-m \
87
-u 100000 \
98
-w 8 \

‎contrib/pg_dtm/tests/transfers.yml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -24,13 +24,13 @@
2424
- name: fill the databases
2525
shell: "./transfers {{connections}} -f -g"
2626
register: transfers_result
27-
- debug: var=transfers_result
27+
- debug: "var=transfers_result"
2828

2929
- hosts: clients
3030
gather_facts: no
3131
tasks:
3232
- name: run transfers
33-
shell: "./transfers {{connections}} -s {{runkeys | d('-g -w 90 -o -u 10000 -r 0')}} -k {{hostvars[inventory_hostname]['offset']}} "
33+
shell: "./transfers {{connections}} -s {{runkeys | d('-g -w 60 -m -o -u 10000 -r 0')}} -k {{hostvars[inventory_hostname]['offset']}} "
3434
register: transfers_result
3535
- debug: var=transfers_result
3636

‎install.sh

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -9,8 +9,8 @@ rm -rf install
99

1010
make install
1111

12-
# cd contrib/pg_dtm/
13-
cd contrib/pg_tsdtm/
12+
cd contrib/pg_dtm/
13+
# cd contrib/pg_tsdtm/
1414

1515
make clean
1616
make
@@ -42,9 +42,9 @@ echo "max_prepared_transactions = 400" >> ./install/data2/postgresql.conf
4242
./install/bin/pg_ctl -D ./install/data2 -l ./install/data2/log start
4343

4444

45-
# cd contrib/pg_dtm/dtmd
46-
# make clean
47-
# make
48-
# rm -rf /tmp/clog/*
49-
# mkdir -p /tmp/clog
50-
# ./bin/dtmd
45+
cd contrib/pg_dtm/dtmd
46+
make clean
47+
make
48+
rm -rf /tmp/clog/*
49+
mkdir -p /tmp/clog
50+
./bin/dtmd

0 commit comments

Comments
 (0)
0