Обсуждение: performance degradation after launching postgres cluster using pgpool-II
Hi, I am a freshman to postgresql, also pgpool-II. I have some performance issues once I bring in the pgpool-II to build the pg cluster. Here I post some system info and the configurations of postgresql and pgpool, hopping you can help me to solve this problem. BTW, I am using the postgres 9.2.4 installed on the Amazon AWS with debian OS(64bits), and the version of pgpool is 3.2.7. There are two nodes in the cluster, working in master/slave mode and replicating data using the streaming replication feature. Mem info: cora@apollo:~$ free total used free shared buffers cached Mem: 31446876 7625428 23821448 0 9468 6312080 -/+ buffers/cache: 1303880 30142996 Swap: 0 0 0 Linux info: cora@apollo:~$ cat /proc/version Linux version 2.6.32-5-xen-amd64 (Debian 2.6.32-48squeeze1) (dannf@debian.org) (gcc version 4.3.5 (Debian 4.3.5-4) ) #1 SMP Mon Feb 25 02:51:39 UTC 2013 The settings of postgresql(only showing the non-default parameters) listen_addresses = '*' port = 9797 max_connections = 750 ssl_renegotiation_limit = 0 shared_buffers = 15GB temp_buffers = 32MB work_mem = 64MB maintenance_work_mem = 128MB effective_io_concurrency = 1000 wal_level = hot_standby checkpoint_segments = 32 archive_mode = on archive_command = 'rsync -a %p apollo:/var/lib/postgresql/9.2/archive/%f </dev/null' max_wal_senders = 1 wal_keep_segments = 32 hot_standby = on enable_indexscan = on enable_seqscan = on random_page_cost = 2.0 effective_cache_size = 5GB default_statistics_target = 10000 constraint_exclusion = on autovacuum = on log_autovacuum_min_duration = 100 autovacuum_max_workers = 6 autovacuum_naptime = 30min autovacuum_vacuum_threshold = 1000 autovacuum_analyze_threshold = 5000 autovacuum_vacuum_scale_factor = 0.2 autovacuum_analyze_scale_factor = 0.1 autovacuum_freeze_max_age = 200000000 autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = -1 hot_standby_feedback = off The configuration of pgpool-II listen_addresses = '*' port = 5432 socket_dir = '/var/run/pgpool2' pcp_port = 9898 pcp_socket_dir = '/var/run/pgpool2' backend_hostname0 = 'apollo' backend_port0 = 9797 backend_weight0 = 1 backend_data_directory0 = '/var/lib/postgresql/9.2/main' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'apollo2' backend_port1 = 9797 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/9.2/main' backend_flag1 = 'ALLOW_TO_FAILOVER' enable_pool_hba = on pool_passwd = 'pool_passwd' ssl = off num_init_children = 32 max_pool = 5 child_life_time = 0 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 debug_level = 0 pid_file_name = '/var/run/pgpool2/pgpool.pid' logdir = '/var/log/pgpool2' connection_cache = off replication_mode = off insert_lock = off replicate_select = off load_balance_mode = on ignore_leading_white_space = on white_function_list = 'foo' black_function_list = '' master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_user = 'postgres' sr_check_password = '×××' delay_threshold = 0 health_check_period = 10 health_check_timeout = 20 failover_command = '/var/lib/postgresql/9.2/main/failover.sh %d "%h" %p %D %m %M "%H" %P' failback_command = '/bin/rm -f /tmp/trigger_file0' recovery_user = 'postgres' recovery_password = 'postgres' recovery_1st_stage_command = 'basebackup.sh' use_watchdog = off At first, I turn on the hot_standby_feedback, but after getting customers' complaint about the poor performance, I decide to turn off, but it seems that it didn't help. Before starting the cluster, one bulk updates through java code would cost about 1 hour to finish, but then it would take twice amount of time. So many thanks for giving your advises... -- View this message in context: http://postgresql.1045698.n5.nabble.com/performance-degradation-after-launching-postgres-cluster-using-pgpool-II-tp5797953.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
> Before starting the cluster, one bulk updates through java code would cost > about 1 hour to finish, but then it would take twice amount of time. pgpool-II is not very good at handling extended protocol (mostly used in Java). If you need to execute large updates, you'd better to connect to PostgreSQL directly. Note that there's no problem that some sessions connect via pgpool-II, while others directly connect PostgreSQL. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Feeling a little sorry about replying you later.
Then, I want to say so many thanks for this advice, cause we pointed our jobserver to the master directly and then the performance issues resolved. My client is so curious that why pgpool doesn't support so welcome platform like java. Hoping it can get improved in the following versions.
So, besides pgpool, do you have any other solutions for postgresql cluster?
Thanks a lot,
Cora
Date: Mon, 7 Apr 2014 06:47:13 -0700
From: [hidden email]
To: [hidden email]
Subject: Re: performance degradation after launching postgres cluster using pgpool-II
> Before starting the cluster, one bulk updates through java code would cost
> about 1 hour to finish, but then it would take twice amount of time.
pgpool-II is not very good at handling extended protocol (mostly used
in Java). If you need to execute large updates, you'd better to
connect to PostgreSQL directly. Note that there's no problem that some
sessions connect via pgpool-II, while others directly connect
PostgreSQL.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Date: Mon, 7 Apr 2014 06:47:13 -0700
From: [hidden email]
To: [hidden email]
Subject: Re: performance degradation after launching postgres cluster using pgpool-II
> Before starting the cluster, one bulk updates through java code would cost
> about 1 hour to finish, but then it would take twice amount of time.
pgpool-II is not very good at handling extended protocol (mostly used
in Java). If you need to execute large updates, you'd better to
connect to PostgreSQL directly. Note that there's no problem that some
sessions connect via pgpool-II, while others directly connect
PostgreSQL.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/performance-degradation-after-launching-postgres-cluster-using-pgpool-II-tp5797953p5798972.html To unsubscribe from performance degradation after launching postgres cluster using pgpool-II, click here.
NAML
NAML
View this message in context: RE: performance degradation after launching postgres cluster using pgpool-II
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.