[pgpool-general: 3852] Pgpool Cache Invaliadtion Problems

The Hills hillsangels2 at verizon.net
Tue Jul 7 08:58:43 JST 2015


Hi all,

I saw the email thread below and suspect I am experiencing the same or a 
similar problem.  I am running 2 independent pgpool instances on 
separate servers against a primary/hot standby postgres pair. The two 
pgpools don't seem to recognize each other's cache invalidating 
activity.    (I have enable_query_cache = true, load_balance_mode = on, 
master_slave_mode = on and pgpool regclass installed on both PG 
servers.)  Do I really have to turn caching off, or can watchdog or 
something else resolve this problem?

The example below shows updates on pgpool 1 not being recognized by the 
pgpool 2:

/ From PGP1  create table family(name text);
// From PGP1  insert into family values('Bob');////
 From PGP1  select * from family;
            --> Bob/
/// From PGP2  select * from family;
            --> Bob
/
F//////rom PGP1  insert into family values('Sally');//
 From PGP1  insert into family values('Mike');//
// From PGP1  select * from family;
            --> Bob
            --> Sally
            --> Mike
/ From PGP2  select * from family;
            --> Bob/

As the existing system was given to me, I am required to receive 
transactions from two Tomcat servers into 2 independent Pgpools.

┌ ─ ─ ─ ─ ─ ─ ─ ─ ┐

││

│ACE│

││

└ ─ ─ ─ ─ ─ ─ ─ ─ ┘

/ \

/\

/\

˅˅

┌ ─ ─ ─ ─ ─ ─ ─ ─ ┐┌ ─ ─ ─ ─ ─ ─ ─ ─ ┐

││││

│ TOMCAT SERVER ││TOMCAT SERVER │

││││

│ PGPOOL INST 1 ││PGPOOL INST 2│

│(ACTIVE/INDEPEN.)││(ACTIVE/INDEPEN.)│

└ ─ ─ ─ ─ ─ ─ ─ ─ ┘└ ─ ─ ─ ─ ─ ─ ─ ─ ┘

│\ /│

READ │ WRITE \/READ │ ONLY

│ /\│

│/ \│

˅˅˅˅

┌ ─ ─ ─ ─ ─ ─ ─ ─ ┐┌ ─ ─ ─ ─ ─ ─ ─ ─ ┐

││││

│ POSTGRES SERVER ││ POSTGRES SERVER │

││││

│ PGPOOL PRIM ││ PG HOT STANDBY│

││││

└ ─ ─ ─ ─ ─ ─ ─ ─ ┘└ ─ ─ ─ ─ ─ ─ ─ ─ ┘


Thanks,

Mike Hill
LMCO, Herndon, VA


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

*Tatsuo Ishii* ishii at postgresql.org 
<mailto:pgpool-general%40pgpool.net?Subject=Re:%20Re%3A%20%5Bpgpool-general%3A%203676%5D%20Re%3A%20Error%20in%20pgpool%20memory%20cache%0A%20invalidation.&In-Reply-To=%3C20150430.182247.762685084071967340.t-ishii%40sraoss.co.jp%3E>
/Thu Apr 30 18:22:47 JST 2015/

  * Previous message: [pgpool-general: 3670] Re: Error in pgpool memory
    cache invalidation.
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/003722.html>
  * Next message: [pgpool-general: 3646] Re: Adding a New Server and
    Recovery
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/003695.html>
  * *Messages sorted by:* [ date ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/date.html#3729>
    [ thread ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/thread.html#3729>
    [ subject ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/subject.html#3729>
    [ author ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/author.html#3729>


------------------------------------------------------------------------

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  <http://www.sraoss.jp/mailman/listinfo/pgpool-general>> 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  <http://www.sraoss.jp/mailman/listinfo/pgpool-general>>
/>>/  > 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  <http://www.sraoss.jp/mailman/listinfo/pgpool-general>>
/>>/  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  <http://www.sraoss.jp/mailman/listinfo/pgpool-general>>
/>>/  >>> 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  <http://www.sraoss.jp/mailman/listinfo/pgpool-general>>
/>>/  >>>> 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.sraoss.jp/mailman/listinfo/pgpool-general>
/>>/  >>>> >http://www.pgpool.net/mailman/listinfo/pgpool-general
/>>/  >>>> _______________________________________________
/>>/  >>>> pgpool-general mailing list
/>>/  >>>>pgpool-general at pgpool.net  <http://www.sraoss.jp/mailman/listinfo/pgpool-general>
/>>/  >>>>http://www.pgpool.net/mailman/listinfo/pgpool-general
/>>/  >>>>
/>>/  >>>
/>>/  >>>
/>>/  >>
/>>/
/

------------------------------------------------------------------------

  * Previous message: [pgpool-general: 3670] Re: Error in pgpool memory
    cache invalidation.
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/003722.html>
  * Next message: [pgpool-general: 3646] Re: Adding a New Server and
    Recovery
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/003695.html>
  * *Messages sorted by:* [ date ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/date.html#3729>
    [ thread ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/thread.html#3729>
    [ subject ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/subject.html#3729>
    [ author ]
    <http://www.sraoss.jp/pipermail/pgpool-general/2015-April/author.html#3729>


------------------------------------------------------------------------
More information about the pgpool-general mailing list 
<http://www.sraoss.jp/mailman/listinfo/pgpool-general>



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150706/e1a59e91/attachment-0001.html>


More information about the pgpool-general mailing list