v0112358

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;

v0112358

wget https://ftp.gnu.org/gnu/gcc/gcc-5.5.0/gcc-5.5.0.tar.bz2
tar xjvf gcc-5.4.0.tar.bz2
yum install -y gmp-devel mpfr-devel libmpc-devel
mkdir gcc-5.5.0-build
cd gcc-5.5.0-build
../gcc-5.5.0/configure --enable-languages=c,c++ --disable-multilib
make -j$(nproc) && make install

v0112358

wget https://cmake.org/files/v3.11/cmake-3.11.0.tar.gz
tar zxvf cmake-3.11.0.tar.gz && cd cmake-3.11.0
export LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/usr/local/lib64:/usr/lib64
./bootstrap
make -j$(nproc)
make install
cmake --version

v0112358

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

v0112358

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

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

v0112358

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}

v0112358

• 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

v0112358

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

v0112358

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.

v0112358

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