Nguyen Tan Vy v0112358

pgagroal

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

Install

yum install centos-release-scl
yum install cmake3 libev libev-devel openssl openssl-devel python36-docutils
yum install devtoolset-8
scl enable devtoolset-8 bash

1/ Enable wal archiving (need to restart PostgreSQL)

cat postgresql.conf
....
archive_mode = always
archive_command = 'test ! -f /wal_archive/%f.zst && pzstd -o /wal_archive/%f.zst %p'
....

2/ Monitor wal archiving

How does pgpool-II handle md5 authentication?

1. PostgreSQL and pgpool stores md5(password+username) into pg_authid or pool_password. From now on I denote string md5(password+username) as "S".
2. When md5 auth is requested, pgpool sends a random number salt "s0" to frontend.
3. Frontend replies back to pgpool with md5(S+s0).
4. pgpool extracts S from pgpool_passwd and calculate md5(S+s0). If #3 and #4 matches, goes to next step.
5. Each backend sends salt to pgpool. Suppose we have two backends b1 and b2, and salts are s1 and s2.
6. pgpool extracts S from pgpool_passwd and calculate md5(S+s1) and send it to b1. pgpool extracts S from pgpool_passwd and calculate md5(S+s2) and send it to b2.
7. If b1 and b2 agree with the authentication, the whole md5 auth process succeeds.

MySQL 5.6

Base memory

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@innodb_additional_mem_pool_size )
/ (1024 * 1024 * 1024) AS BASE_MEMORY_GB;

Latency

ioping -WWW -c 200 -i 0 -L -s 4K /mnt/
...
--- /mnt/ (xfs /dev/nvme0n1) ioping statistics ---
199 requests completed in 14.1 ms, 796 KiB written, 14.1 k iops, 55.3 MiB/s
generated 200 requests in 16.1 ms, 800 KiB, 12.5 k iops, 48.7 MiB/s
min/avg/max/mdev = 61.4 us / 70.7 us / 273.9 us / 16.9 us

innodb_numa_interleave=OFF

# time systemctl restart mysqld

real	0m18.380s
user	0m0.007s
sys	0m0.005s

2021-03-11T11:25:45.500980+07:00 0 [Note] InnoDB: Uses event mutexes

MongoDB info

MongoDB version: 3.6.23
PBM version: 1.6.0
Error messages:

Sep  1 07:47:17 vm-prod-mongodb-2 pbm-agent: 2021-09-01T07:47:17.000+0700 I got command backup [name: 2021-09-01T00:47:16Z, compression: s2] <ts: 1630457236>
Sep  1 07:47:17 vm-prod-mongodb-2 pbm-agent: 2021-09-01T07:47:17.000+0700 I got epoch {0 0}

Local storage

[root@srvsg2 ~]# h2load -n 100000 -c 100 -t 1 -T 5 -m 10 -H 'Accept-Encoding: gzip,deflate' https://xxx.svc-linode.xxx.com/wp-content/uploads/2015/11/logo-1.png
starting benchmark...
spawning thread #0: 100 total client(s). 100000 total requests
TLS Protocol: TLSv1.2
Cipher: ECDHE-RSA-AES256-GCM-SHA384
Server Temp Key: ECDH P-256 256 bits
Application protocol: h2

	global:
	scrape_interval: 10s
	external_labels:
	Environment: sandbox
	Region: gcp
	Source: prometheus

	scrape_configs:
	- job_name: consul-services
	metrics_path: "/metrics"