Closed wolfendale closed 3 weeks ago
While we're here, I'll bring this back up from Discord. I wonder if it's worth merging the wiiu.user_data
, wiiu.network_account_info
, wiiu.principal_basic_info
and wiiu.mii
tables?
They're split right now due to the data inside each one belonging to a specific struct (essentially mapping the struct fields directly to the table columns), but all the data inside all of them is "user data". This results in several queries when updating the data. You've solved the n+1 issue when it comes to querying the data, but even with these changes methods like UpdateAndGetAllInformation
result in several queries each call. UpdateAndGetAllInformation
has 9 queries total, 3 to update the different tables and then 2 to get the preferences and comment data, then the remaining ones for the friends list and such. 9 queries per call means that at just 11 connections at the same time we've eaten up all the Postgres connections.
Also did these issues only exist in the Wii U database or does the 3DS database have similar issues?
I also wonder if it's worth using PgBouncer instead of the manager I wrote. It seems to not be doing a fantastic job, and PgBouncer likely has better performance?
We talked about this on Discord already, merging as-is. We can look into the other stuff mentioned in later PRs
Resolves #21
Changes:
This removes sub queries from:
get_friends_list
get_friend_requests_in
get_friend_requests_out
get_user_block_list
These were all causing an n+1 query problems. In the case of
get_friends_list
there were 5 subqueries per friend so that's actually a 5n+1 problem!Also adds a limit of 100 to friends requests, sent requests have a separate limit to receiving requests
Testing locally with 100 friends this also improved performance from taking ~180ms to taking ~5ms