I’m encountering very slow query issue, any way to check why? Already enabled smart place worker

K

kamamaOP•1/1/25, 9:44 AM

query is simple and datasets are small

G

Gagan Suie•1/1/25, 10:19 AM

does CAST work in d1 queries?

Kkamama I’m encountering very slow query issue, any way to check why? Already enabled sm...

A

Astral•1/1/25, 11:44 AM

@kamama I've reported the same issue yesterday. I guess it's how it is. Maybe it's the free plan.
I've dabbled in trying out different cloud DB solutions as suggested by other members here.
But I've haven't found anything I super like.
I'm gonna continue with D1, and it's slow response times - which I will try to mitigate with the CacheAPI.

AAstral @kamama I've reported the same issue yesterday. I guess it's how it is. Maybe it...

K

kamamaOP•1/1/25, 11:51 AM

If it’s because of free to make the performance poor, I can understand that, but at least let us know. Just worry after paid, the performance is still poor

K

kamamaOP•1/1/25, 12:11 PM

Seems it’s better move to Turso until the issue fixed by Cloudflare team

G

Gagan Suie•1/1/25, 9:55 PM

is there any way to view indexes in D1 dashboard?

L

Larry•1/2/25, 2:15 AM

I don't think there is any intention to increase the overall 10GB limit

T

tcarson•1/2/25, 3:36 AM

Bummer right

K

kamamaOP•1/2/25, 11:05 AM

1 query take 224ms (foods_aus raw) and another one take 1.1s (nutrients_aus raw), both enable smart place and under same region

K

kamamaOP•1/2/25, 11:05 AM

how come it have 5x diff..

K

kamamaOP•1/2/25, 11:08 AM

both records < 10k rows

Kkamama 1 query take 224ms (foods_aus raw) and another one take 1.1s (nutrients_aus raw)...

M

Max (@rozenmd)•1/2/25, 11:55 AM

compare the query

rows_read

rows_read

rows_read

rows_read vs how many rows you get back (or take a look at

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights and look for query efficiency) - chances are you're missing an index

K

kamamaOP•1/2/25, 11:57 AM

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

{
    "query": "select \"id\", \"food_id\", \"nutrient_id\", \"amount\", \"derivation_id\", \"created_at\", \"updated_at\", ? || \"_\" || \"food_id\" as \"foodId\" from \"food_nutrients\" \"foodNutrients\" where \"foodNutrients\".\"food_id\" in (?, ?, ?, ?)",
    "avgRowsRead": 252,
    "totalRowsRead": 504,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 1.75905,
    "totalDurationMs": 3.5181,
    "numberOfTimesRun": 2,
    "queryEfficiency": 0.9682539682539683
  }

i think most of the time consume is on network layer

0

0x3lang•1/2/25, 4:49 PM

anynone can help me? my d1 database stuck: D1 DB is overloaded. Requests queued for too long

0

0x3lang•1/2/25, 4:49 PM

00x3lang anynone can help me? my d1 database stuck: D1 DB is overloaded. Requests queued...

J

James•1/2/25, 5:01 PM

What's your database ID and we can escalate this?

J

James•1/2/25, 5:01 PM

What kind of traffic are you sending to the DB? How many requests?

JJames What kind of traffic are you sending to the DB? How many requests?

0

0x3lang•1/2/25, 5:02 PM

ed9b192b-6631-451c-ba45-efb3baf87f56

JJames What kind of traffic are you sending to the DB? How many requests?

0

0x3lang•1/2/25, 5:02 PM

D1 DB is overloaded. Requests queued for too long.
Suddenly, all inquiries became lengthy

J

James•1/2/25, 5:03 PM

Historically, what kind of traffic are you sending to this? Any estimate on requests per second?

0

0x3lang•1/2/25, 5:04 PM

Less than 100 request queries per second

0

0x3lang•1/2/25, 5:05 PM

for now

J

James•1/2/25, 5:06 PM

Thanks, I've escalated - hopefully the team will get back to you soon

0

0x3lang•1/2/25, 5:07 PM

this is worker error log for now

0

0x3lang•1/2/25, 5:07 PM

My product is now forced to stop running and suffered a lot of losses

0

0x3lang•1/2/25, 5:08 PM

It's 1: 00 a.m. on my side now, can't sleep at all

0

0x3lang•1/2/25, 5:10 PM

It has been running smoothly for more than a month, and the number of visits has increased in recent days, resulting in the above problem.

0

0x3lang•1/2/25, 5:11 PM

:NotLikeThis:

J

James•1/2/25, 5:21 PM

So sorry for the issues. I've escalated to the team and hopefully they will get back to you soon

F

Farhian•1/2/25, 6:13 PM

Hi, does D1 have a 'sleep mode' of some sort? The reason I am asking is because we have noticed that if a database it not accessed during a certain period of time (approx. 24h from our experience), the first request to update the DB after that, takes a long time. Sometimes we are getting a timeout error (even if most of the times, the operation actually succeeded).

F

Farhian•1/2/25, 6:13 PM

After that initial delay, everything is fast as expected again.

E

evefan•1/2/25, 8:21 PM

I'd like to share a local D1 database between by backend worker and Next.js pages app, but I'm having trouble. I'm using the

persist-to

persist-to

persist-to

persist-to flag on my worker and

