Skip to content

Latest commit

 

History

History
133 lines (102 loc) · 4.31 KB

README.md

File metadata and controls

133 lines (102 loc) · 4.31 KB

DB preparing:

Build PostgreSQL image:

$ docker build "$PWD"/postgers/Dockerfile -t test-postgres

Run locally:

$ mkdir "$PWD"/postgres/logs/ && chmod 777 "$PWD"/postgres/logs/

$ docker run -d -p 5432:5432 --cpus=0.5 -m=2g --network host \
  --name postgres-test \
  --user 0 \
  -v "$PWD"/postgres/postgresql.conf:/etc/postgresql/postgresql.conf \
  -v "$PWD"/postgres/logs:/etc/postgresql/pg_log \
  postgres-test -c 'config_file=/etc/postgresql/postgresql.conf'

Uploading data: 20 000 000 строк

$ python3 "$PWD"/inserter/main.py

Performance testing PostgreSQL indexes

Creating results table:

CREATE TABLE testdb.public.measured_time (
    IndexType    VARCHAR,
    DataType     VARCHAR,
    IndexWeight  FLOAT,
    CreationTime FLOAT,
    "="          FLOAT,
    ">"          FLOAT,
    "like"       FLOAT,
    "in"         FLOAT
)

Tests

$ export PYTHONPATH=$PYTHONPATH:$PWD/inserter
$ python "$PWD"/index_performance_test/main.py

Result

IndexType DataType IndexSize CreationDuration = > like in
brin varchar 64 kB 45.01 26.49 22.7 24.8
brin integer 64 kB 30.9 22.5 19.1 23.5
brin date 64 kB 33.79 25.09 16.1 24.5
hash varchar 841 MB 396.39 0.2 28.8 0.1
hash integer 535 MB 318.2 0.0 28.1 0.0
hash date 895 MB 2518.3 4.0 19.4 1.9
btree varchar 139 MB 489.71 0.0 24.3 0.0
btree integer 177 MB 149.9 0.0 18.9 0.09
btree date 132 MB 176.11 0.1 8.8 0.3

Cleaning out container and image:

docker stop test-postgres && docker rmi test-postgres

Monitoring

Start prometheus:

docker run \
  -p 9090:9090 \
  -d \
  --network host \
  -v "$PWD"/postgres/monitoring/prometheus.yml:/etc/prometheus/prometheus.yml \
  --name prometheus \
  prom/prometheus

Start postgres exporter:

docker run \
  --net=host \
  -d \
  -e DATA_SOURCE_NAME="postgresql://postgres:[email protected]:5432/testdb?sslmode=disable" \
  --name postgres-exporter \
  quay.io/prometheuscommunity/postgres-exporter

Start cadvisor:

docker run \
  --volume=/:/rootfs:ro \
  --volume=/var/run:/var/run:ro \
  --volume=/sys:/sys:ro \
  --volume=/var/lib/docker/:/var/lib/docker:ro \
  --volume=/dev/disk/:/dev/disk:ro \
  --publish=8080:8080 \
  --detach=true \
  --name=cadvisor \
  --privileged \
  --device=/dev/kmsg \
  --network host \
  --name cadvisor \
  gcr.io/cadvisor/cadvisor

Start grafana container:

docker run -d -p 3000:3000 --network host --name=grafana \
  --user "$(id -u)" \
  -e "GF_INSTALL_PLUGINS=grafana-clock-panel" \
  grafana/grafana-enterprise

Optional add to grafana:

sudo chmod 777 

--volume "$PWD/data:/var/lib/grafana" \

Start node exporter:

sudo groupadd -f node_exporter && \
useradd -g $(getent group node_exporter | cut -d: -f3) -M -s /bin/false node_exporter && \
mkdir /etc/node_exporter && \
chown node_exporter:node_exporter /etc/node_exporter && \
wget https://github.com/prometheus/node_exporter/releases/download/v1.6.1/node_exporter-1.6.1.linux-amd64.tar.gz && \
tar xvfz node_exporter-1.6.1.linux-amd64.tar.gz && \
cp node_exporter-1.6.1.linux-amd64/node_exporter /usr/bin/
chown node_exporter:node_exporter /usr/bin/node_exporter && \
chmod 664 /usr/lib/systemd/system/node_exporter.service && \
systemctl daemon-reload && \
systemctl start node_exporter && \
rm -rf node_exporter-1.6.1.linux-amd64.tar.gz node_exporter-1.6.1.linux-amd64

Clean up node_exporter:

sudo systemctl stop node_exporter && \
systemctl disable node_exporter && \
rm /usr/bin/node_exporter -f && \
rm /usr/lib/systemd/system/node_exporter.service -f && \
systemctl daemon-reload && \
systemctl reset-failed