[pgpool-general: 3771] Re: Memcache_total_size size

Tatsuo Ishii ishii at postgresql.org
Sat May 30 10:02:19 JST 2015 

Thanks for the offering. Attached is the patch. The patch is against
master branch, but can be applied to 3.4 (stable). If you want to test
with 3.3, please let me know.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> I have a machine with 32 GB and I'd like to use about 80% of it with cache.
> 
> Please let me know when you have a new version of pgpool enabling higher cache value.
> 
> =============================================================
> M.Sc. José Renato Castro Milanez
> Analista de Tecnologia da Informação/TI Analist
> Diretoria de Suporte a Informática/Computer Support Direction
> Universidade Federal de Itajubá/Federal University of Itajubá
> Itajubá - Minas Gerais - Brasil
> Telefone/Phone (55) (35) 3629-1643
> E-mail jrcmilanez at unifei.edu.br - Skype jrcmilanez
> =============================================================
> 
> 
> ----- Mensagem original -----
> De: pgpool-general-request at pgpool.net
> Para: pgpool-general at pgpool.net
> Enviadas: Sexta-feira, 29 de maio de 2015 0:00:04
> Assunto: pgpool-general Digest, Vol 43, Issue 34
> 
> Send pgpool-general mailing list submissions to
> 	pgpool-general at pgpool.net
> 
> To subscribe or unsubscribe via the World Wide Web, visit
> 	http://www.sraoss.jp/mailman/listinfo/pgpool-general
> or, via email, send a message with subject or body 'help' to
> 	pgpool-general-request at pgpool.net
> 
> You can reach the person managing the list at
> 	pgpool-general-owner at pgpool.net
> 
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of pgpool-general digest..."
> 
> 
> Today's Topics:
> 
>    1. [pgpool-general: 3767] Re: Connection Interrupted (Tatsuo Ishii)
>    2. [pgpool-general: 3768] Re: Memcache_total_size size (Tatsuo Ishii)
>    3. [pgpool-general: 3769] Re: Memcache_total_size size
>       (Sajeev Mayandi)
> 
> 
> ----------------------------------------------------------------------
> 
> Message: 1
> Date: Fri, 29 May 2015 07:49:08 +0900 (JST)
> From: Tatsuo Ishii <ishii at postgresql.org>
> To: m.usama at gmail.com
> Cc: pgpool-general at pgpool.net
> Subject: [pgpool-general: 3767] Re: Connection Interrupted
> Message-ID: <20150529.074908.1567420765914507289.t-ishii at sraoss.co.jp>
> Content-Type: Text/Plain; charset=us-ascii
> 
> Usama,
> 
> Thanks for enhancing my fix. I will commit the patch to master and
> V3_4_STABLE.  BTW I got similar report from a 3.3 user and created
> patch for 3.3 stable (mostly same as the patch for master and 3.4). I
> will commit that to 3.3 stable tree as well.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Hi Tatsuo
>> 
>> My conclusion is same as yours for this issue. And I think your patch will
>> fix the problem. I think as part of this patch, adding the error string to
>> the error log if select () call fails will be helpful. Please find the
>> updated patch which just adds the strerror() to the log message reporting
>> the select call failure. Rest of the patch is unchanged.
>> 
>> Thanks
>> Best regards
>> Muhammad Usama
>> 
>> 
>> On Thu, May 28, 2015 at 1:29 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 
>>> Sorry I forgot to attach the patch.
>>>
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp
>>>
>>> > I suspect while the health check routine tries to connect to backend,
>>> > select(2) was interrupted by certain signal. The signal could be
>>> > SIGCHLD, since you have pretty large number (2000) of child process,
>>> > plus you enable child_life_time (300). This means that after pgpool
>>> > becomes idle, child process starts to exit within 0.15 second (=
>>> > 300/2000) in average. If health check is executed (which is every 10
>>> > seconds) at the same time when child exits, select(2) will be
>>> > interrupted.
>>> >
>>> > Fix is, check if the signal is other than SIGALRM (which is used for
>>> > health check by the health check routine itself). If not, just retries
>>> > the select(2).
>>> >
>>> > Attached patch implements it.
>>> >
>>> > Best regards,
>>> > --
>>> > Tatsuo Ishii
>>> > SRA OSS, Inc. Japan
>>> > English: http://www.sraoss.co.jp/index_en.php
>>> > Japanese:http://www.sraoss.co.jp
>>> >
>>> >> Hi Tatsuo
>>> >>
>>> >> Sorry for the late reply. Attached pgpool.cnf, but also in text below
>>> for mailing list.
>>> >>
>>> >>
>>> >> Postgres: 9.3.5
>>> >>
>>> >> Pgpool version: pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64 (from rpm -qa)
>>> >>
>>> >>
>>> >> Pgpool.conf (with out comments)
>>> >>
>>> >> listen_addresses = '*'
>>> >> port = 9999
>>> >> socket_dir = '/tmp'
>>> >> listen_backlog_multiplier = 2
>>> >> pcp_listen_addresses = '*'
>>> >> pcp_port = 9898
>>> >> pcp_socket_dir = '/tmp'
>>> >> backend_hostname0 = 'X.X.X.50'
>>> >> backend_port0 = 5432
>>> >> backend_weight0 = 4
>>> >> backend_data_directory0 = '/var/lib/pgsql/9.3/data'
>>> >> backend_flag0 = 'ALLOW_TO_FAILOVER'
>>> >> backend_hostname1 = 'X.X.X.51'
>>> >> backend_port1 = 5432
>>> >> backend_weight1 = 3
>>> >> backend_data_directory1 = '/var/lib/pgsql/9.3/data'
>>> >> backend_flag1 = 'ALLOW_TO_FAILOVER'
>>> >> backend_hostname2 = 'X.X.X.52'
>>> >> backend_port2 = 5432
>>> >> backend_weight2 = 3
>>> >> backend_data_directory2 = '/var/lib/pgsql/9.3/data'
>>> >> backend_flag2 = 'ALLOW_TO_FAILOVER'
>>> >> enable_pool_hba = on
>>> >> pool_passwd = 'pool_passwd'
>>> >> authentication_timeout = 60
>>> >> ssl = off
>>> >> num_init_children = 2000
>>> >> max_pool = 4
>>> >> child_life_time = 300
>>> >> child_max_connections = 0
>>> >> connection_life_time = 0
>>> >> client_idle_limit = 0
>>> >> log_destination = 'stderr'
>>> >> log_line_prefix = '%p %t '
>>> >> log_standby_delay = 'none'
>>> >> syslog_facility = 'LOCAL0'
>>> >> syslog_ident = 'pgpool'
>>> >> debug_level = 0
>>> >> pid_file_name = '/tmp/pgpool/pgpool.pid'
>>> >> logdir = '/var/log/pgpool'
>>> >> connection_cache = on
>>> >> reset_query_list = 'ABORT; DISCARD ALL'
>>> >> replication_mode = off
>>> >> replicate_select = off
>>> >> insert_lock = on
>>> >> lobj_lock_table = ''
>>> >>
>>> >>
>>> >> replication_stop_on_mismatch = off
>>> >>
>>> >> failover_if_affected_tuples_mismatch = off
>>> >>
>>> >>
>>> >>
>>> >> load_balance_mode = on
>>> >> ignore_leading_white_space = on
>>> >> white_function_list = ''
>>> >> black_function_list =
>>> 'breadcrumbtrails,f_round,t_round,fn_CalculateUserStatement,nextval,setval,nextval,setval,fn_settierlili,fn_GetImageForCategory,tblSessions,articles,fn_ManagerPoints,process,fn.*,fn_.*,fn_RepReport'
>>> >>
>>> >> database_redirect_preference_list = 'otrstest:primary'
>>> >>
>>> >> app_name_redirect_preference_list = ''
>>> >> allow_sql_comments = off
>>> >>
>>> >>
>>> >> master_slave_mode = on
>>> >> master_slave_sub_mode = 'stream'
>>> >>
>>> >>
>>> >> sr_check_period = 10
>>> >> sr_check_user = 'postgres'
>>> >> sr_check_password = ''
>>> >> delay_threshold = 1024
>>> >>
>>> >>
>>> >> follow_master_command = '/etc/pgpool-II/follow_master.sh %d %M %h'
>>> >>
>>> >>
>>> >> health_check_period = 10
>>> >> health_check_timeout = 20
>>> >> health_check_user = 'postgres'
>>> >> health_check_password = ''
>>> >> health_check_max_retries = 2
>>> >> health_check_retry_delay = 1
>>> >> connect_timeout = 10000
>>> >>
>>> >>
>>> >> failover_command = '/etc/pgpool-II/failover.sh %d %P %H %R'
>>> >> failback_command = ''
>>> >>
>>> >> fail_over_on_backend_error = on
>>> >>
>>> >> search_primary_node_timeout = 10
>>> >>
>>> >>
>>> >> recovery_user = 'postgres'
>>> >> recovery_password = ''
>>> >> recovery_1st_stage_command = 'recovery_1st_stage'
>>> >> recovery_2nd_stage_command = ''
>>> >> recovery_timeout = 90
>>> >> client_idle_limit_in_recovery = 0
>>> >>
>>> >>
>>> >>
>>> >>
>>> >> use_watchdog = on
>>> >>
>>> >>
>>> >> trusted_servers = ''
>>> >> ping_path = '/home/apache/sbin'
>>> >>
>>> >>
>>> >> wd_hostname = 'UI-IS-POOL1'
>>> >> wd_port = 9000
>>> >> wd_authkey = ''
>>> >>
>>> >>
>>> >> delegate_IP = 'X.X.X.60'
>>> >> ifconfig_path = '/home/apache/sbin'
>>> >> if_up_cmd = 'ifconfig ens32:0 inet $_IP_$ netmask 255.255.255.0'
>>> >> if_down_cmd = 'ifconfig ens32:0 down'
>>> >>
>>> >>
>>> >> arping_cmd = 'arping -U $_IP_$ -w 1'
>>> >>
>>> >>
>>> >> clear_memqcache_on_escalation = on
>>> >> wd_escalation_command = ''
>>> >>
>>> >>
>>> >>
>>> >> wd_lifecheck_method = 'heartbeat'
>>> >> wd_interval = 3
>>> >>
>>> >>
>>> >> wd_heartbeat_port = 9694
>>> >> wd_heartbeat_keepalive = 2
>>> >> wd_heartbeat_deadtime = 30
>>> >>
>>> >> heartbeat_destination0 = 'X.X.X.61'
>>> >> heartbeat_destination_port0 = 9694
>>> >> heartbeat_device0 = ''
>>> >> heartbeat_destination1 = 'X.X.X.62'
>>> >> heartbeat_destination_port1 = 9694
>>> >> heartbeat_device1 = ''
>>> >>
>>> >>
>>> >> wd_life_point = 3
>>> >> wd_lifecheck_query = 'SELECT 1'
>>> >> wd_lifecheck_dbname = 'template1'
>>> >> wd_lifecheck_user = 'postgres'
>>> >> wd_lifecheck_password = ''
>>> >>
>>> >>
>>> >> other_pgpool_hostname0 = 'X.X.X.62'
>>> >> other_pgpool_port0 = 9999
>>> >> other_wd_port0 = 9000
>>> >>
>>> >>
>>> >> relcache_expire = 0
>>> >>
>>> >> relcache_size = 256
>>> >>
>>> >> check_temp_table = on
>>> >>
>>> >> check_unlogged_table = off
>>> >>
>>> >> memory_cache_enabled = off
>>> >> memqcache_method = 'shmem'
>>> >> memqcache_memcached_host = 'localhost'
>>> >> memqcache_memcached_port = 11211
>>> >> memqcache_total_size = 67108864
>>> >> memqcache_max_num_cache = 1000000
>>> >> memqcache_expire = 0
>>> >> memqcache_auto_cache_invalidation = on
>>> >> memqcache_maxcache = 409600
>>> >> memqcache_cache_block_size = 1048576
>>> >> memqcache_oiddir = '/var/log/pgpool/oiddir'
>>> >> white_memqcache_table_list = ''
>>> >> black_memqcache_table_list = ''
>>> >>
>>> >>
>>> >>
>>> >> Robert Kennedy| Software & Systems Developer|
>>> >>
>>> >> Tel: (011) 557-5700  | Ext: 011 557 5729    | Cell: 084 998 9850
>>> >>
>>> >>  Email : robert at uwiniwin.co.za | Site: www.uwiniwin.co.za
>>> >> Address : The Oval Office Park , Ground Floor Kingsmead, Meadowbrook
>>> Lane, Bryanston
>>> >> -----Original Message-----
>>> >> From: Tatsuo Ishii [mailto:ishii at postgresql.org]
>>> >> Sent: Wednesday, May 27, 2015 11:11 AM
>>> >> To: Robert Kennedy
>>> >> Cc: pgpool-general at pgpool.net
>>> >> Subject: Re: [pgpool-general: 3756] Connection Interrupted
>>> >>
>>> >> Can you please provide details:
>>> >>
>>> >> - pgpool-II version (and RPM version if any)
>>> >> - PostgreSQL version
>>> >> - pgpool.conf
>>> >>
>>> >> Best regards,
>>> >> --
>>> >> Tatsuo Ishii
>>> >> SRA OSS, Inc. Japan
>>> >> English: http://www.sraoss.co.jp/index_en.php
>>> >> Japanese:http://www.sraoss.co.jp
>>> >>
>>> >>> Hi Guys
>>> >>>
>>> >>> I keep getting this error randomly appearing in my logs.
>>> >>>
>>> >>> 23734 2015-05-27 10:45:52 LOG:  failed to connect to PostgreSQL server
>>> on "x.x.x.x:5432" using INET socket
>>> >>> 23734 2015-05-27 10:45:52 DETAIL:  select() system call interrupted
>>> >>> 23734 2015-05-27 10:45:52 ERROR:  failed to make persistent db
>>> connection
>>> >>> 23734 2015-05-27 10:45:52 DETAIL:  connection to host:"x.x.x.x:5432"
>>> failed
>>> >>>
>>> >>> However I can connect to the server on that perfectly, and it seems
>>> pgpool can connect as well as no failover event gets called.
>>> >>>
>>> >>> My concern is I'm loosing data, as this is my primary(master) server.
>>> I do see the same error for my slave as well tho.
>>> >>>
>>> >>> Please advise what may be causing this, and let me know if you require
>>> any more information.
>>> >>>
>>> >>> Robert Kennedy| Software & Systems Developer
>>> >>>
>>> >>> Tel: 011 557 5729 | Ext:229 | Cell: 084 998 9850
>>> >>>
>>> >>> Email: robert at uwiniwin.co.za | Site: www.uwiniwin.co.za
>>> >>> Address: The Oval Office Park , Ground Floor Kingsmead, Meadowbrook
>>> Lane, Bryanston
>>> >>>
>>> >>> [site-award-logo]
>>> >>>
>>> >>> [site-aita-logo]
>>> >>>
>>> >>> [cid:image003.jpg at 01D0986B.205AF960]<
>>> https://twitter.com/intent/follow?original_referer=http%3A%2F%2Fwww.linkedin.com%2Fprofile%2Fview%3Fid%3D7202140%26locale%3Den_US%26trk%3Dtyah&region=follow_link&screen_name=sandmandavid&source=followbutton&variant=2.0
>>> >
>>> >>>
>>> >>> [czxc]<http://www.facebook.com/UwinIwinIncentives>
>>> >>>
>>> >>> [cid:image005.jpg at 01D0986B.205AF960]<
>>> http://www.linkedin.com/company/uwin-iwin-incentives?trk=fc_badge>
>>> >>>
>>> >>> [cid:image006.jpg at 01D0986B.205AF960]<
>>> http://www.linkedin.com/company/uwin-iwin-incentives?trk=fc_badge>
>>> >>>
>>> >>>
>>> >>> [cid:image007.jpg at 01D0986B.205AF960]<http://www.uwiniwin.net/>
>>> >>>
>>> >>>
>>> >>> Please consider the environment before printing this email-
>>> >>>
>>> >>> The views expressed in this e-mail are, unless otherwise stated, those
>>> of the author and not those of Uwin Iwin Incentives (PTY) LTD or its
>>> management. The information in this e-mail is confidential and is intended
>>> solely for the addressee. Access to this e-mail by anyone else is
>>> unauthorised. If you are not the intended recipient, any disclosure,
>>> copying, distribution or any action taken or omitted in reliance on this is
>>> prohibited and may be unlawful. Whilst all reasonable steps are taken to
>>> ensure the accuracy and integrity of information and data transmitted
>>> electronically and to preserve the confidentiality thereof, no liability or
>>> responsibility whatsoever is accepted if information or data is, for
>>> whatever reason, corrupted or does not reach its intended destination.
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> > _______________________________________________
>>> > pgpool-general mailing list
>>> > pgpool-general at pgpool.net
>>> > http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>>
> 
> 
> ------------------------------
> 
> Message: 2
> Date: Fri, 29 May 2015 09:00:03 +0900 (JST)
> From: Tatsuo Ishii <ishii at postgresql.org>
> To: sajeev1mayandi at yahoo.com
> Cc: pgpool-general at pgpool.net
> Subject: [pgpool-general: 3768] Re: Memcache_total_size size
> Message-ID: <20150529.090003.1444239715166434290.t-ishii at sraoss.co.jp>
> Content-Type: Text/Plain; charset=us-ascii
> 
> It turned out that the max value of memcache_total_size is 4294967295
> bytes, which is 4GB -1 (= 2^32 -1). This is not an intended limit (at
> least for me). I will create a patch fixing this if you can try it out
> (I don't have an access to a machine which has 16GB RAM). Can you?
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>>> Hi All
>> 
>>> I am Sajeev mayandi . We are using pgpool in our deployment, Recently I was trying to use the cache feature in pgpool version 3.4.2.  I have 16 Gig of memory, but Unfortunately  the memcache_total_size cannot be even in 8 Gig. some how pgpool supports only a 1 G for memcache_total_size.  
>>> Can you please confirm , how do I increase the size to what I want that is 8G.
>> 
>>> Appreciate your reply.
>> 
>>> Regards,
>>> Sajeev Mayandi
>> 
>> 
> 
> 
> ------------------------------
> 
> Message: 3
> Date: Thu, 28 May 2015 19:12:14 -0700
> From: Sajeev Mayandi <sajeev1mayandi at yahoo.com>
> To: Tatsuo Ishii <ishii at postgresql.org>
> Cc: "pgpool-general at pgpool.net" <pgpool-general at pgpool.net>
> Subject: [pgpool-general: 3769] Re: Memcache_total_size size
> Message-ID: <9C709ED9-BD69-430F-8FB8-65110E6C71BB at yahoo.com>
> Content-Type: text/plain;	charset=us-ascii
> 
> Sure I can try this out for you.
> 
> Thanks,
> 
> Sajeev
> 
> Sent from my iPhone
> 
>> On May 28, 2015, at 5:00 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 
>> It turned out that the max value of memcache_total_size is 4294967295
>> bytes, which is 4GB -1 (= 2^32 -1). This is not an intended limit (at
>> least for me). I will create a patch fixing this if you can try it out
>> (I don't have an access to a machine which has 16GB RAM). Can you?
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> 
>>>> Hi All
>>> 
>>>> I am Sajeev mayandi . We are using pgpool in our deployment, Recently I was trying to use the cache feature in pgpool version 3.4.2.  I have 16 Gig of memory, but Unfortunately  the memcache_total_size cannot be even in 8 Gig. some how pgpool supports only a 1 G for memcache_total_size.  
>>>> Can you please confirm , how do I increase the size to what I want that is 8G.
>>> 
>>>> Appreciate your reply.
>>> 
>>>> Regards,
>>>> Sajeev Mayandi
>>> 
>>> 
> 
> 
> ------------------------------
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
> 
> 
> End of pgpool-general Digest, Vol 43, Issue 34
> **********************************************
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: large_cache.patch
Type: text/x-patch
Size: 4228 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150530/5cd73914/attachment-0001.bin>

More information about the pgpool-general mailing list