Skip to content

4 Comments

  1. Slam
    April 15, 2020 @ 6:09 am

    Hey Dave,

    I have built 2-3 stacks following your rocketstack guide and working perfectly fine. The 4th one I tried was little big wp site with woocommerce and few other plugins. I was working almost ok while DNS wasn’t pointed; as soon as I did that it became extremely slow. When ran ‘top’ command I was surprised to see that mysql cpu usage is always above 250%, unable to figure out what’s going wrong. It’s a 4vcpu 15gb server on ubuntu 18.04lts. Below is my data from top & processlists commands. PLEASE HELP ME.

    top – 10:34:07 up 4 days, 15:00, 1 user, load average: 5.65, 5.66, 5.46
    Tasks: 140 total, 1 running, 91 sleeping, 0 stopped, 0 zombie
    %Cpu0 : 63.6 us, 0.0 sy, 0.0 ni, 36.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
    %Cpu1 : 63.6 us, 0.0 sy, 0.0 ni, 36.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
    %Cpu2 : 63.6 us, 0.0 sy, 0.0 ni, 36.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
    %Cpu3 : 58.3 us, 8.3 sy, 0.0 ni, 33.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
    KiB Mem : 15389976 total, 1872508 free, 2712596 used, 10804872 buff/cache
    KiB Swap: 0 total, 0 free, 0 used. 12250044 avail Mem

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    11559 mysql 20 0 3047232 870016 33736 S 283.3 5.7 5197:46 mysqld
    1 root 20 0 225588 9224 6620 S 0.0 0.1 0:20.75 systemd

    mysql> show processlist;
    +——-+—————–+———–+————–+———+——–+————————+——————————————————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +——-+—————–+———–+————–+———+——–+————————+——————————————————————————————————+
    | 4 | event_scheduler | localhost | NULL | Daemon | 137608 | Waiting on empty queue | NULL |
    | 49810 | root | localhost | NULL | Query | 0 | starting | show processlist |
    | 49812 | my_db | localhost | astra_zotezo | Query | 9 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49813 | my_db | localhost | astra_zotezo | Query | 9 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49817 | my_db | localhost | astra_zotezo | Query | 9 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49818 | my_db | localhost | astra_zotezo | Query | 9 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49819 | my_db | localhost | astra_zotezo | Query | 9 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49820 | my_db | localhost | astra_zotezo | Query | 8 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49822 | my_db | localhost | astra_zotezo | Query | 7 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49823 | my_db | localhost | astra_zotezo | Query | 7 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49824 | my_db | localhost | astra_zotezo | Query | 7 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    | 49825 | my_db | localhost | astra_zotezo | Query | 7 | executing | SELECT post_id FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’ AND meta_value = ‘2020/03/final |
    +——-+—————–+———–+————–+———

    Reply

    • Dave H.
      April 16, 2020 @ 12:37 am

      It sounds like you’re using a different theme and this theme is trying to grab something from wp_postmeta.

      By default, wp_postmeta doesn’t have a usable index on (meta_key, meta_value).

      I highly recommend you get my Scalability Pro plugin and click the button inside there to create indexes which will fix this.

      Reply

      • Slam
        April 16, 2020 @ 4:42 am

        I am using Astra Pro and also have AMP plugin. Are you sure Scalability Pro will solve this? What else it does otherwise?

        Reply

  2. Robert Abela
    September 18, 2020 @ 5:10 am

    Hello Dave,

    Disclaimer: I’m the author of WP Activity Log 🙂

    Interesting write-up. May I ask why you chose to automatically add queries with no indexes and consider them as slow queries?

    In this particular case, Indexes would improve the search and filters. However, for 80%+ of the time the plugin is used to write the logs to the database. Only on occasions it is used by a user to look into the logs, or in your case, to migrate the data to the archiving database.

    So considering that indexes would result in more writing and data space, this is a very good trade off – the plugin is faster for when it is used for what it was built for, to write logs to a database. The user prefers to wait an extra second or two when searching through the logs rather than having an overall slower website.

    Would appreciate your thoughts on the above.

    Reply

Leave a Reply

Your email address will not be published.

Join me on Discord for Q&A and get a 20% discount coupon - Dave Hilditch, WP Intense