Cloudflare Developers•2y ago

0ms response in Europe will still be 100-200 in the US 😉

0ms response in Europe will still be 100-200 in the US

RRuurtjan 0ms response in Europe will still be 100-200 in the US 😉

Nob•6/17/24, 7:25 AM

you do redis replicas in us then

check engine•6/17/24, 7:10 PM

I'm new to D1, @cloudflare/d1 is deprecated on npm. What should i use with node.js?

check engine•6/17/24, 7:12 PM

i dont want to use prisma :<

kian•6/17/24, 7:29 PM

@cloudflare/d1@cloudflare/d1 was always for Workers, not NodeJS - it's just replaced by a built-in equivalent now.

kian•6/17/24, 7:29 PM

The only way to use D1 outside of Workers is the HTTP API, and I don't believe Cloudflare offer any wrappers around that.

Kkian `@cloudflare/d1` was always for Workers, not NodeJS - it's just replaced by a bu...

check engine•6/17/24, 7:40 PM

ohhhh

check engine•6/17/24, 7:40 PM

thanks

beau•6/17/24, 8:46 PM

howdy! i have a database with ~10k deposits. I'm running a query like:

SELECT
  deposits.id,
  deposits.address,
  deposits.amount,
  deposits.token
FROM deposits

SELECT
  deposits.id,
  deposits.address,
  deposits.amount,
  deposits.token
FROM deposits

and it's taking ~30+ seconds. anyone have any clue about what's happening or how we could speed it up?

i've tried cloning my remote data locally and i'm getting similar performance

Bbeau howdy! i have a database with ~10k deposits. I'm running a query like: ``` SELEC...

Max (@rozenmd)•6/17/24, 8:50 PM

do you have a WHERE clause in that query, and do you have indexes on what you're looking for in the WHERE clause?

beau•6/17/24, 8:50 PM

no where clause

beau•6/17/24, 8:50 PM

the above is the whole query

Max (@rozenmd)•6/17/24, 8:52 PM

and when you visit the cloudflare dashboard, there's no warnings about it being an alpha DB, right?

beau•6/17/24, 8:52 PM

where would i check that?

Bbeau where would i check that?

Max (@rozenmd)•6/17/24, 8:53 PM

beau•6/17/24, 8:53 PM

yeah no such warnings

ogshadowfiend•6/17/24, 8:55 PM

Some additional data here… I ran the same query on the Cloudflare console two different times; in both cases the query says it executes in 20-40ms, but the response time is two orders of magnitude larger in one case and 3 orders of magnitude larger in another.

Bbeau yeah no such warnings

Max (@rozenmd)•6/17/24, 8:55 PM

can you share your database id? it's well past the end of my day here but I can look into it tomorrow morning

beau•6/17/24, 8:55 PM

(ogshadowfiend is a coworker)

Max (@rozenmd)•6/17/24, 8:57 PM

the dashboard console uses the HTTP API, which involves a roundtrip to a centralized DB on the US west-coast, it's not designed for performant querying just yet

ogshadowfiend•6/17/24, 8:57 PM

Can def share it—what's the best channel to do that?

Max (@rozenmd)•6/17/24, 8:57 PM

here's fine, they're not secrets

ogshadowfiend•6/17/24, 8:57 PM

276296c7-7c25-4708-9d8e-b8c5ba0ff41f

ogshadowfiend•6/17/24, 8:58 PM

(Of note, we're looking into this because we're getting big timeouts on the worker that's connecting to the db.)

beau•6/17/24, 8:59 PM

~10k rows, the json representation is ~2mb

Max (@rozenmd)•6/17/24, 9:02 PM

how many requests per second are you sending your DB?

beau•6/17/24, 9:05 PM

beau•6/17/24, 9:06 PM

that seems high

beau•6/17/24, 9:06 PM

i'll look into that

Bbeau Click to see attachment

Max (@rozenmd)•6/17/24, 9:10 PM

logs on our end are showing a mix of overloaded (too many requests) errors and CPU time limit exceptions (could be from complex queries, or just too many requests at once)

beau•6/17/24, 9:11 PM

thanks for looking into it; going to see if i can unpeel some stuff and check if that makes a difference

Max (@rozenmd)•6/17/24, 9:12 PM

would recommend throwing the cache API in front of it (even if you have a 60 second TTL, can make a big difference) - https://developers.cloudflare.com/workers/examples/cache-api/#main

ogshadowfiend•6/17/24, 9:12 PM

We actually have the cache API in front of it!

ogshadowfiend•6/17/24, 9:13 PM

We started looking into this because the initial cache request started failing so all subsequent requests started piling on

ogshadowfiend•6/17/24, 9:13 PM

In fact the cache has a 30 minute timeout <_<

ogshadowfiend•6/17/24, 9:13 PM

We can move it to a cron (right now it's just refreshed when it expires basically), but mostly we're very puzzled as to what's causing the slowness in the first place.

Max (@rozenmd)•6/17/24, 9:14 PM

the cache API isn't a global cache, so if your requests are geographically distributed, it'll still hurt

ogshadowfiend•6/17/24, 9:14 PM

It does not seem, at first blush, like we're doing anything super crazy, and it doesn't look, at first blush, like anything crazy changed in our traffic patterns that would explain a spike in db usage or similar.

ogshadowfiend•6/17/24, 9:15 PM

Maybe I need to look at geographic distribution of requests over the last 24-48 hours, that may have shifted.

Bbeau howdy! i have a database with ~10k deposits. I'm running a query like: ``` SELEC...

Max (@rozenmd)•6/17/24, 9:34 PM

is that the only query running?

beau•6/17/24, 9:41 PM

locally, yeah

i've got a branch set up that makes this all go very fast, but i'm effectively doing the joins by hand

beau•6/17/24, 9:41 PM

well, backing up

beau•6/17/24, 9:42 PM

when i totally isolate the deposits query, we're back to fast

beau•6/17/24, 9:42 PM

so the slowness was due to the database being locked up by other concurrent queries as far as i can tell

beau•6/17/24, 9:42 PM

(it's always concurrency)

beau•6/17/24, 9:43 PM

so my immediate concern is fine; i'll do more testing and then come back if we discover more info

beau•6/17/24, 9:43 PM

thanks

Bbeau so the slowness was due to the database being locked up by other concurrent quer...

Max (@rozenmd)•6/17/24, 9:47 PM

the CPU overloaded errors point to other queries taking >30 seconds of CPU time, check out

wrangler d1 insights

wrangler d1 insights

on a recent wrangler version to track it down

beau•6/17/24, 9:48 PM

quisi.do•6/18/24, 6:15 AM

What are your thoughts of separate databases? I have a users table in an auth database, but user-owned resources are in product-specific databases, making foreign key references impossible.

Should I cave and put the tables in the same database so that I can have foreign keys on user IDs, or should I keep a separation of concerns to decrease the blast radius of a security incident?

Phisn•6/18/24, 2:18 PM

If I have a table with 5 million rows. Indexed on a bytes/string key. If I now search for one exact row. Do I have to pay for only one row read since it’s indexed? So if I do it a million times, I only pay for a million rows?

0ms response in Europe will still be 100-200 in the US 😉

Similar Threads

Similar Threads

Similar Threads