(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
[11:02 AM] Andrew Hellershanks: Hello, everyone.
[11:02 AM] Andrew Hellershanks: We seem to be short a few of our regulars.
[11:05 AM] Vincent.Sylvester @hg.zetaworlds.com: I have something rather major to report this week
[11:05 AM] Andrew Hellershanks: Go ahead, Vincent.
[11:05 AM] Vincent.Sylvester @hg.zetaworlds.com: Yesterday I ran a test for about an hour to see if my suspicion on the primitems writes was correct
[11:06 AM] Vincent.Sylvester @hg.zetaworlds.com: Made a binary with a new routine that reads the information from the database to determine what it needs to enter
[11:06 AM] Vincent.Sylvester @hg.zetaworlds.com: Instead of the current routine which deletes everything and then insert every item again
[11:06 AM] Vincent.Sylvester @hg.zetaworlds.com: The difference was quite visible I'd say
[11:06 AM] Vincent.Sylvester @hg.zetaworlds.com: https://zetaworlds.com/images/blog/Disk_Traffic_(md2)_on_eu-cluster2.zetamex.pw3.png
[11:07 AM] Vincent.Sylvester @hg.zetaworlds.com: Granted the test only ran for an hour, so I plan a longer 24 hour test next week or week after that to see how it behaves when the dust settles and users actually interact with things
[11:07 AM] Vincent.Sylvester @hg.zetaworlds.com: There is still the question of what causes it to consider objects as changed and thus triggering a database write in the first place as some of the items I could find in the audit logs of the database are not being actively changed
[11:08 AM] Vincent.Sylvester @hg.zetaworlds.com: Scripts being dormant etc
[11:08 AM] Vincent.Sylvester @hg.zetaworlds.com: There are a number of things that set hasgroupchanged and other flags that can trigger a write, but I haven't found one that fits these cases yet
[11:09 AM] Andrew Hellershanks: Perhaps a forced persist after x amount of time had passed in case something about the objects had changed in some way?
[11:09 AM] Vincent.Sylvester @hg.zetaworlds.com: That's what it seems to do every 10 minutes or so from my local test, but I could not get it to actually hit the primitems write routine yet
[11:10 AM] Vincent.Sylvester @hg.zetaworlds.com: I suspected overwriting SetText with the same data somehow triggered it, but I could not get it to do that
[11:10 AM] Vincent.Sylvester @hg.zetaworlds.com: Looking at the objects they have scripts in them, but they aren't actually doing anything so it's perplexing as to what triggers that
[11:11 AM] Vincent.Sylvester @hg.zetaworlds.com: The new routine first reading and determining a sort of change set to apply does appear to work though and it's probably something to add regardless of whether I'll find the trigger or not
[11:11 AM] Vincent.Sylvester @hg.zetaworlds.com: As you can see from the picture the writes are more than cut in half and the reads are visible on some of the larger inserts it would normally do
[11:12 AM] Vincent.Sylvester @hg.zetaworlds.com: It's a more efficient design
[11:12 AM] Vincent.Sylvester @hg.zetaworlds.com: Technically it could be improved still by using insert on duplicate key update rather than replace into, but that makes the query more complex, which I don't know how that ends up on the db side. Might actually be more data than replace into
[11:13 AM] Vincent.Sylvester @hg.zetaworlds.com: Replace into unfortunately still does a remove and insert with an index rebuild, so it's heavier, but as the new routine already stops it from rewriting existing data it's still massively cuts down on writes
[11:14 AM] Vincent.Sylvester @hg.zetaworlds.com: I noticed this last week after I saw a disk report 800TB of written data in a little under 2 years, which is a death sentence for SSDs, great reads, avoid writes basically
[11:15 AM] Vincent.Sylvester @hg.zetaworlds.com: Less e-waste keeping the disks running for longer
[11:16 AM] Vincent.Sylvester @hg.zetaworlds.com: After the longer test I hope to get some feedback on whether the new routine works or not. While I suspect it will, never know what ugly edge case might break it so testing is required
[11:16 AM] Vincent.Sylvester @hg.zetaworlds.com: There might also be other db interactions that can be improved in a similar way, but one step at a time
[11:18 AM] Andrew Hellershanks: That's great, Vincent. I think it might also reduce the system load slightly.
[11:19 AM] Vincent.Sylvester @hg.zetaworlds.com: It adds a bit of memory usage as it has to read and temp store the information from the database to determine the set of changes it has to write or what to delete, but a few mb of that vs. TB's of disk writes is probably a good trade to make
[11:19 AM] Andrew Hellershanks: /me nods
[11:19 AM] Ubit Umarov: doesnt replace destroy all columns there are specified, resting to default values?
[11:20 AM] Ubit Umarov: i mean, that are not specified
[11:20 AM] Vincent.Sylvester @hg.zetaworlds.com: It does a delete and then insert, resetting the index so the row remains at the same position
[11:21 AM] Vincent.Sylvester @hg.zetaworlds.com: Like I said, may be better to use insert update instead
[11:21 AM] Ubit Umarov: well that is a big diference..
[11:22 AM] Vincent.Sylvester @hg.zetaworlds.com: The bigger difference is still just not writing hundreds of items over and over in the first place, but yeah
[11:22 AM] Ubit Umarov: well we do want to keep the unchanged things.. not destroy them :)
[11:23 AM] Lyr.Lobo @cc.opensimulator.org:8002: /me smiles and nods
[11:23 AM] Ubit Umarov: uff db dev is a strange world
[11:26 AM] Ubit Umarov: anyways you change check that on your test dbs
[11:27 AM] Ubit Umarov: errr ...can check..
[11:27 AM] Vincent.Sylvester @hg.zetaworlds.com: Yeah once I know this version works, insert update should be trivial
[11:28 AM] Ubit Umarov: well not sure abotu the cost of the full read versus write all
[11:29 AM] Ubit Umarov: the db may pack things in a way that it actually may need to readl all row.. reapck it and write
[11:29 AM] Ubit Umarov: so no real saves
[11:29 AM] Vincent.Sylvester @hg.zetaworlds.com: Hopefully longer test will show how it behaves in that regard
[11:30 AM] Ubit Umarov: and ofc replace will just destroy the indexes, it seems and that does not scale well
[11:30 AM] Ubit Umarov: ( this if uncle google is right )
[11:32 AM] Ubit Umarov: any thanks for your reports and the lot of work testing
[11:34 AM] Andrew Hellershanks: Very encouraging results. It would save a ton of DB activity in a busy grid.
[11:36 AM] Ubit Umarov: this is a lot worse since the db table in question has ALL the primitems on a region
[11:37 AM] Ubit Umarov: so it can be a big table
[11:39 AM] Ubit Umarov: well loking to our code
[11:40 AM] Ubit Umarov: no idea why we delete the row and insert again
[11:40 AM] Ubit Umarov: that is bad
[11:42 AM] Andrew Hellershanks: I would have thought a simple UPDATE would have worked.
[11:43 AM] Vincent.Sylvester @hg.zetaworlds.com: Well you also need something that is able to remove the items no longer in the prim
[11:43 AM] Andrew Hellershanks: True.
[11:44 AM] Ubit Umarov: yeah ofc... they are all spread on that table
[11:44 AM] Ubit Umarov: hmm well no
[11:45 AM] Ubit Umarov: remove sould have own api
[11:45 AM] Ubit Umarov: but yeah we aonly have basic store all
[11:46 AM] Ubit Umarov: yeah yes that needs that nonsense delete all
[11:48 AM] Ubit Umarov: well rl calls
[11:48 AM] Ubit Umarov: thanks for youre time, see you here and there :)
[11:48 AM] Ubit Umarov: cya
[11:49 AM] Andrew Hellershanks: ok, Ubit. See you next week.
[11:49 AM] Andrew Hellershanks: We might as well call this gathering done and dusted with so few of us here this week.
[11:49 AM] Lyr.Lobo @cc.opensimulator.org:8002: Thanks for the great discussion
[11:49 AM] Andrew Hellershanks: Thank you both for coming. See you again next week.
[11:50 AM] Lyr.Lobo @cc.opensimulator.org:8002: bye everyone
[11:50 AM] Andrew Hellershanks: Good luck with the rest of your tests, Vincent.
[11:50 AM] Lyr.Lobo @cc.opensimulator.org:8002: Oct 1 is Mal Burns' H Safari
[11:50 AM] Lyr.Lobo @cc.opensimulator.org:8002: honoring his life
[11:50 AM] Andrew Hellershanks: Thank you for the reminder, Lyr.
