-1

I run a wordpress woocommcerce site with millions of rows in tables. After I made partition, there still some slow queries as below:

Question: how can optimize the 3 queries?

Query 1

# Query 1: 0.12 QPS, 2.95x concurrency, ID 0x8DB901765B3761AD at byte 444597956
# This item is included in the report because it matches --limit.
# Scores: V/M = 182.50
# Time range: 2017-04-11T03:47:55 to 2017-04-25T05:04:45
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13  140749
# Exec time     82 3576991s   380us    650s     25s     76s     68s      7s
# Lock time     13     30s    59us     11s   214us   131us    29ms    84us
# Rows sent      0  13.42M       0     100   99.96   97.36    1.79   97.36
# Rows examine  45 570.68G       0  10.93M   4.15M   6.61M   1.15M   3.86M
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     0   4.91G     652  72.07k  36.61k  36.57k   1.10k  36.57k
# Query size    44  62.09M     454     463  462.55  441.81       0  441.81
# String:
# Databases    mydb... (82854/58%)... 1 more
# Hosts        localhost
# Last errno   0 (140715/99%), 1028 (28/0%), 1053 (6/0%)
# Users        mydb... (136700/97%)... 1 more
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms
#  10ms
# 100ms  #
#    1s  ################################################################
#  10s+  ########################
# Tables
#    SHOW TABLE STATUS FROM `mydb2` LIKE 'wp_posts'\G
#    SHOW CREATE TABLE `mydb2`.`wp_posts`\G
# EXPLAIN /*!50100 PARTITIONS*/

SELECT l.ID, 
       post_title, 
       post_content, 
       post_name, 
       post_parent, 
       post_author, 
       post_modified_gmt, 
       post_date, 
       post_date_gmt 
FROM   (SELECT wp_posts.ID
        FROM   wp_posts
        WHERE  wp_posts.post_status = 'publish'
        AND    wp_posts.post_type = 'product'
        AND    wp_posts.post_password = ''
        AND    wp_posts.post_date != '0000-00-00 00:00:00'
        ORDER BY wp_posts.post_modified ASC 
        LIMIT 100 OFFSET 5261200) o 
JOIN   wp_posts l 
ON     l.ID = o.ID\G

Query 2

# Query 2: 0.72 QPS, 0.30x concurrency, ID 0x92F3B1B361FB0E5B at byte 27870878
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.11
# Time range: 2017-04-12T20:11:18 to 2017-04-25T05:04:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         75  764592
# Exec time      7 317112s    51ms     50s   415ms   609ms   214ms   412ms
# Lock time     21     48s       0   143ms    62us   152us   238us    49us
# Rows sent      3 292.10M       0     415  400.59  400.73    8.55  381.65
# Rows examine  42 537.09G       0   1.22M 736.58k   1.09M 312.58k 790.84k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     5  85.08G  89.75k 133.90k 116.68k 112.33k   1.17k 112.33k
# Query size    36  51.77M      71      71      71      71       0      71
# String:
# Databases    mydb... (731943/95%)... 1 more
# Hosts        localhost
# Last errno   0 (764591/99%), 1053 (1/0%)
# Users        mydb... (760189/99%)... 1 more
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  #
# 100ms  ################################################################
#    1s  #
#  10s+  #
# Tables
#    SHOW TABLE STATUS FROM `mydb` LIKE 'wp_options'\G
#    SHOW CREATE TABLE `mydb`.`wp_options`\G
# EXPLAIN /*!50100 PARTITIONS*/

SELECT option_name, option_value 
FROM   wp_options 
WHERE autoload = 'yes'\G

Query 3

# Query 3: 0.00 QPS, 0.14x concurrency, ID 0x885D7EAD0602500C at byte 443806814
# This item is included in the report because it matches --limit.
# Scores: V/M = 185.13
# Time range: 2017-04-11T03:47:28 to 2017-04-25T05:04:42
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    5101
# Exec time      4 174590s      2s    371s     34s    271s     80s      4s
# Lock time      0   651ms    28us   220ms   127us   103us     3ms    57us
# Rows sent      0   4.97k       0       1    1.00    0.99    0.04    0.99
# Rows examine   1  16.86G       0   5.47M   3.38M   5.44M   1.26M   2.62M
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     0 393.64k      73      98   79.02   76.28    0.84   76.28
# Query size     0   1.06M     218     218     218     218       0     218
# String:
# Databases    mydb... (4845/94%)... 1 more
# Hosts        localhost
# Last errno   0 (5093/99%), 1053 (8/0%)
# Users        mydb... (5082/99%), skip-grant... (19/0%)
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #################
# Tables
#    SHOW TABLE STATUS FROM `mydb2` LIKE 'wp_posts'\G
#    SHOW CREATE TABLE `mydb2`.`wp_posts`\G
# EXPLAIN /*!50100 PARTITIONS*/

SELECT COUNT(wp_posts.ID)
FROM   wp_posts
WHERE  wp_posts.post_status = 'publish'
AND    wp_posts.post_type = 'product'
AND    wp_posts.post_password = ''
AND    wp_posts.post_date != '0000-00-00 00:00:00'\G
1
  • 1
    It depends on your definition of "slow" and "fast". Faster disks, more memory. Different configuration settings after adding memory. Your question is pretty broad at the moment, as we don't have any baseline information or any other information regarding mysql configuration, server specs, etc.
    – John K. N.
    Commented Apr 27, 2017 at 8:23

2 Answers 2

2

Query 1:

My condolences, your finger must have fallen off from clicking the Next button to get to OFFSET 5261200 !!

OK, so maybe some search crawler followed that button. (Google has sturdy fingers.)

What to do? First of all don't do Pagination via OFFSET, instead "remember where you left off". This will solve the performance you may or may not have of having to step over thousands of cow pies to get the 'desired' page.

Help! pt-query-digest told you what help to give us, but you have not done it yet:

SHOW TABLE STATUS FROM `mydb` LIKE 'wp_posts'\G
SHOW CREATE TABLE `mydb`.`wp_posts`\G
EXPLAIN SELECT ...

But perhaps the real problem is that WP was not intended to have 5 million postings on one topic! You need a database, not blog software.

Query 2:

You have 1.22M rows in options!?! I repeat, WP is the wrong platform.

Query 3:

I'll stop repeating myself.

0

Never store the enumerated types as strings.

WHERE  wp_posts.post_status = 'publish'
AND    wp_posts.post_type = 'product'

should look like that:

WHERE  wp_posts.post_status = 2
AND    wp_posts.post_type = 7

where '2' and '7' are IDs of corresponding tables post_status and post_type.

Indexes for char/varchar/text fields are significantly slower than numeric ones.

2
  • This is wordpress database structure and we can not change it.
    – chris
    Commented Apr 27, 2017 at 10:05
  • 1
    Not enough slower to explain the problems.
    – Rick James
    Commented Apr 29, 2017 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.