that looks like a bug, will investigate when I'm at a laptop

Can some please explain to me how on earth did i get 4 billion writes to the db? I included some screenshots of the usage reported in different places. I do not get this at all. I get the reads but not the writes

WcaleNieWolny•1/27/24, 3:00 PM

Yeah i get that

WcaleNieWolny•1/27/24, 3:00 PM

but it still makes no sense to me. I only do single insert queries

WcaleNieWolny•1/27/24, 3:00 PM

inserting 1 row into the db per query

WcaleNieWolny•1/27/24, 3:01 PM

i also have 5 indexes but that is still way lower then the billions written/read

WcaleNieWolny•1/27/24, 3:02 PM

this does not make sense. How does a read query cause a write?

Max (@rozenmd)OP•1/27/24, 3:09 PM

if it's something like insert ... returning *, with inefficient indexes you could end up with several writes and a read per insert

Aangora What is the best way to set up transfer of a set of tables from Google bigquery ...

Matt Silverlock•1/27/24, 3:15 PM

A Cron Trigger that reads from BQ -> does what kind of aggregation you need -> writes to D1: https://developers.cloudflare.com/workers/configuration/cron-triggers/

Cron Triggers · Cloudflare Workers docs

Enable your Worker to be executed on a schedule.

WWcaleNieWolny Can some please explain to me how on earth did i get 4 billion writes to the db?...

Matt Silverlock•1/27/24, 3:17 PM

Look at the returned row counts for your queries: https://developers.cloudflare.com/d1/observability/metrics-analytics/#row-counts

Metrics and analytics · Cloudflare D1 docs

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

WcaleNieWolny•1/27/24, 3:19 PM

My writes are near constant. But as you can see when I enable reads (select) I get a lot of write queries which is strange

WWcaleNieWolny this does not make sense. How does a read query cause a write?

Matt Silverlock•1/27/24, 3:19 PM

Without your actual queries it’s impossible to say.

EXPLAIN QUERY PLAN <query>

EXPLAIN QUERY PLAN <query>

will show you what your queries are doing. Functions like

random()

random()

and similar will cause writes.

WcaleNieWolny•1/27/24, 3:19 PM

I use drizzle ORM, is there any way to see query history?

WcaleNieWolny•1/27/24, 3:20 PM

I don’t write the sql myself. I use an ORM

MMax (@rozenmd)that looks like a bug, will investigate when I'm at a laptop

Samantas5855•1/27/24, 3:21 PM

Thank you, feel free to ask if you need further information to reproduce

Samantas5855•1/27/24, 3:21 PM

This looks like the same limit as

Samantas5855•1/27/24, 3:21 PM

This is from analytics

WWcaleNieWolny I use drizzle ORM, is there any way to see query history?

Matt Silverlock•1/27/24, 3:22 PM

Use Drizzle’s logging features: https://orm.drizzle.team/docs/goodies#logging (D1 will have query logging in the near future)

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Samantas5855•1/27/24, 3:23 PM

What's also weird is that while we are using the API daily to push and delete automatically using the script the API Key shows as last used 23 days ago

0864abdbf7f609b4276d3c078a2f186859a70b1c_20240127172207.png

Samantas5855•1/27/24, 3:23 PM

This is probably another bug

Samantas5855•1/27/24, 3:52 PM

Also, while I did use the timetravel feature which restored the database, pages doesn't seem to be pulling from the restored database but rather the current one before restoration occurred

Samantas5855•1/27/24, 4:01 PM

I got it to work by making sql requests on the api which failed

Samantas5855•1/27/24, 4:11 PM

that also sounds like a bug

Samantas5855•1/27/24, 4:13 PM

unless it takes some time to apply and it was coincidental

MMatt Silverlock Without your actual queries it’s impossible to say. `EXPLAIN QUERY PLAN <query>`...

WcaleNieWolny•1/27/24, 4:32 PM

https://bpa.st/TAFQ

WcaleNieWolny•1/27/24, 4:32 PM

here are the sql queries (to long for discord to send)

WWcaleNieWolny i also have 5 indexes but that is still way lower then the billions written/read

WcaleNieWolny•1/27/24, 4:36 PM

weird. I have an index

WcaleNieWolny•1/27/24, 4:37 PM

even if, that does not explain the writes

WcaleNieWolny•1/27/24, 4:40 PM

still does not explain the inserts. Sort of explain reads to the database

SSamantas5855 Thank you, feel free to ask if you need further information to reproduce

Max (@rozenmd)OP•1/27/24, 4:41 PM

can you try again? I just pushed a fix

MMax (@rozenmd)can you try again? I just pushed a fix

Samantas5855•1/27/24, 4:47 PM

Sure thing, give me 5 minutes

WcaleNieWolny•1/27/24, 4:48 PM

I still cannot see the reason for huge inserts

Samantas5855•1/27/24, 4:56 PM

@Max (@rozenmd) You are a godsend

Samantas5855•1/27/24, 4:56 PM

