How long does the same query take to
How long does the same query take to execute in the SQL Editor in the Neon console? You can see the time in the bottom right corner. If it's also slow in the SQL Editor, tTry using the Analyze button to debug why.
Maybe try using a larger compute size too. You might be using the default scaling that starts of 0.25 CPU
21 Replies
eager-peachβ’2y ago
I'll give that a shot ty! Having trouble with SQL editor, it's saying relation not found O_o?
I'm doing
select * from Photo; and getting error relation doesn't exist. I can see the same name in my migration.sql prisma is creating so I'm not sure what's going on. I have the correct database and branch selected.generous-apricotβ’2y ago
Use quotes around
Photo, since it's upper case π
I'm pretty sure that's the issueeager-peachβ’2y ago
That fixed it. It's showing 1ms to fetch entire table. Maybe the issue is the connection string? I have to run but I'll try to see if I can use psql with that connection string or some other way of taking node/next out of the equation. Thanks for your help!
generous-apricotβ’2y ago
You're welcome. I'm thinking there's some latency between your application and the database. Are they in very different regions, and is it a one shot connection where you create a new client instance each time, and then discard it? I know that creating and connecting a client can add low triple digit ms overhead
I recall seeing somewhere that Prisma can add overhead too, but don't quote me on that! π
Actually. I guess based on the code earlier you're not doing what I asked about the client, so yeah, something's funky
eager-peachβ’2y ago
Just did a simple test, I removed
pgbouncer=true from the connect URL and my queries now take 80ms vs 800ms.
So it appears pgbounder is the main culprit. I need to check again but there could be something prisma is doing with that too. I need to remind myself what that thing does on the neon side too.
That string is definitely a prisma thing. I think -pooler in the URL is the neon thinggenerous-apricotβ’2y ago
That's absolutely wild. Maybe @Mahmoud will have some ideas when he gets back online tomorrow. He knows a lot more about Prisma than most of us in here!
We have some advice here: https://neon.tech/docs/guides/prisma#use-connection-pooling-with-prisma
Neon
Connect from Prisma to Neon - Neon Docs
Prisma is an open source, next generation ORM that lets you to manage and interact with your database. This guide covers the following topics Connect to Neon from Prisma Use connection pooling with Pr...
generous-apricotβ’2y ago
I think the TLDR is to make sure you use the Neon pooler URL in conjunction with Prisma's
pgbouncer=trueeager-peachβ’2y ago
Which I am using
generous-apricotβ’2y ago
π¬ damn. OK, let's checkin again tomorrow. I gotta head offline for the evening
eager-peachβ’2y ago
Sounds good, thanks for taking a peak at this with me!
generous-apricotβ’2y ago
Sorry about this performance issue. At least we know it's not directly at the Postgres level, and is instead some weird Prisma/Bouncer interaction
eager-peachβ’2y ago
Ya, tomorrow I can try and get things running in vercel and not just locally so I can see if that's also a factor.
maybe local dev mode behaves differently π€·ββοΈ
generous-apricotβ’2y ago
I hope not! One other idea in the meantime. Could you run that performance check in a for loop that executes 5-10 times and see if subsequent executions are faster.
eager-peachβ’2y ago
I did the equivalent of that (created a button in the UI that triggers the fetch by id). Very consistent timings.
With pgbounder=true: 750-900ms
Without: 80ms
(I too gotta run, thanks again!)
generous-apricotβ’2y ago
Oh, and try the same query via the pooler URL with psql when you get a chance. Have a good evening
fair-roseβ’2y ago
Hmm, that's definitely odd. I know that team is working on upgrading our pgbouncer version today so hopefully that fixes the issue. btw which prisma version are you using
generous-apricotβ’2y ago
@Mr. Kapow! like Mahmoud said, we have pgbouncer updates in progress. If you'd like, you can DM me your Neon user/email and we can ensure we rollout our pgbouncer changes for your user to test. This means you can remove
pgbouncer=true and hopefully get normal query timescorrect-apricotβ’2y ago
Hey @Mr. Kapow! which region are you testing in?
eager-peachβ’2y ago
prisma: 4.14.0
region neon: US East (Ohio)
I'm testing locally from LA
Upgraded to prisma 5.9.1 and am now getting consistent 350-400ms request times with
pgbounder=true and still getting ~80ms without.
Neon username: WesleyKapowgenerous-apricotβ’2y ago
Awesome. That's already a large difference. DM'd you to identify your correct user account and test the new PgBouncer config with your user.