A new release of ktistec that improves database performance is imminent. In the past, database optimization usually meant \"fixing a bunch of poorly constructed queries\", and I'm sure there's more of that to do—I'm not an expert. But this time, I found most of the queries were as good as they were going to get on my watch (I'm not an expert). If you have a million records and you need to filter and count them, that's just going to take some time...
So this time, I focused on caching the results of queries like that (which really means I focused on cache invalidation, right). A case in point is commit d544b1af. Previously, the nodeinfo endpoint filtered and counted posts on every request, and it took +80msec to do that. Worse, the filtering pushed everything else out of the sqlite page cache, which made the next, unrelated database query slow!
Caching this value, and only recounting when I post something, not only dropped the service time for the request to ~1msec but actually improved database performance, generally!
More to come...
#ktistec #fediverse #activitypub #crystallang
", "contentMap":{ "en-US":"A new release of ktistec that improves database performance is imminent. In the past, database optimization usually meant \"fixing a bunch of poorly constructed queries\", and I'm sure there's more of that to do—I'm not an expert. But this time, I found most of the queries were as good as they were going to get on my watch (I'm not an expert). If you have a million records and you need to filter and count them, that's just going to take some time...
So this time, I focused on caching the results of queries like that (which really means I focused on cache invalidation, right). A case in point is commit d544b1af. Previously, the nodeinfo endpoint filtered and counted posts on every request, and it took +80msec to do that. Worse, the filtering pushed everything else out of the sqlite page cache, which made the next, unrelated database query slow!
Caching this value, and only recounting when I post something, not only dropped the service time for the request to ~1msec but actually improved database performance, generally!
More to come...
" }, "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Hashtag","name":"#ktistec","href":"https://epiktistes.com/tags/ktistec"}, {"type":"Hashtag","name":"#fediverse","href":"https://epiktistes.com/tags/fediverse"}, {"type":"Hashtag","name":"#activitypub","href":"https://epiktistes.com/tags/activitypub"}, {"type":"Hashtag","name":"#crystallang","href":"https://epiktistes.com/tags/crystallang"} ], "type":"Note", "id":"https://epiktistes.com/objects/GH2Wcsvi9jM" }