I tried doing `PRAGMA foreign_keys=OFF` and that made it work somehow. (even though it is not suppor

I tried doing

PRAGMA foreign_keys=OFF

PRAGMA foreign_keys=OFF

PRAGMA foreign_keys=OFF

PRAGMA foreign_keys=OFF and that made it work somehow. (even though it is not supported?) Idk if that fixed it but you could at least try

GerbuuunOP•6/16/24, 6:48 PM

I don't really know how foreign keys work in D1. At least not as described in the SQLite docs

GerbuuunOP•6/16/24, 6:49 PM

To me it seems very inconsistent

GerbuuunOP•6/16/24, 6:49 PM

Or a recent update broke something

GGerbuuun I tried doing `PRAGMA foreign_keys=OFF` and that made it work somehow. (even tho...

Hannes•6/16/24, 6:56 PM

Interesting. Did not work for me, unfortunately. Thanks for the idea, though

RRuurtjan I’m working on a domain name availability checker that is faster than you can ty...

Nob•6/17/24, 12:13 AM

use redis if you need smth really fast

NNob use redis if you need smth really fast

Ruurtjan•6/17/24, 4:50 AM

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)

I tried doing `PRAGMA foreign_keys=OFF` and that made it work somehow. (even though it is not suppor

Similar Threads

Similar Threads

Similar Threads