Hey, is anyone able to look at these database (9ba874c7-30f8-436c-a357-2b674e0a2e79) metrics please?

Hey, is anyone able to look at these database (9ba874c7-30f8-436c-a357-2b674e0a2e79) metrics please?
Apparently I've gone through 2.16B Rows read this month on a database with a total of about ~20k rows.
I've checked the insights and this is the main culprit, but running this command returns about 6k rows (total of ~12k reads as the join is pretty much a 1 to 1).

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

npx wrangler d1 insights DB --env="preview" --timePeriod="7d" --sort-by="reads" --json

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

{
    "query": "select \"entry\".\"uuid\", \"entry\".\"type\", \"entry\".\"description\", \"entry\".\"userId\", \"entry\".\"createdOn\", \"entry\".\"updatedOn\", \"engineeringEntry\".\"planType\", \"engineeringEntry\".\"location\", \"engineeringEntry\".\"suburb\", \"engineeringEntry\".\"planStatus\", \"engineeringEntry\".\"designer\", \"engineeringEntry\".\"date\", \"engineeringEntry\".\"subdNumber\", \"engineeringEntry\".\"planRegister\" from \"entry\" left join \"engineeringEntry\" on \"entry\".\"uuid\" = \"engineeringEntry\".\"entryUuid\" where \"entry\".\"type\" = ?",
    "avgRowsRead": 37357286,
    "totalRowsRead": 2316151764,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 4197.161798387097,
    "totalDurationMs": 260224.0315,
    "numberOfTimesRun": 62,
    "queryEfficiency": 0.00016227088873640338
  }

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from entry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6266     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

npx wrangler d1 execute DB --command="select count(*) from engineeringEntry" --env="preview"
┌──────────┐
│ count(*) │
├──────────┤
│ 6265     │
└──────────┘

Thanks

dev•10/7/24, 2:41 AM

This is a quick Bun script to add a migration in d1, but using prisma to calculate the diff. You can adjust this to something else in case you're not using Bun.

import { $ } from 'bun';

// This is the name of the D1 database in Cloudflare and in `wrangler.toml`
const DB_NAME = 'YOUR_DB_NAME';

// Get the migration name from command line arguments
const migrationName = process.argv.slice(2).join('_');

if (!migrationName) {
  throw new Error('Error: Migration name is required.');
}

// Create a new D1 migration
await $`bunx wrangler d1 migrations create ${DB_NAME} ${migrationName}`;

// Find the newly created migration file
const d1MigrationFiles = [...new Bun.Glob(`migrations/*_${migrationName}.sql`).scanSync('.')];

if (d1MigrationFiles.length !== 1) {
  throw new Error(
    `Expected 1 D1 migration file for ${migrationName}, but found ${d1MigrationFiles.length}`
  );
}
const d1MigrationFile = d1MigrationFiles[0];

// Run prisma migrate diff and output to the D1 migration file
await $`bunx prisma migrate diff \
  --from-local-d1 \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script \
  --output ${d1MigrationFile}`;

// Apply the migration to the local D1 database
await $`bunx wrangler d1 migrations apply ${DB_NAME} --local`;

import { $ } from 'bun';

// This is the name of the D1 database in Cloudflare and in `wrangler.toml`
const DB_NAME = 'YOUR_DB_NAME';

// Get the migration name from command line arguments
const migrationName = process.argv.slice(2).join('_');

if (!migrationName) {
  throw new Error('Error: Migration name is required.');
}

// Create a new D1 migration
await $`bunx wrangler d1 migrations create ${DB_NAME} ${migrationName}`;

// Find the newly created migration file
const d1MigrationFiles = [...new Bun.Glob(`migrations/*_${migrationName}.sql`).scanSync('.')];

if (d1MigrationFiles.length !== 1) {
  throw new Error(
    `Expected 1 D1 migration file for ${migrationName}, but found ${d1MigrationFiles.length}`
  );
}
const d1MigrationFile = d1MigrationFiles[0];

// Run prisma migrate diff and output to the D1 migration file
await $`bunx prisma migrate diff \
  --from-local-d1 \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script \
  --output ${d1MigrationFile}`;

// Apply the migration to the local D1 database
await $`bunx wrangler d1 migrations apply ${DB_NAME} --local`;

KKyzer Hey, is anyone able to look at these database (9ba874c7-30f8-436c-a357-2b674e0a2...

Acе•10/7/24, 12:47 PM

Seems like you don’t have indexes on your columns so it’s reading through the entire table to find the row to join on

Acе•10/7/24, 12:50 PM

6266 records in entry multiplied by 6266 records in engineeringEntry, and ran 62 times would equal around 2 billion

AAcе Seems like you don’t have indexes on your columns so it’s reading through the en...

KyzerOP•10/7/24, 1:30 PM

Ugh, thanks. Think I was assuming the primary key index would have been fine, forgetting the joining table isn't actually using the primary key column

LLT•10/7/24, 6:47 PM

What is the recommended way of getting accurate billing metrics data, without generating additional reads through the dashboard?

Should we use the GraphQL Analytics API?

And from what point on will the D1 dashboard generate additional rowsRead? Already from the first overview page on, where the databases are listed?

nejc•10/8/24, 4:24 AM

hi all. I am using import API for D1 https://developers.cloudflare.com/api/operations/cloudflare-d1-import-database. Does anybody used it before and are any limits on the file size? I am getting 500 internal server all the time. Thank you

LLLT What is the recommended way of getting accurate billing metrics data, without ge...

Cyb3r-Jak3•10/8/24, 12:03 PM

GraphQL is the recommend way.

Alexander Hupfer•10/8/24, 1:09 PM

Hi, I have applied migrations via "npx wrangler d1 migrations apply remix-db --local" - how can I connect to the local DB via CLI or otherwise to check the structure?

AAlexander Hupfer Hi, I have applied migrations via "npx wrangler d1 migrations apply remix-db --l...

Cyb3r-Jak3•10/8/24, 1:11 PM

I believe the command is npx wrangler d1 exec —local <sql command>npx wrangler d1 exec —local <sql command>

Alexander Hupfer•10/8/24, 1:17 PM

thanks, npx wrangler d1 execute remix-db --local --command "SELECT name FROM sqlite_master WHERE type='table'" npx wrangler d1 execute remix-db --local --command "SELECT name FROM sqlite_master WHERE type='table'" did the trick

AAlexander Hupfer thanks,` npx wrangler d1 execute remix-db --local --command "SELECT name FROM sq...

James•10/8/24, 2:40 PM

the sqlite file is sitting in the .wrangler folder you could use any db browser / tool that supports opening sqlite files as well, i.e. datagrip

Vinay Reddy•10/9/24, 5:13 PM

Hey, I have implement llama and flux models and deployed as workers , now how can i save every response from them in D1?

1984 Ford Laser•10/10/24, 1:17 AM

Has anyone used D1 with yootheme pro and zoolanders essentials as a custom source? It asks for a host and port, which the docs say I can get from the TCP socket API, but unsure if that applies to my use case. If this option isnt suitable, I will play around with delivering a xml/csv file to yootheme, using a worker to pull the data from D1 and do the formatting. Appreciate any assistance

11984 Ford Laser Has anyone used D1 with yootheme pro and zoolanders essentials as a custom sourc...

Cyb3r-Jak3•10/10/24, 2:06 AM

The only way you can connect with D1 is the HTTP API. They would need to implement support for it

shun•10/10/24, 2:15 AM

Has anyone linked to Redash or Metabase using D1 data? I'd like to know if there is a good way to do this.

toadfans•10/10/24, 9:24 AM

What is this error mean? Anything going on?

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

Ttoadfans What is this error mean? Anything going on? > D1_ERROR: D1 storage operation ex...

o7•10/10/24, 10:52 AM

always happens to me early morning on a simple select on a pk with a very low traffic db

Oo7 always happens to me early morning on a simple select on a pk with a very low tr...

toadfans•10/10/24, 12:46 PM

Same here

NickP•10/10/24, 2:28 PM

Me too

Sam•10/10/24, 5:07 PM

Is the D1 HTTP API down right now?

SSam Is the D1 HTTP API down right now?

Chaika•10/10/24, 5:17 PM

Thanks for the extra info, raised

SSam Is the D1 HTTP API down right now?

ajgeiss0702•10/10/24, 5:33 PM

this is also appears to be effecting the dasboard. You can see database and their tables, but anything other than that wont load

Aajgeiss0702 this is also appears to be effecting the dasboard. You can see database and thei...

Chaika•10/10/24, 5:42 PM

yea they use basically the same api. It looks like the query api is fine (if using direct) but not getting db/table info, which in the dash also prevents using console/metrics/etc tabs. They ack'd the issue

nullptr•10/10/24, 5:45 PM

Is the GUI broken for D1?

Nnullptr Is the GUI broken for D1?

Chaika•10/10/24, 5:46 PM

yes see above

Chaika•10/10/24, 5:49 PM

https://www.cloudflarestatus.com/incidents/p1jpzdbz4sbc

D1 API Performance issues - Increased Errors

Chaika•10/10/24, 5:59 PM

Seems fixed now

Norris Nam•10/11/24, 2:48 AM

Can I use Navicat to connect to Cloudflare D1 Database?

NNorris Nam Can I use Navicat to connect to Cloudflare D1 Database?

Sameer Ali•10/11/24, 6:13 AM

yes, there is a .sqlite file in .wrangler directory. you can open that in Navicat.

aj•10/11/24, 12:25 PM

Hi, could somebody help in short with D1? Using the standard API is really slow. Should I setup a new API using workers and connect it to D1? Or how does it work, so it's quick? A random VPS server is much quicker than the default D1. I don't want to believe that

Aaj Hi, could somebody help in short with D1? Using the standard API is really slow....

Isaac McFadyen•10/11/24, 2:58 PM

The standard API is indeed slow because of the technical design of D1. If you hit the standard API it goes:
you -> Cloudflare core (central location) which processes things -> D1 in a CF datacenter somewhere

Because it bounces through the core it can be slower. If you use the D1 API from a Worker directly, as is the main use-case of D1, it goes:
you -> Worker -> D1 (often near the Worker)

Meaning it skips the core.

So TLDR: Yes, use a Worker for best performance.

aj•10/11/24, 3:07 PM

@Isaac | AS213339 Is it possible to achieve speeds of a VPS server? with the worker we improved the loading times from 1.3 seconds to 700 ms, but VPS is still 50ms. Are there any paid features that make the Worker API almost instant?

Aaj @Isaac | AS213339 Is it possible to achieve speeds of a VPS server? with the wor...

boop•10/11/24, 4:58 PM

You could try smart placement to move the worker closer to the database

Aaj @Isaac | AS213339 Is it possible to achieve speeds of a VPS server? with the wor...

lambrospetrou•10/11/24, 5:47 PM

Is it possible to achieve speeds of a VPS server?

What is a VPS server here? A server accessing a remote database on a different server/same region, accessing database on the same server, your browser accessing a database on a VPS server (where?)? It can mean many things.

In general the D1 REST API is slow, and not meant for your interactive low-latency queries. Using D1 through a Worker is much better. Your D1 database will also be in a fixed location, so your query request location also matters here. Smart placement could help to avoid routing over slow networks and use Cloudflare's network handle routing.

The 50ms you mention is latency for what exactly?

aj•10/12/24, 6:02 PM

@lambrospetrou @Isaac | AS213339
50 ms is the latency of a VPS server where the database is.

Here are some live tests:
This is D1, R2 with an API via Worker: https://cloud.simyz.com/categories
This is a VPS server where the database is stored + R2 for asset storing: https://cloud-db.simyz.com/categories

What options are available to make D1 as fast as possible? If this is the best I can have, this is by far not a competitive product to use for a database.

Aaj @lambrospetrou @Isaac | AS213339 50 ms is the latency of a VPS server where the...

boop•10/12/24, 6:12 PM

Are you using the D1 HTTP API in the worker or accessing the database with a binding?

Bboop Are you using the D1 HTTP API in the worker or accessing the database with a bin...

aj•10/12/24, 6:18 PM

accessing it via binding

Aaj accessing it via binding

boop•10/12/24, 6:18 PM

Is smart placement enabled?

Bboop Is smart placement enabled?

aj•10/12/24, 6:19 PM

I think I just enabled it for Worker. A minute ago. For D1 it was enabled before.

Aaj I think I just enabled it for Worker. A minute ago. For D1 it was enabled before...

boop•10/12/24, 6:23 PM

There is no smart placement for D1 yet unfortunately, it will always live in the region that you created it in. Here's to hoping that smart placement makes it better

Bboop There is no smart placement for D1 yet unfortunately, it will always live in the...

aj•10/12/24, 6:25 PM

I'm wondering if I switch from Free to Paid plan could it help improve the results? by using some of the unlocked paid features and limits?

Aaj I'm wondering if I switch from Free to Paid plan could it help improve the resul...

boop•10/12/24, 6:27 PM

I don't think that would make much of a difference with D1 to Workers

Isaac McFadyen•10/12/24, 6:40 PM

Latency shouldn't be affected by paid plan, nope.

aj•10/12/24, 7:11 PM

@Isaac | AS213339 Is my understanding correct that in production, using D1 with Workers and Smart Placements optimizes and improves delivery performance specifically for the most frequently accessed locations?

aj•10/12/24, 7:12 PM

So at the start latency to Japan could be much worse than after some time if it's commonly accessed?

Aaj @Isaac | AS213339 Is my understanding correct that in production, using D1 with ...

Isaac McFadyen•10/12/24, 7:14 PM

Smart Placement with D1 just moves your Worker near the DB (always, and D1 doesn't move). So the Worker will always be placed near D1.

Hey, is anyone able to look at these database (9ba874c7-30f8-436c-a357-2b674e0a2e79) metrics please?

Similar Threads