Need Help Understanding High CPU Usage, moviegrid.io
Hi guys, I co-developed a popular movie trivia web app, moviegrid.io. Two days ago, we switched to a NextJS + Supabase stack and everything was going well until last night/this morning. We noticed very high cpu usage, and are having trouble identifying the cause. We think it was a result of high user concurrency, but we want to identify any bottlenecks. We decided to upgrade our add-ons but after 30 minutes we still noticed significantly slow queries. We are very new to supabase and I'm hoping someone can help take a look at our dashboard and try to identify the best add on plan for us and other configurations that may help.
23 Replies
Just for clarity the is mainly user helping user here so not clear you would want to share dashboard access. Maybe someone has ideas for you though.
Thanks Gary, theres nothing too sensitive and I'm a little desperate as I'm on a time crunch. Hoping a kind soul has some time today.
I've seen some issues on GitHub that complain on NextJS using too much machine CPU, so maybe you can dwell through those?
If you're talking strictly about supabase, you can take a look at these: https://supabase.com/docs/guides/platform/exhaust-cpu
High CPU Usage | Supabase Docs
Learn what high CPU usage could mean for your Supabase instance and what could have caused it.
Thanks dandis, I got the high cpu use warning on the supabase dashboard and didn’t see anything on the vercel dashboard. Not sure if they are connected though.
I don't think so. That's definitely an issue regarding supabase. You can read through these docs and solve your problem most likely. Do you have any join tables? Sometimes it becomes an anti-pattern.
I don't think I have any join tables, I do have columns that reference other tables in my schema if that's what you mean. I took a look at that article this morning, but ultimately, I'm having trouble identifying the bottleneck. For example, when looking at the query performance page, I see a number of queries that I didn't write taking up the most time.
Do you use realtime?
No I don't believe I am using realtime. I'm sorry, looking further at the queries in query performance I did in fact write them. Would trying to optimize those queries through indexing be a good way forward?
My inital though was this is a connections issue. After upgrading, how long would it take for supabase to open up those connections?
Indexing can help on selects, but hurts a bit on insert/updates so you just need to index properly.
RLS can also be a huge impact on large tables and there are easy changes to make to optimize that. https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
RLS can also be a huge impact on large tables and there are easy changes to make to optimize that. https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
Row Level Security | Supabase Docs
Secure your data using Postgres Row Level Security.
I don't see how a connection issue would manifest in CPU load though.
I actually had rls turned off for my tables through this time period (none of the db data is sensitive). And got it, unfortunately, users are consistently inserting into one of my biggest tables so indexing might not be the best for this.
what is the structure of your biggest table?
This is my schema here, let me know if you need clarification anywhere, the biggest table is the guesses table, which each user writes to quite a bit, maybe 6 or 7 times on average per day
what are the gray diamonds representing?
also, how are you handling rounds within a game?
I'm not sure, looking into that now
So its a daily game, and we have multiple types of games, so a users guesses are stored in the guesses table and I pull in their guesses for a given gameId and their userTokenID
is userToken the username? as in a user can only have 1?
I found the gray diamonds are not nullable columns. userToken is a browser cookie. In my middleware file, I look for a browser cookie. If there is none, I set one, and store the value in the userTokens table
do you have a separate table with user id / user names etc?
actually it's auth.users
No I don't have userIds and usernames, I'm only using userTokens to identify users. I haven't messed with the supabase auth stuff
so I would suggest you try putting an index on guesses (user_Token_id, gameId)
since you can likely query that directly by providing those two items. It's likely your slowest query right now if you don't have that index
Got it thank you! Would you mind if I send you my slowest queries from the query performance page? Just to be sure we're identifying the right bottleneck? I have a couple remote functions I call as well for a couple complex count queries as well that might be taking up some load.
sure
Cool I'll DM you, thanks again