[pgpool-general-jp: 1032] Re: master_slave_sub_mode = 'stream' の時にVSZが1GB以上になる現象について

Tatsuo Ishii ishii @ sraoss.co.jp
2012年 1月 24日 (火) 15:51:41 JST


青山さん

石井です。

うーん、不思議ですね。ちなみに、この状態でfreeコマンドではどのような表
示になるでしょうか?また、念の為ですが、psでの仮想メモリサイズとは一致
していますでしょうか?

あと、一度straceからpgpoolを起動して、そのログを見せていただけますか?
brk(2)の呼ばれ方を見てみたいです。
ちなみに、手元の環境(Vine 6/x86_64)だと、最後に呼ばれたbrkはこんな感じです。

brk(0x742000)                           = 0x742000
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> こんにちは。青山です。
> 
> 石井さん、レスありがとうございます。
> 
>> VSZは時間と共に増えていきますか?増えていくのであれば、メモリーリークと
>> いうことになります。また、pgpool.confを見せていただけますか?
> 
> 起動が完了した時点で、psコマンド等で確認いたしますと、pgpoolの親プロセスの
> VSZは既に1GBオーバーの状態になっています。
> 時間と共にじわじわと増えていく、という感じではありません。
> 
> ただし、起動直後のpgpoolの子プロセスのVSZは、27MB程度と表示されます。
> このため、タイミングとしては、親プロセスが子プロセスをforkした後に、1GBオーバーの
> サイズになっているのではないかと推測しています。
> 起動後しばらく運用して、子プロセスが再生成されると、その子プロセスについては
> 最初からVSZが1GBオーバーとなります。(つまり、親プロセスと同じサイズで生成
> されています。)
> 
> なお、この状態で1日程度放置しておいたところ、子プロセスの1つのVSZがとんでも
> ない値になっていました。(1テラバイトオーバーでしょうか。。。)
> 
> postgres  3903  0.0  0.3 1178760 1512 ?        Ss   Jan23   0:02
> /usr/local/pgpool2/bin/pgpool -f /usr/local/pgpool2/etc/pgpool.conf -d
> postgres  3904  0.0  0.1  27696   840 ?        S    Jan23   0:00  \_
> pgpool: wait for connection request
> postgres  3905  0.0  0.1  27696   840 ?        S    Jan23   0:00  \_
> pgpool: wait for connection request
> postgres  3906  0.0  0.1  27696   840 ?        S    Jan23   0:00  \_
> pgpool: wait for connection request
> postgres  3907  0.0  0.1  27696   840 ?        S    Jan23   0:00  \_
> pgpool: wait for connection request
> postgres  3908  0.0  0.1  27696   840 ?        S    Jan23   0:00  \_
> pgpool: wait for connection request
> postgres  3909  0.0  0.1  27696   584 ?        S    Jan23   0:00  \_
> pgpool: PCP: wait for connection request
> postgres  3910  0.0  4.6 1169531228 23456 ?    S    Jan23   0:03  \_
> pgpool: worker process
> 
> ということは、メモリリークなのでしょうか。
> 
> 以下、テストを行っている環境でのpgpool.confを記載いたします。
> テスト中なので、バックエンドは1つだけで行っています。
> 
> ------------------------------------------------------------------------------------
> # ----------------------------
> # 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 = 'localhost'
>                                    # Host name or IP address to listen on:
>                                    # '*' for all, '' for no TCP/IP connections
>                                    # (change requires restart)
> #port = 9999
> port = 5432
>                                    # Port number
>                                    # (change requires restart)
> socket_dir = '/tmp'
>                                    # 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 = '/tmp'
>                                    # Unix domain socket path for pcp
>                                    # The Debian package defaults to
>                                    # /var/run/postgresql
>                                    # (change requires restart)
> 
> # - Backend Connection Settings -
> 
> backend_hostname0 = '127.0.0.1'
>                                    # Host name or IP address to
> connect to for backend 0
> backend_port0 = 5433
>                                    # Port number for backend 0
> backend_weight0 = 1
>                                    # Weight for backend 0 (only in
> load balancing mode)
> backend_data_directory0 = '/usr/local/pgsql-9.0/data'
>                                    # Data directory for backend 0
> backend_flag0 = 'ALLOW_TO_FAILOVER'
>                                    # Controls various backend behavior
>                                    # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
> #backend_hostname1 = 'host2'
> #backend_port1 = 5433
> #backend_weight1 = 1
> #backend_data_directory1 = '/data1'
> #backend_flag1 = 'ALLOW_TO_FAILOVER'
> 
> # - 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 = './server.key'
>                                    # Path to the SSL private key file
>                                    # (change requires restart)
> #ssl_cert = './server.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 = 5
>                                    # Number of pools
>                                    # (change requires restart)
> max_pool = 4
>                                    # Number of connections per pool
>                                    # (change requires restart)
> 
> # - Life time -
> 
> child_life_time = 15
>                                    # Pool exits after being idle for
> this many seconds
> child_max_connections = 0
>                                    # Pool exits after receiving that
> many connections
>                                    # 0 means no exit
> connection_life_time = 0
>                                    # Connection to backend closes
> after being idle for this many seconds
>                                    # 0 means no close
> client_idle_limit = 0
>                                    # 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 = 'stderr'
>                                    # 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 = 'if_over_threshold'
>                                    # 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/pgpool2/pgpool.pid'
>                                    # PID file name
>                                    # (change requires restart)
> logdir = '/tmp'
>                                    # 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 = off
>                                    # 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 = off
>                                    # 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 = 'currval,lastval,nextval,setval'
>                                    # Comma separated list of function names
>                                    # that write to database
>                                    # Regexp are accepted
> 
> 
> #------------------------------------------------------------------------------
> # MASTER/SLAVE MODE
> #------------------------------------------------------------------------------
> 
> master_slave_mode = on
>                                    # Activate master/slave mode
>                                    # (change requires restart)
> #master_slave_sub_mode = 'slony'
> master_slave_sub_mode = 'stream'
>                                    # Master/slave sub mode
>                                    # Valid values are combinations slony or
>                                    # stream. Default is slony.
>                                    # (change requires restart)
> 
> # - Streaming -
> 
> sr_check_period = 10
>                                    # Streaming replication check period
>                                    # Disabled (0) by default
> sr_check_user = 'nobody'
>                                    # Streaming replication check user
>                                    # This is neccessary even if you
> disable streaming
>                                    # replication delay check by
> sr_check_period = 0
> sr_check_password = ''
>                                    # Password for streaming
> replication check user
> delay_threshold = 10000000
>                                    # 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 = ''
>                                    # 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
> 
> 
> #------------------------------------------------------------------------------
> # 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.
> ------------------------------------------------------------------------------------
> 
> 以上となります。
> 間違えている点などありましたらご指摘いただければありがたいです。
> 
> よろしくお願いいたします。
> _______________________________________________
> pgpool-general-jp mailing list
> pgpool-general-jp @ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp


pgpool-general-jp メーリングリストの案内