[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):

 KEY type, client_id

 keep longtext as is
 rename to data





 Mega comment incoming...

 ----


 @czarate just had a quick call to discuss the proposed structure above.

 With the inevitable naming things discussion, I agree `update_value` would
 be best named `data` to cover potential future use cases (yep, I suggested
 the name initially, sorry). @davidbaumwald  I'd like to keep it as
 LONGTEXT to match the structure used in the meta tables.

 Otherwise, we think `KEY type_client (type,client_id)` would be handy for
 some of the required queries. `type` comes first as it can be queried
 alone, whereas `client_id` queries should always require a `type`.


 @JoeFusco are you happy with the following?



 {{{#!sql
 CREATE TABLE wp_collaboration (
    id bigint(20) unsigned NOT NULL auto_increment, -- also serves as the
 polling cursor
    room varchar(191) NOT NULL default '',          -- e.g.
 postType/post:123, LIKE-able
    type varchar(32) NOT NULL default '',           -- update, sync_step1,
 sync_step2, compaction, awareness
    client_id varchar(32) NOT NULL default '',      -- one per browser tab
    user_id bigint(20) unsigned NOT NULL default '0',
    data longtext NOT NULL,
    date_gmt datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (id),
    KEY type_client_id (type,client_id),
    KEY room (room,id),                             -- covers polling:
 WHERE room = %s AND id > %d
    KEY date_gmt (date_gmt)                         -- covers cron cleanup:
 WHERE date_gmt < %s
 );
 }}}



 ----

 @ellatrix @joehoyle: maybe it would help to have non auto-loaded meta
 data, but I think that would be a larger change and warrant another
 ticket. While I opened the ticket because of the effects on cache, I think
 the new table is a better solve for the issue at hand and allows us to
 avoid altering the Query and meta APIs.

 ----

 @dmsnell I think it would be possible to slow the updates down without
 collaborators in the room. With collaborators in the room, I don't think
 we'd want to do so to a heartbeat frequency.

 FWIW, #64845 is covering frequency of updates after the concern was raised
 by some hosting providers. Are you able to post some thoughts there?

 > Given the timeline and still-broad discussion of scope, design, and even
 table counts or names, can this be resolved inside a temporary structure
 now and gracefully updated to a table once we have more time to iron out
 the basics?

 The table will very quickly grow in size and the way database updates work
 with `dbDelta()` prevent us from truncating the table before altering it,
 so I'd really like to get it as robust as possible for 7.0.

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