persist

persist

persist

persist on my setupDevPlatform() configuration to point to the shared directory, but it seems that instead of Next using the same sqlite file that's already there, it just creates a new one in the same directory.

Ideally, I'd like to use the binding in

next dev --turbopack

next dev --turbopack

next dev --turbopack

next dev --turbopack instead of having to build my Next app every time using

bunx @cloudflare/next-on-pages && wrangler pages dev

bunx @cloudflare/next-on-pages && wrangler pages dev

bunx @cloudflare/next-on-pages && wrangler pages dev

bunx @cloudflare/next-on-pages && wrangler pages dev

J

Jikyu•1/3/25, 2:14 AM

Is there a way to check the deployed location of an existing D1 database?

JJikyu Is there a way to check the deployed location of an existing D1 database?

C

Chaika•1/3/25, 2:30 AM

wrangler d1 info <db-name>

C

Chaika•1/3/25, 2:31 AM

ex: > running_in_region │ ENAM

J

Jikyu•1/3/25, 2:31 AM

thank you!

FFarhian Hi, does D1 have a 'sleep mode' of some sort? The reason I am asking is because ...

C

Chaika•1/3/25, 2:35 AM

Yes, D1 Databases go to sleep / they have cold starts. They should be very quick though, depends what you mean by "a long time", and I suppose database size as well

F

Farhian•1/3/25, 2:37 AM

Thank you for the response. Well we do get a timeout exception from the DB when invoked from a worker

I would say after about 5-10 seconds or so.

F

Farhian•1/3/25, 2:38 AM

For that reason (the error response), we consider to operation to have failed, when in fact it did not.

F

Farhian•1/3/25, 2:40 AM

On DB failures (like this timeout for example), we are stopping other flows which depend on the DB operation passing, leaving us in an inconsistent state (we think that a DB update failed when in fact it was successful)

K

kamamaOP•1/3/25, 6:54 AM

@James can you also escalate the slowness issue to the team ?

V

Voyage Bagage•1/3/25, 7:29 AM

in the process of importing data to my d1 I ran into issues of overloading the worker then time out over 60 sec,
so I have decided to run it locally then copy it over on d1 for production.

what is the good practice/workflow recommended for that case scenario ?
my first attempts were to:

wrangler d1 execute <databasename> --command"SELECT * FROM my-table" > my-table.sql or json

wrangler d1 execute <databasename> --command"SELECT * FROM my-table" > my-table.sql or json

wrangler d1 execute <databasename> --command"SELECT * FROM my-table" > my-table.sql or json

wrangler d1 execute <databasename> --command"SELECT * FROM my-table" > my-table.sql or json

then

wrangler d1 execute <databasename> --remote --file my-table.sql or json

wrangler d1 execute <databasename> --remote --file my-table.sql or json

wrangler d1 execute <databasename> --remote --file my-table.sql or json

wrangler d1 execute <databasename> --remote --file my-table.sql or json

but even with cleaning the logs and isolating the 'results' array that does not get read,
because it only reads INSERT INTO () VALUES()

so I did the following:

sqlite3 .path/to/miniflare/local/db.sqlite ".schema my-table"

sqlite3 .path/to/miniflare/local/db.sqlite ".schema my-table"

sqlite3 .path/to/miniflare/local/db.sqlite ".schema my-table"

sqlite3 .path/to/miniflare/local/db.sqlite ".schema my-table"

got the rigth schema and :

sqlite3 .path/to/miniflare/local/db.sqlite "SELECT      
    'INSERT INTO my-table (            
        key1,key2,etc...
    ) VALUES (' || quote(key1) || ', etc..   
FROM my-table;" > my-table-import.sql

sqlite3 .path/to/miniflare/local/db.sqlite "SELECT      
    'INSERT INTO my-table (            
        key1,key2,etc...
    ) VALUES (' || quote(key1) || ', etc..   
FROM my-table;" > my-table-import.sql

sqlite3 .path/to/miniflare/local/db.sqlite "SELECT      
    'INSERT INTO my-table (            
        key1,key2,etc...
    ) VALUES (' || quote(key1) || ', etc..   
FROM my-table;" > my-table-import.sql

sqlite3 .path/to/miniflare/local/db.sqlite "SELECT      
    'INSERT INTO my-table (            
        key1,key2,etc...
    ) VALUES (' || quote(key1) || ', etc..   
FROM my-table;" > my-table-import.sql

which gave me all the rows in SQL format (INSERT INTO() VALUES())
then ran the file my-table-import.sql
and worked

is there a simpler way ?
thanks

A

Amodeus R.•1/3/25, 12:43 PM

Is it possible to use a backend framework like fastify and postgres for query with a wrangler/d1 project?

AAmodeus R.Is it possible to use a backend framework like fastify and postgres for query wi...

M

Max (@rozenmd)•1/3/25, 1:55 PM

You can use Hono.js (which is similar to Express in terms of dev experience), and bind to a D1 DB to query it from your Worker (https://developers.cloudflare.com/d1/worker-api/) - it uses sqlite's dialect of SQL under the hood, not postgres.

Cloudflare Docs

Workers Binding API · Cloudflare D1 docs

You can execute SQL queries on your D1 database from a Worker using the Worker Binding API. To do this, you can perform the following steps: