What’s the impact of changing the primary key? (breaks multithreading mydumper)

  • Resolved texofant

    (@texofant)


    3 weeks, 5 days ago

    Hi,
    I use mydumper / myloader to transfer the db extremely fast between staging dev and production. Now wp_options and wp_postmeta are huge, so even with multi-threading on, in the end it takes forever for the last two threads to finish building these two tables when the dump cannot be split.
    But splitting in mydumper is only available with a numeric primary key.
    Now I manually changed the primary key back to the original one for these two tables (kept the old primary as unique key), and indeed the dumping and loading works way faster now.
    Could you elaborate on what is the penalty here? what kind of queries will be slower, and on what order of magnitude?
    Thank you!

Viewing 1 replies (of 1 total)
  • Plugin Author OllieJones

    (@olliejones)

    Thanks for the excellent question. It’s all about the performance advantage of clustered indexes, a feature of InnoDB. Here’s the brief explanation.

    The clustered index is the index supporting the table’s primary key. It contains all the table’s data, that’s what clustered means. In fact, the clustered index is the table. So, table lookups which can exploit the primary key have immediate access to all the table’s data, without the CPU and potential IO cost of looking elsewhere for the data. It’s a little faster.

    I didn’t know about mydumper’s need for an autoincrementing integer primary key. As a workaround, you can keep that primary key and still get the compound index on (post_id, meta_key) by using this data definition language for wp_postmeta.

    SET @@sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');

    ALTER TABLE wp_postmeta 
      DROP KEY post_id,
      ADD KEY post_id (post_id, meta_key), 
      DROP KEY meta_key,
      ADD KEY meta_key (meta_key, meta_value(32), post_id),
      ADD KEY meta_value (meta_value(32), meta_id);

    I hope this helps.

Viewing 1 replies (of 1 total)
  • You must be logged in to reply to this topic.