Just wondering, what would the D1 Binding give you that DO SQLite doesn't give you?

HHello, I’m Allie!Just wondering, what would the D1 Binding give you that DO SQLite doesn't give y...

Ian•12/10/24, 2:04 AM

pricing is drastically different on a read-heavy workflow (specially indexed), also includes a much higher monthly allowance on usage for workers paid

Burrito•12/10/24, 2:05 AM

I'm under the impression that DO SQLite has the same pricing as D1? Unless you are talking about the extra cost of DO itself.

BBurrito I'm under the impression that DO SQLite has the same pricing as D1? Unless you a...

Ian•12/10/24, 2:13 AM

DO SQLite AFAIK uses DO pricing, each read and write

Ian•12/10/24, 2:14 AM

Do let me know if this is not true

Burrito•12/10/24, 2:14 AM

There are two pricing models for DO, there's the KV storage backend and the SQLite storage backend. For the SQLite storage backend, pricing model is the same as D1.

Ian•12/10/24, 2:16 AM

Thanks, I had to do a full search of the docs to find it
https://developers.cloudflare.com/durable-objects/best-practices/access-durable-objects-storage/#sqlite-storage-backend

Ian•12/10/24, 2:17 AM

If this is the case, then I have nothing more than to wait for 10GB storage limit per DB

sor4chi/monica•12/10/24, 3:52 AM

Hello. I have a question about D1.

Even for a very simple query like “SELECT 1”, the database latency takes around 500ms. This number is confirmed using serverTiming, so I’m certain it’s not caused by other factors outside D1.

Additionally, I have confirmed that several people in the X community are experiencing the same issue. I tested this by sending requests to Workers with D1 binding from the Tokyo area.

If anyone else can reproduce this, or if you know the cause or any solutions, I would greatly appreciate your input.

Ssor4chi/monica Hello. I have a question about D1. Even for a very simple query like “SELECT 1”...

lambrospetrou•12/10/24, 9:18 AM

Where is your database located? Usually network is what takes up the whole time when querying D1.
The

meta.duration

meta.duration

field should tell you how much the actual SQL query takes when running inside your database.
Everything else is networking to get from your Worker location to your D1 database location.

This is not to say there aren't things to improve internally (there are!!), but if worker and database are far away, then hundreds of ms latency is to be expected.

sor4chi/monica•12/10/24, 10:18 AM

Hi, @lambrospetrou
Indeed, when fetching the meta, it appears to be the case. This suggests that D1 itself is not hopelessly slow.
Does this mean that D1’s location selection strategy is the issue?
As a user, is there any way to intentionally select the location other?
Smart placement didn't work.

Original message was deleted

sor4chi/monica•12/10/24, 10:28 AM

Thank you for the hint.
I recreated the database with --location=apac--location=apac, but the issue persists.

Ssor4chi/monica Thank you for the hint. I recreated the database with `--location=apac`, but the...

lambrospetrou•12/10/24, 10:34 AM

