Cloudflare Developers•2y ago

No hyperdrive (US), No hyperdrive (VPN to EU)

Ping for toastOP•7/3/24, 2:12 PM

So it is reducing the time by roughly 100ms in the US, and increasing the time by 100ms in europe

Ping for toastOP•7/3/24, 2:12 PM

look at that sweet sweet likely ~100ms actual response time in europe though!

AJR•7/3/24, 2:13 PM

Cache misses at about 100-125ms and Cache hits below 50ms are what we often see in our tests, yeah.

AJR•7/3/24, 2:14 PM

I'm interested in seeing what you get when you're in EU tonight, should be pretty slick

Ping for toastOP•7/3/24, 2:14 PM

that's another interesting thing, my caches are missing 100% of the time

Ping for toastOP•7/3/24, 2:15 PM

I suspect this could be due to query complexity

AJR•7/3/24, 2:16 PM

We only cache non-volatile SELECT statements. You have some non-deterministic function in there?

AAJR We only cache non-volatile SELECT statements. You have some non-deterministic f...

Ping for toastOP•7/3/24, 2:16 PM

not positive? like

random

random

AJR•7/3/24, 2:17 PM

https://www.postgresql.org/docs/current/xfunc-volatility.html

PostgreSQL Documentation

38.7. Function Volatility Categories

38.7. Function Volatility Categories # Every function has a volatility classification, with the possibilities being VOLATILE, STABLE, or IMMUTABLE. VOLATILE is …

AJR•7/3/24, 2:17 PM

Anything that pg_proc shows as

provolatile='v'

provolatile='v'

https://www.postgresql.org/docs/current/catalog-pg-proc.html

PostgreSQL Documentation

53.39. pg_proc

53.39. pg_proc # The catalog pg_proc stores information about functions, procedures, aggregate functions, and window functions (collectively also known as routines). …

Ping for toastOP•7/3/24, 2:18 PM

one sec, I'll give them a look

AJR•7/3/24, 2:18 PM

Or anything where there's any UPDATEs, INSERTs, or whatever that would change DB state.

Ping for toastOP•7/3/24, 2:20 PM

I'd expect this one to be cached

select min("created_at") as "min", max("created_at") as "max" from "userentities" where "userentities"."user_id" = $1

select min("created_at") as "min", max("created_at") as "max" from "userentities" where "userentities"."user_id" = $1

Ping for toastOP•7/3/24, 2:20 PM

I can tell its not cached because

AJR•7/3/24, 2:21 PM

No, that one should be fine. min/max don't change DB state, it's not any different than a really janky WINDOW/SELECT/ORDER BY/LIMIT

AJR•7/3/24, 2:21 PM

what's the ID of this new one, I'm curious and can check

Ping for toastOP•7/3/24, 2:22 PM

same one, 5470ba839d3c47d7a9de80ebf5ee2bab

AJR•7/3/24, 2:24 PM

You're using Kysely?

AAJR You're using Kysely?

Ping for toastOP•7/3/24, 2:24 PM

Yes

Ping for toastOP•7/3/24, 2:24 PM

is that my issue?

AJR•7/3/24, 2:24 PM

It's wrapping everything in explicit transactions, which disables caching

AAJR It's wrapping everything in explicit transactions, which disables caching

Ping for toastOP•7/3/24, 2:24 PM

oooh

Ping for toastOP•7/3/24, 2:25 PM

can I disable that?

AJR•7/3/24, 2:25 PM

That I'm not sure of. We don't cache queries that are part of transactions (though in theory we could get at least some of them safely)

Ping for toastOP•7/3/24, 2:26 PM

its interesting, usually I see

begin

begin

and

commit

commit

in my logs when I'm doing a transaction

AJR•7/3/24, 2:26 PM

I imagine there's probably a way to do so? ORMs tend to add extra magic like this, but each one has its own settings.

Ping for toastOP•7/3/24, 2:26 PM

is it wrapping each query in a transaction that is somehow bypassing

begin

begin

and

commit

commit

and doing it on a protocol level?

AJR•7/3/24, 2:26 PM

I'm seeing commits

AJR•7/3/24, 2:26 PM

One sec, let me dig a bit further

Ping for toastOP•7/3/24, 2:27 PM

there are some transactions I make explicitly

Ping for toastOP•7/3/24, 2:27 PM

post, put, patch and delete requests exclusively

AJR•7/3/24, 2:30 PM

Ok, I do see some queries that I'd have expected to be cached that are getting marked "uncacheable", and it's not immediately clear to me why. I'm going to open a ticket for this, since as far as I can tell those should be cooperating. It's probably going to be a bit before I can get to this, but we'll sort it out. If it's affecting you there'll be other users too. Worst case I at least turn up a definitive explanation.

Ping for toastOP•7/3/24, 2:30 PM

Thank you so much!

AJR•7/3/24, 2:31 PM

No problem. Again, I appreciate you taking the time to write stuff up as you go. It helps us a lot.

PPing for toast So it is reducing the time by roughly 100ms in the US, and increasing the time b...

Ping for toastOP•7/3/24, 2:32 PM

Also, thinking about it, cf boston is magically adding ~280-300 of latency might be not surprising if your numbers are 100ms of cache miss (overhead, I assume?), plus of course the 50ms to actually handle it, so that is 130ms of ??? which is less surprising. I assume

There's not any ideas of what could be done on your end (eg. its your isp or whatever)
My best course of action is trying another cloud provider

AAJR No problem. Again, I appreciate you taking the time to write stuff up as you go...

Ping for toastOP•7/3/24, 2:33 PM

I appreciate you being so attentive, its very rare to have this sort of discourse where the person on the other end is both directly involved with the product and actively engaged with users. Keep it up, you rock

PPing for toast Also, thinking about it, cf boston is magically adding ~280-300 of latency might...

AJR•7/3/24, 2:35 PM

That 100ms for cache miss isn't overhead, it's the total time we usually see. It's not quite "total" as there's still some transit after it leaves our network, but we try to have that number be as complete as we can.

Ping for toastOP•7/3/24, 2:35 PM

so then I really am curious where the 250-300 is coming from

Ping for toastOP•7/3/24, 2:36 PM

I mean what would the difference be between localhost → remote db and cloudflare → remote db?

AJR•7/3/24, 2:36 PM

Me too. The fact that it's still there on non-hyperdrive traffic takes it out of my hands a bit, but I'd be curious to know.

Ping for toastOP•7/3/24, 2:36 PM

oh right, that would indicate its a network thing and not a you thing

Ping for toastOP•7/3/24, 2:36 PM

hadn't made that connection

Ping for toastOP•7/3/24, 2:37 PM

makes sense

Ping for toastOP•7/3/24, 2:37 PM

ill give aws a go

PPing for toast Concidently I'll be in europe tonight (US east rn) so I'll be curious to see the...

Ping for toastOP•7/4/24, 4:30 AM

Still traveling so no laptop but it’s very fast

Ping for toastOP•7/4/24, 4:38 AM

God bless monitoring…. 90ms for the db round trip and likely another 50ms of overhead in total, so we’ll say 150ms

Ping for toastOP•7/4/24, 4:40 AM

One future hyperdrive feature of interest might be supporting replicated databases. You might be able to do this by setting up multiple hyperdrive bindings and switching yourself?

Ping for toastOP•7/4/24, 4:42 AM

Cuz yeah sub 200ms would be slick

Ping for toastOP•7/4/24, 4:49 AM

Bro what

Ping for toastOP•7/4/24, 4:49 AM

No hyperdrive (US), No hyperdrive (VPN to EU)

Similar Threads

Similar Threads

Similar Threads