slow database query

  • kiwibird

    (@kiwibird)


    7 years, 8 months ago

    I found this sql cause the site slow down and mysql database has more then 800 process.

    SELECT DISTINCT
    ID,
    postnumber,
    cntaccess AS sum_count
    FROM
    wp_top_ten
    INNER JOIN wp_posts ON postnumber = ID
    WHERE
    1 = 1
    AND blog_id = 1
    AND (
    wp_posts.post_status = ‘publish’
    OR wp_posts.post_status = ‘inherit’
    )
    AND wp_posts.post_type IN (‘post’)
    ORDER BY
    sum_count DESC
    LIMIT 0,
    50

    • This topic was modified 7 years, 8 months ago by kiwibird.
Viewing 11 replies - 1 through 11 (of 11 total)
  • Plugin Author Ajay

    (@ajay)

    7 years, 8 months ago

    Can you please check if there are the PRIMARY KEYS? You can try to use the button in the Tools page to regenerate these. It sometimes doesn’t work on some sites and then you’ll need to use the below code but change wp_ if you have a custom prefix for the tables.

    ALTER TABLE wp_top_ten DROP PRIMARY KEY;
ALTER TABLE wp_top_ten_daily DROP PRIMARY KEY;
ALTER TABLE wp_top_ten ADD PRIMARY KEY(postnumber, blog_id);
ALTER TABLE wp_top_ten_daily ADD PRIMARY KEY(postnumber, dp_date, blog_id);

    Always backup your database before doing any major changes just to be safe.

    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    I recreated the index and i will observed it for a while.Thanks for you help

    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    Hello, after a few days, I found the query is also very slow.
    below is the query explain.
    explain SELECT DISTINCT ID, postnumber, SUM(cntaccess) as sum_count FROM wp_top_ten_daily INNER JOIN wp_posts ON postnumber=ID WHERE 1=1 AND blog_id = 1 AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘inherit’) AND dp_date >= ‘2018-09-29 0’ AND wp_posts.post_type IN (‘post’) GROUP BY postnumber ORDER BY sum_count DESC LIMIT 0, 50

    +——+————-+——————+——–+———————————————-+———+———+——————————————–+———+———————————————-+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +——+————-+——————+——–+———————————————-+———+———+——————————————–+———+———————————————-+
    | 1 | SIMPLE | wp_top_ten_daily | index | PRIMARY | PRIMARY | 21 | NULL | 1686564 | Using where; Using temporary; Using filesort |
    | 1 | SIMPLE | wp_posts | eq_ref | PRIMARY,bk2,wp_posts_idx_type_status_date_id | PRIMARY | 8 | wordpress.wp_top_ten_daily.postnumber | 1 | Using where |
    +——+————-+——————+——–+———————————————-+———+———+——————————————–+———+———————————————-+

    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    i found there are many same sql at same time. my cache time is hour. i think after the cache has expired, too many sql running at same time cause database slow.

    SELECT DISTINCT ID, postnumber, SUM(cntaccess) as sum_count FROM wp_top_ten_daily INNER JOIN wp_posts ON postnumber=ID WHERE 1=1 AND blog_id = 1 AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘inherit’) AND dp_date >= ‘2018-10-05 0’ AND wp_posts.post_type IN (‘post’) GROUP BY postnumber ORDER BY sum_count DESC LIMIT 0, 50

    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    I think the widgets should always read data from the cache, and every time the cache time is up, refresh the cache，when the refresh cache process is running,the widgets shoud use old cache data so even if it runs very slowly, it won’t cause database slow.

    • This reply was modified 7 years, 8 months ago by kiwibird.
    Plugin Author Ajay

    (@ajay)

    7 years, 8 months ago

    Thanks for the detailed checking. If you’ve enabled the cache from the plugins page, the widget uses the transienst api to cache the posts output and then refreshes it automatically.

    One thing I notice is that you said there are multiple queries which confuses me. Do you have different widgets on the page from Top 10? Or, is it the single one generating this?

    Also, noticed the huge size of the wp_top_ten_daily table. Do you have the maintenance enabled to clean up this table at regular intervals?

    https://ps.w.org/top-10/assets/screenshot-6.png?rev=1770116

    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    I really have multiple widget using different period range.

    But i found the same sql is totally same,if the multiple sql is made from diferrent widget ,the time in the sql should be different .

    AND dp_date >= ‘2018-10-05 0’

    I think that means when the cache expire but not generated complete because of the slow database query.And then the next requests is coming.So many query directly made so many sql,so the database have many process to running these sql,that will cause database high load.After the first query is complete,the cache has been generate. And after the cache generated,widget will read from the cache and database work well.

    And huge size of the wp_top_ten_daily table is because i have nearly 40 million posts,i have no idea to reduce it

    my setting is in the image.

    https://i.postimg.cc/TwQtb6Wr/image.jpg

    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    Because of my poor english.Maybe I can’t express my meaning very well.
    But i found a project may said what i want to said…..

    https://github.com/10up/Async-Transients

    That its said is my thought….
    https://i.postimg.cc/28qmm4y7/image.png

    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    • This reply was modified 7 years, 8 months ago by kiwibird.
    Thread Starter kiwibird

    (@kiwibird)

    7 years, 8 months ago

    After testing for a week.I found this plugin is useful but when runinng on a large site.The sql its made is a big performance issue can cause database down again and again.So i give up this plugin. Thanks for yuor amazing work.

    Plugin Author Ajay

    (@ajay)

    7 years, 7 months ago

    Thanks for the detailed testing. I’ll take a look at the aync transients. There is clearly more to do from the testing perspective for larger sites particularly around the caching.

    Thread Starter kiwibird

    (@kiwibird)

    7 years, 7 months ago

    Thank you for your reply, I am looking forward to this useful plugin getting better and better.

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘slow database query’ is closed to new replies.