IFDB Database Performance Issues

Traffic is up, but not wildly so.

image

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;

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.

17 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. :)

6 Likes

I had just noticed earlier today that IFDB was no longer feeling as crunchy. Thanks!

1 Like

The recommendations are showing games I have marked as “played”.

Does it show games that you’ve marked “not interested”?

It does not show games marked as “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