Re: Blocker on checkpoint
Fred Toussi via Hsqldb-user <[email protected]> Thu, 14 Apr 2022 09:31:16 +0100
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============4871906196163284051== Content-Type: multipart/alternative; boundary=992aee31f2ff46a0b8e9fec761b6a94b --992aee31f2ff46a0b8e9fec761b6a94b Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: quoted-printable It is not clear what may be the cause. Enable SQL EVENT LOG LEVEL 2 and = see if there is any indication. In the HSLQDB Guide, it is mentioned that NIO use was reported be proble= matic in some setups. The latest versions of HSQLDB support table spaces. If you turn table sp= aces on for a database with large cached tables, you probably do not nee= d to do CHECKPOINT DEFRAG so often. Fred On Thu, Apr 14, 2022, at 07:48, S=C3=A9bastien P=C3=A9r=C3=A8s-Labourdet= te wrote: >=20 > Hi. >=20 > Another instance is now causing same issue. >=20 > Everything is fine for a certain amount of time as well as checking in= stance individually in-process : > - checkpoint / defrag > - perform check all table index and fix > - shutdown >=20 > At some point, unable to checkpoint / defrag properly. > Disk becoming full after a month or so, unable to backup, hsqldb unabl= e to sync io, databases becomes corrupted .... >=20 > What may be causing defrag / checkpoint to hang like that ? >=20 >=20 >=20 > *De : *Fred Toussi Via Hsqldb-user <[email protected]> > *Envoy=C3=A9 : * Vendredi 8 Avril 2022 18:18:03 > *=C3=80 : *Hsqldb User List <[email protected]> > *Cc : *Fred Toussi <[email protected]> > *Objet : *Re: [Hsqldb-user] Blocker on checkpoint >> There's obviusly an issue with the problematic instance.=20 >>=20 >> Execute SHUTDOWN on this particular instance and open it as an in-pro= cess database using DatabaseManagerSwing then try "perform check all tab= le index and fix". If it doesn't end, there is something wrong that it c= annot fix. Also try SHUTDOWN SCRIPT. If it doesn't work either you can g= et SupportWare and send the database for a possible fix. >>=20 >> Fred >>=20 >> On Fri, Apr 8, 2022, at 10:39, S=C3=A9bastien P=C3=A9r=C3=A8s-Labourd= ette wrote: >>=20 >>> Hi there. >>>=20 >>> Use case is the following >>> - Hsqldb server 2.5.1 with 14 database instances, some 100% memory, >>> some 100% cached. >>> - Background java job to perform checkpoint defrag on all instances. >>> - Job running well for 4/5 days. >>> - At some point, checkpoint defrag does never end on one specific i= nstance >>> - Shutdown server does never end (waiting for the problematic insta= nce >>> to shutdown but never comes) >>> - Force to kill the server process >>> - Restart server and all goes well for the next 4/5 days >>>=20 >>> The problematic instance does not mention any memory table (all cach= ed) >>>=20 >>> For this one, as well as some others, we have >>> - SET FILES WRITE DELAY FALSE >>>=20 >>> As well as >>> - SET FILES BACKUP INCREMENT TRUE >>> - SET FILES NIO TRUE >>> - SET FILES NIO SIZE 1024 >>>=20 >>> Statements such as "perform check all table index and fix" does never >>> end as well. >>>=20 >>> Storage / hardware is the same for all 14 instances, database "size"= is >>> 33Mb, other non-problematic databases goes beyond +600Mb >>>=20 >>> Any clues ? >>>=20 >>> -- >>> S=C3=A9bastien >>>=20 >>>=20 >>> _______________________________________________ >>> Hsqldb-user mailing list >>> [email protected] >>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >>>=20 >>> Attachments: >>> * signature.asc >>>=20 >> _______________________________________________ >> Hsqldb-user mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >>=20 >=20 > _______________________________________________ > Hsqldb-user mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/hsqldb-user >=20 >=20 > *Attachments:* > * signature.asc --992aee31f2ff46a0b8e9fec761b6a94b Content-Type: text/html;charset=utf-8 Content-Transfer-Encoding: quoted-printable <!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso= Normal,p.MsoNoSpacing{margin:0}</style></head><body><div style=3D"font-f= amily:Arial;">It is not clear what may be the cause. Enable SQL EVENT LO= G LEVEL 2 and see if there is any indication.<br></div><div style=3D"fon= t-family:Arial;"><br></div><div style=3D"font-family:Arial;">In the HSLQ= DB Guide, it is mentioned that NIO use was reported be problematic in so= me setups.<br></div><div style=3D"font-family:Arial;"><br></div><div sty= le=3D"font-family:Arial;">The latest versions of HSQLDB support table sp= aces. If you turn table spaces on for a database with large cached table= s, you probably do not need to do CHECKPOINT DEFRAG so often.<br></div><= div style=3D"font-family:Arial;"><br></div><div style=3D"font-family:Ari= al;">Fred<br></div><div style=3D"font-family:Arial;"><br></div><div>On T= hu, Apr 14, 2022, at 07:48, S=C3=A9bastien P=C3=A9r=C3=A8s-Labourdette w= rote:<br></div><blockquote type=3D"cite" id=3D"qt" style=3D""><div id=3D= "qt-smartTemplate4-template"><br></div><div>Hi.<br></div><div> <br></div= ><div> Another instance is now causing same issue.<br></div><div> <br></= div><div> Everything is fine for a certain amount of time as well as che= cking instance individually in-process :<br></div><div> - checkpoint= / defrag<br></div><div> - perform check all table index and fix<b= r></div><div> - shutdown<br></div><div> <br></div><div> At some po= int, unable to checkpoint / defrag properly.<br></div><div> Disk becomin= g full after a month or so, unable to backup, hsqldb unable to sync io, databases becomes corrupted ....<br></div><div> <= br></div><div> What may be causing defrag / checkpoint to hang like that= ?<br></div><div> <br></div><div> <br></div><div> <br></div><div id=3D"q= t-smartTemplate4-quoteHeader"><div style=3D"font-size:9pt;font-family:Ar= ial, Helvetica;padding-bottom:1px;color:rgb(0, 0, 0);padding-top:1px;"><= div><hr size=3D"1"><b>De : </b>Fred Toussi Via Hsqldb-user <a class=3D"q= t-moz-txt-link-rfc2396E" href=3D"mailto:[email protected]= t"><[email protected]></a><br></div><div> <b>Envoy= =C3=A9 : </b> Vendredi 8 Avril 2022 18:18:03<br></div><div> <b>=C3=80 : = </b>Hsqldb User List <a class=3D"qt-moz-txt-link-rfc2396E" href=3D"mailt= o:[email protected]"><[email protected]= et></a><br></div><div> <b>Cc : </b>Fred Toussi <a class=3D"qt-moz-txt= -link-rfc2396E" href=3D"mailto:[email protected]"><fredt@us= ers.sourceforge.net></a><br></div><div> <b>Objet : </b>Re: [Hsqldb-us= er] Blocker on checkpoint<br></div></div></div><blockquote type=3D"cite"= cite=3D"mid:[email protected]"><pre= class=3D"qt-moz-quote-pre">There's obviusly an issue with the problemat= ic instance.=20 Execute SHUTDOWN on this particular instance and open it as an in-proces= s database using DatabaseManagerSwing then try "perform check all table = index and fix". If it doesn't end, there is something wrong that it cann= ot fix. Also try SHUTDOWN SCRIPT. If it doesn't work either you can get = SupportWare and send the database for a possible fix. Fred On Fri, Apr 8, 2022, at 10:39, S=C3=A9bastien P=C3=A9r=C3=A8s-Labourdett= e wrote: <br></pre><blockquote type=3D"cite"><pre class=3D"qt-moz-quote-pre">Hi t= here. Use case is the following - Hsqldb server 2.5.1 with 14 database instances, some 100% memory, some 100% cached. - Background java job to perform checkpoint defrag on all instance= s. - Job running well for 4/5 days. - At some point, checkpoint defrag does never end on one specific = instance - Shutdown server does never end (waiting for the problematic inst= ance to shutdown but never comes) - Force to kill the server process - Restart server and all goes well for the next 4/5 days The problematic instance does not mention any memory table (all cached) For this one, as well as some others, we have - SET FILES WRITE DELAY FALSE As well as - SET FILES BACKUP INCREMENT TRUE - SET FILES NIO TRUE - SET FILES NIO SIZE 1024 Statements such as "perform check all table index and fix" does never end as well. Storage / hardware is the same for all 14 instances, database "size" is 33Mb, other non-problematic databases goes beyond +600Mb Any clues ? -- S=C3=A9bastien _______________________________________________ Hsqldb-user mailing list <a class=3D"qt-moz-txt-link-abbreviated" href=3D"mailto:Hsqldb-user@list= s.sourceforge.net">[email protected]</a> <a class=3D"qt-moz-txt-link-freetext" href=3D"https://lists.sourceforge.= net/lists/listinfo/hsqldb-user">https://lists.sourceforge.net/lists/list= info/hsqldb-user</a> Attachments: * signature.asc <br></pre></blockquote><pre class=3D"qt-moz-quote-pre">_________________= ______________________________ Hsqldb-user mailing list <a class=3D"qt-moz-txt-link-abbreviated" href=3D"mailto:Hsqldb-user@list= s.sourceforge.net">[email protected]</a> <a class=3D"qt-moz-txt-link-freetext" href=3D"https://lists.sourceforge.= net/lists/listinfo/hsqldb-user">https://lists.sourceforge.net/lists/list= info/hsqldb-user</a> <br></pre></blockquote><div><br></div><div>_____________________________= __________________<br></div><div>Hsqldb-user mailing list<br></div><div>= <a href=3D"mailto:[email protected]">[email protected]= ourceforge.net</a><br></div><div><a href=3D"https://lists.sourceforge.ne= t/lists/listinfo/hsqldb-user">https://lists.sourceforge.net/lists/listin= fo/hsqldb-user</a><br></div><div><br></div><div><br></div><div><b>Attach= ments:</b><br></div><ul><li>signature.asc<br></li></ul></blockquote><div= style=3D"font-family:Arial;"><br></div></body></html> --992aee31f2ff46a0b8e9fec761b6a94b-- --===============4871906196163284051== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline --===============4871906196163284051== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Hsqldb-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/hsqldb-user --===============4871906196163284051==--