i added code to log slow queries in ktistec and it's already paying dividends. most are obviously missing indexes and it's great to fix them, but the latest example—which is missing an index—is querying a table that only has one row (in my single user instance). should that table need an index on that column? i mean, just return that row...
fwiw, a slow query is currently anything that takes longer than 50msec. i wonder if that is tight enough...?
", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Hashtag","name":"#ktistec","href":"https://epiktistes.com/tags/ktistec"} ], "type":"Note", "id":"https://epiktistes.com/objects/yAuFh0pW9qk" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2023-11-28T14:39:58.000Z", "attributedTo":"https://ruby.social/users/onghu", "inReplyTo":"https://epiktistes.com/objects/yAuFh0pW9qk", "replies":"https://ruby.social/users/onghu/statuses/111488689665936051/replies", "to":["https://www.w3.org/ns/activitystreams#Public"], "cc":["https://ruby.social/users/onghu/followers","https://epiktistes.com/actors/toddsundsted"], "content":"@toddsundsted I don't fully get it but yes, there doesn't seem to be a good reason for a table with a single row take that long to be scanned and to return a value. The only thing that I am reminded of was when we were using SQLite3 and things were slow because our "simplified" API wrapper mistakenly was reopening the database, running the query, and then returning the data and closing it again for every query!
", "attachment":[], "tag":[ {"type":"Mention","name":"@toddsundsted@epiktistes.com","href":"https://epiktistes.com/actors/toddsundsted"} ], "url":["https://ruby.social/@onghu/111488689665936051"], "type":"Note", "id":"https://ruby.social/users/onghu/statuses/111488689665936051" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2023-11-28T15:03:06.034Z", "attributedTo":"https://epiktistes.com/actors/toddsundsted", "inReplyTo":"https://ruby.social/users/onghu/statuses/111488689665936051", "to":["https://www.w3.org/ns/activitystreams#Public","https://ruby.social/users/onghu"], "cc":["https://epiktistes.com/actors/toddsundsted/followers"], "content":"@onghu thanks! yes, i haven't ruled out bugs somewhere else. running the query in the console does confirm it is scanning the table. on the other hand, the timing indicates a reasonable 6msec—not the 100+msec i'm seeing in the application...
", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Mention","name":"@onghu@ruby.social","href":"https://ruby.social/users/onghu"} ], "type":"Note", "id":"https://epiktistes.com/objects/cjdrtjqBq3Y" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2023-11-28T15:05:02.000Z", "attributedTo":"https://ruby.social/users/onghu", "inReplyTo":"https://epiktistes.com/objects/cjdrtjqBq3Y", "replies":"https://ruby.social/users/onghu/statuses/111488788207393433/replies", "to":["https://www.w3.org/ns/activitystreams#Public"], "cc":["https://ruby.social/users/onghu/followers","https://epiktistes.com/actors/toddsundsted"], "content":"@toddsundsted all the best 👍
", "attachment":[], "tag":[ {"type":"Mention","name":"@toddsundsted@epiktistes.com","href":"https://epiktistes.com/actors/toddsundsted"} ], "url":["https://ruby.social/@onghu/111488788207393433"], "type":"Note", "id":"https://ruby.social/users/onghu/statuses/111488788207393433" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2023-11-29T00:35:07.000Z", "attributedTo":"https://feed.yopp.me/users/alex", "inReplyTo":"https://epiktistes.com/objects/yAuFh0pW9qk", "replies":"https://feed.yopp.me/users/alex/statuses/111491029879717746/replies", "to":["https://www.w3.org/ns/activitystreams#Public"], "cc":["https://feed.yopp.me/users/alex/followers","https://epiktistes.com/actors/toddsundsted"], "content":"@toddsundsted yeah, if you go deep enough in this rabbit hole, it might be beneficial to have index for even a tiny amount of records, because query planner can use index to find record in just one tree check hop and just fetch it by it’s internal address.
Otherwise it uses diffirent search algo for “table scan” & it’s usually slower. Weird but works, especially if you have very hot small table.
Not sure though what qps you need so see effect on 1-record-table. But on 100’s it’s visible.
", "attachment":[], "tag":[ {"type":"Mention","name":"@toddsundsted@epiktistes.com","href":"https://epiktistes.com/actors/toddsundsted"} ], "url":["https://feed.yopp.me/@alex/111491029879717746"], "type":"Note", "id":"https://feed.yopp.me/users/alex/statuses/111491029879717746" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2023-11-29T00:49:12.662Z", "attributedTo":"https://epiktistes.com/actors/toddsundsted", "inReplyTo":"https://feed.yopp.me/users/alex/statuses/111491029879717746", "to":["https://www.w3.org/ns/activitystreams#Public","https://feed.yopp.me/users/alex"], "cc":["https://epiktistes.com/actors/toddsundsted/followers"], "content":"@alex yeah, i'm going to give it a try. it's one account per user, and i only support one user now, but that could change. i also can't imagine that the cost of that index is going to be a problem.
", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Mention","name":"@alex@feed.yopp.me","href":"https://feed.yopp.me/users/alex"} ], "type":"Note", "id":"https://epiktistes.com/objects/vX20nZhxQro" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2023-11-29T00:37:26.000Z", "attributedTo":"https://feed.yopp.me/users/alex", "inReplyTo":"https://epiktistes.com/objects/yAuFh0pW9qk", "replies":"https://feed.yopp.me/users/alex/statuses/111491039005529654/replies", "to":["https://www.w3.org/ns/activitystreams#Public"], "cc":["https://feed.yopp.me/users/alex/followers","https://epiktistes.com/actors/toddsundsted","https://ruby.social/users/alexanderadam"], "content":"@toddsundsted @alexanderadam also try to find tool for your DB that can watch frequency of query shapes. 1000s of 0.05ms queries (like bogus 1-many loads) might have tremendous effect on performance and they won’t be visible in slow query logs
", "attachment":[], "tag":[ {"type":"Mention","name":"@toddsundsted@epiktistes.com","href":"https://epiktistes.com/actors/toddsundsted"}, {"type":"Mention","name":"@alexanderadam@ruby.social","href":"https://ruby.social/users/alexanderadam"} ], "url":["https://feed.yopp.me/@alex/111491039005529654"], "type":"Note", "id":"https://feed.yopp.me/users/alex/statuses/111491039005529654" } ] }