Hi OpenLDAP-Technical,
First post, thanks in advance for any assistance.
I am trying to manage database size for an application in which a fixed DB size is necessary (user-specified and these users are ornery). My preferred strategy is:
- write to the DB until it's (nearly?) full - at that point, prune entries matching a certain criteria (either deleting them or backing them up to an auxiliary DB of some sort).
I have all of this working, in fact, except that once I hit MDB_MAP_FULL on mdb_put(), I can no longer do anything with the DB, including deleting records. mdb_cursor_del() also returns MDB_MAP_FULL at that point.
If I had some idea of when the DB was nearly full, I could do my pruning prophylactically, I guess. Resizing the database upward isn't really a solution, given the constraints of this particular application.
Some other observations: - on OSX (BSD?), if I do not set MDB_WRITEMAP in mdb_env_open(), MDB_MAP_FULL is _never_ returned, even when the DB is full. Setting MDB_WRITEMAP disables DB sparseness, though (so my 64MB-mapsize db with one record is 64MB on disk). Bug or feature?
- on Windows, the database size is always the same as the mapsize. Possibly there's simply no support for sparse MM files on Windows, but I thought it's worth checking.
OK, that's enough for the first post, thanks once again for any insight.
Jeremy Bernstein
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Jeremy
Jeremy Bernstein wrote:
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Use mdb_stat() before performing the _put(). If the total number of pages in use is large (whatever threshold you choose, e.g. 90%) then start pruning.
Look at the mdb_stat command's output to get an idea of what you're looking for.
Hi Howard,
Thanks for your response. I tried using mdb_stat() previously, but I wasn't getting useful information. For instance, when testing a mini-DB (64K, 16 pages I believe), I had something like 1 branch page, 2 leaf pages and 0 overflow pages when I started getting MDB_MAP_FULL. My data is an 11 byte struct with an 8 byte key FWIW, so I'm not blasting the DB with big records, either.
It could just be that such mini DBs aren't going to be accurately statted, but it seemed like that method wasn't going to be reliable. Did I overlook something?
Thanks Jeremy
Am 11.06.2013 um 19:32 schrieb Howard Chu hyc@symas.com:
Jeremy Bernstein wrote:
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Use mdb_stat() before performing the _put(). If the total number of pages in use is large (whatever threshold you choose, e.g. 90%) then start pruning.
Look at the mdb_stat command's output to get an idea of what you're looking for.
-- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
Jeremy Bernstein wrote:
Hi Howard,
Thanks for your response. I tried using mdb_stat() previously, but I wasn't
getting useful information. For instance, when testing a mini-DB (64K, 16 pages I believe), I had something like 1 branch page, 2 leaf pages and 0 overflow pages when I started getting MDB_MAP_FULL. My data is an 11 byte struct with an 8 byte key FWIW, so I'm not blasting the DB with big records, either.
It could just be that such mini DBs aren't going to be accurately statted,
but it seemed like that method wasn't going to be reliable. Did I overlook something?
Your entire mapsize was only 64K, 16 pages? That's not going to work well. Please read the LMDB presentations to understand why not. Remember that in addition to the main data pages, there is also a 2nd DB maintaining a list of old pages, and since LMDB uses copy-on-write every single write you make is going to dirty multiple pages, and dirty pages cannot be reused until 2 transactions after they were freed. So you need enough free space in the map to store ~3 copies of your largest transaction, in addition to the static data.
Thanks Jeremy
Am 11.06.2013 um 19:32 schrieb Howard Chu hyc@symas.com:
Jeremy Bernstein wrote:
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Use mdb_stat() before performing the _put(). If the total number of pages in use is large (whatever threshold you choose, e.g. 90%) then start pruning.
Look at the mdb_stat command's output to get an idea of what you're looking for.
-- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
Thanks Howard,
OK, so I tried this again with a slightly more modest toy database (after reading the presentation, thanks), 1MB (256 pages). Blasting a bunch of records into it at once (with a transaction grain of 100 records) I am getting MDB_MAP_FULL with 1 branch, 115 leaf and 0 overflow nodes. So I suppose that I can use 1/3 of the database size (85 leaf pages in this example) as a rough guideline as to when I should prune. My real database are between 4 and 128MB, 32MB being typical and my real transactions are generally a bit smaller.
Does that seem reasonable to you, or do I need to be working on a different scale entirely?
Jeremy
Am 11.06.2013 um 20:11 schrieb Howard Chu hyc@symas.com:
Your entire mapsize was only 64K, 16 pages? That's not going to work well. Please read the LMDB presentations to understand why not. Remember that in addition to the main data pages, there is also a 2nd DB maintaining a list of old pages, and since LMDB uses copy-on-write every single write you make is going to dirty multiple pages, and dirty pages cannot be reused until 2 transactions after they were freed. So you need enough free space in the map to store ~3 copies of your largest transaction, in addition to the static data.
Thanks Jeremy
Am 11.06.2013 um 19:32 schrieb Howard Chu hyc@symas.com:
Jeremy Bernstein wrote:
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Use mdb_stat() before performing the _put(). If the total number of pages in use is large (whatever threshold you choose, e.g. 90%) then start pruning.
Look at the mdb_stat command's output to get an idea of what you're looking for.
-- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
Jeremy Bernstein wrote:
Thanks Howard,
OK, so I tried this again with a slightly more modest toy database (after reading the presentation, thanks), 1MB (256 pages). Blasting a bunch of records into it at once (with a transaction grain of 100 records) I am getting MDB_MAP_FULL with 1 branch, 115 leaf and 0 overflow nodes. So I suppose that I can use 1/3 of the database size (85 leaf pages in this example) as a rough guideline as to when I should prune. My real database are between 4 and 128MB, 32MB being typical and my real transactions are generally a bit smaller.
Does that seem reasonable to you, or do I need to be working on a different scale entirely?
I doubt that the cutover point will scale as linearly as that, you should just experiment further with your real data.
Jeremy
Am 11.06.2013 um 20:11 schrieb Howard Chu hyc@symas.com:
Your entire mapsize was only 64K, 16 pages? That's not going to work well. Please read the LMDB presentations to understand why not. Remember that in addition to the main data pages, there is also a 2nd DB maintaining a list of old pages, and since LMDB uses copy-on-write every single write you make is going to dirty multiple pages, and dirty pages cannot be reused until 2 transactions after they were freed. So you need enough free space in the map to store ~3 copies of your largest transaction, in addition to the static data.
Thanks Jeremy
Am 11.06.2013 um 19:32 schrieb Howard Chu hyc@symas.com:
Jeremy Bernstein wrote:
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Use mdb_stat() before performing the _put(). If the total number of pages in use is large (whatever threshold you choose, e.g. 90%) then start pruning.
Look at the mdb_stat command's output to get an idea of what you're looking for.
OK, that seems to be working. Thanks for the tip!
One thing that hung me up along the way: in my pruning, I am using a cursor to iterate through the records and then mdb_cursor_del() to chop dead ones. mdb_cursor_del() seems to set the cursor to the next record, so I'm actually starting at the last record and moving backward through the entries with mdb_cursor_get(…, MDB_PREV). So far so good.
The problem is that mdb_cursor_get(…, MDB_PREV) will continue to return 0 when there are no records left in the database. Obviously this is easy to work around, but it seems like a bug to me.
Thanks so much for your assistance, happy to have this simplified version working.
Jeremy
Am 11.06.2013 um 22:16 schrieb Howard Chu hyc@symas.com:
Jeremy Bernstein wrote:
Thanks Howard,
OK, so I tried this again with a slightly more modest toy database (after reading the presentation, thanks), 1MB (256 pages). Blasting a bunch of records into it at once (with a transaction grain of 100 records) I am getting MDB_MAP_FULL with 1 branch, 115 leaf and 0 overflow nodes. So I suppose that I can use 1/3 of the database size (85 leaf pages in this example) as a rough guideline as to when I should prune. My real database are between 4 and 128MB, 32MB being typical and my real transactions are generally a bit smaller.
Does that seem reasonable to you, or do I need to be working on a different scale entirely?
I doubt that the cutover point will scale as linearly as that, you should just experiment further with your real data.
Jeremy
Am 11.06.2013 um 20:11 schrieb Howard Chu hyc@symas.com:
Your entire mapsize was only 64K, 16 pages? That's not going to work well. Please read the LMDB presentations to understand why not. Remember that in addition to the main data pages, there is also a 2nd DB maintaining a list of old pages, and since LMDB uses copy-on-write every single write you make is going to dirty multiple pages, and dirty pages cannot be reused until 2 transactions after they were freed. So you need enough free space in the map to store ~3 copies of your largest transaction, in addition to the static data.
Thanks Jeremy
Am 11.06.2013 um 19:32 schrieb Howard Chu hyc@symas.com:
Jeremy Bernstein wrote:
Although I didn't figure out a good way to do what I want, this is what I am now doing:
if (MDB_MAP_FULL while putting) { abort txn, close the database reopen the database @ larger mapsize perform some pruning of dead records commit txn, close the database reopen the database @ old mapsize try to put again }
At this point, the database is probably larger than the old mapsize. To handle that, I make a copy of the DB, kill the original, open a new database and copy the records from the old DB to the new one.
All of this is a lot more complicated and code-verbose than I want, but it works and seems to be reliable.
Nevertheless, if there's an easier way, I'm all ears. Thanks for your thoughts.
Use mdb_stat() before performing the _put(). If the total number of pages in use is large (whatever threshold you choose, e.g. 90%) then start pruning.
Look at the mdb_stat command's output to get an idea of what you're looking for.
-- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
Hi,
I may have spoken too soon. I'm now using a 32MB toy database, typical for my users. I'm pruning at 90% which seems to be about the right number, but… the number of pages in my mdb_stat() isn't reducing. So I'll do a round of pruning, maybe 5% of the records. But the next time I come around to mdb_put(), I have to do it again. Until some critical pruning mass is reached (for instance, I started pruning at 688622 and the pages didn't reduce below 90% until I had pruned down to 389958). My transaction grain is a very modest 50. I've tried closing and reopening the environment just to ensure that everything is fresh, but it doesn't change the stat I'm getting.
Does that make any sense/ring any bells? Thanks again for your help so far.
Jeremy
Am 12.06.2013 um 00:39 schrieb Jeremy Bernstein jeremy.d.bernstein@googlemail.com:
OK, that seems to be working. Thanks for the tip!
Jeremy Bernstein wrote:
Hi,
I may have spoken too soon. I'm now using a 32MB toy database, typical for
my users. I'm pruning at 90% which seems to be about the right number, but… the number of pages in my mdb_stat() isn't reducing. So I'll do a round of pruning, maybe 5% of the records. But the next time I come around to mdb_put(), I have to do it again. Until some critical pruning mass is reached (for instance, I started pruning at 688622 and the pages didn't reduce below 90% until I had pruned down to 389958). My transaction grain is a very modest 50. I've tried closing and reopening the environment just to ensure that everything is fresh, but it doesn't change the stat I'm getting.
Does that make any sense/ring any bells? Thanks again for your help so far.
That's normal for a B+tree with multiple nodes on a page. And if you're only looking at mdb_stat() of the main DB, you're only seeing part of the picture. Again, look at the output of the mdb_stat command line tool. Use "mdb_stat -ef" so you also see the freelist info.
The trick, in your case, is to make sure that the number of free pages is always sufficient, and also the number number of freelist entries. A freelist entry is created by a single commit, and you want to always have at least 3 of them (because the 2 most recent ones are not allowed to be used). If you do all of your deletes in a single commit you will not free up usable space as quickly as doing them in several commits.
That makes sense, but I'm also seeing a similar problem with the freelist entries. Once I start pruning (btw I'm not deleting in a single commit -- my granularity is also 50 deletes per transaction, then I commit and reset the cursor before continuing), the freepages freezes at a certain value and doesn't refresh until some critical point is reached.
When my DB pages hit 80%: Freelist: Entries: 3 Free pages: 157 Main DB Tree depth: 3 Branch pages: 33 Leaf pages: 6521 Overflow pages: 0 Entries: 611087
Then I waited for the DB pages - free pages to hit 80%: Freelist: Entries: 5 Free pages: 78 Main DB: Tree depth: 3 Branch pages: 34 Leaf pages: 6599 Overflow pages: 0
And now I'm in this purge loop again, where my Leaf Pages on the main DB remain stable at 6599, and my free pages remain at 78 (5 entries), while the number of entries in my main DB is being reduced by ~20000 each purge cycle. Puts and dels are getting committed at least every 50 transactions, and I'm closing and reopening the entire environment after every purge.
I'm using the same technique as in mdb_stat.c to determine the free pages, and running mdb_stat.c on the db while I'm in the debugger confirms my numbers. So it really looks like something isn't getting updated, but I'm not really able to judge.
Maybe this isn't worth the trouble, and I should just backup the full base and start a new one, but that feels like surrender to me. :-)
Thanks for any insight, much appreciated, Jeremy
Am 12.06.2013 um 18:43 schrieb Howard Chu hyc@symas.com:
Does that make any sense/ring any bells? Thanks again for your help so far.
That's normal for a B+tree with multiple nodes on a page. And if you're only looking at mdb_stat() of the main DB, you're only seeing part of the picture. Again, look at the output of the mdb_stat command line tool. Use "mdb_stat -ef" so you also see the freelist info.
The trick, in your case, is to make sure that the number of free pages is always sufficient, and also the number number of freelist entries. A freelist entry is created by a single commit, and you want to always have at least 3 of them (because the 2 most recent ones are not allowed to be used). If you do all of your deletes in a single commit you will not free up usable space as quickly as doing them in several commits.
openldap-technical@openldap.org
-
Howard Chu -
Jeremy Bernstein