[wp-trac] [WordPress Trac] #64696: Real time collboration effectively disables persistent post caches while anyone edits a post

[WordPress Trac]

#64696: Real time collboration effectively disables persistent post caches while
anyone edits a post
--------------------------------------+--------------------------
 Reporter:  peterwilsoncc             |       Owner:  (none)
     Type:  defect (bug)              |      Status:  new
 Priority:  normal                    |   Milestone:  7.0
Component:  Posts, Post Types         |     Version:  trunk
 Severity:  normal                    |  Resolution:
 Keywords:  has-patch has-unit-tests  |     Focuses:  performance
--------------------------------------+--------------------------

Comment (by peterwilsoncc):

 These are notes shared by @annezazu in the release squad Slack channel a
 few days as the result of some testing of database queries against various
 approaches:

 In this document:
 * Core: Refers to WordPress Develop code at the time of testing, this is
 in [https://github.com/WordPress/wordpress-
 develop/commit/88fe6bf7a5defe8103d29324fc2ec85b24ed05b9 WordPress
 /wordpress-devel at 88fe6bf7a5defe81]
 * Custom DB table: Tested against [https://github.com/WordPress/wordpress-
 develop/pull/11256 PR#11256] by @JoeFusco
 * Transients: Tested against [https://github.com/WordPress/wordpress-
 develop/pull/11348 PR#11348] by me. This also includes just in time cache
 flushing for the meta API

 Judging by the notes in the transients awareness column, this was tested
 on a system with a persistent cache.

 > ## Query Analysis: Three Storage Approaches for RTC
 >
 > We captured query logs from three Jurassic Ninja test sites during ~2
 minutes of collaborative editing (two browser tabs, same post) to compare
 the database cost of each storage backend.
 >
 > ||=                     =||= Core (trunk) =||= Custom DB table    =||=
 Transients          =||
 > || Total SQL queries     || 3,893          || 1,277 **(−67%)**     ||
 766 **(−80%)**        ||
 > || Total DB time         || 1,649 ms       || 834 ms **(−49%)**    ||
 383 ms **(−77%)**     ||
 > || Queries per sync poll || 27.5           || 9.8 **(−64%)**       ||
 5.3 **(−81%)**        ||
 > || Awareness DB queries  || 2,841          || 886 **(−69%)**       || 0
 **(−100%)**         ||
 >
 >
 > Core stores everything — awareness and updates — in `wp_postmeta`,
 generating nearly 4,000 queries in under two minutes. The Custom DB
 approach moves both to a dedicated `wp_collaboration` table, cutting
 queries by two-thirds. The Transients approach goes further: awareness is
 served from the object cache with zero SQL, reducing total queries by 80%.
 >
 > The performance gap comes entirely from query volume, not individual
 query speed — average execution time was under 1 ms across all three.
 Awareness state is the biggest factor: it's read and written on every poll
 cycle, and Core needs multiple `wp_postmeta` queries each time.
 Eliminating those queries (Transients) or consolidating them into a
 purpose-built table (Custom DB) is what drives the improvement.
 >
 > A hybrid of the two — transients for awareness, custom table for updates
 — could combine the strengths of both approaches.

 Based on the final comment with the hybrid approach, I have prepared
 [https://github.com/WordPress/wordpress-develop/pull/11599 PR#11599] to
 use the hybrid approach (it's forked from Joe's custom DB table PR).

 In the latest PR:

 * Awareness is stored in the object cache when a persistent cache is
 available (this is the key to the transients approach, not transients per
 se)
 * Awareness is stored in the custom table when a persistent cache is not
 available
 * Updates and sync data is stored in the custom table (unchanged from
 Joe's PR#11256)

 The hybrid approach is pending testing for database queries against both
 installs with and without a persistent cache.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/64696#comment:158>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform