[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.pgpool.net/pipermail/pgpool-general/attachments/20150706/e1a59e91/attachment.htm>
More information about the pgpool-general
mailing list