Cloudflare Developers•2y ago

Though note that the HTTP API is pretty slow, so we do generally recommend running traffic through a

Though note that the HTTP API is pretty slow, so we do generally recommend running traffic through a Worker anyway

HHello, I’m Allie!Though note that the HTTP API is pretty slow, so we do generally recommend runni...

dhrubo•2/26/24, 7:48 AM

Yes. Getting there ... Will report

dhrubo•2/26/24, 9:30 AM

It would be great if the API provided database meta data ): -

Tony Leung•2/26/24, 9:54 AM

Hi guys, is it normal to see a huge spike in queries/row metric of D1? I'm observing an unreasonably huge hit in my D1

Tony Leung•2/26/24, 9:55 AM

Sam•2/26/24, 9:56 AM

If you have 100 rows, and do 15 queries, then you'll read 1.5k rows

You can solve this by creating indexes

SSam If you have 100 rows, and do 15 queries, then you'll read 1.5k rows You can sol...

Tony Leung•2/26/24, 10:01 AM

I've created some indexes, but a lot of my queries does not use that much of row read though

Sam•2/26/24, 10:02 AM

8k queries read isn't even that many. You did over 200 queries. That's 40 rows read per query

Tony Leung•2/26/24, 10:04 AM

in my dev db, which i didn't use for a week also have some suspicious queries

SSam 8k queries read isn't even that many. You did over 200 queries. That's 40 rows r...

Tony Leung•2/26/24, 10:05 AM

I didn't see any traffic on my site, that's the problem. My dev site which is not open to the world and left untouch for a week also has this problem

Original message was deleted

Tony Leung•2/26/24, 10:08 AM

my site has zero trust guarded and all of endpoints are protected with JWT tho.

Tony Leung•2/26/24, 10:08 AM

my .workers.dev are protected by zero trust too

Tony Leung•2/26/24, 10:09 AM

cf pages does not let me turn off pages.dev domain anw

Tony Leung•2/26/24, 10:10 AM

i also have a cron trigger which run every 30 mins, but it only pokes into a table which has no more than 2 records and this record is well indexed. I'm also not enable any cron trigger on my dev site

Sam•2/26/24, 10:12 AM

You can read 5 million rows for free every single day. And if you're well protected, then everything should be fine.

Max (@rozenmd)•2/26/24, 10:12 AM

@saitama looking at your data in the dashboard counts as reads

Max (@rozenmd)•2/26/24, 10:12 AM