it worked

Samantas5855•1/27/24, 4:56 PM

What was the issue?

SSamantas5855 What was the issue?

Max (@rozenmd)OP•1/27/24, 5:04 PM

work-in-progress feature i'm working on that'll let folks debug their queries easier (to make them faster and cost less rows read/written) - turns out an internal service was refusing queries bigger than 5KB, and I didn't expect that

MMax (@rozenmd)work-in-progress feature i'm working on that'll let folks debug their queries ea...

Samantas5855•1/27/24, 5:18 PM

That seems useful

WWcaleNieWolny weird. I have an index

WcaleNieWolny•1/27/24, 6:45 PM

@Matt any idea from those explain statements / queries what is causing the write?

chientrm.com•1/27/24, 11:54 PM

Uhm, where can I find more analytics about this? suddenly 754k rows read

Cchientrm.com Uhm, where can I find more analytics about this? suddenly 754k rows read

Matt Silverlock•1/28/24, 12:21 AM

Read the docs on rows: https://developers.cloudflare.com/d1/observability/metrics-analytics/

754k in just under 30 days is well within the 25 billion included in the paid plan. You’d need to do 33,156x that volume to exceed what’s included.

Metrics and analytics · Cloudflare D1 docs

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

Pepper•1/28/24, 6:18 AM

Is there any good way to edit data manually through a gui? I don't want to have to click through 20 pages to get to my most recently added row using the dashboard. I tried this https://github.com/JacobLinCool/d1-manager but it didn't work (maybe I set it up incorrectly but I just get a white screen).

GitHub

GitHub - JacobLinCool/d1-manager: D1 Manager is a web UI and API fo...

D1 Manager is a web UI and API for Cloudflare D1, a serverless SQL database. It provides a web interface for managing databases, tables, and records, as well as an AI assistant to help you write qu...

WWcaleNieWolny @Matt any idea from those explain statements / queries what is causing the write...

K•1/28/24, 8:16 AM

There is a thread about this in the community forum. https://community.cloudflare.com/t/d1-rows-written-is-counted-in-select-query/564031 . This is what I guess is happening in your case. Tagging @Matt . It would be better to mention this behavior somewhere in docs.

kian•1/28/24, 8:22 AM

Yeah, it's usually just virtual/temporary tables.

kaopanana•1/28/24, 11:03 AM

can i test d1 with postman

Kkaopanana can i test d1 with postman

Cyb3r-Jak3•1/28/24, 1:44 PM

Yes, you can use the REST API

I am interested to start a new project using Pages but I want to use D1 in this Pages app.
I've gone ahead and setup my basic Pages repo where I have a

/site

/site

directory where I keep my frontend and is what is built when I run

npm run build

npm run build

to the

/dist

/dist

directory.
And I have my

/functions

/functions

directory which is of course where I am storing my backend API.
I am wondering if I should use a

/sql

/sql

directory to house the SQL and migrations?
Or should this be kept in a different repo?

Main question - how should I be structuring my Pages repo and use/include D1 migrations?

EDAC•1/28/24, 9:17 PM

I've been seeing a lot of exceptions from my worker, all caused by the generic error message "D1_ERROR: Error: Error 9000: something went wrong". It seems to come and go, but when it happens more than half of my traffic ends with exceptions.

This doesn't seem to be input-related, as I can run the worker with identical inputs later on and it works. (This is a very simple app, all it really does is take GET values and save them).

Any suggestions on what I can do to troubleshoot this?

ItsWendell•1/28/24, 11:49 PM

Why is it that when I do a count

count(distinct "id")

count(distinct "id")

I get rows_written in meta?

{
  "collectionItems": {
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.6292,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 3862528,
        "rows_read": 1014,
        "rows_written": 342
      },
      "results": [
        {
          "count(distinct \"id\")": 342
        }
      ]
    }
}

{
  "collectionItems": {
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.6292,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 3862528,
        "rows_read": 1014,
        "rows_written": 342
      },
      "results": [
        {
          "count(distinct \"id\")": 342
        }
      ]
    }
}

ItsWendell•1/29/24, 1:29 AM

@colexemi ahhh that makes sense, good to know thanks I'll check for that!

Adi•1/29/24, 5:39 AM

Hey all, wanted some help in understanding the metrics I'm seeing on my D1 database.
There is always an inconsistency in the write queries vs the rows written.
E.g over the last 24 hours There has been 1 write query but 6.47k rows written? Trying to work out how that happens.
I initially thought maybe it's index rebalancing, but that doesn't make sense because the underlying data doesn't change (only 1 write query).

{ "collectionItems": { "success": true, "meta": { "served_by": "v3-prod", "duration": 0.6292, "changes": 0, "last_row_id": 0, "changed_db": false, "size_after": 3862528, "rows_read": 1014, "rows_written": 342 }, "results": [ { "count(distinct \"id\")": 342 } ] } }

that looks like a bug, will investigate when I'm at a laptop

Similar Threads