One question I have about read replication, would it improve performance when writing to the DB? I n

One question I have about read replication, would it improve performance when writing to the DB? I now see a reduction in performance on write when running any sort of

SELECT

SELECT

query, will those be split to a separate replica?

itsmatteomanfOP•5/31/23, 8:23 PM

And secondary question, any ETA on backups on the experimental backend? It's very hard to export data now, even a manual backup would be great in the meantime.

Iitsmatteomanf One question I have about read replication, would it improve performance when wr...

Matt Silverlock•5/31/23, 8:35 PM

Clients will read from the (geographically) nearest read replica. That may sometimes be the leader (write+read). What kind of perf reduction are you seeing here?

Matt Silverlock•5/31/23, 8:36 PM

(Per the blog) We're entirely moving to Point in Time Recovery for the foreseeable future vs. snapshot based backups, since PITR is a) infinitely more flexible and b) is better than snapshots in terms of actually restoring your DB. What you won't be able to do until GA is export a DB directly.

MMatt Silverlock Clients will read from the (geographically) nearest read replica. That may somet...

itsmatteomanfOP•5/31/23, 8:45 PM

Not much, tbh, but I've doing a lot of requests and I saw some slowdowns after a couple of queries in the console (somewhat big, but not massive ones). Understood. Will check next time the queue runs and in case report back

One thing I'm not sure about is ~~where would a scheduled Worker run and, more importantly~~ (this was dumb lul

) where the consumers for queues would run to locate the db closer to there (especially with the replicas on the horizon as the users would hit only the read side)

MMatt Silverlock (Per the blog) We're entirely moving to Point in Time Recovery for the foreseeab...

itsmatteomanfOP•5/31/23, 8:45 PM

I see, so the wait is for the Time Travel feature... hopefully soon!

itsmatteomanfOP•5/31/23, 8:46 PM

A good export option would be nice regardless, tbh

Iitsmatteomanf A good export option would be nice regardless, tbh 🙂

Matt Silverlock•5/31/23, 9:06 PM

We'd need to solve import - you may not care, but many others will. Import is also tricky to solve.

Iitsmatteomanf Not much, tbh, but I've doing a lot of requests and I saw some slowdowns after a...

Matt Silverlock•5/31/23, 9:06 PM

You shouldn't typically see a slowdown as our storage implementation is non-blocking (it's effectively a WAL-mode implementation):

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

MMatt Silverlock We'd need to solve import - _you_ may not care, but many others will. Import is ...

itsmatteomanfOP•5/31/23, 9:07 PM

Oh yeah, I had to bypass that, too... but you can use a very big .sql.sql file if you really want to (up to a point)

MMatt Silverlock You shouldn't typically see a slowdown as our storage implementation is non-bloc...

itsmatteomanfOP•5/31/23, 9:08 PM

It might have been something else, unfortunately I don't have just DB calls, but also a 1.1.1.11.1.1.1 fetch and it's a queue consumer, so who knows. Will report back if it becomes a pattern

Zzszszsz What is the recommended way to get the row_id (or sth) from a `insert or ignore`...

Max (@rozenmd)•6/1/23, 8:19 AM

RETURNING *RETURNING *

Zzszszsz Hmmm...how ?

Max (@rozenmd)•6/1/23, 8:20 AM

INSERT INTO some_table (name) VALUES ('something') RETURNING idINSERT INTO some_table (name) VALUES ('something') RETURNING id for example

Max (@rozenmd)•6/1/23, 8:21 AM

saves you a round trip to the db

Leon338•6/1/23, 6:18 PM

Hello, Quick Question about the Pricing. So for example I have a ordered List of IDs and select one small single Entry, it would maybe count as 4-5 Reads and now if I compare it to KV it would be 100x cheaper to retrieve a single Entry. And once there is Read Replication it'll be fast, too. My Question is how can it be so much cheaper? Like I understand the Pricing has to be like that for bigger Queries, but sounds to good to be true

Leon338•6/1/23, 6:20 PM

I know. Have you read the Question?

Leon338•6/1/23, 6:21 PM

No the Database Solution would be 100x cheaper. Even tho it would count some reads on writing for the Index

Unsmart•6/1/23, 6:29 PM

As I have already said in the past the pricing difference entirely depends. If you max out the size allowed in KV, D1 would be an outrageous amount more.
d1-database

Though it is technically impossible to have that large of a record in D1. If all of your values are small D1 can definitely be cheaper given they bill based on units so the smaller the write/read the smaller the cost.

D1 storage does cost more though so at some point once you reach a certain storage amount KV will pretty much always be cheaper. Of course that depends on you reaching a fairly large storage amount.

Also note that reads on D1 each row that gets scanned even if not returned will add to the bill so if you have a bad query that scans the entire table you could in theory be billed quite a lot of read units per query.

yeehow•6/1/23, 6:41 PM

Running into an error today when trying to apply migrations. Was working fine for me yesterday so not sure whats going on.

[ERROR] A request to the Cloudflare API (/accounts/53b0f0b6a2f098df4d15b72741f23ba1/d1/database/0e8a4afd-b72b-4f12-99c2-1c7c9c9a74e5/backup) failed.

  The request is malformed: Experimental DBs do not support backups yet:
  0e8a4afd-b72b-4f12-99c2-1c7c9c9a74e5 (v3) [code: 7400]

  If you think this is a bug, please open an issue at:
  https://github.com/cloudflare/workers-sdk/issues/new/choose

[ERROR] A request to the Cloudflare API (/accounts/53b0f0b6a2f098df4d15b72741f23ba1/d1/database/0e8a4afd-b72b-4f12-99c2-1c7c9c9a74e5/backup) failed.

  The request is malformed: Experimental DBs do not support backups yet:
  0e8a4afd-b72b-4f12-99c2-1c7c9c9a74e5 (v3) [code: 7400]

  If you think this is a bug, please open an issue at:
  https://github.com/cloudflare/workers-sdk/issues/new/choose

yeehow•6/1/23, 7:04 PM

Yeah agreed

Yyeehow Running into an error today when trying to apply migrations. Was working fine fo...

Max (@rozenmd)•6/1/23, 7:22 PM

when you apply a migration for an experimental backend db, (for now) you need to pass --experimental-backend--experimental-backend

MMax (@rozenmd)when you apply a migration for an experimental backend db, (for now) you need to...

yeehow•6/1/23, 7:22 PM

ahhh ok thank you

Original message was deleted

Leon338•6/1/23, 9:21 PM

I'm not entirely sure but logically it has to scan every CompanyName or atleast the first digit, because even the last row could be this CompanyName. Could be fixed by setting a Limit at the End for example to find only the first one. But yes an Index sorted by CompanyName should help alot. Basically would change from O(n) to O(log(n))

Leon338•6/1/23, 9:26 PM

And keep in Mind the Reads are Size based. If your CompanyNames has an average length of 8 chars it would take 500 scans for one Read Unit. But maybe the Where statement is optimized so that it doesnt even read the entire CompanyName

Original message was deleted

Leon338•6/1/23, 9:34 PM

Nevermind I think im wrong with the Index. The WHERE LIKE statement can be different and also search if something contains anything. I don't even think a Index would help there and it's always O(n)

Original message was deleted

Leon338•6/1/23, 10:10 PM

Not if you have a % at the start and at the end, thats always an entire Table Scan

Leon338•6/1/23, 10:16 PM

But you could try to use Contains instead of Like that should be faster and is more optimized I guess

Leon338•6/1/23, 10:20 PM

And if the Company Name contains multiple Words you can look into a Fulltext Index, but I mean this type of Queries will always scan alot of rows

One question I have about read replication, would it improve performance when writing to the DB? I n

Similar Threads

Similar Threads

Similar Threads