Problem/Motivation

- K/V store uses a merge() query
- Merge() does do DELETE + INSERT _every_ time
- Therefore the data is always written - even if its the same

Proposed resolution

- Use an upsert() instead
- While upsert() usually can only be used when there is one key, the implementation works fine to update also the collection as long as (key, collection) is a unique index. (and it should be)

- In case we don't want upsert, we could get() before set() and not set() if its the same - we are racy anyway, so the get() before set() is not a problem.

Both save 3s / 80s for installation of standard profile.

Remaining tasks

- Do it

CommentFileSizeAuthor
#2 make_k_v_store_for-2755831-2.patch774 bytesfabianx
#11 drupal-kv-store-upsert-2755831-11.patch765 bytest-lo
#17 drupal-kv-store-upsert-2755831-17.patch777 bytesanmolgoyal74
#17 interdiff_11_17.txt1.14 KBanmolgoyal74
#18 2755831-18-with-3191465-2.patch11.69 KBdaffie

Issue fork drupal-2755831

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks

Comments

Fabianx created an issue. See original summary.

fabianx’s picture

fabianx commented
StatusFileSize
new make_k_v_store_for-2755831-2.patch774 bytes

This will likely fail unit tests.

Also setIfNotExists() is totally wrong - as it always sets via the merge query. Should use a try { insert() } instead.

fabianx’s picture

fabianx commented
Status: Active » Needs review
fabianx’s picture

fabianx commented

Thanks for whoever tested PGSQL, which does not support upsert(), but uses DELETE + INSERT.

I think get() before set() is easier and because write operations are so expensive, this should still save a lot.

We can likely do it also just in state() (one of the biggest offenders during installation) or use a quick decorator, so that it works for all backends.

This might save ~ 1 min for test bot PHP 5.5.

bzrudi71’s picture

bzrudi71 commented

@Fabianx, it was me. I had the feeling that this will fail on PostgreSQL ;) Native UPSERTs are supported as of PostgreSQL >= 9.5, for all other versions we had to work around in #2542776: Add an Upsert class. But that made PostgreSQL testing terrible slow. We worked around that in #2549323: Improve the Upsert query implementation for PostgreSQL, but it's still not as fast as before the UPSERT implementation and seems buggy anyway, so not sure what can be done in this issue to make PostgreSQL rock again ;)

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.0-beta1 was released on August 3, 2016, which means new developments and disruptive changes should now be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.0-alpha1 will be released the week of January 30, 2017, which means new developments and disruptive changes should now be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.0-alpha1 will be released the week of January 17, 2018, which means new developments and disruptive changes should now be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

t-lo’s picture

t-lo
Primary language English
Location Bristol, UK
commented
StatusFileSize
new drupal-kv-store-upsert-2755831-11.patch765 bytes

Here's a re-roll

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

daffie’s picture

daffie commented
Status: Needs review » Needs work
Issue tags: +Needs reroll
anmolgoyal74’s picture

anmolgoyal74 commented
Status: Needs work » Needs review
StatusFileSize
new drupal-kv-store-upsert-2755831-17.patch777 bytes
new interdiff_11_17.txt1.14 KB

Re-rolled.

daffie’s picture

daffie commented
Status: Needs review » Postponed
Issue tags: -Needs reroll
StatusFileSize
new 2755831-18-with-3191465-2.patch11.69 KB

@anmolgoyal74: Thank you for the reroll.

I see now that changing the query from a merge query to a upsert query is failing, because upsert queries on take one key and the key_value table has a primary key with multiple columns.
I opened #3191465: Change upsert queries to allow multiple keys to change upsert queries to take in multiple key fields.
Postponing this issue on the mentioned issue.
Adding a combined patch to see if it works.

daffie’s picture

daffie commented
Title: Make K/V store for database faster by using upsert OR get() before set() » [PP-2] Make K/V store for database faster by using upsert OR get() before set()
Related issues: +#2755831: Make keyvalue and config stores for database faster by using upsert OR get() before set(), +#3159073: Use the new UPSERT capability from SQLite 3.24

This issue is also blocked by #3159073: Use the new UPSERT capability from SQLite 3.24.

amateescu’s picture

amateescu commented

Looks like a duplicate of #2547493: Add support for unique / primary key constraints composed of multiple fields for Upsert queries.. or that is a duplicate of this one :)

daffie’s picture

daffie commented
Related issues: -#2755831: Make keyvalue and config stores for database faster by using upsert OR get() before set() +#3191465: Change upsert queries to allow multiple keys

@amateescu: I forgot about that issue. I will let you choose which one to close. My preference would be to do the one that you will think will land the quickest.

catch’s picture

catch
he/him
Primary language English
commented
Title: [PP-2] Make K/V store for database faster by using upsert OR get() before set() » [PP-1] Make K/V store for database faster by using upsert OR get() before set()
Status: Postponed » Active

#3159073: Use the new UPSERT capability from SQLite 3.24 is in.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.

longwave made their first commit to this issue’s fork.

longwave opened merge request !15246

longwave’s picture

longwave
he/him
Primary language English
Location UK
commented
Title: [PP-1] Make K/V store for database faster by using upsert OR get() before set() » Make keyvalue and config stores for database faster by using upsert OR get() before set()
Status: Active » Needs review

Not sure if this needs to be postponed.

In a trivial functional test we do 2,639 database queries, many of which are to the keyvalue and config tables. Swapping the merge queries for upsert drops 285 (11%) of these queries. A spot check of some tests still pass locally, let's see what CI thinks.

longwave changed the visibility of the branch 2755831-upsert-keyvalue to hidden.

longwave’s picture

longwave
he/him
Primary language English
Location UK
commented
Title: Make keyvalue and config stores for database faster by using upsert OR get() before set() » [PP-1] Make keyvalue and config stores for database faster by using upsert OR get() before set()
Status: Needs review » Postponed

OK so that nearly worked, but broke all the build tests :)

amateescu’s picture

amateescu commented

The key_value table has a composite primary key, so we still have to do #2547493: Add support for unique / primary key constraints composed of multiple fields for Upsert queries first :)

amateescu’s picture

amateescu commented
Title: [PP-1] Make keyvalue and config stores for database faster by using upsert OR get() before set() » Make keyvalue and config stores for database faster by using upsert OR get() before set()
Status: Postponed » Closed (duplicate)

Looking at the MR from that issue again, this is actually a duplicate because we're changing the key_value queries there.

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

amateescu’s picture

amateescu commented
Related issues: +#2547493: Add support for unique / primary key constraints composed of multiple fields for Upsert queries