Experience Persistent Storage - DB management, how are sorted key values ?

[Taisha Emerald]

How are sorted the key value in the db? By creation order? Update a key value change its storage position?

At what I understood there's no way to sort the key-values directly in the db, and it must be done by a script, which makes me think that the script must retrieve all the data to, as example, sort players by scores to make ranks, or I missed something...

I don't know if it's technically pertinent, but it should be great to be able to sort the key-value pairs of an experience directly in the db (maybe using the values to sort key-value pairs, or being able to sort by keys or by values?)

The need of a script to simply sort the data makes me think that we'll still need an external db, to do the job an easiest and faster way, but maybe I am wrong...

 

[Lucia Nightfire]

See the feature requests and their comments for:

https://jira.secondlife.com/browse/BUG-7147

https://jira.secondlife.com/browse/BUG-6998

[Taisha Emerald]

Thank you Lucia !

So if I understand well, as Oz Linden commented on JIRA "This is not feasible for performance reasons.
The key/value store in unordered, and in the general case cannot be made ordered."

So now my question, if I can not really interact with the db without retrieving all the data to simply sort players scores, why I would use the Experience Persistent storage instead of directly store and sort everything on my servers?

Maybe I didn't yet matched the way it has been made for, at least it will be useful to store the players assets, but not their scores...

[Taisha Emerald]

In order to manage the data base size, we again need to retrieve all the key-values, or read them, to after sort all by date, list the older and delete them ? Or periodically check all key-values and delete older than X months by last update date?

And case, if you consider that you have stored the key-value creation/update date in the value it mean that you must : get the number of keys, get the name of keys, read the keys, Json parse the values, and eventually delete a bunch of key-values, and this if you have thousands of players in your experience?! That doesn't mean huge functions calls, resources/memory usage ?

I pain to figure how to manage a disordered data base you can't sort in any way without retrieving all the data, please bring me a light !

[Lucia Nightfire]

Without sorting, there currently is no way to cleanup an arbitrary sorted database without checking each entry.

Apparently LL is concerned about the potential resource usage tied with sorting done on substantially large entry counts and/or frequency of use.

IMO, they could still offer options for sorting with one of the following restrictions:

- Allow sorting after a period of time that is based on the number of entries currently saved in the kvp store.

- Add an additional cost for the option of sorting based on the kvp store size regardless of usage. (whenever pricing is finalized)

[Qie Niangao]

Not to necessarily recommend this, but if it's known in advance what sorts are to be performed on the data, it may be practical to maintain one's own sort index and persist that index in separate entries of the same store. If it happens that only a chronological sort by insertion time is needed, that index might be reasonably efficient to maintain -- and certainly better than an exhaustive scan.

[Taisha Emerald]

After testing, it seems that the kvp store is sorted by insertion/update order, which is fine and makes my last post useless and answer my first question.

However, the ability to sort the db in different ways should be really great.

[Lucia Nightfire]

You may want to add and delete with variable sized entries, ie ~128KB, ~256KB, ~512KB, etc sizes. From my testing, data placement is memory block oriented and fills the nearest available block that can contain the size of the data.

[Loki Eliot]

I'm currently using an external Database to store Daily scores of clocking into work as part of an RP. Of course im now looking into the XP persistant database as an SL based alternative. But while external MySQL data basing has built in data management, SL has none.

How are others managing their XP databases? is it possible to create and script my own database managment app in SL?

[Lucia Nightfire]

Sadly since there is no sorting option, by alphanumeric key name or by key value update time, you will have the most likely incude some kind of time value in the key name itself and check every single key when running a "cleanup" protocol.

I made a region visitor data tracker app that uses the kvp store to save agent stats as well as day stats.

Both agent and day key names contain a day number value which is looked for during the post-midnight cleanup session.

I currently have it set to delete any agent data that is older than a day.

This device is running in two slightly busy regions that together, typically acquire about 1500 agent data values a day.

This means during the cleanup session, about 3000 key values are read, of which, half are deleted.

This typically takes an average of 100 seconds to complete.