Skip to content

Instantly share code, notes, and snippets.

@racmanuel
Forked from xhrix/one-million-posts.md
Created October 31, 2022 23:26
Show Gist options
  • Save racmanuel/08fff2a4dc9754962dcf568b208d88c9 to your computer and use it in GitHub Desktop.
Save racmanuel/08fff2a4dc9754962dcf568b208d88c9 to your computer and use it in GitHub Desktop.
One Million Posts - Wordpress

One million posts

Let's create one million posts to see the performance of a Wordpress site.

Creation of a post

The purpose of this document is to find out what is the underlying data that gets modified in a wordpress database when a post with an image are created.

What I did

  1. I unziped the Worpress.
  2. I created a brand new database.
  3. I installed Wordpress.

At that point, I created a database backup pristine_wp.sql.

  1. I logged in.
  2. I uploaded an image (i-am-a-splash-image.png) to media, and filled it's "alt text".
  3. I created a tag for the post debugtag.
  4. I created a post with it's "title", "content", "excerpt", an image in its content, a featured image, a category (uncategorized) and a tag.
  5. I published the post.
  6. I logged out.

At that point, I created another database backup: first_post_wp.sql.

System Specs

The system I used to test this was:

  • OS: MacOS Sierra 10.12.4
  • PHP Version: 7.1.1
  • Worpress Version: 4.7.3
  • Database type: MySQL

The date when this operation was performed was April 7th, 2017.

Things that were modified between pristine_wp.sql and first_post_wp.sql

wp_post_meta

A total of 5 entries in the wp_postmeta were created.

The first one is an edit lock. It might not be important.

{
    "meta_id": 3,
    "post_id": 4,
    "meta_key": "_edit_lock",
    "meta_value": "1491615951:1" // It seems to be the unix epocth of the lock : userId.
}

The second one relates a file (the image that I uploaded) to the post of type attachment that was created to hold the image.

{
    "meta_id": 4,
    "post_id": 5, // ID of the post of type `attachment` that holds the image.
    "meta_key": "_wp_attached_file", // Indicates that this post has an attached file.
    "meta_value": "2017/04/i-am-a-splash-image.png" // Path to the file.
}

The third one is all the metadata of the image I uploaded. That metadata contains information relating it's condition as an attachment.

    {
        "meta_id": 5,
        "post_id": 5,
        "meta_key": "_wp_attachment_metadata",
        "meta_value": `a:5:{s:5:"width";i:1717;s:6:"height";i:1040;s:4:"file";s:31:"2017/04/i-am-a-splash-image.png";s:5:"sizes";a:5:{s:9:"thumbnail";a:4:{s:4:"file";s:31:"i-am-a-splash-image-150x150.png";s:5:"width";i:150;s:6:"height";i:150;s:9:"mime-type";s:9:"image/png";}s:6:"medium";a:4:{s:4:"file";s:31:"i-am-a-splash-image-300x182.png";s:5:"width";i:300;s:6:"height";i:182;s:9:"mime-type";s:9:"image/png";}s:12:"medium_large";a:4:{s:4:"file";s:31:"i-am-a-splash-image-768x465.png";s:5:"width";i:768;s:6:"height";i:465;s:9:"mime-type";s:9:"image/png";}s:5:"large";a:4:{s:4:"file";s:32:"i-am-a-splash-image-1024x620.png";s:5:"width";i:1024;s:6:"height";i:620;s:9:"mime-type";s:9:"image/png";}s:32:"twentyseventeen-thumbnail-avatar";a:4:{s:4:"file";s:31:"i-am-a-splash-image-100x100.png";s:5:"width";i:100;s:6:"height";i:100;s:9:"mime-type";s:9:"image/png";}}s:10:"image_meta";a:12:{s:8:"aperture";s:1:"0";s:6:"credit";s:0:"";s:6:"camera";s:0:"";s:7:"caption";s:0:"";s:17:"created_timestamp";s:1:"0";s:9:"copyright";s:0:"";s:12:"focal_length";s:1:"0";s:3:"iso";s:1:"0";s:13:"shutter_speed";s:1:"0";s:5:"title";s:0:"";s:11:"orientation";s:1:"0";s:8:"keywords";a:0:{}}}`
    }

