[pgpool-general: 2374] Re: pgpool II clustering + replication - poor performance
Tatsuo Ishii
ishii at postgresql.org
Tue Jan 7 17:35:29 JST 2014
I'm not familiar with sysbench, but I do have run pgbench against
pgpool-II cluster and got positive performance.
Here is the details:
(1) Server side. PostgreSQL and pgpool-II are running on the same box.
Up to 4 exact same boxes are prepared.
CPU :Xeon E5-2670(3.06GHz 6 cores) x 2 (12 cores)
Memory:64GB
HDD:600GB x 2
Redhat Enterprise Linux 6.2
PostgreSQL 9.2.1
pgpool-II 3.2.1
(2) pgbench side. Pgbench runs on following machine. Up to 4 exact
same boxes are prepared.
Xeon X5576 (3.06GHz 6 cores) x2 (12 cores)
Memory: 16GB
(3) pgbench DB
scale factor is 1000 (15GB database). pgbench runs as follows:
pgbench -c 100 -t 300 -j 20 -f ro_script -n
Where "ro_script" is as follows:
¥set nbranches :scale
¥set ntellers 10 * :scale
¥set naccounts 100000 * :scale
¥set range 2000
¥set aidmax :naccounts - :range
¥setrandom aid 1 :aidmax
¥setrandom bid 1 :nbranches
¥setrandom tid 1 :ntellers
¥setrandom delta -5000 5000
SELECT count(abalance) FROM
pgbench_accounts WHERE aid
BETWEEN :aid and :aid + :range;
(4) The result
Non cluster TPS: 28,564
1 node cluster 23,554 (82% of non cluster)
2 nodes cluster 47,152 (165% of non cluster)
3 nodes cluster 71,166 (249% of non cluster)
4 nodes cluster 94,876 (332% of non cluster)
All cluster TPS are sum of each pgpool-II+PostgreSQL+pgbench sets.
My guess is, pgbench -S produces too light load. That's the reason why
we created a custom script.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Hello,
>
> I'm trying to implement pgpool-II clustering + replication and am
> experiencing *very* poor performance.
>
> Any assistance would be greatly appreciated.
>
> *Description*:
>
> When pgpool is in front of PostgreSQL there is a significant loss in
> performance.
>
> *Troubleshooting*:
>
> 1) Try different times of benchmarks:
> * I have run pgbench (with and without -C), sysbench and real world SELECT
> statements.
>
> 2) Adjustment to connections, this hasn't made any noticeable performance
> difference from defaults.
>
> 3) Run pgpool with a single PostgreSQL server rather than a cluster of two.
> * I experience the exact same performance with a single node + pgpool as
> with a two node cluster.
>
> 2) Monitor hardware / VM performance:
> * No iowait showing in vmstat
> * CPU 1 is around 90%, 2 around 70%, 3 around 50% etc...
>
>
> *Environment*:
>
> *Server*: Debian 7, x64 (Virtualised on XenServer 6.2), 8x Xen E5 2560
> vCores, 8GB ECC RAM
> *PG version*: PostgreSQL 9.3
> *PG-Pool Package*: 3.3.1-1.pgdg70+1
> *Storage*: iSCSI SAN (Shared), 12x 15K SAS Spindles, 10GbE connection
> *Network*: 2x 10GbE
> *Layout*: 2x PostgreSQL servers (int-pgca-01, int-pgca-02), pgpool
> installed on the same server, *port 5432 is directly to PostgreSQL*, *5433
> is to pgpool*
>
> *Performance Information*:
>
> # Clustered Test
>
> postgres at int-pgca-01:~$ pgbench -j 8 -T 120 -c 16 -S -p 5433
> bench_replication
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 75
> query mode: simple
> number of clients: 16
> number of threads: 8
> duration: 120 s
> number of transactions actually processed: 3749277
> tps = 31243.529780 (including connections establishing)
> tps = 31254.610781 (excluding connections establishing)
>
> # Unclustered Test
>
> postgres at int-pgca-01:~$ pgbench -j 8 -T 120 -c 16 -S -p 5432
> bench_replication
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 75
> query mode: simple
> number of clients: 16
> number of threads: 8
> duration: 120 s
> number of transactions actually processed: 6268572
> tps = 52237.871024 (including connections establishing)
> tps = 52253.665169 (excluding connections establishing)
>
>
> --
>
> # Clustered Test
>
> root at int-pgca-01:~/0.4/sysbench# ./sysbench --pgsql-user=postgres
> --pgsql-password=password --pgsql-port=5433 --pgsql-db=sysbench
> --pgsql-host=localhost --db-driver=pgsql --oltp-read-only=on
> --oltp-test-mode=simple --init-rng --max-requests=0 --max-time=120
> --num-threads=8 run --test=oltp
> sysbench 0.4.12: multi-threaded system evaluation benchmark
>
> Running the test with following options:
> Number of threads: 8
> Initializing random number generator from timer.
>
> Random number generator seed is 0 and will be ignored
>
>
> Doing OLTP test.
> Running simple OLTP test
> Doing read-only test
> Using Special distribution (12 iterations, 1 pct of values are returned in
> 75 pct cases)
> Using "BEGIN" for starting transactions
> Using auto_inc on the id column
> Threads started!
> Time limit exceeded, exiting...
> (last message repeated 7 times)
> Done.
>
> OLTP test statistics:
> queries performed:
> read: 683690
> write: 0
> other: 0
> total: 683690
> transactions: 683690 (5697.35 per sec.)
> deadlocks: 0 (0.00 per sec.)
> read/write requests: 683690 (5697.35 per sec.)
> other operations: 0 (0.00 per sec.)
>
> General statistics:
> total time: 120.0013s
> total number of events: 683690
> total time taken by event execution: 953.1451
> response time:
> min: 0.44ms
> avg: 1.39ms
> max: 43.94ms
> approx. 95 percentile: 2.28ms
>
> Threads fairness:
> events (avg/stddev): 85461.2500/27464.93
> execution time (avg/stddev): 119.1431/0.27
>
>
>
> # Unclustered Test
>
> root at int-pgca-01:~/0.4/sysbench# ./sysbench --pgsql-user=postgres
> --pgsql-password=password --pgsql-port=5432 --pgsql-db=sysbench
> --pgsql-host=localhost --db-driver=pgsql --oltp-read-only=on
> --oltp-test-mode=simple --init-rng --max-requests=0 --max-time=120
> --num-threads=8 run --test=oltp
> sysbench 0.4.12: multi-threaded system evaluation benchmark
>
> Running the test with following options:
> Number of threads: 8
> Initializing random number generator from timer.
>
> Random number generator seed is 0 and will be ignored
>
>
> Doing OLTP test.
> Running simple OLTP test
> Doing read-only test
> Using Special distribution (12 iterations, 1 pct of values are returned in
> 75 pct cases)
> Using "BEGIN" for starting transactions
> Using auto_inc on the id column
> Threads started!
> Time limit exceeded, exiting...
> (last message repeated 7 times)
> Done.
>
> OLTP test statistics:
> queries performed:
> read: 5037163
> write: 0
> other: 0
> total: 5037163
> transactions: 5037163 (41976.19 per sec.)
> deadlocks: 0 (0.00 per sec.)
> read/write requests: 5037163 (41976.19 per sec.)
> other operations: 0 (0.00 per sec.)
>
> General statistics:
> total time: 120.0005s
> total number of events: 5037163
> total time taken by event execution: 922.9731
> response time:
> min: 0.12ms
> avg: 0.18ms
> max: 7.78ms
> approx. 95 percentile: 0.25ms
>
> Threads fairness:
> events (avg/stddev): 629645.3750/1562.43
> execution time (avg/stddev): 115.3716/0.02
>
>
> *Configuration Information*:
>
> # ----------------------------
> # pgPool-II configuration file
> # ----------------------------
> #
> # This file consists of lines of the form:
> #
> # name = value
> #
> # Whitespace may be used. Comments are introduced with "#" anywhere on a
> line.
> # The complete list of parameter names and allowed values can be found in
> the
> # pgPool-II documentation.
> #
> # This file is read on server startup and when the server receives a SIGHUP
> # signal. If you edit the file on a running system, you have to SIGHUP the
> # server for the changes to take effect, or use "pgpool reload". Some
> # parameters, which are marked below, require a server shutdown and restart
> to
> # take effect.
> #
>
>
> #------------------------------------------------------------------------------
> # CONNECTIONS
> #------------------------------------------------------------------------------
>
> # - pgpool Connection Settings -
>
> listen_addresses = '*'
> # Host name or IP address to listen on:
> # '*' for all, '' for no TCP/IP
> connections
> # (change requires restart)
> port = 5433
> # Port number
> # (change requires restart)
> socket_dir = '/var/run/postgresql'
> # Unix domain socket path
> # The Debian package defaults to
> # /var/run/postgresql
> # (change requires restart)
>
>
> # - pgpool Communication Manager Connection Settings -
>
> pcp_port = 9898
> # Port number for pcp
> # (change requires restart)
> pcp_socket_dir = '/var/run/postgresql'
> # Unix domain socket path for pcp
> # The Debian package defaults to
> # /var/run/postgresql
> # (change requires restart)
>
> # - Backend Connection Settings -
>
> backend_hostname0 = 'int-pgca-01'
> backend_port0 = 5432
> backend_weight0 = 1
>
> backend_data_directory0 = '/data'
> backend_port0 = 5432
> backend_weight0 = 1
>
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> backend_port0 = 5432
> backend_weight0 = 1
>
>
> # - Authentication -
>
> enable_pool_hba = off
> # Use pool_hba.conf for client
> authentication
> authentication_timeout = 60
> # Delay in seconds to complete client
> authentication
> # 0 means no timeout.
>
> # - SSL Connections -
>
> ssl = off
> # Enable SSL support
> # (change requires restart)
> ssl_key = ''
> # Path to the SSL private key file
> # (change requires restart)
> ssl_cert = ''
> # Path to the SSL public certificate file
> # (change requires restart)
> ssl_ca_cert = ''
> # Path to a single PEM format file
> # containing CA root certificate(s)
> # (change requires restart)
> ssl_ca_cert_dir = ''
> # Directory containing CA root
> certificate(s)
> # (change requires restart)
>
>
> #------------------------------------------------------------------------------
> # POOLS
> #------------------------------------------------------------------------------
>
> # - Pool size -
>
> num_init_children = 64
> # Number of pools
> # (change requires restart)
> max_pool = 8
> # Number of connections per pool
> # (change requires restart)
>
> # - Life time -
>
> child_life_time = 300
> # Pool exits after being idle for this
> many seconds
> child_max_connections = 200
> # Pool exits after receiving that many
> connections
> # 0 means no exit
> connection_life_time = 120
> # Connection to backend closes after
> being idle for this many seconds
> # 0 means no close
> client_idle_limit = 25
> # Client is disconnected after being
> idle for that many seconds
> # (even inside an explicit transactions!)
> # 0 means no disconnection
>
>
> #------------------------------------------------------------------------------
> # LOGS
> #------------------------------------------------------------------------------
>
> # - Where to log -
>
> log_destination = 'syslog'
> # Where to log
> # Valid values are combinations of
> stderr,
> # and syslog. Default to stderr.
>
> # - What to log -
>
> print_timestamp = on
> # Print timestamp on each line
> # (change requires restart)
>
> log_connections = off
> # Log connections
> log_hostname = off
> # Hostname will be shown in ps status
> # and in logs if connections are logged
> log_statement = off
> # Log all statements
> log_per_node_statement = off
> # Log all statements
> # with node and backend informations
> log_standby_delay = 'none'
> # Log standby delay
> # Valid values are combinations of
> always,
> # if_over_threshold, none
>
> # - Syslog specific -
>
> syslog_facility = 'LOCAL0'
> # Syslog local facility. Default to
> LOCAL0
> syslog_ident = 'pgpool'
> # Syslog program identification string
> # Default to 'pgpool'
>
> # - Debug -
>
> debug_level = 0
> # Debug message verbosity level
> # 0 means no message, 1 or more mean
> verbose
>
>
> #------------------------------------------------------------------------------
> # FILE LOCATIONS
> #------------------------------------------------------------------------------
>
> pid_file_name = '/var/run/postgresql/pgpool.pid'
> # PID file name
> # (change requires restart)
> logdir = '/var/log/postgresql'
> # Directory of pgPool status file
> # (change requires restart)
>
>
> #------------------------------------------------------------------------------
> # CONNECTION POOLING
> #------------------------------------------------------------------------------
>
> connection_cache = on
> # Activate connection pools
> # (change requires restart)
>
> # Semicolon separated list of queries
> # to be issued at the end of a session
> # The default is for 8.3 and later
> reset_query_list = 'ABORT; DISCARD ALL'
> # The following one is for 8.2 and before
> #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
>
>
> #------------------------------------------------------------------------------
> # REPLICATION MODE
> #------------------------------------------------------------------------------
>
> replication_mode = on
> # Activate replication mode
> # (change requires restart)
> replicate_select = off
> # Replicate SELECT statements
> # when in replication or parallel mode
> # replicate_select is higher priority
> than
> # load_balance_mode.
>
> insert_lock = on
> # Automatically locks a dummy row or a
> table
> # with INSERT statements to keep SERIAL
> data
> # consistency
> # Without SERIAL, no lock will be issued
> lobj_lock_table = ''
> # When rewriting lo_creat command in
> # replication mode, specify table name to
> # lock
>
> # - Degenerate handling -
>
> replication_stop_on_mismatch = off
> # On disagreement with the packet kind
> # sent from backend, degenerate the node
> # which is most likely "minority"
> # If off, just force to exit this session
>
> failover_if_affected_tuples_mismatch = off
> # On disagreement with the number of
> affected
> # tuples in UPDATE/DELETE queries, then
> # degenerate the node which is most
> likely
> # "minority".
> # If off, just abort the transaction to
> # keep the consistency
>
>
> #------------------------------------------------------------------------------
> # LOAD BALANCING MODE
> #------------------------------------------------------------------------------
>
> load_balance_mode = on
> # Activate load balancing mode
> # (change requires restart)
> ignore_leading_white_space = on
> # Ignore leading white spaces of each
> query
> white_function_list = ''
> # Comma separated list of function names
> # that don't write to database
> # Regexp are accepted
> black_function_list = 'nextval,setval'
> # Comma separated list of function names
> # that write to database
> # Regexp are accepted
>
>
> #------------------------------------------------------------------------------
> # MASTER/SLAVE MODE
> #------------------------------------------------------------------------------
>
> master_slave_mode = off
> # Activate master/slave mode
> # (change requires restart)
> master_slave_sub_mode = 'slony'
> # Master/slave sub mode
> # Valid values are combinations slony or
> # stream. Default is slony.
> # (change requires restart)
>
> # - Streaming -
>
> sr_check_period = 0
> # Streaming replication check period
> # Disabled (0) by default
> sr_check_user = 'nobody'
> # Streaming replication check user
> # This is necessary even if you disable
> # streaming replication delay check with
> # sr_check_period = 0
> sr_check_password = ''
> # Password for streaming replication
> check user
> delay_threshold = 0
> # Threshold before not dispatching query
> to standby node
> # Unit is in bytes
> # Disabled (0) by default
>
> # - Special commands -
>
> follow_master_command = ''
> # Executes this command after master
> failover
> # Special values:
> # %d = node id
> # %h = host name
> # %p = port number
> # %D = database cluster path
> # %m = new master node id
> # %H = hostname of the new master node
> # %M = old master node id
> # %P = old primary node id
> # %% = '%' character
>
>
> #------------------------------------------------------------------------------
> # PARALLEL MODE AND QUERY CACHE
> #------------------------------------------------------------------------------
>
> parallel_mode = off
> # Activates parallel query mode
> # (change requires restart)
> enable_query_cache = off
> # Activates query cache
> # (change requires restart)
>
> pgpool2_hostname = ''
> # Set pgpool2 hostname
> # (change requires restart)
>
> # - System DB info -
>
> system_db_hostname = 'localhost'
> # (change requires restart)
> system_db_port = 5432
> # (change requires restart)
> system_db_dbname = 'pgpool'
> # (change requires restart)
> system_db_schema = 'pgpool_catalog'
> # (change requires restart)
> system_db_user = 'pgpool'
> # (change requires restart)
> system_db_password = ''
> # (change requires restart)
>
>
> #------------------------------------------------------------------------------
> # HEALTH CHECK
> #------------------------------------------------------------------------------
>
> health_check_period = 0
> # Health check period
> # Disabled (0) by default
> health_check_timeout = 20
> # Health check timeout
> # 0 means no timeout
> health_check_user = 'nobody'
> # Health check user
> health_check_password = ''
> # This parameter is not yet implemented.
> # Password for health check user
>
>
> #------------------------------------------------------------------------------
> # FAILOVER AND FAILBACK
> #------------------------------------------------------------------------------
>
> failover_command = ''
> # Executes this command at failover
> # Special values:
> # %d = node id
> # %h = host name
> # %p = port number
> # %D = database cluster path
> # %m = new master node id
> # %H = hostname of the new master node
> # %M = old master node id
> # %P = old primary node id
> # %% = '%' character
> failback_command = ''
> # Executes this command at failback.
> # Special values:
> # %d = node id
> # %h = host name
> # %p = port number
> # %D = database cluster path
> # %m = new master node id
> # %H = hostname of the new master node
> # %M = old master node id
> # %P = old primary node id
> # %% = '%' character
>
> fail_over_on_backend_error = on
> # Initiates failover when writing to the
> # backend communication socket fails
> # This is the same behaviour of pgpool-II
> # 2.2.x and previous releases
> # If set to off, pgpool will report an
> # error and disconnect the session.
>
>
> #------------------------------------------------------------------------------
> # ONLINE RECOVERY
> #------------------------------------------------------------------------------
>
> recovery_user = 'nobody'
> # Online recovery user
> recovery_password = ''
> # Online recovery password
> recovery_1st_stage_command = ''
> # Executes a command in first stage
> recovery_2nd_stage_command = ''
> # Executes a command in second stage
> recovery_timeout = 90
> # Timeout in seconds to wait for the
> # recovering node's postmaster to start
> up
> # 0 means no wait
> client_idle_limit_in_recovery = 0
> # Client is disconnected after being idle
> # for that many seconds in the second
> stage
> # of online recovery
> # 0 means no disconnection
> # -1 means immediate disconnection
>
> #------------------------------------------------------------------------------
> # MEMORY CACHE
> #------------------------------------------------------------------------------
>
>
> memory_cache_enable = on
>
> memqcache_method = 'shmem'
>
> memqcache_expire = 0
>
> memqcache_auto_cache_invalidation = on
>
> memqcache_maxcache = 1
> #white_memqcache_table_list
> #black_memqcache_table_list
> #memqcache_oiddir
>
>
> #------------------------------------------------------------------------------
> # OTHERS
> #------------------------------------------------------------------------------
>
> relcache_expire = 0
> # Life time of relation cache in seconds.
> # 0 means no cache expiration(the
> default).
> # The relation cache is used for cache
> the
> # query result against PostgreSQL system
> # catalog to obtain various information
> # including table structures or if it's a
> # temporary table or not. The cache is
> # maintained in a pgpool child local
> memory
> # and being kept as long as it survives.
> # If someone modify the table by using
> # ALTER TABLE or some such, the relcache
> is
> # not consistent anymore.
> # For this purpose, cache_expiration
> # controls the life time of the cache.
More information about the pgpool-general
mailing list