Is anyone else experience degraded D1 performance when querying data? I have a 640kB database and it

Is anyone else experience degraded D1 performance when querying data? I have a 640kB database and it will sometimes (around 1/8 times) take a really long time (~ 5-10 secs) to execute the query.

I was blaming my SQL queries at the start, but I just saw this happen on a

SELECT * FROM <table>

SELECT * FROM <table>

SELECT * FROM <table>

SELECT * FROM <table> query against a table with 4 rows from the D1 dashboard console.

I've noticed this occurring from the start of this day. I've attached a screenshot of my network health from

speed.cloudflare.com

speed.cloudflare.com

speed.cloudflare.com

speed.cloudflare.com. I'm in Melb, Aus.

kindaraffyOP•5/19/24, 2:43 PM

kindaraffyOP•5/19/24, 2:44 PM

D1 returns a result at around 0:280:28

Kkindaraffy Is anyone else experience degraded D1 performance when querying data? I have a 6...

Max (@rozenmd)•5/19/24, 7:05 PM

whats your databaseId?

MMax (@rozenmd)whats your databaseId?

kindaraffyOP•5/19/24, 9:04 PM

Hi Max, thank you for looking into this :). The database id is bfd358ee-8478-4a08-a37d-75dcabbf231bbfd358ee-8478-4a08-a37d-75dcabbf231b

notjoemartinez•5/20/24, 5:48 AM

Is there a way run a worker locally while it talks to a remote d1-database? The get-started tutorial only shows you how to develop local db then creates a separate db for remote usage. I'd like to be able to query the remote db from a local dev environment.

Nnotjoemartinez Is there a way run a worker locally while it talks to a remote d1-database? The ...

Hello, I’m Allie!•5/20/24, 5:56 AM

Not at the moment. Either you run the Worker locally, or you run it remotely, and get access to a real D1 instance

notjoemartinez•5/20/24, 6:00 AM

~~darn back to testing in prod~~ I guess I could just run a script to dump remote into a local db when testing. I'm not sure how that's going to scale when I enable FTS and virtual tables though. Would be intrested to see how other people handle this.

Nnotjoemartinez Is there a way run a worker locally while it talks to a remote d1-database? The ...

James Flores•5/20/24, 7:26 AM

Use the API instead and you should be able to

JJames Flores Use the API instead and you should be able to

Hello, I’m Allie!•5/20/24, 7:30 AM

This can work, though note first that this requires an API wrapper around the normal D1 API, it doesn't support the same operations as the normal D1 API, and will in most cases perform worse than a real bound D1 database

HHello, I’m Allie!This can work, though note first that this requires an API wrapper around the no...

James Flores•5/20/24, 7:31 AM

Thanks for that info. I couldn’t find any JS examples for React and D1 so I ended up using the API.

JJames Flores Thanks for that info. I couldn’t find any JS examples for React and D1 so I ende...

Hello, I’m Allie!•5/20/24, 7:46 AM

I generally wouldn’t recommend using D1, or any Cloudflare API directly from the frontend

Hello, I’m Allie!•5/20/24, 7:47 AM

Other than direct upload stuff for R2/Stream/Calls etc

HHello, I’m Allie!I generally wouldn’t recommend using D1, or any Cloudflare API directly from the...

James Flores•5/20/24, 7:47 AM

Oh sorry it’s in my backend

James Flores•5/20/24, 7:47 AM

I got that wrong.

HHello, I’m Allie!This can work, though note first that this requires an API wrapper around the no...

notjoemartinez•5/20/24, 9:26 AM

Well that's basically what I'm trying to build to begin with so I have a bit of a chicken and the egg problem here. I'm considering executing wrangler SQL commands through a child process if workers will allow it. I'll look into the D1 API before I open that can of worms.

Enduriel•5/20/24, 9:27 AM

What guarantees does D1 provide for its randomblob generation? Can't find any reference for that but it'd be interesting to know how much we can use it

Nnotjoemartinez Well that's basically what I'm trying to build to begin with so I have a bit of ...

Hello, I’m Allie!•5/20/24, 9:41 AM

If possible, I would probably deploy a proxy Worker then make calls through it instead

Hello, I’m Allie!•5/20/24, 9:41 AM

That would also be more performant

Owl Reddy•5/20/24, 12:37 PM

why doesn't Hyperdrive support D1? I'm confused.

kian•5/20/24, 12:40 PM

Hyperdrive is for connecting to your own, traditional databases hosted elsewhere

kian•5/20/24, 12:41 PM

There's no need for it when connecting to something already on Cloudflare

Kkian There's no need for it when connecting to something already on Cloudflare

Owl Reddy•5/20/24, 2:18 PM

But it is very useful for decreasing load on the db and to serve the requests faster. If I'm getting a bunch of requests for a few records from 1000s of records it'll save me massive amount of time and save the db from load if cached. I'm considering self hosting postgres but I'm a bit afraid of shooting myself in the foot

cosbgn•5/20/24, 2:48 PM

With: npx wrangler d1 exportnpx wrangler d1 export can I blacklist a specific table (migrations)?

cosbgn•5/20/24, 2:49 PM

or can I alternatively list multiple tables in --table--table ? From the docs it seem that you can either export all tables or only one specific. I would like all but migrations.

cosbgn•5/20/24, 2:50 PM

The idea is to make a copy of my production db locally, this works with export && executeexport && execute but migrations mess everything up ✘ [ERROR] UNIQUE constraint failed: d1_migrations.id✘ [ERROR] UNIQUE constraint failed: d1_migrations.id

