in Linux

Fix high CPU usage by WordPress and MySQL

Today one of our wordpress sites had very high server load and it was being caused by MySQL

So I went to the mysql console, and looked up the process list:

So this guy is appearing a lot
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Let’s see how it’s behaving with explain
explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

It’s scanning 226k rows to get its search results!

Probably some moronic plugin is doing this and wordpress does not add an index on that table. The solution is simple, let’s add an index!

ALTER TABLE wp_options ADD INDEX (`autoload`);

Now let’s run explain again

From scanning 226k it went down to 408!, 3 orders of magnitude drop.

And now the CPU load went below 4%, crisis averted.

Write a Comment

Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Webmentions

  • Overload WordPress Disebabkan Query Autoload – HostingCeria

    […] Saat ini jika query yang sama hanya dijalankan berdasarkan 408 record yang berarti terdapat efisiensi query sebesar 500%!CPU Load dari server/hosting anda seharusnya akan turun jika sebelumnya masalah autoload ini menjadi kendala.— Artikel ini disadur dari gubatron.com — […]