[pgpool-general: 3669] Re: Error in pgpool memory cache invalidation.
Tatsuo Ishii
ishii at postgresql.org
Wed Apr 29 07:40:34 JST 2015
Probably pgpool log with -d (debugging option) turned on will provide
more useful information.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> Message got clipped again. Resending
>
> Hi,
>
> This commit seems to have fixed the issue. However cache invalidation when
> multiple pgpools are used (connecting to same memcache server) is (?)
> broken even with 3.3.5. Here are the logs from 3.3.5
>
> https://gist.github.com/dbalan/c5a93458d5c091c28c71
>
> My test setup is two instances of 3.3.5 running on 9999 and 9988 of same
> machine with memcache running in localhost.
>
> Logs:
>
>>From first pgpool,
> # First, read and update data - invalidates cache - this works fine.
> psql -U ruser -d mydb -h 127.0.0.1 -p 9999
> Password for user ruser:
> psql (9.4.1)
> Type "help" for help.
>
> mydb=> select comments from users where id=1;
> comments
> ----------------
> boot me please
> (1 row)
>
> mydb=> update users set comments='hello' where id=1;
> UPDATE 1
> mydb=> select comments from users where id=1;
> comments
> ----------
> hello
> (1 row)
>
> mydb=>
>
>
> Second pgpool:
> # Connects via second deamon, reads stale data, i/e updates from this
> pgpool instance never invalidate cache.
> λ ~/ psql -U ruser -d mydb -h 127.0.0.1 -p 9988
> Password for user ruser:
> psql (9.4.1)
> Type "help" for help.
>
> mydb=> select comments from users where id=1;
> comments
> ----------
> hello
> (1 row)
>
> mydb=> update users set comments='hello world' where id=1;
> UPDATE 1
> mydb=> select comments from users where id=1;
> comments
> ----------
> hello
> (1 row)
>
> mydb=>
>
>
> The configs and full logs are attached in the link, They both are same
> except for the directory paths and ports - not sure whats going wrong here.
>
>
> *DHANANJAY *| Infrastructure Engineer
> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
> Web: www.plivo.com | Twitter: @plivo <http://twitter.com/plivo>,
> @notmycommit <https://twitter.com/notmycommit>
>
> Free Incoming SMS for All US Short Codes – Get One Today!
> <https://www.plivo.com/sms-short-code/?utm=emailsig>
>
> On Mon, Apr 27, 2015 at 5:29 PM, Dhananjay Balan <dhananjay at plivo.com>
> wrote:
>
>> Hi,
>>
>> This commit seems to have fixed the issue. However cache invalidation when
>> multiple pgpools are used (connecting to same memcache server) is (?)
>> broken even with 3.3.5. Here are the logs from 3.3.5
>>
>> https://gist.github.com/dbalan/c5a93458d5c091c28c71
>>
>> My test setup is two instances of 3.3.5 running on 9999 and 9988 of same
>> machine with memcache running in localhost.
>>
>> From first pgpool,
>>
>> # First, read and update data - invalidates cache - this works fine.
>> psql -U ruser -d mydb -h 127.0.0.1 -p 9999
>> Password for user ruser:
>> psql (9.4.1)
>> Type "help" for help.
>>
>> mydb=> select comments from users where id=1;
>> comments
>> ----------------
>> boot me please
>> (1 row)
>>
>> mydb=> update users set comments='hello' where id=1;
>> UPDATE 1
>> mydb=> select comments from users where id=1;
>> comments
>> ----------
>> hello
>> (1 row)
>>
>> mydb=>
>>
>>
>> Second pgpool:
>> # Connects via second deamon, reads stale data, i/e updates from this
>> pgpool instance never invalidate cache.
>> λ ~/ psql -U ruser -d mydb -h 127.0.0.1 -p 9988
>> Password for user ruser:
>> psql (9.4.1)
>> Type "help" for help.
>>
>> mydb=> select comments from users where id=1;
>> comments
>> ----------
>> hello
>> (1 row)
>>
>> mydb=> update users set comments='hello world' where id=1;
>> UPDATE 1
>> mydb=> select comments from users where id=1;
>> comments
>> ----------
>> hello
>> (1 row)
>>
>> mydb=>
>>
>>
>> The configs and full logs are attached in the link, They both are same
>> except for the directory paths and ports - not sure whats going wrong here.
>>
>> https://gist.github.com/dbalan/c5a93458d5c091c28c71
>>
>>
>>
>>
>> *DHANANJAY *| Infrastructure Engineer
>> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>> Web: www.plivo.com | Twitter: @plivo <http://twitter.com/plivo>,
>> @notmycommit <https://twitter.com/notmycommit>
>>
>> Free Incoming SMS for All US Short Codes – Get One Today!
>> <https://www.plivo.com/sms-short-code/?utm=emailsig>
>>
>>
>>
>> On Thu, Apr 23, 2015 at 5:11 PM, Muhammad Usama <m.usama at gmail.com> wrote:
>>
>>> Hi
>>>
>>> I have found the problem, It was because of the buffer for storing table
>>> oids was created in wrong memory context.
>>> I have pushed the fix for it in master and 3_4 stable branches.
>>>
>>> Thanks
>>> Best regards
>>> Muhammad Usama
>>>
>>>
>>> On Wed, Apr 22, 2015 at 2:09 PM, Tatsuo Ishii <ishii at postgresql.org>
>>> wrote:
>>>
>>>> Thanks for the report.
>>>>
>>>> I confirm this with pgpool-II 3.4.1. I also found that this does not
>>>> happen with 3.3.5 (I tested with shmem).
>>>>
>>>> Here is the test script.
>>>> -------------------------------------
>>>> drop table t1;
>>>> create table t1(i int, j text);
>>>> insert into t1 values(1, 'aaa');
>>>> select * from t1;
>>>> select * from t1;
>>>> update t1 set j = 'bbb' where i = 1;
>>>> select * from t1;
>>>> -------------------------------------
>>>>
>>>> Will look into this...
>>>>
>>>> Best regards,
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese:http://www.sraoss.co.jp
>>>>
>>>> > Looks like only part of this email ended up in the mailing list , not
>>>> > sure how. Resending again - hope it works this time.
>>>> >
>>>> > pgpool doesn't seem to be invalidating caches properly. See the
>>>> > example test case below
>>>> >
>>>> > psql -U ruser -d mydb -h 127.0.0.1 -p 9999
>>>> > Password for user ruser:
>>>> > psql (9.4.1)
>>>> > Type "help" for help.
>>>> >
>>>> > mydb=> select comments from users where id=1;
>>>> > comments
>>>> > ----------
>>>> > boooo
>>>> > (1 row)
>>>> >
>>>> > # comes from cache
>>>> > mydb=> select comments from users where id=1;
>>>> > comments
>>>> > ----------
>>>> > boooo
>>>> > (1 row)
>>>> >
>>>> > # works
>>>> > mydb=> update users set comments='hello' where id=1;
>>>> > UPDATE 1
>>>> > mydb=> select comments from users where id=1;
>>>> > comments
>>>> > ----------
>>>> > hello
>>>> > (1 row)
>>>> >
>>>> > # stale
>>>> > mydb=> update users set comments='hello world' where id=1;
>>>> > UPDATE 1
>>>> > mydb=> select comments from users where id=1;
>>>> > comments
>>>> > ----------
>>>> > hello
>>>> > (1 row)
>>>> >
>>>> > # also stale
>>>> > mydb=> update users set comments='booo' where id=1;
>>>> > UPDATE 1
>>>> > mydb=> select comments from users where id=1;
>>>> > comments
>>>> > ----------
>>>> > hello
>>>> > (1 row)
>>>> >
>>>> >
>>>> > From the second write onwards, the cache is not invalidated and stale
>>>> > value is being served. Is it a bug or am I doing something wrong? This
>>>> > happens with both cache backends (memcache and shmem but test logs are
>>>> > from memcache backend)
>>>> >
>>>> > Test setup is postgresql-9.4 and memcached 1.4.21 and pgpool-3.4.2. my
>>>> > configs and logs from test are here
>>>> >
>>>> > https://gist.github.com/dbalan/d635993a3e532a083751
>>>> >
>>>> > Regards,
>>>> > --------
>>>> > DHANANJAY | Infrastructure Engineer
>>>> > Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>>>> > Web: www.plivo.com | Twitter: @plivo, @notmycommit
>>>> > DHANANJAY | Infrastructure Engineer
>>>> > Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>>>> > Web: www.plivo.com | Twitter: @plivo, @notmycommit
>>>> >
>>>> > Free Incoming SMS for All US Short Codes – Get One Today!
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Apr 21, 2015 at 12:33 PM, Dhananjay Balan <dhananjay at plivo.com>
>>>> wrote:
>>>> >> pgpool doesn't seem to be invalidating caches properly. See the
>>>> >> example test case below
>>>> >>
>>>> >> psql -U ruser -d mydb -h 127.0.0.1 -p 9999
>>>> >> Password for user ruser:
>>>> >> psql (9.4.1)
>>>> >> Type "help" for help.
>>>> >>
>>>> >> mydb=> select comments from users where id=1;
>>>> >> comments
>>>> >> ----------
>>>> >> boooo
>>>> >> (1 row)
>>>> >>
>>>> >> # comes from cache
>>>> >> mydb=> select comments from users where id=1;
>>>> >> comments
>>>> >> ----------
>>>> >> boooo
>>>> >> (1 row)
>>>> >>
>>>> >> # works
>>>> >> mydb=> update users set comments='hello' where id=1;
>>>> >> UPDATE 1
>>>> >> mydb=> select comments from users where id=1;
>>>> >> comments
>>>> >> ----------
>>>> >> hello
>>>> >> (1 row)
>>>> >>
>>>> >> # stale
>>>> >> mydb=> update users set comments='hello world' where id=1;
>>>> >> UPDATE 1
>>>> >> mydb=> select comments from users where id=1;
>>>> >> comments
>>>> >> ----------
>>>> >> hello
>>>> >> (1 row)
>>>> >>
>>>> >> # also stale
>>>> >> mydb=> update users set comments='booo' where id=1;
>>>> >> UPDATE 1
>>>> >> mydb=> select comments from users where id=1;
>>>> >> comments
>>>> >> ----------
>>>> >> hello
>>>> >> (1 row)
>>>> >>
>>>> >>
>>>> >> From the second write onwards, the cache is not invalidated and stale
>>>> >> value is being served. Is it a bug or am I doing something wrong? This
>>>> >> happens with both cache backends (memcache and shmem but test logs are
>>>> >> from memcache backend)
>>>> >>
>>>> >> Test setup is postgresql-9.4 and memcached 1.4.21 and pgpool-3.4.2. my
>>>> >> configs and logs from test are here
>>>> >>
>>>> >> https://gist.github.com/dbalan/d635993a3e532a083751
>>>> >>
>>>> >> Regards,
>>>> >> --------
>>>> >> DHANANJAY | Infrastructure Engineer
>>>> >> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>>>> >> Web: www.plivo.com | Twitter: @plivo, @notmycommit
>>>> > _______________________________________________
>>>> > 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
>>>>
>>>
>>>
>>
More information about the pgpool-general
mailing list