particularly the list of tables, is a very inefficient query (since sqlite doesn't provide a means of accurately fetching row counts)

Max (@rozenmd)•2/26/24, 10:13 AM

(we're removing the row count query)

Tony Leung•2/26/24, 10:13 AM

oh no... I was regularly open the dashboard and saw the high read spike

Tony Leung•2/26/24, 10:14 AM

so D1 regularly scan my db for metrics? Because I saw the metric before I open the dashboard

TTony Leung so D1 regularly scan my db for metrics? Because I saw the metric before I open t...

Max (@rozenmd)•2/26/24, 10:14 AM

no, only when you open the dashboard

Original message was deleted

Tony Leung•2/26/24, 10:15 AM

my landing page does not query anything, it's just a plain html page so it shouldn't query anything

Max (@rozenmd)•2/26/24, 10:16 AM

you can use wrangler d1 insightswrangler d1 insights to see what the high rows_read queries are

Max (@rozenmd)•2/26/24, 10:16 AM

i'll bet its a nasty select * from (select count(*) from table...);select * from (select count(*) from table...);

MMax (@rozenmd)you can use `wrangler d1 insights` to see what the high rows_read queries are

Tony Leung•2/26/24, 10:16 AM

thanks

Tony Leung•2/26/24, 10:17 AM

I will try it now

Max (@rozenmd)•2/26/24, 10:17 AM

(if the command doesn't exist, you'll need to bump wrangler)

Tony Leung•2/26/24, 10:20 AM

I'm having a table which has a few thousands of rows. Should I store more than 100k rows in it?

Tony Leung•2/26/24, 10:21 AM

(I've heard that SQLite is not suitable for large table, but I doubt that is the problem since you guys choose SQLite to be D1, not smth like Derby or Postgres, so it should be mitigated)

Tony Leung•2/26/24, 10:26 AM

ah yes... select sqlite masterselect sqlite master and select (selectselect (select contributes almost 10k

TTony Leung I'm having a table which has a few thousands of rows. Should I store more than 1...

Max (@rozenmd)•2/26/24, 10:26 AM

row count doesn't matter as much as DB size - as you approach 2GB, sharding becomes important

Max (@rozenmd)•2/26/24, 10:27 AM

D1 beta specifically struggles with high write workloads (>400 requests per second), we can do a lot more reads per second, and read replication should multiply that

Tony Leung•2/26/24, 10:27 AM

okay, I'm having a table which containing large text, i guess I have to move it now

MMax (@rozenmd)row count doesn't matter as much as DB size - as you approach 2GB, sharding beco...

Tony Leung•2/26/24, 10:28 AM

is there anyway I can dump a db like old D1? I can't find any command which supports it

TTony Leung okay, I'm having a table which containing large text, i guess I have to move it ...

Max (@rozenmd)•2/26/24, 10:28 AM

what's the use case?

TTony Leung is there anyway I can dump a db like old D1? I can't find any command which supp...

Max (@rozenmd)•2/26/24, 10:29 AM

we're working on adding dump functionality atm, until then we recommend https://github.com/nora-soderlund/cloudflare-d1-backups

MMax (@rozenmd)what's the use case?

Tony Leung•2/26/24, 10:31 AM

I mindlessly store a large paragraph (about 4-6k characters each row) into d1 in the development phase, planned to move it to R2 but completely forgot until now

Tony Leung•2/26/24, 10:31 AM

it's not recommended to store it in any SQL anw, even when FTS is needed i should index it to elasticsearch

Max (@rozenmd)•2/26/24, 10:34 AM

ah yeah was going to suggest KV but if you need to search the text, that's harder

Tony Leung•2/26/24, 10:42 AM

thanks for your support

Max (@rozenmd)•2/26/24, 10:50 AM

no worries!

bun•2/26/24, 1:06 PM

transactions ?

bun•2/26/24, 1:16 PM

bun•2/26/24, 1:17 PM

db.batch([
    db.insert(users).values(),
    db.insert(posts).values({ userId: sql`last_insert_rowid()` })
])

db.batch([
    db.insert(users).values(),
    db.insert(posts).values({ userId: sql`last_insert_rowid()` })
])

will something like this work ?

Uulrikstrid What is the easiest way of resetting a local d1? Just delete the file?

Max (@rozenmd)•2/26/24, 2:31 PM

pretty much - or keep a bunch of DROP TABLE IF EXISTSDROP TABLE IF EXISTS at the start of your schema.sql, you can then add a package.json script that runs wrangler d1 execute dbname --local --file=schema.sqlwrangler d1 execute dbname --local --file=schema.sql to reinitialize the db as needed

Ddhrubo Yes. Getting there ... Will report

dhrubo•2/26/24, 4:28 PM

seeing response times between 400-600 ms - not ideal but will let clients decide.

Ddhrubo seeing response times between 400-600 ms - not ideal but will let clients decide...

Max (@rozenmd)•2/26/24, 4:38 PM

don't use the HTTP API if you want performance.

MMax (@rozenmd)don't use the HTTP API if you want performance.

dhrubo•2/26/24, 5:22 PM

Yeh for sure. But received feature request for DB2REST an open source project that I maintain on GitHub . Will add the perf issue as part of docs so that users are aware of it.

Original message was deleted

dhrubo•2/26/24, 6:04 PM

Good suggestion will check this option once the non worker version is out of the gate

Beyondo•2/26/24, 6:58 PM

What is the point of D1PreparedStatementD1PreparedStatement if we could just D1Database->exec()D1Database->exec()? I get it can be "faster" to prepare a statement and bind different values, but since CloudFlare Workers are temporary, then what's the point? If we know that our worker is always gonna execute the same single query over and over (not multiple queries), does it make a difference in performance?

Though note that the HTTP API is pretty slow, so we do generally recommend running traffic through a

Similar Threads

Similar Threads

Similar Threads