[pgpool-general: 3664] Re: Error in pgpool memory cache invalidation.
Dhananjay Balan
dhananjay at plivo.com
Tue Apr 28 16:13:45 JST 2015
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
>>>
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20150428/9141591c/attachment.htm>
More information about the pgpool-general
mailing list