cosbgn•5/20/24, 2:52 PM

This is what I do:

"db:export": "npx wrangler d1 export db_name --remote --no-schema --output export.sql && npx wrangler d1 execute db_name --file=export.sql"

"db:export": "npx wrangler d1 export db_name --remote --no-schema --output export.sql && npx wrangler d1 execute db_name --file=export.sql"

Vikash•5/21/24, 3:45 AM

Random error in D1, is there anything I can do to fix this?

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

Code

export const getUser = async (
  env: Bindings,
  userId: number
): Promise<Resource | null> => {
  const sqlQuery = env.DB.prepare(
    `SELECT * FROM Users WHERE user_id = ?`
  ).bind(userId);
  return await sqlQuery.first();
};

export const getUser = async (
  env: Bindings,
  userId: number
): Promise<Resource | null> => {
  const sqlQuery = env.DB.prepare(
    `SELECT * FROM Users WHERE user_id = ?`
  ).bind(userId);
  return await sqlQuery.first();
};

Vikash•5/21/24, 3:46 AM

OOwl Reddy But it is very useful for decreasing load on the db and to serve the requests fa...

Max (@rozenmd)•5/21/24, 10:47 AM

https://developers.cloudflare.com/workers/examples/cache-api/ might help

Cloudflare Docs

Using the Cache API · Cloudflare Workers docs

Documentation for Cloudflare Workers, a serverless execution environment that allows you to create entirely new applications or augment existing ones …

roftt•5/21/24, 12:18 PM

Any update on the bug with importing a database with large rows? [wrangler 3.53.1] https://github.com/cloudflare/workers-sdk/issues/4510

VVikash Random error in D1, is there anything I can do to fix this? ``` D1_ERROR: D1 s...

Matt Silverlock•5/21/24, 12:19 PM

This typically means your query is running for too long. Do you have an index on

user_id

user_id

MMatt Silverlock This typically means your query is running for too long. Do you have an index on...

Vikash•5/21/24, 12:29 PM

That’s primary key

VVikash That’s primary key

Matt Silverlock•5/21/24, 1:28 PM

Can you still confirm it’s using the index for me? I want to see the output: https://developers.cloudflare.com/d1/build-with-d1/use-indexes/#test-an-index

Otherwise please file a support ticket.

Cloudflare Docs

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) …

Pol•5/21/24, 6:46 PM

Hey, I'm trying to create a trigger on my D1 Database. Applying the query directly with sqlite works, but when done with

execute

execute

or with migrations, it outputs this:

✘ [ERROR] incomplete input

✘ [ERROR] incomplete input

This is the trigger:

CREATE TRIGGER delete_item_tags
    AFTER DELETE ON list_item
begin
    DELETE FROM list_item_tags
    WHERE item_id = OLD.id;
END;

CREATE TRIGGER delete_item_tags
    AFTER DELETE ON list_item
begin
    DELETE FROM list_item_tags
    WHERE item_id = OLD.id;
END;

Pol•5/21/24, 6:47 PM

This happens when running execute with a migration file (--file--file) and as a command (

--command "CREATE TRIGGER delete_item_tags after delete on list_item begin DELETE FROM list_item_tags WHERE item_id = OLD.id; END;"

--command "CREATE TRIGGER delete_item_tags after delete on list_item begin DELETE FROM list_item_tags WHERE item_id = OLD.id; END;"

)

PPol Hey, I'm trying to create a trigger on my D1 Database. Applying the query direct...

kian•5/21/24, 6:53 PM

Try making beginbegin fully caps, i.e BEGINBEGIN

Pol•5/21/24, 7:03 PM

oh god, that solved it

Pol•5/21/24, 7:03 PM

I had to forgot begin

Pol•5/21/24, 7:04 PM

Thank you

kian•5/21/24, 7:08 PM

Wrangler itself splits the input SQL into statements, and it checks specifically for BEGINBEGIN (case-sensitive) to see if it's a compound statement.

Might be worth making it case-insensitive @Max (@rozenmd)?

Isaac McFadyen•5/21/24, 7:40 PM

^ yeah the sql specification is case-insensitive so would make sense

Pol•5/21/24, 8:45 PM

Just created a PR for this https://github.com/cloudflare/workers-sdk/pull/5889

GitHub

[wrangler] Made D1 BEGIN and CASE regex case insensitive by polvall...

What this PR solves / how to test
Follows sqlite spec for case insensitive BEGIN and CASE (discussed in Discord)
Solves not using these operators not in caps leading into a wrangler exception.
✘ [E...

Hhill Does anybody have experience with binding multiple workers to a D1 database? Cur...

IEatBeans•5/22/24, 12:25 AM

I have done this without issue

IEatBeans•5/22/24, 12:26 AM

I have one bound to 4 pages projects and one worker

MMatt Silverlock Can you still confirm it’s using the index for me? I want to see the output: htt...

Vikash•5/22/24, 4:57 AM

This one had index, but there was another inline query in same function without an index. I just created one...let's see how it improve it.

By the way, it will be really good to show slow/not index queries on dashboard with suggestion index to quickly create to improve performance. Something like this in Atlas dashboard.

PPorush Manjhi I was able to migrate by running the migration commands on the D1 console. Howev...

Hello, I’m Allie!•5/22/24, 8:20 AM

That’s a D1 internal table

Is anyone else experience degraded D1 performance when querying data? I have a 640kB database and it

Similar Threads

Similar Threads

Similar Threads