Jog my memory, you're putting your DB in Europe?
Jog my memory, you're putting your DB in Europe?
PYeah, germany
PI have a lot of customers who would prefer it there, but I would be willing to switch cloud providers at least if that could cut down on latency (eg it looks like just from a ping test that aws has 200ms faster from here to germany)
AMy immediate guess is that the VPN to Europe is doing a lap back to a pool in the US based on your fairly irregular access patterns. All clients in a region are meant to share a pool node so the EU client should be basically all EU->EU and fairly quick, but I wonder if the VPN is passing origin info such that it's being treated as a US source. I'm curious what the non-Hyperdrive latencies look like.
Individual providers are going to have different peering arrangements and different latencies to some degree. Obviously there's a limit to what we can do there. I'm surprised to see AWS be that much faster, though.
Individual providers are going to have different peering arrangements and different latencies to some degree. Obviously there's a limit to what we can do there. I'm surprised to see AWS be that much faster, though.
Pyeah me as well, its shocking but ping tests show similar. So the 350-500ms makes sense, just the local cf looks a bit odd. I'm deploying without hyperdrive now
PNo hyperdrive (US), No hyperdrive (VPN to EU)
PSo it is reducing the time by roughly 100ms in the US, and increasing the time by 100ms in europe
Plook at that sweet sweet likely ~100ms actual response time in europe though!
ACache misses at about 100-125ms and Cache hits below 50ms are what we often see in our tests, yeah.
AI'm interested in seeing what you get when you're in EU tonight, should be pretty slick
Pthat's another interesting thing, my caches are missing 100% of the time
PI suspect this could be due to query complexity
AWe only cache non-volatile SELECT statements. You have some non-deterministic function in there?
Pnot positive? like
random?APostgreSQL Documentation
38.7. Function Volatility Categories # Every function has a volatility classification, with the possibilities being VOLATILE, STABLE, or IMMUTABLE. VOLATILE is …
AAnything that pg_proc shows as
https://www.postgresql.org/docs/current/catalog-pg-proc.html
provolatile='v'https://www.postgresql.org/docs/current/catalog-pg-proc.html
PostgreSQL Documentation
53.39. pg_proc # The catalog pg_proc stores information about functions, procedures, aggregate functions, and window functions (collectively also known as routines). …
Pone sec, I'll give them a look
AOr anything where there's any UPDATEs, INSERTs, or whatever that would change DB state.
PI'd expect this one to be cached
select min("created_at") as "min", max("created_at") as "max" from "userentities" where "userentities"."user_id" = $1PI can tell its not cached because
ANo, 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
Awhat's the ID of this new one, I'm curious and can check
Psame one, 5470ba839d3c47d7a9de80ebf5ee2bab
AYou're using Kysely?
PYes
Pis that my issue?
AIt's wrapping everything in explicit transactions, which disables caching
Pcan I disable that?
AThat 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)
Pits interesting, usually I see
begin and commit in my logs when I'm doing a transaction
AI imagine there's probably a way to do so? ORMs tend to add extra magic like this, but each one has its own settings.
Pis it wrapping each query in a transaction that is somehow bypassing
begin and commit and doing it on a protocol level?AI'm seeing commits
AOne sec, let me dig a bit further
Pthere are some transactions I make explicitly
Ppost, put, patch and delete requests exclusively
AOk, 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.
PThank you so much!
ANo problem. Again, I appreciate you taking the time to write stuff up as you go. It helps us a lot.
PAlso, 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
PI 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
AThat 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.
Pso then I really am curious where the 250-300 is coming from
PI mean what would the difference be between localhost → remote db and cloudflare → remote db?
AMe 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.
Poh right, that would indicate its a network thing and not a you thing
Phadn't made that connection
Pmakes sense
Pill give aws a go
PStill traveling so no laptop but it’s very fast
