yeah no such warnings

O

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

M

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

B

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

(ogshadowfiend is a coworker)

M

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

O

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

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

M

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

here's fine, they're not secrets

O

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

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

O

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.)

B

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

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

M

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

how many requests per second are you sending your DB?

B

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

B

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

that seems high

B

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

i'll look into that

Bbeau Click to see attachment

M

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)

B

beauOP•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

M

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

O

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

We actually have the cache API in front of it!

O

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

O

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

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

O

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.

M

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

O

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.

O

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...

M

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

is that the only query running?

B

beauOP•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

B

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

well, backing up

B

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

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

B

beauOP•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

B

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

(it's always concurrency)

B

beauOP•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

B

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

thanks

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

M

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

wrangler d1 insights

wrangler d1 insights on a recent wrangler version to track it down

B

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

Q

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?

P

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?

R

Robin Lindner•6/18/24, 3:43 PM

What are real-world use-cases for Cloudflare D1 and what are use cases when I should not use it?

E.g. some ERP or CRM might be fast idea with D1 in first place, but some big companies might exceed the maximum D1 size.
Also things like backuping, etc. - I know there are scripts and tools, but is there currently a "fully managed solution" for this?

RRobin Lindner What are real-world use-cases for Cloudflare D1 and what are use cases when I sh...

H

harshil1712•6/19/24, 8:25 AM

Hey Robin,
You can always add more DBs to scale your application. D1 comes with Time Travel that does automatic backups for you. You can learn more about it here: https://developers.cloudflare.com/d1/reference/time-travel/

Cloudflare Docs

Time Travel and backups · Cloudflare D1 docs

Time Travel is D1’s approach to backups and point-in-time-recovery, and allows you to restore a database to any minute within the last 30 days.

N

nuno•6/19/24, 5:17 PM

Hey guys, quick question, Raw D1 Database query will support params with multiple statements at any point in time?

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

J

JustinNoel•6/20/24, 11:56 AM

There doesn't seem to be a way to access a D1 backup via the API. I'd like to be able to setup a cron worker to get a backup and send it somewhere else for off site (or off Cloudflare) backup.

Is that possible?

M

maj•6/20/24, 12:32 PM

You could use wrangler:

npx wrangler d1 export <db-name> --remote --output backup.sql

npx wrangler d1 export <db-name> --remote --output backup.sql

npx wrangler d1 export <db-name> --remote --output backup.sql

npx wrangler d1 export <db-name> --remote --output backup.sql

Mmaj You could use wrangler: `npx wrangler d1 export <db-name> --remote --output back...

J

JustinNoel•6/20/24, 1:48 PM

Right, but you can only do that with a permanently on computer. I'd like to call an API from a cron worker to do this.

JJustinNoel Right, but you can only do that with a permanently on computer. I'd like to cal...

S

Sam•6/20/24, 1:51 PM

What about using the export api used in the Wrangler CLI?
https://github.com/cloudflare/workers-sdk/blob/main/packages/wrangler/src/d1/export.ts#L145

SSam What about using the export api used in the Wrangler CLI? https://github.com/clo...

M

Matt Silverlock•6/20/24, 2:37 PM

@Max (@rozenmd) @geelen we should think about documenting this in the public API schema once we're happy with it - it'll then get pulled into the SDKs

A

Aman Singh•6/21/24, 8:09 AM

Hey, how do I add constraints like UNIQUE and NOT NULL to the attributes of the table present in D1??

AAman Singh Hey, how do I add constraints like UNIQUE and NOT NULL to the attributes of the ...

H

harshil1712•6/21/24, 8:37 AM

Do you want to add the constraints in an existing table or you want to do that when creating a new table? Either way, you can use the SQLite statements to do that. Note that SQLite does not support altering existing columns. You will have to use a workround for that

Hharshil1712 Do you want to add the constraints in an existing table or you want to do that w...

A

Aman Singh•6/21/24, 8:49 AM

Thank you for the reply. I wanted to add the constraints while creating a new table.

AAman Singh Thank you for the reply. I wanted to add the constraints while creating a new ta...

H

harshil1712•6/21/24, 8:51 AM

Here's an example:

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

Hharshil1712 Here's an example: ``` CREATE TABLE "Post" ( "id" INTEGER NOT NULL PRIMARY KEY...

A

Aman Singh•6/21/24, 8:52 AM

Thank you. I will do as above

Similar Threads

Similar Threads

Similar Threads