[pgpool-general: 3676] Re: Error in pgpool memory cache invalidation.

Tatsuo Ishii ishii at postgresql.org
Thu Apr 30 18:22:47 JST 2015


Hum. You are right. Consider following scenario:

1) pgpool-II A receives "SELECT * FROM t1 ..." and create a cache on memecached

2) pgpool-II B receives "UPDATE t1 ..." and tries to invalidate the query cache created at #1.

#2 will fail because pgpool-II B does not have the table oid file.

We need to have a way to share info among A & B regarding table
oids. In the mean time is it possible for you to send write queries
only one of A or B? This will avoid the problem above.

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

> Hi,
> 
>>From my intial reading of the source code,
> 
> 1. For every query cached memcached writes the cache-key on
> oidir/<db_oid>/<table_oid>
> 2. If there is an update on a table, read each hash from <Table_oid> file
> and delete in memcache.
> 
> If this is what pgpool uses to invalidate cache - two pgpools, using same
> memcache will not invalidate cache properly. Please correct me if there are
> more to this logic.
> 
> 
> 
> 
> 
> 
> 
> 
> *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 Wed, Apr 29, 2015 at 4:10 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> 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