Or put personalized recommendations on a separate page so it’s only loaded if people want it.
4 Likes
Mariadbd has started running at 100% CPU all the time, or at least every time I look. Looking at my mail logs, this started happening Dec 13th. It’s been intermittent – I see alerts Dec 13, 14, 18, 19, and 20 (today).
This looks like a big shift, not an incremental increase over time. Either something changed in the code, or the site started getting a lot more hits from somewhere. It may be worth checking the logs to distinguish those cases.
5 Likes
Traffic is up, but not wildly so.
1 Like
Is this something that a small cache (memcache, Redis) might help with? (I’ve not looked at the ways the queries are structured, just spitballing.)
2 Likes
I think the usual enterprise answer is to use something like Elasticsearch but that might be too big a hammer to reach for.
2 Likes
Yes, I did say MariaDB doesn’t support Materialized views, but you can emulate it. It’s just a table to cache the data that the view shows.
I have no idea why you wouldn’t want to use stored procedures. One of the advantages of stored procedures compared to other programs, is that they can read and write data without causing any network traffic or overhead on the server.
2 Likes
Don’t be afraid for stored procedures. They are a valuable tool.
1 Like
The outgoing requests to IFWiki on game pages can also be reduced, but I don’t know what percentage that is of the overall load time: Avoid querying IFWiki whenever a game page is loaded · Issue #273 · iftechfoundation/ifdb-suggestion-tracker · GitHub
(BTW I think the issue should be tagged with “performance”)
1 Like
It wouldn’t be silly to push a change turning off a specific feature (recommendations, ifwiki requests, etc) and watch what happens to the mariadb process.
1 Like
I think I just find stored procedures hard to read, debug, and understand.
They’re like invisible goto statements. You can write application code that generates SQL statements to execute, but under the hood there’s a stored procedure doing magic that you may not be familiar with.
Stored procedures make it difficult to answer questions like these:
- How did this table get updated?
- Why didn’t this table get updated by stored procedure X?
- This table was updated by a stored procedure incorrectly. What exactly did it do?
Still, clearly, stored procedures are the best way to implement materialized views, and we need a materialized view, so, I’ll do it. I’ll see if I can make some time for this in the next month or so…
4 Likes
Over the past few days, we’ve rolled out a bunch of query performance improvements. Here are the highlights;
- Game ratings materialized view: This change now makes it more likely that numbers like “number of reviews” etc. will be wrong. But, it seems to be faster. Generate a materialized view for
gameRatingsSandbox0by dfabulich · Pull Request #270 · iftechfoundation/ifdb · GitHub - Search by tags: We used to just search the entire list of games, and now, we use an index. Use gametags table when searching for tags by salty-horse · Pull Request #269 · iftechfoundation/ifdb · GitHub
- Removed personalized recommendations on the home page: On the home page, if you’re logged in, we’d try to calculate which users were similar to you, by finding who gave similar ratings to you. (We called this
user_proximity.) Then, we’d find games that similar players had rated highly, and present a short random list of them in the footer of the home page. It never worked well. We’ll need to turn this off “for now,” but I expect we’ll leave it turned off forever-ish. (We now just show a few random top-rated games that you haven’t marked as “played” before.) Disable user-proximity query temporarily by dfabulich · Pull Request #274 · iftechfoundation/ifdb · GitHub - Removed automatic cross-recommendations on game listings: On each game listing page, people can manually add cross recommendations, but the system would try to automatically cross recommend some games. The algorithm was very slow and bad, even worse than the user proximity query. I again expect we’ll leave this turned off forever-ish. Temporarily disable automatic cross recommendations by dfabulich · Pull Request #280 · iftechfoundation/ifdb · GitHub We now only show manual cross-recommendations, and hide the section if no one has manually recommended similar games.
- Search by author: Now we use a fulltext index. (I think it might be a bit more permissive than our old query? But the sort order is the same, so you probably won’t notice any difference.) Add fulltext index for author search by dfabulich · Pull Request #277 · iftechfoundation/ifdb · GitHub
- Implicit search by TUID: I’d implemented a thing back in October where you could type a game’s TUID in the search box, without typing
tuid:first, and you’d automatically go straight to the game. But the way I wrote it deoptimized the search-by-text feature. I’ve re-implemented it so it should be fast and correct. - Implicit search by IFID: And now we have implicit search by IFID, too, optimized by an index. This finally properly fixes Can't select game by TUID or IFID when editing Cross-References · Issue #300 · iftechfoundation/ifdb-suggestion-tracker · GitHub where the “Select a Game” popup on cross-recommendations, competitions, etc. would say “Search for a game by title, IFID, or TUID” but it was lying, you could only do regular searches, and search by IFID only with
ifid:ortuid:parameters. - Optimized search by plain text: We had a fulltext index for title, author, description, and tags, so if you search for a word, we’d try to find it in any of those places, but we’d also search the title specifically for partial matches, which convinced the database that it wasn’t worth checking the index; it just scanned the entire
gamestable every time you searched by plain text. But that was silly, because the fulltext index already supports partial matches, so I just removed them. (I’ve confirmed that searches forounterfeit Monkeywithout the C still returned Counterfeit Monkey.) As far as I can tell, the search results are exactly the same as before, except a zillion times faster. Remove LIKE clauses from fulltext search, to reactivate fulltext index by dfabulich · Pull Request #276 · iftechfoundation/ifdb · GitHub - Frequent Fiction materialized view: Now we compute Frequent Fiction scores every five minutes, instead of computing them as you query for them. Create a materialized view for userScores by dfabulich · Pull Request #272 · iftechfoundation/ifdb · GitHub
There are still just a couple slow queries left. https://github.com/iftechfoundation/ifdb-suggestion-tracker/labels/slow%20query We’ll continue to whack these moles over time.
18 Likes
The mariadb process is taking significantly less CPU now. Hovering between 10% and 40%, at a very quick glance, where it was jammed over 100% a few weeks ago.
So you’re fixing the right things. :)
7 Likes
I had just noticed earlier today that IFDB was no longer feeling as crunchy. Thanks!
1 Like
Does it show games that you’ve marked “not interested”?
It does for me.
I don’t know how it would affect performance, but it would make sense to me to exclude the “not interested” games from recommendations.
1 Like
Nice, it feels a lot faster.
To be clear, I don’t know if it’s meant to; I just tested it out and found that it did. But if that’s not a bug, I agree that it would be a good idea to exclude those from the recommendations too.
1 Like
The query is designed to exclude games that you’ve reviewed, wishlisted, played, or marked as “not interested.” I don’t reproduce the issue you reported where a game you marked “not interested” showed up.
EDIT: Wait, I spoke too soon. I believe it should be correctly personalized for you now.
2 Likes
Yes, it seems to be working as intended for me now!
2 Likes