Skip to contentSkip to conversation

prserv: Enable database sharing

RFC Superseded Joshua Watt 2 participants 14 messages Apr 23, 2024 – Apr 30, 2024 3 messages not in the archive

lore
Share & tools
lore
#0 lore raw

RFC 1/1 prserv: Enable database sharing

sqlite3 can allow multiple processes to access the database simultaneously, but it must be opened correctly. The key change is that the database is no longer opened in "exclusive" mode (defaulting to shared mode). In addition, the journal is set to "WAL" mode, as this is the most efficient for dealing with simultaneous access between different processes. In order to keep the database performance, synchronous mode is set to "off". The WAL journal will protect against incomplete transactions in any given client, however the database will not be protected against unexpected power loss from the OS (which is a fine trade off for performance, and also the same as the previous implementation).

Finally, the _execute() API now uses a database cursor. The cursor automatically makes sure that the query happens in an atomic transaction and commits when finished.

NOTE: THIS MAY BE INCOMPLETE; All APIs need to be evaluated to see if the transaction (cursor) needs to cover mode than one "execute" statement.

---
 bitbake/lib/prserv/db.py | 7 +++----
 1 file changed, 3 insertions(+), 4 deletions(-)
bitbake/lib/prserv/db.py +3 -4
index b4bda7078cd..e7a2d27e950 100644
@@ -292,11 +292,10 @@ class PRData(object):
                 raise e
         uri = "file:%s%s" % (self.filename, "?mode=ro" if self.read_only else "")
         logger.debug("Opening PRServ database '%s'" % (uri))
-        self.connection=sqlite3.connect(uri, uri=True, isolation_level="EXCLUSIVE", check_same_thread = False)
+        self.connection=sqlite3.connect(uri, uri=True)
         self.connection.row_factory=sqlite3.Row
-        if not self.read_only:
-            self.connection.execute("pragma synchronous = off;")
-            self.connection.execute("PRAGMA journal_mode = MEMORY;")
+        self.connection.execute("pragma synchronous = off;")
+        self.connection.execute("PRAGMA journal_mode = WAL;")
         self._tables={}
 
     def disconnect(self):
Signature · 1 line
2.34.1
#1 lore raw

RFC v2 1/1 prserv: Enable database sharing

sqlite3 can allow multiple processes to access the database simultaneously, but it must be opened correctly. The key change is that the database is no longer opened in "exclusive" mode (defaulting to shared mode). In addition, the journal is set to "WAL" mode, as this is the most efficient for dealing with simultaneous access between different processes. In order to keep the database performance, synchronous mode is set to "off". The WAL journal will protect against incomplete transactions in any given client, however the database will not be protected against unexpected power loss from the OS (which is a fine trade off for performance, and also the same as the previous implementation).

Finally, the _execute() API now uses a database cursor. The cursor automatically makes sure that the query happens in an atomic transaction and commits when finished.

NOTE: THIS MAY BE INCOMPLETE; All APIs need to be evaluated to see if the transaction (cursor) needs to cover mode than one "execute" statement.

---
 bitbake/lib/prserv/db.py | 19 ++++++++-----------
 1 file changed, 8 insertions(+), 11 deletions(-)
bitbake/lib/prserv/db.py +8 -11
index b4bda7078cd..8ee8d458194 100644
@@ -9,6 +9,7 @@ import os.path
 import errno
 import prserv
 import time
+from contextlib import closing
 
 try:
     import sqlite3
@@ -62,21 +63,18 @@ class PRTable(object):
         end = start + 20
         while True:
             try:
-                return self.conn.execute(*query)
+                with closing(self.conn.cursor()) as cursor:
+                    return cursor.execute(*query)
             except sqlite3.OperationalError as exc:
                 if 'is locked' in str(exc) and end > time.time():
                     continue
                 raise exc
 
     def sync(self):
-        if not self.read_only:
-            self.conn.commit()
-            self._execute("BEGIN EXCLUSIVE TRANSACTION")
+        pass
 
     def sync_if_dirty(self):
