Re: Sqlite checkpoint issue
Venugopal Thotakura <[email protected]> Wed, 1 Jul 2020 03:31:42 -0700 (PDT)
| Newsgroups | gmane.comp.web.zope.zodb |
|---|---|
| Message-ID | <[email protected]> |
------=_Part_1090_121096353.1593599502589 Content-Type: multipart/alternative; boundary="----=_Part_1091_1847811118.1593599502589" ------=_Part_1091_1847811118.1593599502589 Content-Type: text/plain; charset="UTF-8" What a coincidence, definetly we are lucky here :) Thank you so much for your promt reply and providing much needed insights for us. I think I didn't give more details of our architecture.. Will put some more details, so it may help others as well.. We connect to db from multiple processes - 2 read process (async read with our async connection pool to limit the connections) - 1 write process (async write, serialized with batch) Our writer is async write, uses queue and writes them in batches (so all writes are serial). We have bridged zodb sync stuff with async, we run all the zodb sync APIs in threadpool executor except connection handling (so the reason for having our async connection pool and prefork conneciton to not block the main thread). Also zodb internal connection pool (sync) doesn't bound to the limit, and pool size can go unbounded.. causing memory havoc (we can limit memory heap, etc I guess).. so we would want to control the pool. Main thread take care of ensuring the connections are shared amoung the coroutines and which inturn runs inside a thread.. and when context ends, pool get the connection back.. Auto-checkpoints should be enabled by default, unless your copy of sqlite > was compiled in a strange way. RelStorage does not disable > auto-checkpoints, but it will log what the default value is when a > connection is opened (it's usually 1000): We are using standard sqlite compiled version.. Ya we have checked about the 1k page default value.. https://github.com/zodb/relstorage/blob/master/src/relstorage/adapters/sqlite/drivers.py#L512 But comment here and the default value got us thinking like that.. Perhaps, we should have debuggged connection default settings.. I have tested this, even with small values, WAL size still going up with 50 requests per second. so autocheckpoint may not help us much I guess if we have not mistaken. (The `wal_checkpoint` pragma is a one-time operation, not a persistent setting that applies to auto-checkpoints. The pragmas are executed when a connection is opened, so that last line will cause each new connection to run a checkpoint. The function-call syntax needed to actually pass `FULL`, e.g., `PRAGMA wal_checkpoint(FULL)`, is not supported for the pragmas executed at connection open, though, so the default value of `PASSIVE` gets used instead. That's probably lucky, because a FULL checkpoint is a blocking operation and might never successfully complete...) Oh thanks for insight! Luck here again, however Writes are all serialized, we can have FULL mode also if we understand it correctly.. since we are not blocking any other writers (single wirte in our case) and not blocking reader here, and our backlog would clear up as soon its done with checkpoint. > Auto-checkpoints are always PASSIVE checkpoints (and never block). > PASSIVE checkpoints will mark the space in the WAL file that's available > for re-use, but if there are open *transactions* viewing the database as-of > some time in the past, that part of the WAL won't be available for re-use. > Depending on the workload, those transactions could be using data at the > end of the WAL, in which case new uses of the WAL will have to grow the > file. > The problem is open transactions, not necessarily open connections. > > How do you know if there's an open transaction, and what can you do about > controlling them? First and most importantly, always use the transaction manager in its > explicit mode. There are a number of benefits to that, but most relevant > here is that when the transaction manager is in explicit mode, ZODB alters > the way it uses RelStorage, and RelStorage is able to manage the underlying > database transaction in a much better way. We use context manager for every single db operation, so all transactions are commited by the end of the db operation. You have pointed us to right direction about transaction being opened. We are running manual checkpoint also in the tranaction, I think this is one reason for the issue (but with some hack - droping load connection before transacion & droping store connection after transaction, we were able to get it working). 2) Manually invoke a checkpoint *immediately* after committing. This only > works if the transaction managers are in explicit mode, and it has to be > immediately after committing so that the connection is not in its own way. > This works for a single writer. (And yes, this is using non-public APIs so > it's likely to break in the future. Hopefully the changes I make to > RelStorage alleviate the issue enough, but if not we can look at doing > something more sophisticated and permanent.) With manual checkpoint, explicit mode and not running the manual checkpoint in another tranction did the trick.. Its working fine now without hacking sol (droping load connection, etc). We were in wrong impression that issue was due to connections, but thanks for the hint. I think we can leave with it for now, but looking forward for the more sophistacted solution to trigger manual checkpoint.. We apprecaite your prompt feedback and very thankful for that. Thank you for the sample code, its very useful! On Wednesday, 1 July 2020 01:45:50 UTC+5:30, Jason Madden wrote: > > As luck would have it, I've been looking into this very issue over the > past few days and can offer some insight. > > > On Jun 30, 2020, at 02:13, Venugopal Thotakura <[email protected] > <javascript:>> wrote: > > > > Hi, > > > > We are using sqlite storage engine, we are facing issues with > checkpointing (WAL size going into GB.. It looks like by default > checkpointing is disabled, so we enabled auto checkpointing.. > > Auto-checkpoints should be enabled by default, unless your copy of sqlite > was compiled in a strange way. RelStorage does not disable > auto-checkpoints, but it will log what the default value is when a > connection is opened (it's usually 1000): > > DEBUG:relstorage.adapters.sqlite.drivers:Connection: <Connection at > 0x113348c30 to '...' in_transaction=False>. > Using sqlite3 version: 3.32.3. > Default connection settings: {... 'wal_autocheckpoint': > 1000,...} > Changing connection settings: {'synchronous': 2, 'cache_spill': > 483, 'foreign_keys': 0}. > Desired connection settings: {'synchronous': 1, 'cache_spill': > 0, 'foreign_keys': 1}. > Unapplied connection settings: {}. > > > > <pragmas> > > wal_autocheckpoint 100 > > wal_checkpoint FULL > > </pragmas> > > (The `wal_checkpoint` pragma is a one-time operation, not a persistent > setting that applies to auto-checkpoints. The pragmas are executed when a > connection is opened, so that last line will cause each new connection to > run a checkpoint. The function-call syntax needed to actually pass `FULL`, > e.g., `PRAGMA wal_checkpoint(FULL)`, is not supported for the pragmas > executed at connection open, though, so the default value of `PASSIVE` gets > used instead. That's probably lucky, because a FULL checkpoint is a > blocking operation and might never successfully complete...) > > > > > Now, It looks like its trying to do checkpointing, however due to the > connections arleady open, it couldn't do that.. > > Auto-checkpoints are always PASSIVE checkpoints (and never block). PASSIVE > checkpoints will mark the space in the WAL file that's available for > re-use, but if there are open *transactions* viewing the database as-of > some time in the past, that part of the WAL won't be available for re-use. > Depending on the workload, those transactions could be using data at the > end of the WAL, in which case new uses of the WAL will have to grow the > file. > > The problem is open transactions, not necessarily open connections. > > How do you know if there's an open transaction, and what can you do about > controlling them? > > > So to give overview of our architecture.. > > > > We connect to db from multiple processes > > - 2 read process (async read with our async connection pool to limit the > connections) > > - 1 write process (async write) > > > > > > Here is our async pool.. we prefork the connections (we are seeing > sometimes db.open is taking longer time to create connections, so we are > doing prefork) and use them. > > > First and most importantly, always use the transaction manager in its > explicit mode. There are a number of benefits to that, but most relevant > here is that when the transaction manager is in explicit mode, ZODB alters > the way it uses RelStorage, and RelStorage is able to manage the underlying > database transaction in a much better way. > > Second, to be able to use explicit mode (as well as one of the mitigations > discussed below), I suspect you may need to drop the idea of "pre-forking" > connections. Let the ZODB DB and its connection pool manage that. (If > you're find it sometimes slow to open connections, you may need to adjust > the size of the ZODB connection pool.) Begin a transaction, open a > connection, do the work, then commit/rollback the transaction, and finally > close the connection. By carefully bounding the transaction and connection > lifecycle this way, together with using an explicit transaction manager, > you can be sure about the lifetime of the underlying database transaction > as well. > > Lastly (and this is not a problem on your side, it's an issue I need to > address in RelStorage) having exactly one concurrent writer actually > exacerbates the situation a little bit. That's because of the way > connections are internally handled in RelStorage. Basically, one connection > can get in its own way (again, depending on the workload) and when a commit > triggers an auto-checkpoint, it may not be able to clean up all the WAL > pages. But if another connection commits shortly after that and triggers an > auto-checkpoint, then it may be able to make more progress. If there's only > ever one writer, though, it's likely to keep tripping over its own feet. > (This can still happen with multiple writers but I think it's more rare.) > > This is something I will change in RelStorage (I have a prototype fix > now). But in the meantime, there are two possible mitigations: > > 1) Use a very low `wal_autocheckpoint` so that connections are cleaning up > after each other more often. This only matters, of course, if there is more > than one writer. > 2) Manually invoke a checkpoint *immediately* after committing. This only > works if the transaction managers are in explicit mode, and it has to be > immediately after committing so that the connection is not in its own way. > This works for a single writer. (And yes, this is using non-public APIs so > it's likely to break in the future. Hopefully the changes I make to > RelStorage alleviate the issue enough, but if not we can look at doing > something more sophisticated and permanent.) > > > Here's an example program demonstrating this. If the transaction managers > are in explicit mode, then the WAL never grows beyond two pages (8K); if > they are left implicit, then by the time executing finishes, the WAL has > grown to 80MB. > > > import os > import logging > > import transaction > from ZODB.config import databaseFromString > > db_config = """ > %import relstorage > <zodb> > pool-size 1 > <relstorage> > keep-history false > <sqlite3> > data-dir /tmp/rstest > </sqlite3> > </relstorage> > </zodb> > """ > > logger = logging.getLogger(__name__) > > def report_sizes(): > os.system('ls -lh /tmp/rstest') > > def run_transaction(db): > tx = transaction.begin() > conn = db.open() > root = conn.root() > root['key'] = 'abcd' * 1000 > tx.commit() > conn.close() > checkpoint(conn) > > def checkpoint(conn): > sc = conn._storage._load_connection.cursor > sc.execute('pragma main.wal_checkpoint(passive)') > sc.fetchall() # Must fetchall! Plus there's actual useful info in the > results. > > > def main(): > logging.basicConfig(level=logging.DEBUG) > logging.getLogger('txn').setLevel(logging.ERROR) > # Make sure the global (default) transaction manager is > # explicit. > transaction.manager.explicit = True > db = databaseFromString(db_config) > > > # Open an isolated connection. If the transaction > # manager isn't in explicit mode, it really will talk to > # the database now, which will cause WAL to start accumulating. > # Use explicit transaction managers to prevent that from happening > # and keep a tight reign on transaction duration. > isolated_txm = transaction.TransactionManager() > isolated_txm.explicit = True # If this is commented out, the WAL grows > without bound > c1 = db.open(isolated_txm) > > report_sizes() > for _ in range(10): > for _ in range(1000): > run_transaction(db) > report_sizes() > > c1.close() > db.close() > report_sizes() > > > if __name__ == '__main__': > main() > > ~Jason > > -- You received this message because you are subscribed to the Google Groups "zodb" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/zodb/ea577081-bbb3-436f-9281-2af3c282983ao%40googlegroups.com. ------=_Part_1091_1847811118.1593599502589 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div>What a coincidence, definetly we are lucky here :)</d= iv><div><br></div><div>Thank you so much for your promt reply and providing= much needed insights for us.</div><div><br></div><div><br></div><div>I thi= nk I didn't give more details of our architecture.. Will put some more = details, so it may help others as well..</div><div><br></div><div><br></div= ><div class=3D"prettyprint" style=3D"background-color: rgb(250, 250, 250); = border-color: rgb(187, 187, 187); border-style: solid; border-width: 1px; o= verflow-wrap: break-word;"><code class=3D"prettyprint"><div class=3D"subpre= ttyprint"><span style=3D"color: #606;" class=3D"styled-by-prettify">We</spa= n><span style=3D"color: #000;" class=3D"styled-by-prettify"> connect to db = </span><span style=3D"color: #008;" class=3D"styled-by-prettify">from</span= ><span style=3D"color: #000;" class=3D"styled-by-prettify"> multiple proces= ses<br></span><span style=3D"color: #660;" class=3D"styled-by-prettify">-</= span><span style=3D"color: #000;" class=3D"styled-by-prettify"> </span><spa= n style=3D"color: #066;" class=3D"styled-by-prettify">2</span><span style= =3D"color: #000;" class=3D"styled-by-prettify"> read process </span><span s= tyle=3D"color: #660;" class=3D"styled-by-prettify">(</span><span style=3D"c= olor: #000;" class=3D"styled-by-prettify">async read </span><span style=3D"= color: #008;" class=3D"styled-by-prettify">with</span><span style=3D"color:= #000;" class=3D"styled-by-prettify"> </span><span style=3D"color: #008;" c= lass=3D"styled-by-prettify">our</span><span style=3D"color: #000;" class=3D= "styled-by-prettify"> async connection pool to limit the connections</span>= <span style=3D"color: #660;" class=3D"styled-by-prettify">)</span><span sty= le=3D"color: #000;" class=3D"styled-by-prettify"><br></span><span style=3D"= color: #660;" class=3D"styled-by-prettify">-</span><span style=3D"color: #0= 00;" class=3D"styled-by-prettify"> </span><span style=3D"color: #066;" clas= s=3D"styled-by-prettify">1</span><span style=3D"color: #000;" class=3D"styl= ed-by-prettify"> write process </span><span style=3D"color: #660;" class=3D= "styled-by-prettify">(</span><span style=3D"color: #000;" class=3D"styled-b= y-prettify">async write</span><span style=3D"color: #660;" class=3D"styled-= by-prettify">,</span><span style=3D"color: #000;" class=3D"styled-by-pretti= fy"> serialized </span><span style=3D"color: #008;" class=3D"styled-by-pret= tify">with</span><span style=3D"color: #000;" class=3D"styled-by-prettify">= batch</span><span style=3D"color: #660;" class=3D"styled-by-prettify">)</s= pan><span style=3D"color: #000;" class=3D"styled-by-prettify"><br><br><br><= /span><span style=3D"color: #606;" class=3D"styled-by-prettify">Our</span><= span style=3D"color: #000;" class=3D"styled-by-prettify"> writer </span><sp= an style=3D"color: #008;" class=3D"styled-by-prettify">is</span><span style= =3D"color: #000;" class=3D"styled-by-prettify"> async write</span><span sty= le=3D"color: #660;" class=3D"styled-by-prettify">,</span><span style=3D"col= or: #000;" class=3D"styled-by-prettify"> uses queue </span><span style=3D"c= olor: #008;" class=3D"styled-by-prettify">and</span><span style=3D"color: #= 000;" class=3D"styled-by-prettify"> writes them </span><span style=3D"color= : #008;" class=3D"styled-by-prettify">in</span><span style=3D"color: #000;"= class=3D"styled-by-prettify"> batches </span><span style=3D"color: #660;" = class=3D"styled-by-prettify">(</span><span style=3D"color: #000;" class=3D"= styled-by-prettify">so all writes are serial</span><span style=3D"color: #6= 60;" class=3D"styled-by-prettify">).</span><span style=3D"color: #000;" cla= ss=3D"styled-by-prettify"> <br><br><br></span><span style=3D"color: #606;" = class=3D"styled-by-prettify">We</span><span style=3D"color: #000;" class=3D= "styled-by-prettify"> have bridged zodb sync stuff </span><span style=3D"co= lor: #008;" class=3D"styled-by-prettify">with</span><span style=3D"color: #= 000;" class=3D"styled-by-prettify"> async</span><span style=3D"color: #660;= " class=3D"styled-by-prettify">,</span><span style=3D"color: #000;" class= =3D"styled-by-prettify"> we run all the zodb sync </span><span style=3D"col= or: #606;" class=3D"styled-by-prettify">APIs</span><span style=3D"color: #0= 00;" class=3D"styled-by-prettify"> </span><span style=3D"color: #008;" clas= s=3D"styled-by-prettify">in</span><span style=3D"color: #000;" class=3D"sty= led-by-prettify"> threadpool executor </span><span style=3D"color: #008;" c= lass=3D"styled-by-prettify">except</span><span style=3D"color: #000;" class= =3D"styled-by-prettify"> connection handling </span><span style=3D"color: #= 660;" class=3D"styled-by-prettify">(</span><span style=3D"color: #000;" cla= ss=3D"styled-by-prettify">so the reason </span><span style=3D"color: #008;"= class=3D"styled-by-prettify">for</span><span style=3D"color: #000;" class= =3D"styled-by-prettify"> having </span><span style=3D"color: #008;" class= =3D"styled-by-prettify">our</span><span style=3D"color: #000;" class=3D"sty= led-by-prettify"> async connection pool </span><span style=3D"color: #008;"= class=3D"styled-by-prettify">and</span><span style=3D"color: #000;" class= =3D"styled-by-prettify"> prefork conneciton to </span><span style=3D"color:= #008;" class=3D"styled-by-prettify">not</span><span style=3D"color: #000;"= class=3D"styled-by-prettify"> block the main thread</span><span style=3D"c= olor: #660;" class=3D"styled-by-prettify">).</span><span style=3D"color: #0= 00;" class=3D"styled-by-prettify"> </span><span style=3D"color: #606;" clas= s=3D"styled-by-prettify">Also</span><span style=3D"color: #000;" class=3D"s= tyled-by-prettify"> zodb </span><span style=3D"color: #008;" class=3D"style= d-by-prettify">internal</span><span style=3D"color: #000;" class=3D"styled-= by-prettify"> connection pool </span><span style=3D"color: #660;" class=3D"= styled-by-prettify">(</span><span style=3D"color: #000;" class=3D"styled-by= -prettify">sync</span><span style=3D"color: #660;" class=3D"styled-by-prett= ify">)</span><span style=3D"color: #000;" class=3D"styled-by-prettify"> doe= sn</span><span style=3D"color: #080;" class=3D"styled-by-prettify">'t b= ound to the limit, and pool size can go unbounded.. causing memory havoc (w= e can limit memory heap, etc I guess).. so we would want to control the poo= l. Main thread take care of ensuring the connections are shared amoung the = coroutines and which inturn runs inside a thread.. and when context ends, p= ool get the connection back.. <br><br><br><br></span></div></code></div><di= v><br><br><br><br></div><blockquote class=3D"gmail_quote" style=3D"margin: = 0px 0px 0px 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left:= 1ex;">=C2=A0Auto-checkpoints should be enabled by default, unless your cop= y of sqlite was compiled in a strange way. RelStorage does not disable auto= -checkpoints, but it will log what the default value is when a connection i= s opened (it's usually 1000):</blockquote><div><br></div><div>We are us= ing standard sqlite compiled version.. Ya we have checked about the 1k page= default value..=C2=A0 <br><br>https://github.com/zodb/relstorage/blob/mast= er/src/relstorage/adapters/sqlite/drivers.py#L512</div><div>But comment her= e and the default value got us thinking like that.. Perhaps, we should have= debuggged connection default settings..=C2=A0</div><div><br></div><div>I h= ave tested this, even with small values, WAL size still going up with 50 re= quests per second. so autocheckpoint may not help us much I guess if we hav= e not mistaken.</div><div><br></div><div>=C2=A0(The `wal_checkpoint` pragma= is a one-time operation, not a persistent setting that applies to auto-che= ckpoints. The pragmas are executed when a connection is opened, so that las= t line will cause each new connection to run a checkpoint. The function-cal= l syntax needed to actually pass `FULL`, e.g., `PRAGMA wal_checkpoint(FULL)= `, is not supported for the pragmas executed at connection open, though, so= the default value of `PASSIVE` gets used instead. That's probably luck= y, because a FULL checkpoint is a blocking operation and might never succes= sfully complete...)<br></div><div><br></div><div>Oh thanks for insight!=C2= =A0</div><div><br></div><div>Luck here again, however Writes are all serial= ized, we can have FULL mode also if we understand it correctly.. since we a= re not blocking any other writers (single wirte in our case) and not blocki= ng reader here, and our backlog would clear up as soon its done with checkp= oint.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin= : 0px 0px 0px 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-lef= t: 1ex;"><br>=C2=A0 Auto-checkpoints are always PASSIVE checkpoints (and ne= ver block). PASSIVE checkpoints will mark the space in the WAL file that= 9;s available for re-use, but if there are open *transactions* viewing the = database as-of some time in the past, that part of the WAL won't be ava= ilable for re-use. Depending on the workload, those transactions could be u= sing data at the end of the WAL, in which case new uses of the WAL will hav= e to grow the file.<br>=C2=A0The problem is open transactions, not necessar= ily open connections.<br>=C2=A0<br>How do you know if there's an open t= ransaction, and what can you do about controlling them?</blockquote><div><b= r><div><br class=3D"Apple-interchange-newline"><blockquote class=3D"gmail_q= uote" style=3D"margin: 0px 0px 0px 0.8ex; border-left: 1px solid rgb(204, 2= 04, 204); padding-left: 1ex;">=C2=A0First and most importantly, always use = the transaction manager in its explicit mode. There are a number of benefit= s to that, but most relevant here is that when the transaction manager is i= n explicit mode, ZODB alters the way it uses RelStorage, and RelStorage is = able to manage the underlying database transaction in a much better way.</b= lockquote></div><div><br></div></div><div>We use context manager for every = single db operation, so all transactions are commited by the end of the db = operation.=C2=A0</div><div><br></div><div>You have pointed us to right dire= ction about transaction being opened. We are running manual checkpoint also= in the tranaction, I think this is one reason for the issue (but with some= hack - droping load connection before transacion & droping store conne= ction after transaction, we were able to get it working).</div><div><br><br= ><blockquote class=3D"gmail_quote" style=3D"margin: 0px 0px 0px 0.8ex; bord= er-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">2) Manually invo= ke a checkpoint *immediately* after committing. This only works if the tran= saction managers are in explicit mode, and it has to be immediately after c= ommitting so that the connection is not in its own way. This works for a si= ngle writer. (And yes, this is using non-public APIs so it's likely to = break in the future. Hopefully the changes I make to RelStorage alleviate t= he issue enough, but if not we can look at doing something more sophisticat= ed and permanent.)</blockquote><div>=C2=A0</div></div><div>With manual chec= kpoint, explicit mode and not running the manual checkpoint in another tran= ction did the trick.. Its working fine now without hacking sol (droping loa= d connection, etc). We were in wrong impression that issue was due to conne= ctions, but thanks for the hint.<br><br>I think we can leave with it for no= w, but looking forward for the more sophistacted solution to trigger manual= checkpoint..=C2=A0</div><div><br><br></div><div>We apprecaite your prompt = feedback and very thankful for that. Thank you for the sample code, its ver= y useful!<br><br><br></div><br>On Wednesday, 1 July 2020 01:45:50 UTC+5:30,= Jason Madden wrote:<blockquote class=3D"gmail_quote" style=3D"margin: 0;m= argin-left: 0.8ex;border-left: 1px #ccc solid;padding-left: 1ex;">As luck w= ould have it, I've been looking into this very issue over the past few = days and can offer some insight. <br> <br>> On Jun 30, 2020, at 02:13, Venugopal Thotakura <<a href=3D"java= script:" target=3D"_blank" gdf-obfuscated-mailto=3D"W1g1YkUpAgAJ" rel=3D"no= follow" onmousedown=3D"this.href=3D'javascript:';return true;" oncl= ick=3D"this.href=3D'javascript:';return true;">[email protected]</a>= > wrote: <br>>=20 <br>> Hi, <br>>=20 <br>> We are using sqlite storage engine, we are facing issues with chec= kpointing (WAL size going into GB.. It looks like by default checkpointing = is disabled, so we enabled auto checkpointing.. <br> <br>Auto-checkpoints should be enabled by default, unless your copy of sqli= te was compiled in a strange way. RelStorage does not disable auto-checkpoi= nts, but it will log what the default value is when a connection is opened = =C2=A0(it's usually 1000): <br> <br>DEBUG:relstorage.adapters.<wbr>sqlite.drivers:Connection: <Connectio= n at 0x113348c30 to '...' in_transaction=3DFalse>. <br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Using sqlite3 version: = 3.32.3. <br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Default =C2=A0 =C2=A0co= nnection settings: {... 'wal_autocheckpoint': 1000,...} <br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Changing =C2=A0 connect= ion settings: {'synchronous': 2, 'cache_spill': 483, 'f= oreign_keys': 0}. <br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Desired =C2=A0 =C2=A0co= nnection settings: {'synchronous': 1, 'cache_spill': 0, = 9;foreign_keys': 1}. <br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Unapplied =C2=A0connect= ion settings: {}. <br> <br> <br>> <pragmas> <br>> =C2=A0 =C2=A0 =C2=A0 =C2=A0wal_autocheckpoint 100 <br>> =C2=A0 =C2=A0 =C2=A0 =C2=A0wal_checkpoint FULL <br>> </pragmas> <br> <br>(The `wal_checkpoint` pragma is a one-time operation, not a persistent = setting that applies to auto-checkpoints. The pragmas are executed when a c= onnection is opened, so that last line will cause each new connection to ru= n a checkpoint. The function-call syntax needed to actually pass `FULL`, e.= g., `PRAGMA wal_checkpoint(FULL)`, is not supported for the pragmas execute= d at connection open, though, so the default value of `PASSIVE` gets used i= nstead. That's probably lucky, because a FULL checkpoint is a blocking = operation and might never successfully complete...) <br> <br>>=20 <br>> Now, It looks like its trying to do checkpointing, however due to = the connections arleady open, it couldn't do that.. <br> <br>Auto-checkpoints are always PASSIVE checkpoints (and never block). PASS= IVE checkpoints will mark the space in the WAL file that's available fo= r re-use, but if there are open *transactions* viewing the database as-of s= ome time in the past, that part of the WAL won't be available for re-us= e. Depending on the workload, those transactions could be using data at the= end of the WAL, in which case new uses of the WAL will have to grow the fi= le. <br> <br>The problem is open transactions, not necessarily open connections. <br> <br>How do you know if there's an open transaction, and what can you do= about controlling them? <br> <br>> So to give overview of our architecture.. <br>>=20 <br>> We connect to db from multiple processes <br>> - 2 read process (async read with our async connection pool to lim= it the connections) <br>> - 1 write process (async write) <br>>=20 <br>>=20 <br>> Here is our async pool.. we prefork the connections (we are seeing= sometimes db.open is taking longer time to create connections, so we are d= oing prefork) and use them. <br> <br> <br>First and most importantly, always use the transaction manager in its e= xplicit mode. There are a number of benefits to that, but most relevant her= e is that when the transaction manager is in explicit mode, ZODB alters the= way it uses RelStorage, and RelStorage is able to manage the underlying da= tabase transaction in a much better way.=20 <br> <br>Second, to be able to use explicit mode (as well as one of the mitigati= ons discussed below), I suspect you may need to drop the idea of "pre-= forking" connections. Let the ZODB DB and its connection pool manage t= hat. (If you're find it sometimes slow to open connections, you may nee= d to adjust the size of the ZODB connection pool.) Begin a transaction, ope= n a connection, do the work, then commit/rollback the transaction, and fina= lly close the connection. By carefully bounding the transaction and connect= ion lifecycle this way, together with using an explicit transaction manager= , you can be sure about the lifetime of the underlying database transaction= as well. <br> <br>Lastly (and this is not a problem on your side, it's an issue I nee= d to address in RelStorage) having exactly one concurrent writer actually e= xacerbates the situation a little bit. That's because of the way connec= tions are internally handled in RelStorage. Basically, one connection can g= et in its own way (again, depending on the workload) and when a commit trig= gers an auto-checkpoint, it may not be able to clean up all the WAL pages. = But if another connection commits shortly after that and triggers an auto-c= heckpoint, then it may be able to make more progress. If there's only e= ver one writer, though, it's likely to keep tripping over its own feet.= (This can still happen with multiple writers but I think it's more rar= e.) <br> <br>This is something I will change in RelStorage (I have a prototype fix n= ow). But in the meantime, there are two possible mitigations:=20 <br> <br>1) Use a very low `wal_autocheckpoint` so that connections are cleaning= up after each other more often. This only matters, of course, if there is = more than one writer. <br>2) Manually invoke a checkpoint *immediately* after committing. This on= ly works if the transaction managers are in explicit mode, and it has to be= immediately after committing so that the connection is not in its own way.= This works for a single writer. (And yes, this is using non-public APIs so= it's likely to break in the future. Hopefully the changes I make to Re= lStorage alleviate the issue enough, but if not we can look at doing someth= ing more sophisticated and permanent.) <br> <br> <br>Here's an example program demonstrating this. If the transaction ma= nagers are in explicit mode, then the WAL never grows beyond two pages (8K)= ; if they are left implicit, then by the time executing finishes, the WAL h= as grown to 80MB. <br> <br> <br>import os <br>import logging <br> <br>import transaction <br>from ZODB.config import databaseFromString <br> <br>db_config =3D """ <br>%import relstorage <br><zodb> <br>=C2=A0 pool-size 1 <br>=C2=A0 <relstorage> <br>=C2=A0 =C2=A0 =C2=A0keep-history false <br>=C2=A0 =C2=A0 =C2=A0<sqlite3> <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 data-dir /tmp/rstest <br>=C2=A0 =C2=A0 =C2=A0</sqlite3> <br>=C2=A0 </relstorage> <br></zodb> <br>""" <br> <br>logger =3D logging.getLogger(__name__) <br> <br>def report_sizes(): <br>=C2=A0 =C2=A0 os.system('ls -lh /tmp/rstest') <br> <br>def run_transaction(db): <br>=C2=A0 =C2=A0 tx =3D transaction.begin() <br>=C2=A0 =C2=A0 conn =3D db.open() <br>=C2=A0 =C2=A0 root =3D conn.root() <br>=C2=A0 =C2=A0 root['key'] =3D 'abcd' * 1000 <br>=C2=A0 =C2=A0 tx.commit() <br>=C2=A0 =C2=A0 conn.close() <br>=C2=A0 =C2=A0 checkpoint(conn) <br> <br>def checkpoint(conn): <br>=C2=A0 =C2=A0 sc =3D conn._storage._load_<wbr>connection.cursor <br>=C2=A0 =C2=A0 sc.execute('pragma main.wal_checkpoint(passive)') <br>=C2=A0 =C2=A0 sc.fetchall() # Must fetchall! Plus there's actual us= eful info in the results. <br> <br> <br>def main(): <br>=C2=A0 =C2=A0 logging.basicConfig(level=3D<wbr>logging.DEBUG) <br>=C2=A0 =C2=A0 logging.getLogger('txn').<wbr>setLevel(logging.ER= ROR) <br>=C2=A0 =C2=A0 # Make sure the global (default) transaction manager is <br>=C2=A0 =C2=A0 # explicit. <br>=C2=A0 =C2=A0 transaction.manager.explicit =3D True <br>=C2=A0 =C2=A0 db =3D databaseFromString(db_config) <br> <br> <br>=C2=A0 =C2=A0 # Open an isolated connection. If the transaction <br>=C2=A0 =C2=A0 # manager isn't in explicit mode, it really will talk= to <br>=C2=A0 =C2=A0 # the database now, which will cause WAL to start accumul= ating. <br>=C2=A0 =C2=A0 # Use explicit transaction managers to prevent that from = happening <br>=C2=A0 =C2=A0 # and keep a tight reign on transaction duration. <br>=C2=A0 =C2=A0 isolated_txm =3D transaction.<wbr>TransactionManager() <br>=C2=A0 =C2=A0 isolated_txm.explicit =3D True # If this is commented out= , the WAL grows without bound <br>=C2=A0 =C2=A0 c1 =3D db.open(isolated_txm) <br> <br>=C2=A0 =C2=A0 report_sizes() <br>=C2=A0 =C2=A0 for _ in range(10): <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 for _ in range(1000): <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 run_transaction(db) <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 report_sizes() <br> <br>=C2=A0 =C2=A0 c1.close() <br>=C2=A0 =C2=A0 db.close() <br>=C2=A0 =C2=A0 report_sizes() <br> <br> <br>if __name__ =3D=3D '__main__': <br>=C2=A0 =C2=A0 main() <br> <br>~Jason <br> <br></blockquote></div> <p></p> -- <br /> You received this message because you are subscribed to the Google Groups &= quot;zodb" group.<br /> To unsubscribe from this group and stop receiving emails from it, send an e= mail to <a href=3D"mailto:[email protected]">zodb+unsubscri= [email protected]</a>.<br /> To view this discussion on the web visit <a href=3D"https://groups.google.c= om/d/msgid/zodb/ea577081-bbb3-436f-9281-2af3c282983ao%40googlegroups.com?ut= m_medium=3Demail&utm_source=3Dfooter">https://groups.google.com/d/msgid/zod= b/ea577081-bbb3-436f-9281-2af3c282983ao%40googlegroups.com</a>.<br /> ------=_Part_1091_1847811118.1593599502589-- ------=_Part_1090_121096353.1593599502589--