[pgpool-general: 3670] Re: Error in pgpool memory cache invalidation.
Dhananjay Balan
dhananjay at plivo.com
Wed Apr 29 18:32:44 JST 2015
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
> >>>>
> >>>
> >>>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20150429/9176fcbc/attachment.htm>
More information about the pgpool-general
mailing list