Let's unserialize the meta_value of the previous bunch of data:

{
    "width": 1717,
    "height": 1040,
    "file": "2017/04/i-am-a-splash-image.png",
    "sizes": {
        "thumbnail": {
            "file": "i-am-a-splash-image-150x150.png",
            "width": 150,
            "height": 150,
            "mime-type": "image/png"
        },
        "medium": {
            "file": "i-am-a-splash-image-300x182.png",
            "width": 300,
            "height": 182,
            "mime-type": "image/png"
        },
        "medium_large": {
            "file": "i-am-a-splash-image-768x465.png",
            "width": 768,
            "height": 465,
            "mime-type": "image/png"
        },
        "large": {
            "file": "i-am-a-splash-image-1024x620.png",
            "width": 1024,
            "height": 620,
            "mime-type": "image/png"
        },
        "twentyseventeen-thumbnail-avatar": {
            "file": "i-am-a-splash-image-100x100.png",
            "width": 100,
            "height": 100,
            "mime-type": "image/png"
        }
    },
    "image_meta": {
        "aperture": "0",
        "credit": "",
        "camera": "",
        "caption": "",
        "created_timestamp": "0",
        "copyright": "",
        "focal_length": "0",
        "iso": "0",
        "shutter_speed": "0",
        "title": "",
        "orientation": "0",
        "keywords": []
    }
}

The fourth entry relates the post of the image to it's "alt text".

{
    "meta_id": 6,
    "post_id": 5, // ID of the post of type `attachment` that holds the image.
    "meta_key": "_wp_attachment_image_alt", // Indicates that this metadata holds the alt text of the image.
    "meta_value": "I am an alt text." // Alt text of the image.
}

The fifth entry relates the entry of the very post to it's thumbnail id.

{
    "meta_id": 7,
    "post_id": 4, // ID of the very post.
    "meta_key": "_thumbnail_id", // Indicates that this metadata relates the very post to it's thumbnail.
    "meta_value": "5" // ID of the post of the image.
}

wp_posts

A total of 4 entries in the wp_posts table were created.

The first is an Auto Draft with post_type post. It might not be imporant.


The second one is the important one, the entry of the very post.

{
    ID: 4,
    post_author: 1,
    post_date: '2017-04-08 01:47:04',
    post_date_gmt: '2017-04-08 01:47:04',
    post_content: `I am just a simple content.

[caption id="attachment_5" align="alignnone" width="300"]<img class="size-medium wp-image-5" src="http://localhost/razon/wp-content/uploads/2017/04/i-am-a-splash-image-300x182.png" alt="I am an alt text." width="300" height="182" /> I am an image caption.[/caption]`,
    post_title: 'I am a debug title',
    post_excerpt: 'I am a simple excerpt.',
    post_status: 'publish',
    comment_status: 'open',
    ping_status: 'open',
    post_password: '',
    post_name: 'i-am-a-debug-title',
    to_ping: '',
    pinged: '',
    post_modified: '2017-04-08 01:47:04',
    post_modified_gmt: '2017-04-08 01:47:04',
    post_content_filtered: '',
    post_parent: 0,
    guid: 'http://localhost/razon/?p=4',
    menu_order: 0,
    post_type: 'post',
    post_mime_type: '',
    comment_count: 0,
}

The thirth one is the one for the image.

{
    ID: 5,
    post_author: 1,
    post_date: "2017-04-08 01:45:00",
    post_date_gmt: "2017-04-08 01:45:00",
    post_content: "I am a description.",
    post_title: "i-am-a-splash-image",
    post_excerpt: "I am an image caption.",
    post_status: "inherit",
    comment_status: "open",
    ping_status: "closed",
    post_password: "",
    post_name: "i-am-a-splash-image",
    to_ping: "",
    pinged: "",
    post_modified: "2017-04-08 01:45:28",
    post_modified_gmt: "2017-04-08 01:45:28",
    post_content_filtered: "",
    post_parent: 4,
    guid: "http://localhost/razon/wp-content/uploads/2017/04/i-am-a-splash-image.png",
    menu_order: 0,
    post_type: "attachment",
    post_mime_type: "image/png",
    comment_count: 0
}

The fourth one is a Revition with post_type revition. It might not be imporant.

wp_terms_relationships

The first entry in wp_terms_relationships relates the very post to the term "uncategorized" under the taxonomy "category".

{
    object_id: 4, // This is the ID of the post.
    term_taxonomy_id: 1, // This is the ID of the default term, "uncategorized" under the taxonomy "category"
    term_order: 0,
}

The second entry in wp_terms_relationships relates the very post to the term "debugtag" under the taxonomy "post_tag".

{
    object_id: 4, // This is the ID of the post.
    term_taxonomy_id: 2, // This is for the term I created, "debugtag" under the taxonomy "post_tag".
    term_order: 0,
}

wp_term_taxonomy

When I created a tag with name debugtag, this entry was created in wp_term_taxonomy.

{
    term_taxonomy_id: 2,
    term_id: 2,
    taxonomy: 'post_tag',
    description: '',
    parent: 0,
    count: 1,
}

That seems to be a built-in type of taxonomy which all the tags of a post have.

wp_terms

When I created a tag with name debugtag, this entry was created in wp_terms.

{
    term_id: 2,
    name: 'debugtag',
    slug: 'debugtag',
    term_group: 0,
}

That seems to be the actual tag I created.

wp_usersmeta

Entries with meta_key 'wp_dashboard_quick_press_last_post_id', 'closedpostboxes_post', 'metaboxhidden_post', 'wp_user-settings', 'wp_user-settings-time' were created. They seem to be not important.

Also, the entry with meta_key 'session_tokens' was deleted. It might be due to the fact I logged out when I created the post.

Files created when I uploaded the image

The images were created in this directory wp-content/uploads/2017/04.

The image files were:

  • i-am-a-splash-image-100x100.png
  • i-am-a-splash-image-1024x620.png
  • i-am-a-splash-image-150x150.png
  • i-am-a-splash-image-300x182.png
  • i-am-a-splash-image-768x465.png
  • i-am-a-splash-image.png

Creating one million posts

To create one million posts, I can start from the first_post_wp.sql database, that contains an uploaded image, and a post. I will no longer need to upload images or create more post types of type attachment, or terms, or taxonomies.

The thing I want to create is:

- A post of post type `post`.
    - That post must have title, content, excerpt.
    - The content of the post must have an attachment (the same as the debug post).
    - That post must have a tag. I will use the `debugtag` i previously created.
    - That post must have a category. I will use the `uncategorized` default category of Wordpress.

So, the tables I will have to insert data into are in this order:

Insert into wp_posts

{
    // ID: 4, // This must be ommited so MySQL can assign an ID automatically.
    post_author: 1, // Keep it constant to assign it to my usrer ID.
    post_date: '2017-04-08 01:47:04', // Keep it constant. We don't want to mess witih dates.
    post_date_gmt: '2017-04-08 01:47:04', // Keep it constant. We don't want to mess witih dates.
    // In the content add the ${$new_post_id} to differentiate it.
    post_content: `I am just a simple content x${$new_post_id}.

[caption id="attachment_5" align="alignnone" width="300"]<img class="size-medium wp-image-5" src="http://localhost/razon/wp-content/uploads/2017/04/i-am-a-splash-image-300x182.png" alt="I am an alt text." width="300" height="182" /> I am an image caption.[/caption]`,
    // In the title of the post add the ${$new_post_id} to differentiate it.
    post_title: `I am a debug title x${$new_post_id}`,
    // In the excerpt add the ${$new_post_id} to diffetentiate it.
    post_excerpt: `I am a simple excerpt x${$new_post_id}.`,
    post_status: 'publish', // Keep it constant. We want all our posts to be published.
    comment_status: 'open', // Keep it.
    ping_status: 'open', // Keep it.
    post_password: '', // Keep it.
    // The post name MUST be unique. To ensure this, we add the `x-${$new_post_id}` at the end of the value.
    post_name: `i-am-a-debug-title-x-${$new_post_id}`,
    to_ping: '', // Keep it.
    pinged: '', // Keep it.
    post_modified: '2017-04-08 01:47:04', // Keep it.
    post_modified_gmt: '2017-04-08 01:47:04', // Keep it.
    post_content_filtered: '', // Keep it.
    post_parent: 0, // Keep it.
    // The guid MUST be unique, and also, the 'p' param must be the ID of the post. To achieve this, we use our friend ${$new_post_id}.
    guid: `http://localhost/razon/?p=${$new_post_id}`,
    menu_order: 0, // Keep it.
    post_type: 'post', // Keep it.
    post_mime_type: '', // Keep it.
    comment_count: 0, // Keep it.
}

Insert into wp_post_meta

To relate the post to its thumbnail:

{
    // "meta_id": 7, // This must be ommited so MySQL can assign an ID automatically.
    "post_id": $new_post_id, // ID of the recently created post.
    "meta_key": "_thumbnail_id", // Keep this constant. Indicates that this metadata relates the very post to it's thumbnail.
    "meta_value": "5" // Keep this constant. ID of the post of the image, the only one I have uploaded.
}

Insert into wp_terms_relationships

Relate the new post to the term uncategorized under the taxonomy category.

{
    object_id: $new_post_id, // ID of the new post.
    term_taxonomy_id: 1, // ID of the default term, "uncategorized" under the taxonomy "category"
    term_order: 0,
}

Relate the new post to the term debugtag under the taxonomy post_tag.

{
    object_id: $new_post_id, // ID of the new post.
    term_taxonomy_id: 2, // This is for the term I created, "debugtag" under the taxonomy "post_tag".
    term_order: 0,
}

The MySQL script

drop procedure if exists load_foo_test_data;

