Why is the number of read rows so high again? For 24 hours, I managed to optimize my database by add

Why is the number of read rows so high again? For 24 hours, I managed to optimize my database by adding indexes, but since 5 P.M. UTC everything is like before.

LeoOP•1/13/25, 8:57 PM

The number of queries did not grow btw and the site usage is like normal.

LLeo Why is the number of read rows so high again? For 24 hours, I managed to optimiz...

lambrospetrou•1/13/25, 9:09 PM

Are you sure your changes on the index are applied? The workload before and after don't seem similar since pre-17:00 it's close to zero basically.
What is your database ID?

Llambrospetrou Are you sure your changes on the index are applied? The workload before and afte...

LeoOP•1/13/25, 9:10 PM

c35ed856-6db5-4c84-8ed4-f1da79b17926

LeoOP•1/13/25, 9:11 PM

I'm also confused af

LLeo c35ed856-6db5-4c84-8ed4-f1da79b17926

lambrospetrou•1/13/25, 9:16 PM

Your database is fine, no errors at all. You have two SELECT queries that are causing this I think probably due to lack of index. Make sure you applied your changes/optimizations on the remote database and you didn't only update a local test database if using wrangler.

LeoOP•1/13/25, 9:17 PM

I used the web dashboard to directly apply these changes to prod, and everything was fine until 17:00

LLeo I used the web dashboard to directly apply these changes to prod, and everything...

lambrospetrou•1/13/25, 9:19 PM

Check the D1 insights in the dashboard for slow queries to see your slow queries and confirm if the indexes you added apply to those queries (their where filters).

LeoOP•1/13/25, 9:22 PM

I can't find the insights you mean, I just have:

Tables
Console
Time Travel
Metrics
Settings

LLeo I can't find the insights you mean, I just have: Tables Console Time Travel Met...

lambrospetrou•1/13/25, 9:23 PM

Ah, they are through the API or wrangler: https://developers.cloudflare.com/d1/observability/metrics-analytics/#query-insights

LeoOP•1/13/25, 9:29 PM

The indexes worked yesterday and if I query the DB using the dashboard, a fairly normal row count appears... and I did not change anything since 12:00 today. I still don't see where the issue is and how to optimize these away.

Thanks for the insights btw

LLeo The indexes worked yesterday and if I query the DB using the dashboard, a fairly...

lambrospetrou•1/13/25, 9:35 PM

If you copy paste your slowest query in your D1 console dashboard and run it prefixed with

EXPLAIN

EXPLAIN

you are hopefully going to see if the database does a scan or if it's uses an index.
There isn't anything problematic with your database either way or a broader issue, so it's something with the query.

LeoOP•1/13/25, 9:35 PM

Ok, thank you, I'll take a look on this tomorrow.

Devils•1/13/25, 11:26 PM

Anyway to cluster multiple D1 Databases together, or would I essentially have to create custom middleware?

Devils•1/13/25, 11:28 PM

Looking to scale, but I'd rather just migrate over to something like YugabyteDB or TiDB and host my own database infastructure if I'm going to have to make custom middleware.

DDevils Anyway to cluster multiple D1 Databases together, or would I essentially have to...

1984 Ford Laser•1/13/25, 11:31 PM

Most of Cloudflare's dev platform requires some sort of custom middleware at some point. Durable Objects might be a solution to your problem, but again you'll need some amount of custom middleware

11984 Ford Laser Most of Cloudflare's dev platform requires some sort of custom middleware at som...

Devils•1/13/25, 11:32 PM

Interesting, great to know now than later.. didn't want to dig to deep into something that I'm just going to have to migrate away from anyways.

Devils•1/13/25, 11:32 PM

Working on API layers so.

Devils•1/13/25, 11:32 PM

Devils•1/13/25, 11:35 PM

The only products from Cloudflare I've really implemented is R2, coming from Backblaze, the costs for R2 are a lot cheaper. I had no idea that most of the products actually needed workers and stuff to work out of the box.

1984 Ford Laser•1/13/25, 11:37 PM

The idea is that you can build the system to work exactly how you need it. That might not work for every use case, but it means you have so much freedom to build to suit your use case

11984 Ford Laser The idea is that you can build the system to work exactly how you need it. That ...

Devils•1/13/25, 11:38 PM

I think its just the fact that not only are you paying for D1 costs, but workers as well. Nonetheless I can understand it. Thanks my friend.

Chris Jayden•1/14/25, 8:56 AM

I just found out about the 10 GB limit on D1. And I think I'll need at least 30-50gb.

I know D1's can horizontally scale but I can't find any docs on how you would create a D1 instance per tentant/organization in a worker. Does anyone has any examples or docs they could point to?

CChris Jayden I just found out about the 10 GB limit on D1. And I think I'll need at least 30-...

lambrospetrou•1/14/25, 9:22 AM

At the moment you cannot create D1 databases dynamically within your worker code. You need to create one through the D1 Rest API or the dashboard, and then add it in your wrangler.toml (and/or dynamically patch your worker on the fly).

So, in your case you probably just need to create 5-10 dbs and add them in your wrangler.

There is no automatic migration of the data from your existing DB to those new dbs, just to be clear though.

Llambrospetrou At the moment you cannot create D1 databases dynamically within your worker code...

Chris Jayden•1/14/25, 9:27 AM

Thanks for your quick answer. I think I'll be switching from D1 to a database that has a higher limit, until Workers can dynamically handle new D1 instances for tenants.

Chris Jayden•1/14/25, 9:29 AM

~~I did see in the docs just now that you can increase a limit, but there's not much information regarding that; https://developers.cloudflare.com/d1/platform/limits/~~

Actually, scratch that, the docs say

Note that the 10 GB limit of a D1 database cannot be further increased.

CChris Jayden Thanks for your quick answer. I think I'll be switching from D1 to a database th...

lambrospetrou•1/14/25, 9:30 AM

BTW, Durable Objects are the same underlying SQL engine, and you can create as many of those as you need just within your Worker code, so if that works for you, it would probably be less work than migrating to a whole other system.

Llambrospetrou BTW, Durable Objects are the same underlying SQL engine, and you can create as m...

Chris Jayden•1/14/25, 9:34 AM

That's interesting. What are the differences?

Chris Jayden•1/14/25, 9:35 AM

I see the sqlite is quite a recent thing in DO? https://developers.cloudflare.com/durable-objects/best-practices/access-durable-objects-storage/#sqlite-storage-backend

Chris Jayden•1/14/25, 9:36 AM

This is a helpful blog I just found that helps to explain that use case a bit more; https://www.lambrospetrou.com/articles/durable-objects-cloudflare/

Lambros Petrou personal website

Durable Objects (DO) — Unlimited single-threaded servers spread acr...

Understand Cloudflare Durable Objects and use them to simplify your architecture.

CChris Jayden I see the sqlite is quite a recent thing in DO? https://developers.cloudflare.co...

lambrospetrou•1/14/25, 9:40 AM

Yeah, SQLite in DO launched a few months ago, but it has been used by D1 for more than a year now.

If you also want to see how to think about using many DOs check out https://developers.cloudflare.com/reference-architecture/diagrams/storage/durable-object-control-data-plane-pattern/

I have a real example implementing that pattern explained in https://www.lambrospetrou.com/articles/tiddlyflare/

Also https://developers.cloudflare.com/durable-objects/best-practices/access-durable-objects-storage/#sql-in-durable-objects-vs-d1 has some light comparison between the two.

Chris Jayden•1/14/25, 9:40 AM

Haha i just noticed

Chris Jayden•1/14/25, 9:40 AM

that's your blog

Chris Jayden•1/14/25, 9:40 AM

what a weird coincidence

Chris Jayden•1/14/25, 9:41 AM

these articles are terrific @lambrospetrou

CChris Jayden I just found out about the 10 GB limit on D1. And I think I'll need at least 30-...

Devils•1/14/25, 11:20 AM

Personally my stack right now is 3 VPS’s all with around 80gb of storage running YugabyteDB’s..

Devils•1/14/25, 11:20 AM

But like lambrospetrou said, it would be much harder to switch or migrate to another database.

Devils•1/14/25, 11:21 AM

I’m having to build a loadbalancer and pooler.

Devils•1/14/25, 11:21 AM

Even managed databases are eh.

DDevils Personally my stack right now is 3 VPS’s all with around 80gb of storage running...

Chris Jayden•1/14/25, 1:21 PM

Appreciate you chiming in. Noted ! i'm really intrigued by Durable Objects. They seem underrated/underappreciated. I thought they were mostly for real-time stuff, but it seems like a great way to scale!

FFriday Candour great to scale? elaborate.

Isaac McFadyen•1/14/25, 6:03 PM

You can create unlimited Durable Objects each with their own SQLite database.

Isaac McFadyen•1/14/25, 6:08 PM

You're correct, unlimited is never unlimited, but in this case "unlimited" is so high that if you reached that point you'd have an Enterprise agreement with them and could negotiate higher limits anyway.
Using D1 via API is slow because of the architecture, it's not some grand scheme to force you into using a Worker. D1 is located on Cloudflare's edge network but the API routes through the cores (because it was designed well before D1 and that was how it was built).

Chaika•1/14/25, 6:11 PM

They run on the same proxy machine that handles your connection

Chaika•1/14/25, 6:11 PM

so yes, very edge, about as edge as you can get lol

Ashkan•1/14/25, 11:06 PM

Hi Dear CF Team, In the D1 metric tab I think it lacks some informative data, such as what are the top 5 or most read tables (in some situations when an cyber attack happens the most read tables are the clues for security location hole). (feedback)

AAshkan Hi Dear CF Team, In the D1 metric tab I think it lacks some informative data, su...

Chaika•1/14/25, 11:09 PM

D1 has the insights command in wrangler: https://developers.cloudflare.com/d1/observability/metrics-analytics/#query-insights, which can show the most executed queries, slowest, reading the most rows/writing the most rows, etc

Cloudflare Docs

Metrics and analytics · Cloudflare D1 docs

D1 exposes database analytics that allow you to inspect query volume, query latency, and storage size across all and/or each database in your account.

Why is the number of read rows so high again? For 24 hours, I managed to optimize my database by add

Similar Threads

Similar Threads

Similar Threads