Unfortunately, region-wise location hints are the only way to influence its location at the moment. In certain regions this is still a very wide area though, indeed. For example, APAC can be anywhere between Singapore and Osaka (https://where.durableobjects.live/region/apac).
I can't promise anything on this yet, but it's something we are thinking on how to improve.
Read replication will definitely help with reads on this depending on where your queries originate, but writes are going to still be impacted.

For now, if your servers/workers are in a single location (e.g. Tokyo), try creating one a few times and see if you get one close to you.

Original message was deleted

sor4chi/monica•12/10/24, 10:40 AM

here

Llambrospetrou Unfortunately, region-wise location hints are the only way to influence its loca...

sor4chi/monica•12/10/24, 10:41 AM

After hitting the API about 10 times, it suddenly became faster.
Does this mean that the location of the Durable Object hosting D1 has moved?

Ssor4chi/monica After hitting the API about 10 times, it suddenly became faster. Does this mean ...

lambrospetrou•12/10/24, 10:42 AM

After creation the database doesn't move no. The first query is always going to be slow since you are cold starting your database, its location being cached in Cloudflare's CDN etc.

Llambrospetrou After creation the database doesn't move no. The first query is always going to ...

sor4chi/monica•12/10/24, 10:44 AM

Thank you, I understand now.
I apologize if my understanding is incorrect, but I had heard that D1 delivers “read replicas to the edge,” so I assumed that READ operations would be reasonably fast regardless of the location.

Ssor4chi/monica Thank you, I understand now. I apologize if my understanding is incorrect, but I...

lambrospetrou•12/10/24, 10:54 AM

Read replication is coming soon yes, but it's not rolled out yet.
Even with read replication though, you are still going to go over the network on a different machine, or location even, so it's not going to be a single-digit millisecond solution either (it cannot be due to physics!).

o7•12/10/24, 11:10 AM

if i run a

count + 1

count + 1

update query in a batch statement 1000 times, will it end up being 1000 at the end? ie batches are transactional?

Original message was deleted

o7•12/10/24, 12:11 PM

thanks !

Llambrospetrou Unfortunately, region-wise location hints are the only way to influence its loca...

o7•12/10/24, 12:24 PM

I know promises cant be made, but would this essentially just consist of a finer control for location hints?
e.g. when dynamic bindings arrive, and I create a DB for each client, I can ensure the DB spawns in as close as possible to them. Instead of randomly getting a DB handed to you in a random city.

This would be amazing especially if R2 matched this behavior when doing D1 + R2 writes together, which would improve performance by a lot...

Original message was deleted

Max (@rozenmd)•12/10/24, 1:56 PM

for pure query duration? seems pretty slow - does your WHERE clause hit your index?

Original message was deleted

lambrospetrou•12/10/24, 3:10 PM

How are you measuring this duration? How much data are those rows?

Original message was deleted

lambrospetrou•12/10/24, 3:14 PM

Couple hundreds rows, but how much data? 200KB, 2MB, 200MB?

Original message was deleted

lambrospetrou•12/10/24, 3:14 PM

So this is from your worker code, not the

meta.duration

meta.duration

field of the D1 response, right? If yes, tens of milliseconds is expected.
If you want to see the actual SQL query duration check the

meta.duration

meta.duration

field.

Original message was deleted

Max (@rozenmd)•12/10/24, 3:18 PM

Yeah

meta.duration

meta.duration

tells you how long it actually took - anything else gives you network noise

Original message was deleted

Max (@rozenmd)•12/10/24, 3:22 PM

meta.duration

meta.duration

measures how long sqlite took to actually get your data

measuring from your Worker includes time it took to reach D1's Worker, time for D1's Worker to reach the DB, and back

In short,

meta.duration

meta.duration

is all you can actually influence (apart from enabling smart placement, I guess)

Max (@rozenmd)•12/10/24, 3:23 PM

The Workers Platform is built on Workers

Max (@rozenmd)•12/10/24, 3:25 PM

I haven't used it apart from a demo or two, but I'd expect some kind of metadata returned from the

.all()

.all()

function?

Original message was deleted

Max (@rozenmd)•12/10/24, 3:30 PM

ah apparently not: https://github.com/drizzle-team/drizzle-orm/issues/1811

GitHub

[FEATURE]: Enhanced Metadata Support for Cloudflare D1 · Issue #181...

The Cloudflare D1 API can provide valuable metadata, including operation duration and rows read. As D1 pricing is based on rows read, incorporating this metadata into Drizzle queries would be immen...

James•12/10/24, 4:04 PM

I found drizzle has just enough edge cases to not trust it, and just stick to raw sql.

I do use drizzle to generate migrations and then do custom type casting w the drizzle schema definitions I make.

CedricHadjian•12/10/24, 4:12 PM

Hi peeps, we got a table that has 12,000,000 rows and we're facing this error:

D1_ERROR: D1 DB is overloaded. Requests queued for too long.

D1_ERROR: D1 DB is overloaded. Requests queued for too long.

This is the query that we're running:

SELECT query, COUNT(*) as occurrences FROM queries GROUP BY query ORDER BY occurrences DESC LIMIT ?;

SELECT query, COUNT(*) as occurrences FROM queries GROUP BY query ORDER BY occurrences DESC LIMIT ?;

We're passing a limit of 10 and still getting this error, what can we do to bypass the error?

James•12/10/24, 4:31 PM

Drizzle has some bugs i got irritated with, batch queries breaks when you have multiple schemas in the return set, some date js typing bugs

Original message was deleted

CedricHadjian•12/10/24, 4:38 PM

Yeah :/ these are our searches, any other way I could retrieve most searched queries?

James•12/10/24, 4:39 PM

example, ran into this bug, still open, have to patch my build until i migrate off drizzle for one set of my app
https://github.com/drizzle-team/drizzle-orm/issues/2323

GitHub

[BUG]: value.getTime is not a function · Issue #2323 · drizzle-tea...

What version of drizzle-orm are you using? 0.30.10 What version of drizzle-kit are you using? 0.21.2 Describe the Bug Bug Report: Error when using time-related queries Description When attempting t...

CCedricHadjian Yeah :/ these are our searches, any other way I could retrieve most searched que...

James•12/10/24, 4:40 PM

run a query plan and add the right index

Original message was deleted

CedricHadjian•12/10/24, 4:42 PM

I've never tried it before, lemme check out what it is, thanks!

JJames run a query plan and add the right index

CedricHadjian•12/10/24, 4:44 PM

Would you elaborate?

Original message was deleted

Max (@rozenmd)•12/10/24, 4:47 PM

I've been using raw SQL with my paid side projects for about 8 years now, it's honestly fine (and if you're strict about typing your query output, you won't notice the difference)

Max (@rozenmd)•12/10/24, 4:47 PM

(we also use raw SQL internally for D1)

MMax (@rozenmd)I've been using raw SQL with my paid side projects for about 8 years now, it's h...

Isaac McFadyen•12/10/24, 10:53 PM

^ I'd advocate the use of

zod

zod

or similar because it enforces exact 1:1 match between types and actual output share versus types drifting away from the actual type

Isaac McFadyen•12/10/24, 10:53 PM

IMO it's better to have an error thrown from type mismatch than have types that aren't actually correct

Kisaragi•12/11/24, 12:21 PM

Is there a way in D1 table schema to define a uuid column?
The

id INTEGER PRIMARY KEY AUTOINCREMENT

id INTEGER PRIMARY KEY AUTOINCREMENT

is only one-digit, I want to replace this with 10-digit uuid. Anyone got a solution for this?
Thanks!!

Kisaragi•12/11/24, 12:29 PM

Dug around a bit, I guess manually generating

id = str(uuid.uuid4())

id = str(uuid.uuid4())

is the easiest. I thought there might be some native methods from cloudflare side.

Llimzykenneth Is something going on for D1 at the moment as I'm seeing gateway timeout when ru...

TeeLusk•12/11/24, 10:31 PM

We see it every so often on one of our apps as well. Seems to happen the first time the db is requested after sitting idle for a while (like in a dev environment) but then works after the first failure. Haven’t tracked down a cause

AAlexander Thoukydides I am currently seeing a lot of `500 Internal Server Error` errors from D1.

Max (@rozenmd)•12/12/24, 11:49 AM

Whats your database id?

AAlexander Thoukydides I am currently seeing a lot of `500 Internal Server Error` errors from D1.

Max (@rozenmd)•12/12/24, 11:57 AM

Also where are you seeing the errors? wrangler/dash, HTTP API, your own Worker?

Just wondering, what would the D1 Binding give you that DO SQLite doesn't give you?

Similar Threads

Similar Threads

Similar Threads