Skip to content

Instantly share code, notes, and snippets.

@akhdaniel

akhdaniel/PostgreSQL Clustering CITUS.md

Last active December 26, 2024 14:11
Show Gist options
  • Save akhdaniel/fb3303696d5398ef73e10599993d1f6a to your computer and use it in GitHub Desktop.
Save akhdaniel/fb3303696d5398ef73e10599993d1f6a to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL Clustering CITUS
Raw
PostgreSQL Clustering CITUS.md

Install Steps on Worker:

curl https://install.citusdata.com/community/deb.sh | sudo bash

sudo apt-get -y install postgresql-13-citus-10.0

sudo pg_conftool 13 main set shared_preload_libraries citus

sudo pg_conftool 13 main set listen_addresses '*'

Edit pg_hba.conf file:

sudo vi /etc/postgresql/13/main/pg_hba.conf

Allow Cordinater Node IP or network to access this node:


host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

host    all             all             10.0.0.0/8              trust  # allow the same network
host    all             all             <cordinator-ip>/32      trust  # allow server IP address

THIS IS JUST AN EXAMPLE: These settings are too permissive for some environments, see our notes about Increasing Worker Security. The PostgreSQL manual explains how to make them more restrictive.

  1. http://docs.citusdata.com/en/v10.0/admin_guide/cluster_management.html#worker-security
  2. http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Now, Go to PG TUNE website adn tune your postgres according to hardware:

https://pgtune.leopard.in.ua/#/

Put the recommended parameter to the end of the config

sudo vi /etc/postgresql/13/main/postgresql.conf

for example:


#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# DB Version: 13
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 8 GB
# CPUs num: 4
# Connections num: 300
# Data Storage: ssd

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3495kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Restart Postgres

sudo service postgresql restart
sudo update-rc.d postgresql enable

Create CITUS extension on EVERY DATABASE on EVERY NODE including Odoo application database later. This sample command only enable citus extansion on postgres database (the default one from postgres installation):

sudo -i -u postgres psql -c "CREATE EXTENSION citus;"

Install Steps on Cordinator Node

Repeat the same steps above.

plus these steps ONLY on cordinator node:

sudo -i -u postgres psql -c "SELECT * from citus_add_node('<worker-ip1>', 5432);"
sudo -i -u postgres psql -c "SELECT * from citus_add_node('<worker-ip2>', 5432);"
sudo -i -u postgres psql -c "SELECT * FROM citus_get_active_worker_nodes();"

Ready to use Citus!

Test Benchmark

Install PGBENCH on master node

apt-get install postgresql postgresql-contrib

Init pgbench on Master node on db postgres:

pgbench -i -s 50 postgres

Let's see the created PGBENCH Tables:

psql 

postgres=# \d
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | citus_tables     | view  | postgres
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(5 rows)

Perform test on single master node postgres db (before clustering):

pgbench -c 10 -j 2 -t 10000 postgres

Parameters:

  • -c = number of clients
  • -j = number of thread
  • -t = number of transaction

Test Result:

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 4.222 ms
tps = 2368.770074 (including connections establishing)
tps = 2369.012675 (excluding connections establishing)

NOW, Propagate tables to all clusters. This should be done on ALL database and tables that need to be distributed.

sudo -i -u postgres psql -c "SELECT create_distributed_table('pgbench_branches',   'bid');"
sudo -i -u postgres psql -c "SELECT create_distributed_table('pgbench_accounts',   'aid');"
sudo -i -u postgres psql -c "SELECT create_distributed_table('pgbench_history',   'aid');"
sudo -i -u postgres psql -c "SELECT create_distributed_table('pgbench_tellers',   'tid');"

Go to worker node, and see the created distributed tables on each workers:

postgres@citus-worker1:~$ psql
psql (13.2 (Ubuntu 13.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | citus_tables            | view  | postgres
 public | pgbench_accounts_102171 | table | postgres
 public | pgbench_accounts_102175 | table | postgres
 public | pgbench_accounts_102179 | table | postgres
 public | pgbench_accounts_102183 | table | postgres
 public | pgbench_accounts_102187 | table | postgres
 public | pgbench_accounts_102191 | table | postgres
 public | pgbench_accounts_102195 | table | postgres
 public | pgbench_accounts_102199 | table | postgres
 public | pgbench_branches_102139 | table | postgres
 public | pgbench_branches_102143 | table | postgres
 public | pgbench_branches_102147 | table | postgres
 public | pgbench_branches_102151 | table | postgres
 public | pgbench_branches_102155 | table | postgres
 public | pgbench_branches_102159 | table | postgres
 public | pgbench_branches_102163 | table | postgres
 public | pgbench_branches_102167 | table | postgres
 public | pgbench_history_102203  | table | postgres
 public | pgbench_history_102207  | table | postgres
 public | pgbench_history_102211  | table | postgres
 public | pgbench_history_102215  | table | postgres
 public | pgbench_history_102219  | table | postgres
 public | pgbench_history_102223  | table | postgres
 public | pgbench_history_102227  | table | postgres
 public | pgbench_history_102231  | table | postgres
 public | pgbench_tellers_102235  | table | postgres
 public | pgbench_tellers_102239  | table | postgres
 public | pgbench_tellers_102243  | table | postgres
 public | pgbench_tellers_102247  | table | postgres
 public | pgbench_tellers_102251  | table | postgres
 public | pgbench_tellers_102255  | table | postgres
 public | pgbench_tellers_102259  | table | postgres
 public | pgbench_tellers_102263  | table | postgres
(33 rows)

Create Test Baseline

pgbench -c 256 -j 2 -t 10000 postgres

Parameters:

  • -c = number of clients
  • -j = pgbench worker process
  • -t = number of transactions to execute

the baseline test run was two pgbench worker processes simulating 10,000 transactions from 256 clients for a total of 100,000 transactions.

Sample Result:

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average: 4.176 ms
tps = 23094.718707 (including connections establishing)
tps = 23094.874350 (excluding connections establishing)

Will see a significatnt amount of TPS number! The Numbers depends on your hardware CPU and RAM

Referece

  1. https://docs.citusdata.com/en/stable/use_cases/multi_tenant.html#multi-tenant-applications
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment