Not currently possible, how many replicas do you have? Which db provider are you using?
Not currently possible, how many replicas do you have? Which db provider are you using?
SWill read replicas be supported in the near future, like when using SELECT queries Hyperdrive automatically uses the nearest Postgres servers? Ideally with the same set-up as HTTP load balancing
AWe are using Hyperdrive, but it seems to be very slow. A query that should take 20ms-30ms is actually taking 800-900ms. The dashboard reports a query latency of 150ms, but it should still be less than 800ms. The database is located in Bangalore (BLR) and the worker is in Chennai (MAA), so they are quite close to each other.
AIs there a way to check in which location the hyperdrive is?
A@anirudh What is your hyperdrive ID? Send it in a DM?
Is this consistently the latency you're seeing, or is this a spike?
Is this consistently the latency you're seeing, or is this a spike?
AWe have made some changes since June 15th, yes. Please DM me with some details of your setup, if you wouldn't mind. Where's your origin DB, where are most of your users, what driver/version are you using, and any other info you're able to share.
AAlso, is this slowdown still going, or is it back to normal when connected with Hyperdrive now?
AIf you wouldn't mind DM'ing me the Hyperdrive ID, I can confirm. Based on what you've said so far, no I don't think you should switch back at this time if you have a reasonably workable alternative. I have a suspicion about root cause here, but will confirm.
EThere is any list that I can use to limit hyperdrive connections to my postgres to be only accept cloudflare ips? , I'm running a postgres server but not very confortable to be too open to internet.
JHi! Any updates about supporting tunnels?
AIn progress, currently being actively worked on. We'll make announcements when they're good to go.
At the moment I'm able to run most of our test suite across a tunnel, so that's exciting. Not quite ready for prime time yet, though.
At the moment I'm able to run most of our test suite across a tunnel, so that's exciting. Not quite ready for prime time yet, though.
PI'm sorry to have so many things go wrong, but I'm also seeing latency issues with hyperdrive. The pictures, in order:
- localhost
- me → cf hosted worker
- me → VPN to europe → cf hosted worker
All three of these are connecting to the same database. So, it would appear, just by connecting to a cloudflare server within a 50 mile radius of me, the response time has doubled. The VPN has a 100ms overhead. Obviously in all cases its not fast, but the difference is staggering. For the record, a query not involving the database takes 50-59ms without the VPN, and 150ms with the VPN
PConcidently I'll be in europe tonight (US east rn) so I'll be curious to see the speeds there
POf course, all of these are on a warm connection
Pif you'd like, I could run a test of CF worker → database without hyperdrive
ASure, that'd at least test how much of this is due to where your pool is being placed, and how much is the overhead of the VPN/routing itself.
AJog 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?