openldap-technical May 2024

openldap-technical@openldap.org

19 participants
18 discussions

Storage overhead of LMDB vs SQLite3 for file hierarchy indexing scenario
by Jérôme Carretero 13 May '24

13 May '24

Hi, I wanted to share some thoughts and findings and perhaps collect some comments about a small test I did: I used LMDB vs. SQLite3 to perform indexing of a file tree. In this scenario, we're capturing file paths, along with some metadata (size, mtime, and hashes, let's use only sha1 for this example), and we want to be able to efficiently walk the tree, as well as resolve a file's info by id or by path. I'm showing the SQL schema first, as it helps formalizing the "NoSQL" case: CREATE TABLE IF NOT EXISTS files ( id INTEGER PRIMARY KEY AUTOINCREMENT, sha1 BLOB, size INTEGER, mtime INTEGER ); CREATE TABLE IF NOT EXISTS pathsegments ( id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER, file_id INTEGER, segment_name TEXT, FOREIGN KEY(parent_id) REFERENCES pathsegments(id), FOREIGN KEY(file_id) REFERENCES files(id) ); CREATE INDEX IF NOT EXISTS idx_sha1 ON files (sha1); CREATE INDEX IF NOT EXISTS idx_fileid2segment ON pathsegments (file_id); CREATE INDEX IF NOT EXISTS idx_parent_id2segment ON pathsegments (parent_id); Capturing the full path of each entry as a string was not something I wanted, and was also wasteful in the few datasets I tested it on. So we're using path segments, and a full path can be reconstructed; in the walking situation, from root to leaves, or if we have a file ID and want its path, from the leaves to the root, using the fileid2segment index and the parent information. And if files get moved around, we can alter the database without rewriting many paths. With lmdb, as I was conscious that each database would "intrinsically" build an index, I packed the file_id->{mtime,size,hash} as fid2attrs and segment_id->{parent_id,name} as sid2kids databases, and we still have segment_id->kids DUPSORT sid2kids for doing asciibetical readdir like the SQL idx_parent_id2segment. So we have our 2 tables with 2+3 indexes in SQL, and 4 DBs in lmdb (fid2attrs, sid2kids, sid2dirname, hash2fid). I implemented this (in Python) with sqlite and lmdb (using COBS for serialization, for simplicity and because it has the nice property of keeping serialized varint unsigned integers with preserved ordering), and went on to build dbs from a dataset of 474782 small files and 45014 directories (which I've coincidentally uploaded at https://archive.org/download/ftp.modland.com_pub_modules). Well, I was surprised that the SQLite3 file was weighing 67,149,824 bytes, and the lmdb one was larger at 82,718,720 bytes. In particular, looking at database statistics, it seems that the hash to id index/DB had unexpected (for me) overhead of ~ 112% to payload size with lmdb (and it was using less payload), with a size of 24932352 bytes for lmdb's DB vs. 15298560 for SQLite's index; SQLite statistics: Percentage of total database...................... 22.8% Number of entries................................. 474782 Bytes of storage consumed......................... 15298560 Bytes of payload.................................. 12311437 80.5% Bytes of metadata................................. 1469162 9.6% B-tree depth...................................... 3 Average payload per entry......................... 25.93 Average unused bytes per entry.................... 3.20 Average metadata per entry........................ 3.09 Average fanout.................................... 109.00 Non-sequential pages.............................. 2986 80.0% Maximum payload per entry......................... 26 Entries that use overflow......................... 0 0.0% Index pages used.................................. 34 Primary pages used................................ 3701 Overflow pages used............................... 0 Total pages used.................................. 3735 Unused bytes on index pages....................... 16998 12.2% Unused bytes on primary pages..................... 1500963 9.9% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 1517961 9.9% LMDB: depth=3 branch_pages=66 leaf_pages=6021 overflow_pages=0 Keys size: 9495640 Values size: 2261081 Payload size: 11756721 Pages size: 24932352 Average key size: 20.000 (20-20) Average value size: 4.762 (1-5) Average entry size: 24.762 Entries number: 474782 Overhead : 13175631 Overhead/payload ratio: 1.121 Overhead/entry avg: 27.751 I checked the overhead for in-order insertions and it's still around 50% for this key/value sizes, much more than SQLite does (~21%) for random insertions. But even if we were to remove this index, SQLite's file would still be tighter... That's it! Best regards, -- Jérôme

2 1

Re: failed to add jpegPhoto attribute to sql backend
by Quanah Gibson-Mount 13 May '24

13 May '24

--On Sunday, May 12, 2024 3:58 AM +0700 Muchammad Nur Hidayat <hidactive(a)gmail.com> wrote: > > Thank you for the clarification. > At the moment i have to add binary data manually in mysql. It's works > pretty well since I never perform any db config. > > Hope it will be fixed in the latest update. I will re-iterate that back-sql is entirely experimental, unmaintained, and it should not be used with a production environment. It is also magnitudes slower than the native back-mdb backend, so if you're only using the mysql instance for LDAP, it would be much better to switch to the native, supported backend database. Regards, Quanah

1 0

How to properly monitor MDB usage
by Benjamin Renard 08 May '24

08 May '24

Hi, I'm looking for the right method to monitor the usage of an MDB database according to the limit of its size imposed via the parameter olcDbMaxSize. Currently, I am using the following command: # mdb_stat -e /var/lib/ldap/accesslog/ Environment Info Map address: (nil) Map size: 4294967296 Page size: 4096 Max pages: 1048576 Number of pages used: 1048572 Last transaction ID: 24153425 Max readers: 126 Number of readers used: 18 Status of Main DB Tree depth: 1 Branch pages: 0 Leaf pages: 1 Overflow pages: 0 Entries: 6 Here, I determine the percentage of database usage with the information "Number of pages used" and "Max pages". This seemed to be a good method, but I noticed that after a significant purge of objects in the directory, the number of used pages does not seem to decrease. Is there another and/or better method to monitor this? Failing that, is there a solution to release "falsely" used pages, ideally without too much downtime of the service (so not a dump/restore)? I read that it was possible to use the "mdb_copy -c" command, but I am a little concerned about the duration on a database with approximately 1,000,000 entries. Thank you in advance. -- Benjamin Renard - Easter-eggs 44-46 rue de l'Ouest - 75014 Paris - France - Métro Gaité Phone: +33 (0) 1 43 35 00 37 - mailto:brenard@easter-eggs.com

8 9

Query for attribute before adding new entry.
by Singley, Norman 08 May '24

08 May '24

Hi All. I have had a question come down from our Enterprise information team - We currently create a separate identity in oldap for students vs faculty/staff, but want to start creating only one identity going forward. The DN in the ldif is unique for these two identities, but they do share a common attribute. Is there a way in the ldapmodify add process to query the whole directory for an attribute coming from the ldif file, and then if it doesn't exist reject the add for that identity, and then of course go on processing the rest of the file? My gut says no, or at least not without some scripting that I am not familiar with. Thanks for your time. Norman Singley Directory Services / IT University of Montana 406 243 6799 Norman.singley(a)umontana.edu

3 4

failed to add jpegPhoto attribute to sql backend
by Muchammad Nur Hidayat 08 May '24

08 May '24

hi I'm having trouble adding the jpegPhoto attribute to sql, slapd is sending the jpeg data to argv[2] as binary, not hex or base64. So it can't be passed to the add_proc query in sql. is there an option or configuration to set it to hex or base64.

2 1

failed to add jpegPhoto attribute to sql backend
by Muchammad Nur Hidayat 07 May '24

07 May '24

1 0

how to identify users or service accounts that have write access
by kalybox2020＠gmail.com 07 May '24

07 May '24

In openldap 2.4, how to identify users or service accounts that have write access. Can we do ldapsearch and find out?

3 3

Not affected: gitlab cve-2023-7028
by Quanah Gibson-Mount 04 May '24

04 May '24

Hello, Community members may be aware of <https://nvd.nist.gov/vuln/detail/CVE-2023-7028> I wanted to note that the OpenLDAP self hosted gitlab instance is not affected by this vulnerability as we regularly update our gitlab instance. Regards, Quanah

1 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

openldap-technical May 2024