Mailing-List: contact user-help@ignite.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ignite.apache.org
MIME-Version: 1.0
In-Reply-To: <1461250959756-4415.post@n6.nabble.com>
References: <1461250959756-4415.post@n6.nabble.com>
Date: Fri, 22 Apr 2016 11:09:49 +0300
Message-ID: 
 <CAMegbcLzagSU770dAc94MOwPetqbE1jJQfFuy=spWv6stEmpdA@mail.gmail.com>
Subject: Re: Write-behind and foreign keys in DB
From: Alexei Scherbakov <alexey.scherbakoff@gmail.com>
To: user@ignite.apache.org
Content-Type: multipart/alternative; boundary=001a114e7202cedf3205310e5938

--001a114e7202cedf3205310e5938
Content-Type: text/plain; charset=UTF-8

Hi,

You should not use write-behind mode in such scenario.
Concerning performance issues: are you using connection pooling library?
If no, I recommend you to use a library like c3p0 for connection pooling
and prepared statement caching.


2016-04-21 18:02 GMT+03:00 vetko <Attila.Vetesi@wolterskluwer.com>:

> Hi All,
>
> I am experiencing an issue while trying to use write-behind on caches
> connected to tables which have foreign key constraints between them.
> Seemingly the write-behind mechanism is not executing the updates/inserts
> in
> a deterministic order, but rather is trying to push all the collected
> changes per each cache consecutively in some unknown order. But as we have
> foreign keys in the tables, the order of the operation matters, so parent
> objects should be inserted/updated first, and children only after that
> (otherwise foreign key violations are thrown from the DB).
>
> It seems that the current implementation is trying to workaround this
> problem on a trial-and-error basis
> (org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore:888), which
> means
> that it will periodically retry to flush the changes again and again for
> the
> caches in case of which a constraint violation occured. So the "child"
> cache
> will periodically retry to flush, until the "parent" cache gets flushed
> first. This ultimately will result in getting the data into the DB, but it
> also means a lot of unsuccessful tries in case of complex hierarchical
> tables, until the correct order is "found". This results in poor
> performance
> and unnecessary shelling of the DB.
>
> Do you have any suggestions how could I circumvent this issue?
>
> (Initially I were trying with write-through, but it resulted in VERY poor
> performance, because the CacheAbstractJdbcStore is seemingly opening a new
> prepared statement for each insert/update operation.)
>
> (Using Ignite 1.4)
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Write-behind-and-foreign-keys-in-DB-tp4415.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>


-- 

Best regards,
Alexei Scherbakov

--001a114e7202cedf3205310e5938
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div><br></div><div>You should not use write-behind mod=
e in such scenario.</div><div>Concerning performance issues: are you using =
connection pooling library?</div><div>If no, I recommend you to use a libra=
ry like c3p0 for connection pooling and prepared statement caching.</div><d=
iv><br></div><div><br></div></div><div class=3D"gmail_extra"><br><div class=
=3D"gmail_quote">2016-04-21 18:02 GMT+03:00 vetko <span dir=3D"ltr">&lt;<a =
href=3D"mailto:Attila.Vetesi@wolterskluwer.com" target=3D"_blank">Attila.Ve=
tesi@wolterskluwer.com</a>&gt;</span>:<br><blockquote class=3D"gmail_quote"=
 style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi=
 All,<br>
<br>
I am experiencing an issue while trying to use write-behind on caches<br>
connected to tables which have foreign key constraints between them.<br>
Seemingly the write-behind mechanism is not executing the updates/inserts i=
n<br>
a deterministic order, but rather is trying to push all the collected<br>
changes per each cache consecutively in some unknown order. But as we have<=
br>
foreign keys in the tables, the order of the operation matters, so parent<b=
r>
objects should be inserted/updated first, and children only after that<br>
(otherwise foreign key violations are thrown from the DB).<br>
<br>
It seems that the current implementation is trying to workaround this<br>
problem on a trial-and-error basis<br>
(org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore:888), which mean=
s<br>
that it will periodically retry to flush the changes again and again for th=
e<br>
caches in case of which a constraint violation occured. So the &quot;child&=
quot; cache<br>
will periodically retry to flush, until the &quot;parent&quot; cache gets f=
lushed<br>
first. This ultimately will result in getting the data into the DB, but it<=
br>
also means a lot of unsuccessful tries in case of complex hierarchical<br>
tables, until the correct order is &quot;found&quot;. This results in poor =
performance<br>
and unnecessary shelling of the DB.<br>
<br>
Do you have any suggestions how could I circumvent this issue?<br>
<br>
(Initially I were trying with write-through, but it resulted in VERY poor<b=
r>
performance, because the CacheAbstractJdbcStore is seemingly opening a new<=
br>
prepared statement for each insert/update operation.)<br>
<br>
(Using Ignite 1.4)<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href=3D"http://apache-ignite-users.70518.x=
6.nabble.com/Write-behind-and-foreign-keys-in-DB-tp4415.html" rel=3D"norefe=
rrer" target=3D"_blank">http://apache-ignite-users.70518.x6.nabble.com/Writ=
e-behind-and-foreign-keys-in-DB-tp4415.html</a><br>
Sent from the Apache Ignite Users mailing list archive at Nabble.com.<br>
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div class=
=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div dir=3D"ltr=
"></div><div><div dir=3D"ltr"><br>Best regards,<br></div><div>Alexei Scherb=
akov<br></div></div></div></div></div></div>
</div>

--001a114e7202cedf3205310e5938--