citus 11 参考简单使用

标签：11 haproxy stats POSTGRES 参考 citus key timeout

基于官当的docker-compose 模板简单测试下citus 11 的查询特性，对于负载lb 基于了haproxy

测试说明

使用docker-compose 进行worker 节点的扩容，注意是先准备好的，实际使用的时候会基于管理sql 添加节点的

环境准备

docker-compose 文件

version: "3"
services:
  haproxy:
    build: ./
    volumes:
      - "./haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg"
    ports:
      - 5433:5433
      - 8404:8404
  master:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_master"
    image: "citusdata/citus:11.0.2"
    ports: [ "${COORDINATOR_EXTERNAL_PORT:-5432}:5432" ]
    labels: [ "com.citusdata.role=Master" ]
    environment:
      &AUTH
      POSTGRES_USER: "${POSTGRES_USER:-postgres}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD}"
      PGUSER: "${POSTGRES_USER:-postgres}"
      PGPASSWORD: "${POSTGRES_PASSWORD}"
      POSTGRES_HOST_AUTH_METHOD: "${POSTGRES_HOST_AUTH_METHOD:-trust}"
  worker:
    image: "citusdata/citus:11.0.2"
    labels: [ "com.citusdata.role=Worker" ]
    depends_on: [ manager ]
    environment: *AUTH
    command: "/wait-for-manager.sh"
    volumes:
      - healthcheck-volume:/healthcheck
  manager:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_manager"
    image: "citusdata/membership-manager:0.3.0"
    volumes:
      - "${DOCKER_SOCK:-/var/run/docker.sock}:/var/run/docker.sock"
      - healthcheck-volume:/healthcheck
    depends_on: [ master ]
    environment: *AUTH
volumes:
  healthcheck-volume:
haproxy 配置

global
    log         127.0.0.1 local2
 
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    # daemon
 
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats
 
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  tcplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000
 
resolvers mynameservers
   nameserver ns1 127.0.0.11:53
 
frontend mysite
  bind :5433
  default_backend webservers
 
backend webservers
  balance roundrobin
  mode tcp
  server web1 worker:5432 check maxconn 300 resolvers mynameservers # 基于dns 的lb,为了测试，没有添加协调节点的服务
 
frontend stats
    bind *:8404
    mode http
    stats enable
    stats uri /stats
    http-request use-service prometheus-exporter if { path /metrics }
    stats refresh 10s
    stats admin if LOCALHOST
haproxy 基础镜像

FROM dalongrong/haproxy:2.6-debian-quic

测试

启动

docker-compose up -d

worker 节点扩容
默认的member 服务会自动添加节点的

docker-compose -p citus scale worker=5

效果

SELECT master_get_active_worker_nodes();

citus 11 参考简单使用_添加节点

haproxy 监控

citus 11 参考简单使用_docker_02

创建分布式表


CREATE TABLE data (key text primary key, value jsonb);
SELECT create_distributed_table('data','key');
 
CREATE TABLE data_audit (operation text, key text, new_value jsonb, change_time timestamptz default now());
SELECT create_distributed_table('data_audit','key', colocate_with := 'data');
 
-- we know this function only writes to a co-located table using the same key
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS trigger
AS $$
DECLARE
BEGIN
    INSERT INTO data_audit VALUES (TG_OP, Coalesce(OLD.key, NEW.key), NEW.value);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- so, it is safe to enable triggers on distributed tables
SET citus.enable_unsafe_triggers TO on;
 
CREATE TRIGGER data_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON data
FOR EACH ROW EXECUTE FUNCTION audit_trigger();