-        if self.dirty:
-            self.sync()
-            self.dirty = False
+        pass
 
     def _getValueHist(self, version, pkgarch, checksum):
         data=self._execute("SELECT value FROM %s WHERE version=? AND pkgarch=? AND checksum=?;" % self.table,
@@ -292,11 +290,10 @@ class PRData(object):
                 raise e
         uri = "file:%s%s" % (self.filename, "?mode=ro" if self.read_only else "")
         logger.debug("Opening PRServ database '%s'" % (uri))
-        self.connection=sqlite3.connect(uri, uri=True, isolation_level="EXCLUSIVE", check_same_thread = False)
+        self.connection=sqlite3.connect(uri, uri=True)
         self.connection.row_factory=sqlite3.Row
-        if not self.read_only:
-            self.connection.execute("pragma synchronous = off;")
-            self.connection.execute("PRAGMA journal_mode = MEMORY;")
+        self.connection.execute("pragma synchronous = off;")
+        self.connection.execute("PRAGMA journal_mode = WAL;")
         self._tables={}
 
     def disconnect(self):
Signature · 1 line
2.34.1
#2 lore raw

Hi Joshua

Quoted 11 lines · Joshua Watt

sqlite3 can allow multiple processes to access the database simultaneously, but it must be opened correctly. The key change is that the database is no longer opened in "exclusive" mode (defaulting to shared mode). In addition, the journal is set to "WAL" mode, as this is the most efficient for dealing with simultaneous access between different processes. In order to keep the database performance, synchronous mode is set to "off". The WAL journal will protect against incomplete transactions in any given client, however the database will not be protected against unexpected power loss from the OS (which is a fine trade off for performance, and also the same as the previous implementation).

Thanks, I also made experiments with the "WAL" journal mode, but didn't go on as it didn't solve my empty database issues. However, I agree it seems like the way to go!

Quoted 5 lines

Finally, the _execute() API now uses a database cursor. The cursor automatically makes sure that the query happens in an atomic transaction and commits when finished.

Quoted 8 lines

NOTE: THIS MAY BE INCOMPLETE; All APIs need to be evaluated to see if the transaction (cursor) needs to cover mode than one "execute" statement.

---
  bitbake/lib/prserv/db.py | 19 ++++++++-----------
  1 file changed, 8 insertions(+), 11 deletions(-)

Which branch did you use to generate your patch? I can't manage to apply it to either "master", "master-next" or the branch I shared recently...

Ah, just found it. You used "nanbield".

I'll test it in my branch and "torture" it with regular tests and my new bitbake selftests. Thanks,
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

A message that is not in the archive (sent off-list or not yet received): [email protected]

#4 lore raw

Hi Joshua

Quoted 5 lines · Michael Opdenacker via lists.openembedded.org

Which branch did you use to generate your patch? I can't manage to apply it to either "master", "master-next" or the branch I shared recently...

Ah, just found it. You used "nanbield".

I'll test it in my branch and "torture" it with regular tests and my new bitbake selftests.

Oops, the tests are failing:

======================================================================
ERROR: test_0b_db (prserv.tests.FunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/mike/work/yocto/poky/bitbake/lib/prserv/tests.py", line 114, in test_0b_db self.assertTrue(self.table.test_package(version, pkgarch))
  File "/home/mike/work/yocto/poky/bitbake/lib/prserv/db.py", line 76, in test_package row=data.fetchone()
sqlite3.ProgrammingError: Cannot operate on a closed cursor.

Let me send you my patch series with your change included. I'll send them through a private e-mail. Don't want to spam the list at this stage. Thanks
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#5 lore raw
Quoted 15 lines · Michael Opdenacker

Hi Joshua

Quoted 8 lines · Michael Opdenacker via lists.openembedded.org

Which branch did you use to generate your patch? I can't manage to apply it to either "master", "master-next" or the branch I shared recently...

Ah, just found it. You used "nanbield".

I'll test it in my branch and "torture" it with regular tests and my new bitbake selftests.

Oops, the tests are failing:

Quoted 11 lines

======================================================================
ERROR: test_0b_db (prserv.tests.FunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/mike/work/yocto/poky/bitbake/lib/prserv/tests.py", line
114, in test_0b_db
self.assertTrue(self.table.test_package(version, pkgarch))
File "/home/mike/work/yocto/poky/bitbake/lib/prserv/db.py", line 76,
in test_package
row=data.fetchone()
sqlite3.ProgrammingError: Cannot operate on a closed cursor.

Ah right. Ok so instead of doing the cursor operation in the _exectue(), you'll need to do it in each API call. The general strategy is to do:

def _importHist():
if self.read_only:
return None
with closing(self.conn.cursor()) as cursor:
# Use cursor.execute() for all statements, e.g. row = cursor.execute("SELECT value ....") data = row.fetchone()

You can use the same cursor for multiple queries, so you only need the one for each API call (e.g. you don't need to make a new one for each execute statement).

With this strategy I don't think you'll ever get DB locking errors, so you should be able to eliminate the _execute() wrapper

Just make sure you _always_ use a cursor to execute any database queries. It will ruin your day if you combine self.conn.execute() with cursor queries :)

The cursor makes your database modifications atomic, that is any changes you make to the database aren't applied until the cursor is closed, and then they are applied atomically so either all the changes you made are commited, or none of them. Keep in mind that this does _not_ mean that your database reads are also atomic with your writes; if another process commits to the database between your read and write (or even between two reads) it's possible you will be dealing with a stale understanding of the database when you commit.

Sorry about the nanbield branch, I wasn't paying attention

Quoted 11 lines

Let me send you my patch series with your change included. I'll send them through a private e-mail. Don't want to spam the list at this stage. Thanks
Michael.

--
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

#6 lore raw

Hi Joshua

Many thanks for the tips!

Quoted 58 lines · Joshua Watt
Quoted 13 lines · Michael Opdenacker

Hi Joshua

Quoted 8 lines · Michael Opdenacker via lists.openembedded.org

Which branch did you use to generate your patch? I can't manage to apply it to either "master", "master-next" or the branch I shared recently...

Ah, just found it. You used "nanbield".

I'll test it in my branch and "torture" it with regular tests and my new bitbake selftests.

Oops, the tests are failing:

Quoted 11 lines

======================================================================
ERROR: test_0b_db (prserv.tests.FunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/mike/work/yocto/poky/bitbake/lib/prserv/tests.py", line
114, in test_0b_db
self.assertTrue(self.table.test_package(version, pkgarch))
File "/home/mike/work/yocto/poky/bitbake/lib/prserv/db.py", line 76,
in test_package
row=data.fetchone()
sqlite3.ProgrammingError: Cannot operate on a closed cursor.

Ah right. Ok so instead of doing the cursor operation in the _exectue(), you'll need to do it in each API call. The general strategy is to do:

def _importHist():
if self.read_only:
return None
with closing(self.conn.cursor()) as cursor:
# Use cursor.execute() for all statements, e.g. row = cursor.execute("SELECT value ....") data = row.fetchone()

You can use the same cursor for multiple queries, so you only need the one for each API call (e.g. you don't need to make a new one for each execute statement).

With this strategy I don't think you'll ever get DB locking errors, so you should be able to eliminate the _execute() wrapper

Just make sure you _always_ use a cursor to execute any database queries. It will ruin your day if you combine self.conn.execute() with cursor queries :)

The cursor makes your database modifications atomic, that is any changes you make to the database aren't applied until the cursor is closed, and then they are applied atomically so either all the changes you made are commited, or none of them. Keep in mind that this does _not_ mean that your database reads are also atomic with your writes; if another process commits to the database between your read and write (or even between two reads) it's possible you will be dealing with a stale understanding of the database when you commit.

Quoted 1 line

Sorry about the nanbield branch, I wasn't paying attention

What you suggest seems to work fine.

By the way, I still had empty databases after leaving the tests, but adding:
self.conn.commit()
after a group or INSERT or UPDATE commands did the trick.

What's strange is that the hashserver code does without doing this.

Anyway, let me test that two servers can access the same database now. If everything works, I'll be able to send a V5 soon!

Cheers
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#7 lore raw
Quoted 66 lines · Michael Opdenacker

Hi Joshua

Many thanks for the tips!

Quoted 58 lines · Joshua Watt
Quoted 13 lines · Michael Opdenacker

Hi Joshua

Quoted 8 lines · Michael Opdenacker via lists.openembedded.org

Which branch did you use to generate your patch? I can't manage to apply it to either "master", "master-next" or the branch I shared recently...

Ah, just found it. You used "nanbield".

I'll test it in my branch and "torture" it with regular tests and my new bitbake selftests.

Oops, the tests are failing:

Quoted 11 lines

======================================================================
ERROR: test_0b_db (prserv.tests.FunctionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/mike/work/yocto/poky/bitbake/lib/prserv/tests.py", line
114, in test_0b_db
self.assertTrue(self.table.test_package(version, pkgarch))
File "/home/mike/work/yocto/poky/bitbake/lib/prserv/db.py", line 76,
in test_package
row=data.fetchone()
sqlite3.ProgrammingError: Cannot operate on a closed cursor.

Ah right. Ok so instead of doing the cursor operation in the _exectue(), you'll need to do it in each API call. The general strategy is to do:

def _importHist():
if self.read_only:
return None
with closing(self.conn.cursor()) as cursor:
# Use cursor.execute() for all statements, e.g. row = cursor.execute("SELECT value ....") data = row.fetchone()

You can use the same cursor for multiple queries, so you only need the one for each API call (e.g. you don't need to make a new one for each execute statement).

With this strategy I don't think you'll ever get DB locking errors, so you should be able to eliminate the _execute() wrapper

Just make sure you _always_ use a cursor to execute any database queries. It will ruin your day if you combine self.conn.execute() with cursor queries :)

The cursor makes your database modifications atomic, that is any changes you make to the database aren't applied until the cursor is closed, and then they are applied atomically so either all the changes you made are commited, or none of them. Keep in mind that this does _not_ mean that your database reads are also atomic with your writes; if another process commits to the database between your read and write (or even between two reads) it's possible you will be dealing with a stale understanding of the database when you commit.

Quoted 1 line

Sorry about the nanbield branch, I wasn't paying attention

Quoted 7 lines

What you suggest seems to work fine.

By the way, I still had empty databases after leaving the tests, but adding:
self.conn.commit()
after a group or INSERT or UPDATE commands did the trick.

What's strange is that the hashserver code does without doing this.

It has commit() calls also, look in sqlite.py. I frequently forget that the cursor doesn't implicitly commit when closed (unlike SQL Alchemy, which _does_ do that) :)

Quoted 12 lines

Anyway, let me test that two servers can access the same database now. If everything works, I'll be able to send a V5 soon!

Cheers
Michael.

--
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

#8 lore raw

Hi Joshua,

Quoted 3 lines · Joshua Watt

It has commit() calls also, look in sqlite.py. I frequently forget that the cursor doesn't implicitly commit when closed (unlike SQL Alchemy, which _does_ do that) :)

Correct, I must have checked the wrong file.

Actually, my code should have all the commit() calls it needs. I put one every time a change is made to the database.

However, I still have this very weird issue:

* NOK: in bitbake-selftests, the tests that use the PR server run successfully (except test_3b_readonly, because of the issue), but the PR table is empty in the database file (if opened through the sqlite3 command line). It seems that the table is only updated in memory. It must be so, otherwise the tests wouldn't pass.
* OK: the only bitbake-selftests that use the DB functions directly don't have this issue
* OK: creating an image with BitBake does create a non empty table in the PR database file (cache/prserv.sqlite3)

Would anyone have any clue why?

I've just submitted the latest version of my code if you want to have a look or test by yourself:
https://lore.kernel.org/bitbake-devel/[email protected]/T/#t

You can run the bitbake selftests as follows:
bitbake-selftest prserv.tests

Thanks in advance
Cheers
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#9 lore raw
Quoted 25 lines · Michael Opdenacker

Hi Joshua,

Quoted 3 lines · Joshua Watt

It has commit() calls also, look in sqlite.py. I frequently forget that the cursor doesn't implicitly commit when closed (unlike SQL Alchemy, which _does_ do that) :)

Correct, I must have checked the wrong file.

Actually, my code should have all the commit() calls it needs. I put one every time a change is made to the database.

However, I still have this very weird issue:

* NOK: in bitbake-selftests, the tests that use the PR server run successfully (except test_3b_readonly, because of the issue), but the PR table is empty in the database file (if opened through the sqlite3 command line). It seems that the table is only updated in memory. It must be so, otherwise the tests wouldn't pass.
* OK: the only bitbake-selftests that use the DB functions directly don't have this issue
* OK: creating an image with BitBake does create a non empty table in the PR database file (cache/prserv.sqlite3)

Quoted 1 line

Would anyone have any clue why?

I think it might actually be returning the correct value ("0"). Remember that each test case starts with a blank database, and I don't see any code in that populates anything into the database in that test.... so "0" seems like it would be the correct PR value?

Quoted 17 lines

I've just submitted the latest version of my code if you want to have a look or test by yourself:
https://lore.kernel.org/bitbake-devel/[email protected]/T/#t

You can run the bitbake selftests as follows:
bitbake-selftest prserv.tests

Thanks in advance
Cheers
Michael.

--
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

#10 lore raw

Hi Joshua,

Quoted 25 lines · Joshua Watt
Quoted 23 lines · Michael Opdenacker

Hi Joshua,

Quoted 3 lines · Joshua Watt

It has commit() calls also, look in sqlite.py. I frequently forget that the cursor doesn't implicitly commit when closed (unlike SQL Alchemy, which _does_ do that) :)

Correct, I must have checked the wrong file.

Actually, my code should have all the commit() calls it needs. I put one every time a change is made to the database.

However, I still have this very weird issue:

* NOK: in bitbake-selftests, the tests that use the PR server run successfully (except test_3b_readonly, because of the issue), but the PR table is empty in the database file (if opened through the sqlite3 command line). It seems that the table is only updated in memory. It must be so, otherwise the tests wouldn't pass.
* OK: the only bitbake-selftests that use the DB functions directly don't have this issue
* OK: creating an image with BitBake does create a non empty table in the PR database file (cache/prserv.sqlite3)

Quoted 5 lines
Quoted 1 line

Would anyone have any clue why?

I think it might actually be returning the correct value ("0"). Remember that each test case starts with a blank database, and I don't see any code in that populates anything into the database in that test.... so "0" seems like it would be the correct PR value?

Wow, your remark made me understand that the setUp() function is run before *every test*, and not only once for each class of tests! If I merge my read-only tests with the previous ones, making only one test, they now work as expected (except for the very last one, maybe a bug in my code).

Many thanks! You saved my day :)
Cheers
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

A message that is not in the archive (sent off-list or not yet received): [email protected]

#12 lore raw
Quoted 3 lines · Michael Opdenacker via lists.openembedded.org

By the way, I still had empty databases after leaving the tests, but adding:
self.conn.commit()
after a group or INSERT or UPDATE commands did the trick.

For the record, my database was created with the below settings:

self.connection.execute("PRAGMA synchronous = NORMAL;") self.connection.execute("PRAGMA journal_mode = WAL;")

Cheers
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#13 lore raw
Quoted 9 lines · Michael Opdenacker
Quoted 4 lines · Michael Opdenacker via lists.openembedded.org

By the way, I still had empty databases after leaving the tests, but adding:
self.conn.commit()
after a group or INSERT or UPDATE commands did the trick.

Quoted 3 lines

For the record, my database was created with the below settings:

self.connection.execute("PRAGMA synchronous = NORMAL;")

synchronous only affects when sqlite makes the sync() syscall, and therefore should have no impact on multiple processes using the database. Setting it NORMAL will cause some sync() calls to occur such that if power loss occurs, there is no (or very minimal) chance of database corruption (but perhaps lost data). Since sync() generally has a higher than normal impact on Yocto builds, the hash server defaults to OFF, so that there are no sync() calls. The data is correct and fine, as long as no power loss occurs and can be shared between multiple processes and doesn't affect build performance.

Quoted 10 lines

self.connection.execute("PRAGMA journal_mode = WAL;")

Cheers
Michael.

--
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

A message that is not in the archive (sent off-list or not yet received): [email protected]

#15 lore raw
Quoted 11 lines · Michael Opdenacker via lists.openembedded.org
Quoted 3 lines · Michael Opdenacker via lists.openembedded.org

By the way, I still had empty databases after leaving the tests, but adding:
self.conn.commit()
after a group or INSERT or UPDATE commands did the trick.

For the record, my database was created with the below settings:

self.connection.execute("PRAGMA synchronous = NORMAL;") self.connection.execute("PRAGMA journal_mode = WAL;")

Oh, forget it, it's gone again (databases being empty again). Maybe, I looked too quickly. I'll keep investigating and will keep you posted.
Michael.

Signature · 3 lines
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com
#16 lore raw
Quoted 11 lines · Michael Opdenacker
Quoted 8 lines · Michael Opdenacker via lists.openembedded.org
Quoted 4 lines · Michael Opdenacker via lists.openembedded.org

By the way, I still had empty databases after leaving the tests, but adding:
self.conn.commit()
after a group or INSERT or UPDATE commands did the trick.

Quoted 8 lines
Quoted 4 lines

For the record, my database was created with the below settings:

self.connection.execute("PRAGMA synchronous = NORMAL;") self.connection.execute("PRAGMA journal_mode = WAL;")

Oh, forget it, it's gone again (databases being empty again). Maybe, I looked too quickly.
I'll keep investigating and will keep you posted.

I'm thinking something else is wrong then (maybe in the test setup?). The original code as I read it would probably have had some sort of database error due to the exclusive lock instead of just being empty, and I can't think of any case in the new code where the database would just appear empty if both servers are actually pointing to the same database

Quoted 7 lines

Michael.

--
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com