| Newsgroups |
gmane.comp.web.zope.zodb |
| Message-ID |
<[email protected]> |
------=_Part_994_2007232602.1593502405304
Content-Type: multipart/alternative;
boundary="----=_Part_995_149967897.1593502405304"
------=_Part_995_149967897.1593502405304
Content-Type: text/plain; charset="UTF-8"
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..
<pragmas>
wal_autocheckpoint 100
wal_checkpoint FULL
</pragmas>
Now, It looks like its trying to do checkpointing, however due to the
connections arleady open, it couldn't do that..
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)
Also we have tried to manually run the checkpoint, for every 1min (but no
luck)
conn._storage._load_connection.cursor.execute("PRAGMA wal_checkpoint(3)")
we are getting database locked issue.. so I presume, since read connections
are already opened, its unable to complete the process.. I could confirm
when read processes are killed and write can execute it.
Traceback (most recent call last):
File "/opt/app/xxx.py", line 508, in updater
conn._storage._load_connection.cursor.execute("PRAGMA wal_checkpoint(3)")
File
"/usr/local/lib/python3.7/site-packages/relstorage/adapters/sqlite/drivers.py",
line 113, in execute
return sqlite3.Cursor.execute(self, stmt)
sqlite3.OperationalError: database table is locked
Also I could confirm same behaviour from sqlite3 client.
/opt/app # sqlite3 main.sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> PRAGMA wal_checkpoint(3);
0|1148|1148
sqlite>
We have tried closing connections, but zodb connection object close doesn't
close the underlying storage connections.. instead it put them in the
internal pool.. we could find a way to close storage connections with
public APIs.
We have tried to the drop the internal storage conenctions (_load &
_store), then it seems to be working well..
(env) venu@venu:~/workspace/xxx$ lsof main.sqlite3
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
python 1953 venu 8ur REG 8,2 376709120 14429800 main.sqlite3
python 1953 venu 12ur REG 8,2 376709120 14429800 main.sqlite3
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.
def ctor(self):
tm = transaction.TransactionManager()
conn = db.open(tm)
return conn, tm
store = {
"read": [asyncio.Semaphore(value=rlimit), []],
"write": [asyncio.Semaphore(value=wlimit), []],
}
store["read"][1] = [ctor() for i in range(rlimit)]
store["write"][1] = [ctor() for i in range(wlimit)]
@asynccontextmanager
async def pooled(mode="read"):
lock = store[mode][0]
conns = store[mode][1]
await lock.acquire()
try:
conn = conns.pop(0)
if conn[0].opened is None:
logging.info("connection closed, creating new one")
conn = ctor()
yield conn
finally:
conns.append(conn)
# conn[0].close()
conn[0]._storage._load_connection.drop()
conn[0]._storage._store_connection.drop()
lock.release()
We find this not proper way, kind of hackish and also, lots of stuff is
obscure for us still..
So would like to confirm with you whether we are missing anything or is
there any API's exsits to clear storage conenctions to allow sqlite to do
checkpointing..
Thanks in advace..
- Venu
--
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/fc68a966-8489-4c3a-bc06-eb347b7310bfn%40googlegroups.com.
------=_Part_995_149967897.1593502405304
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div>Hi,</div><div><br></div><div>We are using sqlite storage engine, we ar=
e facing issues with checkpointing (WAL size going into GB.. It looks like =
by default checkpointing is disabled, so we enabled auto checkpointing..</d=
iv><div><br></div><pragmas><br> wal_autocheckpoint 100<br> wal_checkp=
oint FULL<br></pragmas><br><br><div>Now, It looks like its trying to =
do checkpointing, however due to the connections arleady open, it couldn't =
do that..</div><div><br></div><div>So to give overview of our architecture.=
.</div><div><br></div><div>We connect to db from multiple processes</div><d=
iv>- 2 read process (async read with our async connection pool to limit the=
connections)</div><div>- 1 write process (async write)</div><div><br></div=
><div>Also we have tried to manually run the checkpoint, for every 1m=
in (but no luck)</div><div>conn._storage._load_connection.cursor.execute("P=
RAGMA wal_checkpoint(3)")</div><div><br></div><div><br></div><div>we are ge=
tting database locked issue.. so I presume, since read connections are alre=
ady opened, its unable to complete the process.. I could confirm when read =
processes are killed and write can execute it.</div><div>Traceback (most re=
cent call last):</div><div> File "/opt/app/xxx.py", line 508, in updat=
er</div><div> conn._storage._load_connection.cursor.execute("PRAGMA wa=
l_checkpoint(3)")</div><div> File "/usr/local/lib/python3.7/site-packa=
ges/relstorage/adapters/sqlite/drivers.py", line 113, in execute</div><div>=
return sqlite3.Cursor.execute(self, stmt)</div><div>sqlite3.Operation=
alError: database table is locked</div><div><br></div><div><br></div><div>A=
lso I could confirm same behaviour from sqlite3 client.</div><div>/opt/app =
# sqlite3 main.sqlite3</div><div>SQLite version 3.28.0 2019-04-16 19:49:53<=
/div><div>Enter ".help" for usage hints.</div><div>sqlite> PRAGMA wal_ch=
eckpoint(3);</div><div>0|1148|1148</div><div>sqlite></div><div><br></div=
><div><br></div><div>We have tried closing connections, but zodb connection=
object close doesn't close the underlying storage connections.. instead it=
put them in the internal pool.. we could find a way to close storage conne=
ctions with public APIs.</div><div><br></div><div>We have tried to the drop=
the internal storage conenctions (_load & _store), then it seems to be=
working well..</div><div>(env) venu@venu:~/workspace/xxx$ lsof main.sqlite=
3</div><div>COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME</div><div>py=
thon 1953 venu 8ur REG 8,2 376709120 14429800 main.sqlite3</div><div>python=
1953 venu 12ur REG 8,2 376709120 14429800 main.sqlite3</div><div><br></div=
><div><br></div><div>Here is our async pool.. we prefork the connections (w=
e are seeing sometimes db.open is taking longer time to create connections,=
so we are doing prefork) and use them.</div><div><br></div><div>def ctor(s=
elf):</div><div> tm =3D transaction.TransactionM=
anager()</div><div> conn =3D db.open(tm)</div><d=
iv> return conn, tm</div><div><br></div><div><br=
></div><div>store =3D {</div><div> =
"read": [asyncio.Semaphore(value=3Drlimit), []],</div><div> &=
nbsp; "write": [asyncio.Semaphore(value=3Dwlimit), []]=
,</div><div> }</div><div>store["read"][1] =3D [c=
tor() for i in range(rlimit)]</div><div>store["write"][1] =3D [ctor() for i=
in range(wlimit)]</div><div><br></div><div><br></div><div>@asynccontextman=
ager</div><div> async def pooled(mode=3D"read"):=
</div><div> lock =3D store[mode][0=
]</div><div> conns =3D store[mode]=
[1]</div><div> await lock.acquire(=
)</div><div> try:</div><div> =
conn =3D conns.pop(0)</di=
v><div> if conn[0].o=
pened is None:</div><div> &=
nbsp; logging.info("connection closed, creating new one")</di=
v><div>  =
; conn =3D ctor()</div><div>  =
; yield conn</div><div> fin=
ally:</div><div> con=
ns.append(conn)</div><div> =
# conn[0].close()</div><div>  =
; conn[0]._storage._load_connection.drop()</div><div> &=
nbsp; conn[0]._storage._store_con=
nection.drop()</div><div> &=
nbsp; lock.release()</div><div><br></div><div><br></div><div><br></div><div=
>We find this not proper way, kind of hackish and also, lots of stuff is ob=
scure for us still.. </div><div><br></div><div>So would like to confir=
m with you whether we are missing anything or is there any API's exsits to =
clear storage conenctions to allow sqlite to do checkpointing..</div><div><=
br></div><div>Thanks in advace..</div><div><br></div><div><br></div><div>- =
Venu</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/fc68a966-8489-4c3a-bc06-eb347b7310bfn%40googlegroups.com?ut=
m_medium=3Demail&utm_source=3Dfooter">https://groups.google.com/d/msgid/zod=
b/fc68a966-8489-4c3a-bc06-eb347b7310bfn%40googlegroups.com</a>.<br />
------=_Part_995_149967897.1593502405304--
------=_Part_994_2007232602.1593502405304--