[pgpool-general: 9357] Re: WARNING: failed to lock semaphore

[Tatsuo Ishii]

[Cc:ed to pgpoo-general]

Hello Maria,

Please Cc: to pgpool-general so that our community can share valuable
info.

> Hello Tatsuo,
> 
> This is how we installed pgpool.
> 
> mkdir -p /local/opt/points/pgpool/4.4.6
> cd /usr/src
> wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.4.6.tar.gz
> tar -xvzf pgpool-II-4.4.6.tar.gz
> cd pgpool-II-4.4.6/
> ./configure --prefix=/local/opt/points/pgpool/4.4.6 --enable-sequence-lock
> --with-openssl
> make ; make install
> 
> Are we missing something?

No, except --enable-sequence-lock is not necessary if you run pgpool
with streaming replication mode.

BTW, I suspect the source of the problem is, pgpool failed to acquire
semaphore at the startup for some reasons. To confirm the theory (and
possibly to know the cause), can you run pgpool with strace and share
the output?  For example:

strace -f --trace=ipc pgpool [options....]

If you succeed, you will see something like:

semget(IPC_PRIVATE, 8, IPC_CREAT|IPC_EXCL|0600) = 655401
semctl(655401, 0, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 1, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 2, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 3, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 4, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 5, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 6, SETVAL, 0x7ffe00000001) = 0
semctl(655401, 7, SETVAL, 0x7ffe00000001) = 0

> On Wed, Feb 12, 2025 at 10:23 AM Maria Nieva <maria.nieva at points.com> wrote:
> 
>>
>> Dear Tatsuo,
>>
>> Thank you for your response.
>>
>> I've attached the pgpool log from our production environment during the
>> upgrade.   The semaphore warning shows right away.  We have since rolled
>> back to version 4.1.1. I've also included the config file for your
>> reference.
>>
>> Please let me know if you have any further questions.
>>
>>
>> Best Regards,
>> Maria
>>
>>
>>
>>
>> On Tue, Feb 11, 2025 at 7:40 PM Tatsuo Ishii <ishii at postgresql.org> wrote:
>>
>>> [Cc:ed to pgpool-general]
>>>
>>> Thank you for the log. There's no semaphore errors. Now I need to know
>>> how you installed 4.4.6. If from RPM, did you use Pgpool-II community
>>> version?
>>>
>>> https://protect2.fireeye.com/v1/url?k=31323334-501d2dca-3131568e-454455534531-ba8a251b4f567a67&q=1&e=e45aec18-0dee-41a2-bde7-d39d532f71f2&u=https%3A%2F%2Fpgpool.net%2Fmediawiki%2Findex.php%2FYum_Repository
>>>
>>> Can you share pgpool.conf? (please mask sensitive information such as
>>> password or IP).
>>>
>>> Best reagards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS K.K.
>>> English:
>>> https://protect2.fireeye.com/v1/url?k=31323334-501d2dca-3131568e-454455534531-bbb96eab86ee7dab&q=1&e=e45aec18-0dee-41a2-bde7-d39d532f71f2&u=http%3A%2F%2Fwww.sraoss.co.jp%2Findex_en%2F
>>> Japanese:http://www.sraoss.co.jp
>>>
>>>
>>> > Dear Tatsuo,
>>> >
>>> > Thank you for your prompt response. I have attached our pgpool log for
>>> your
>>> > review.
>>> >
>>> > Please advise on how to resolve the semaphore error. Your guidance is
>>> > greatly appreciated.
>>> >
>>> >
>>> >
>>> >
>>> > On Mon, Feb 10, 2025 at 7:09 PM Tatsuo Ishii <ishii at postgresql.org>
>>> wrote:
>>> >
>>> >> > Hello,
>>> >> >
>>> >> > We recently upgraded pgpool from 4.1.1 to 4.4.6.  We noticed that we
>>> are
>>> >> > getting this
>>> >> >
>>> >> > 2025-02-08 01:39:33.724 PROCESS: WORKER DB: [No Connection] USER: [No
>>> >> > Connection] APPL: sr_check_worker % WARNING:  failed to lock
>>> semaphore
>>> >> [snip]
>>> >> > Connection] APPL: sr_check_worker % LOCATION:  pool_sema.c:202
>>> >> >
>>> >> > This doesn't show much
>>> >> >
>>> >> > ipcs -s
>>> >> >
>>> >> > ------ Semaphore Arrays --------
>>> >> > key        semid      owner      perms      nsems
>>> >>
>>> >> Strange. In your case you should see something like:
>>> >>
>>> >> ------ Semaphore Arrays --------
>>> >> key        semid      owner      perms      nsems
>>> >> 0x00000000 655386     t-ishii    600        8
>>> >>
>>> >> ("t-ishii" is the user name of the user who started pgpool)
>>> >>
>>> >> > This is in our *pgpool.conf *
>>> >> > backend_clustering_mode = 'streaming_replication'
>>> >> > num_init_children = 250
>>> >> > min_spare_children = 25
>>> >> > max_spare_children = 50
>>> >> > max_pool = 2
>>> >> > child_life_time = 300
>>> >> > child_max_connections = 2
>>> >> > connection_life_time = 300
>>> >> > client_idle_limit = 300
>>> >> >
>>> >> > Can you please help us understand why we see the warning? What is it?
>>> >>
>>> >> Pgpool worker process complains that it cannot aquire a semaphore.
>>> >>
>>> >> > How
>>> >> > do we resolve this?
>>> >>
>>> >> To resolve the problem, we need the pgpool log at the start up because
>>> >> the semaphore is configured at that time.
>>> >>
>>> >> Best reagards,
>>> >> --
>>> >> Tatsuo Ishii
>>> >> SRA OSS K.K.
>>> >> English:
>>> >>
>>> https://protect2.fireeye.com/v1/url?k=31323334-501d2dca-3131568e-454455534531-bbb96eab86ee7dab&q=1&e=beae9c64-ec11-4d5c-8783-026e28457a81&u=http%3A%2F%2Fwww.sraoss.co.jp%2Findex_en%2F
>>> >> Japanese:http://www.sraoss.co.jp
>>> >>
>>>
>>