delimiter #
create procedure load_foo_test_data()
  begin

    declare v_max int unsigned default 1; -- Alter this number up to one million, be careful not to overload your database, it might die.
    declare v_counter int unsigned default 0;

    start transaction;
    while v_counter < v_max do

      -- CREATE POST ----------------------------------------------------------------------------
      -- ----------------------------------------------------------------------------------------
      INSERT INTO `wp_posts` (
          -- `ID`, // This must be ommited so MySQL can assign an ID automatically.
          `post_author`,
          `post_date`,
          `post_date_gmt`,
          `post_content`,
          `post_title`,
          `post_excerpt`,
          `post_status`,
          `comment_status`,
          `ping_status`,
          `post_password`,
          `post_name`,
          `to_ping`,
          `pinged`,
          `post_modified`,
          `post_modified_gmt`,
          `post_content_filtered`,
          `post_parent`,
          `guid`,
          `menu_order`,
          `post_type`,
          `post_mime_type`,
          `comment_count`
          ) VALUES (
          1, -- post_author // Keep it constant to assign it to my usrer ID.
          '2017-04-08 01:47:04', -- post_date // Keep it constant. We don't want to mess witih dates.
          '2017-04-08 01:47:04', -- post_date_gmt // Keep it constant. We don't want to mess witih dates.
          '', -- post_content // Updated later on.
          '', -- post_title // Updated later on.
          '', -- post_excerpt // Updated later on.
          'publish', -- post_status // Keep it constant. We want all our posts to be published.
          'open', -- comment_status // Keep it.
          'open', -- ping_status // Keep it.
          '', -- post_password // Keep it.
          '', -- post_name // Updated later on.
          '', -- to_ping // Keep it.
          '', -- pinged // Keep it.
          '2017-04-08 01:47:04', -- post_modified // Keep it.
          '2017-04-08 01:47:04', -- post_modified_gmt // Keep it.
          '', -- post_content_filtered // Keep it.
          0, -- post_parent // Keep it.
          '', -- guid // Updated later on.
          0, -- menu_order // Keep it.
          'post', -- post_type // Keep it.
          '', -- post_mime_type // Keep it.
          1 -- comment_count // Keep it.
      );
      set @future_id = LAST_INSERT_ID();
      UPDATE `wp_posts` SET
        -- In the content add the ${$new_post_id} to differentiate it.
        `post_content` =  CONCAT('I am just a simple content x-', @future_id, '.

[caption id="attachment_5" align="alignnone" width="300"]<img class="size-medium wp-image-5" src="http://localhost/wp-content/uploads/2017/04/i-am-a-splash-image-300x182.png" alt="I am an alt text." width="300" height="182" /> I am an image caption.[/caption]'),
        -- In the title of the post add the ${$new_post_id} to differentiate it.
        `post_title` = CONCAT('I am a debug title x-', @future_id),
        -- In the excerpt add the ${$new_post_id} to diffetentiate it.
        `post_excerpt` = CONCAT('I am a simple excerpt x-', @future_id),
        -- The post name MUST be unique. To ensure this, we add the `x-${$new_post_id}` at the end of the value.
        `post_name` =  CONCAT('i-am-a-debug-title-x-', @future_id),
        -- The guid MUST be unique, and also, the 'p' param must be the ID of the post. To achieve this, we use our friend ${$new_post_id}.
        `guid` = CONCAT('http://localhost/?p=', @future_id)
      WHERE `ID` = @future_id
    ;


      -- UPDATE WP_POST_META ----------------------------------------------------------------------
      -- ------------------------------------------------------------------------------------------
      -- These two guys were added when I created the post programatically with PHP, but not when I created the post using the normal UI tools of the Worpress admin. So, I won't use them.
      INSERT INTO `wp_postmeta` (
          -- `meta_id`, // This must be ommited so MySQL can assign an ID automatically.
          `post_id`,
          `meta_key`,
          `meta_value`
          ) VALUES (
          @future_id, -- post_id // ID of the recently created post.
          '_thumbnail_id', -- meta_key // Keep this constant. Indicates that this metadata relates the very post to it's thumbnail.
          '5' -- meta_value // Keep this constant. ID of the post of the image, the only one I have uploaded.
      );
      -- INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (@future_id, '_pingme', '1');
      -- INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (@future_id, '_encloseme', '1');


      -- UPDATE WP_TERM_RELATIONSHIPS -------------------------------------------------------------
      -- ------------------------------------------------------------------------------------------

      -- Relate the new post to the term `uncategorized` under the taxonomy `category`.
      -- object_id: $new_post_id, // ID of the new post.
      -- term_taxonomy_id: 1, // ID of the default term, "uncategorized" under the taxonomy "category"
      -- term_order: 0,
      INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (@future_id, 1, 0);

      -- Relate the new post to the term `debugtag` under the taxonomy `post_tag`.
      -- object_id: $new_post_id, // ID of the new post.
      -- term_taxonomy_id: 2, // This is for the term I created, "debugtag" under the taxonomy "post_tag".
      -- term_order: 0,
      INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (@future_id, 2, 0);


      -- UPDATE COUNTER ---------------------------------------------------------------------------
      set v_counter = v_counter + 1;
    end while;
    commit;
  end #
delimiter ;

call load_foo_test_data();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment