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

Revisions

  1. akhdaniel revised this gist May 1, 2021. 1 changed file with 10 additions and 10 deletions.
    20 changes: 10 additions & 10 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -88,7 +88,7 @@ sudo service postgresql restart
    sudo update-rc.d postgresql enable
    ```

    Create CITUS extension on Every DATABASE on the node including Odoo application database. This sample command only enable citus extansion on postgres database (the default one from postgres installation):
    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;"
    @@ -125,7 +125,7 @@ Install PGBENCH on master node
    apt-get install postgresql postgresql-contrib
    ```

    Init pgbench on Master node
    Init pgbench on Master node on db postgres:

    ```
    pgbench -i -s 50 postgres
    @@ -148,7 +148,7 @@ postgres=# \d
    ```

    Perform test on single master node postgres db (without clustering):
    Perform test on single master node postgres db (before clustering):
    ```
    pgbench -c 10 -j 2 -t 10000 postgres
    ```
    @@ -233,14 +233,14 @@ postgres=# \d

    Create Test Baseline
    ```
    pgbench -c 10 -j 2 -t 10000 postgres
    pgbench -c 256 -j 2 -t 10000 postgres
    ```
    Parameters:
    * -c = number of clients
    * -j = pgbench worker process
    * -t = number of transactions to execute

    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 10 clients for a total of 100,000 transactions.
    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:
    @@ -259,7 +259,7 @@ tps = 23094.874350 (excluding connections establishing)
    ```


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



  2. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -88,7 +88,7 @@ sudo service postgresql restart
    sudo update-rc.d postgresql enable
    ```

    Create CITUS extension on Every DATABASE on the node including Odoo application database. This step only enable citus extansion on postgres database (the default one from postgres installation):
    Create CITUS extension on Every DATABASE on the node including Odoo application database. 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;"
  3. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 11 additions and 4 deletions.
    15 changes: 11 additions & 4 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -18,19 +18,26 @@ Edit pg_hba.conf file:
    sudo vi /etc/postgresql/13/main/pg_hba.conf
    ```

    Allow Other Node IP or network to access this node:
    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 <server-ip>/32 trust # allow server IP address
    host all all <cordinator-ip>/32 trust # allow server IP address
    ```

    Go to PG TUNE website adn tune your postgres according to hardware:
    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/#/

    @@ -89,7 +96,7 @@ sudo -i -u postgres psql -c "CREATE EXTENSION citus;"



    Install Steps on Cordinator
    Install Steps on Cordinator Node
    --------------------------

    Repeat the same steps above.
  4. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 103 additions and 17 deletions.
    120 changes: 103 additions & 17 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -81,7 +81,7 @@ sudo service postgresql restart
    sudo update-rc.d postgresql enable
    ```

    Create CITUS extension on Every DATABASE:
    Create CITUS extension on Every DATABASE on the node including Odoo application database. This step only enable citus extansion on postgres database (the default one from postgres installation):

    ```
    sudo -i -u postgres psql -c "CREATE EXTENSION citus;"
    @@ -92,15 +92,13 @@ sudo -i -u postgres psql -c "CREATE EXTENSION citus;"
    Install Steps on Cordinator
    --------------------------

    repeat the same steps above.
    Repeat the same steps above.

    plus these:
    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();"
    ```
    @@ -114,37 +112,121 @@ Ready to use Citus!
    Test Benchmark
    ------------------

    Install PGBENCH
    Install PGBENCH on master node

    ```
    apt-get install postgresql postgresql-contrib
    ```

    Create DB
    Init pgbench on Master node

    ```
    pgbench -i -s 50 postgres
    ```
    Let's see the created PGBENCH Tables:

    ```
    su - postgres
    psql
    CREATE DATABASE example
    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)
    ```
    Init pgbench

    Perform test on single master node postgres db (without clustering):
    ```
    pgbench -i -s 50 example
    pgbench -c 10 -j 2 -t 10000 postgres
    ```
    Created Tables:
    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)
    ```

    Propagate tables to all clusters

    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 10 -j 2 -t 10000 example
    pgbench -c 10 -j 2 -t 10000 postgres
    ```

    c = number of clients
    @@ -165,12 +247,16 @@ number of threads: 2
    number of transactions per client: 10000
    number of transactions actually processed: 100000/100000
    latency average: 4.176 ms
    tps = 2394.718707 (including connections establishing)
    tps = 2394.874350 (excluding connections establishing)
    tps = 23094.718707 (including connections establishing)
    tps = 23094.874350 (excluding connections establishing)
    ```


    Note: Increase shared_buffers to increase TPS
    Will see a significatnt amount of TPS number!



    Referece
    ---------------
    1. https://docs.citusdata.com/en/stable/use_cases/multi_tenant.html#multi-tenant-applications
    2.
  5. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 6 additions and 4 deletions.
    10 changes: 6 additions & 4 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -74,19 +74,21 @@ max_parallel_maintenance_workers = 2
    ```

    Create CITUS extension on Every DATABASE:
    Restart Postgres

    ```
    sudo -i -u postgres psql -c "CREATE EXTENSION citus;"
    sudo service postgresql restart
    sudo update-rc.d postgresql enable
    ```

    Restart postgres:
    Create CITUS extension on Every DATABASE:

    ```
    sudo systemctl restart postgresql
    sudo -i -u postgres psql -c "CREATE EXTENSION citus;"
    ```



    Install Steps on Cordinator
    --------------------------

  6. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -74,6 +74,12 @@ max_parallel_maintenance_workers = 2
    ```

    Create CITUS extension on Every DATABASE:

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

    Restart postgres:

    ```
  7. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -21,12 +21,12 @@ sudo vi /etc/postgresql/13/main/pg_hba.conf
    Allow Other Node IP or network to access this node:

    ```
    host all all 10.0.0.0/8 trust
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust
    host all all <server-ip>/32 trust
    host all all 10.0.0.0/8 trust # allow the same network
    host all all <server-ip>/32 trust # allow server IP address
    ```

  8. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -73,6 +73,14 @@ max_parallel_workers = 4
    max_parallel_maintenance_workers = 2
    ```

    Restart postgres:

    ```
    sudo systemctl restart postgresql
    ```


    Install Steps on Cordinator
    --------------------------

  9. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 5 additions and 7 deletions.
    12 changes: 5 additions & 7 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -47,33 +47,31 @@ for example:
    # CUSTOMIZED OPTIONS
    #------------------------------------------------------------------------------
    # Add settings for extensions here
    # DB Version: 13
    # OS Type: linux
    # DB Type: web
    # Total Memory (RAM): 2 GB
    # Total Memory (RAM): 8 GB
    # CPUs num: 4
    # Connections num: 300
    # Data Storage: ssd
    max_connections = 300
    shared_buffers = 512MB
    effective_cache_size = 1536MB
    maintenance_work_mem = 128MB
    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 = 873kB
    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
    ```
    Install Steps on Cordinator
    --------------------------
  10. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 43 additions and 0 deletions.
    43 changes: 43 additions & 0 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -30,8 +30,51 @@ host all all <server-ip>/32 trust
    ```

    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
    #------------------------------------------------------------------------------
    # Add settings for extensions here
    # DB Version: 13
    # OS Type: linux
    # DB Type: web
    # Total Memory (RAM): 2 GB
    # CPUs num: 4
    # Connections num: 300
    # Data Storage: ssd
    max_connections = 300
    shared_buffers = 512MB
    effective_cache_size = 1536MB
    maintenance_work_mem = 128MB
    checkpoint_completion_target = 0.9
    wal_buffers = 16MB
    default_statistics_target = 100
    random_page_cost = 1.1
    effective_io_concurrency = 200
    work_mem = 873kB
    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
    ```
    Install Steps on Cordinator
    --------------------------

  11. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 5 additions and 2 deletions.
    7 changes: 5 additions & 2 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -9,13 +9,16 @@ 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 '*'
    sudo vi /etc/postgresql/13/main/pg_hba.conf
    ```

    Edit pg_hba.conf file:

    Allow Server IP to access this node
    ```
    sudo vi /etc/postgresql/13/main/pg_hba.conf
    ```

    Allow Other Node IP or network to access this node:

    ```
    host all all 10.0.0.0/8 trust
  12. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 63 additions and 0 deletions.
    63 changes: 63 additions & 0 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -51,3 +51,66 @@ Ready to use Citus!



    Test Benchmark
    ------------------

    Install PGBENCH

    ```
    apt-get install postgresql postgresql-contrib
    ```

    Create DB
    ```
    su - postgres
    psql
    CREATE DATABASE example
    ```
    Init pgbench

    ```
    pgbench -i -s 50 example
    ```
    Created Tables:

    ```
    ```

    Propagate tables to all clusters

    ```
    ```


    Create Test Baseline
    ```
    pgbench -c 10 -j 2 -t 10000 example
    ```

    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 10 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 = 2394.718707 (including connections establishing)
    tps = 2394.874350 (excluding connections establishing)
    ```


    Note: Increase shared_buffers to increase TPS



  13. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -17,15 +17,15 @@ sudo vi /etc/postgresql/13/main/pg_hba.conf
    Allow Server IP to access this node


    ``
    ```
    host all all 10.0.0.0/8 trust
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust
    host all all <server-ip>/32 trust
    ``
    ```



  14. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,6 @@ Install Steps on Worker:
    -----------------------------------

    ```
    curl https://install.citusdata.com/community/deb.sh | sudo bash
    sudo apt-get -y install postgresql-13-citus-10.0
    @@ -17,8 +16,8 @@ sudo vi /etc/postgresql/13/main/pg_hba.conf

    Allow Server IP to access this node

    ``

    ``
    host all all 10.0.0.0/8 trust

    host all all 127.0.0.1/32 trust
  15. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,6 +16,7 @@ sudo vi /etc/postgresql/13/main/pg_hba.conf


    Allow Server IP to access this node

    ``

    host all all 10.0.0.0/8 trust
  16. akhdaniel revised this gist Apr 30, 2021. 1 changed file with 4 additions and 11 deletions.
    15 changes: 4 additions & 11 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,18 +1,14 @@
    Install Nodes:
    Install Steps on Worker:
    -----------------------------------

    ```
    # Add Citus repository for package manager
    curl https://install.citusdata.com/community/deb.sh | sudo bash
    # install the server and initialize db
    sudo apt-get -y install postgresql-13-citus-10.0
    # preload citus extension
    sudo pg_conftool 13 main set shared_preload_libraries citus
    sudo pg_conftool 13 main set listen_addresses '*'
    sudo vi /etc/postgresql/13/main/pg_hba.conf
    @@ -21,25 +17,22 @@ sudo vi /etc/postgresql/13/main/pg_hba.conf

    Allow Server IP to access this node
    ``
    # Allow unrestricted access to nodes in the local network. The following ranges
    # correspond to 24, 20, and 16-bit blocks in Private IPv4 address spaces.

    host all all 10.0.0.0/8 trust

    # Also allow the host unrestricted access to connect to itself
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust

    # server IP
    host all all <server-ip>/32 trust

    ``



    Install Master
    Install Steps on Cordinator
    --------------------------

    the same steps above
    repeat the same steps above.

    plus these:

  17. akhdaniel created this gist Apr 30, 2021.
    60 changes: 60 additions & 0 deletions PostgreSQL Clustering CITUS.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,60 @@
    Install Nodes:
    -----------------------------------

    ```
    # Add Citus repository for package manager
    curl https://install.citusdata.com/community/deb.sh | sudo bash
    # install the server and initialize db
    sudo apt-get -y install postgresql-13-citus-10.0
    # preload citus extension
    sudo pg_conftool 13 main set shared_preload_libraries citus
    sudo pg_conftool 13 main set listen_addresses '*'
    sudo vi /etc/postgresql/13/main/pg_hba.conf
    ```


    Allow Server IP to access this node
    ``
    # Allow unrestricted access to nodes in the local network. The following ranges
    # correspond to 24, 20, and 16-bit blocks in Private IPv4 address spaces.
    host all all 10.0.0.0/8 trust

    # Also allow the host unrestricted access to connect to itself
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust

    # server IP
    host all all <server-ip>/32 trust

    ``



    Install Master
    --------------------------

    the same steps above

    plus these:

    ```
    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!