![\"\"](\"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2017\/06\/Screen-Shot-2017-06-15-at-8.38.19-AM.png?resize=640%2C44\")
<\/p>\nToday one of our wordpress sites had very high server load and it was being caused by MySQL<\/p>\n
<\/p>\n
So I went to the mysql console, and looked up the process list:
\n![\"\"](\"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2017\/06\/Screen-Shot-2017-06-15-at-8.51.02-AM.png?resize=640%2C118\")
<\/p>\n
So this guy is appearing a lot Let’s see how it’s behaving with It’s scanning 226k rows to get its search results!<\/p>\n 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!<\/p>\n Now let’s run From scanning 226k it went down to 408!, 3 orders of magnitude drop.<\/p>\n And now the CPU load went below 4%, crisis averted.<\/p>\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[43],"tags":[304,698,1574,1117],"class_list":["post-3686","post","type-post","status-publish","format-standard","hentry","category-linux","tag-cpu","tag-mysql","tag-ops","tag-wordpress"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5Unzf-Xs","jetpack-related-posts":[{"id":762,"url":"https:\/\/www.gubatron.com\/blog\/philip-antoniades-from-sun-mysql-presents-at-nyphporg\/","url_meta":{"origin":3686,"position":0},"title":"Philip Antoniades from Sun-MySQL presents at NYPHP.org","author":"gubatron","date":"March 26, 2008","format":false,"excerpt":"Last night I had the opportunity to attend to my first NYPHP.org meetup. In this ocassion, Philip Antoniades formerly MySQL's Sales Engineering Manager, now Sun-MySQL's Sales Engineering Manager gave a talk related to Sun's recent purchase of MySQL for USD $1,000,000,000. Project Indiana I must say at the beginning of\u2026","rel":"","context":"In "Free Software"","block_context":{"text":"Free Software","link":"https:\/\/www.gubatron.com\/blog\/category\/free-software\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":644,"url":"https:\/\/www.gubatron.com\/blog\/episodio-073-2008-empieza-con-todo-mysql-wordpress-mercadolibre-blueray-y-mas\/","url_meta":{"origin":3686,"position":1},"title":"Episodio 073 – 2008 empieza con todo, MySQL, WordPress, MercadoLibre, BlueRay y mas","author":"gubatron","date":"January 24, 2008","format":false,"excerpt":"Agreganos a tu iTunes | Descarga en MP3 | Suscribete a este podcast | Skypeanos Dale Play aqui mismo Guarda TODOS tus archivos gratis en MyBloop.com! Gracias a la gente de OyeEsto.com por la gorra! Noticias Nos entrevista TuxInfo.com.ar - Revista sobre software libre, y software libre comercial Internet TV\u2026","rel":"","context":"In "Podcast"","block_context":{"text":"Podcast","link":"https:\/\/www.gubatron.com\/blog\/category\/podcast\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":272,"url":"https:\/\/www.gubatron.com\/blog\/project-snowrss\/","url_meta":{"origin":3686,"position":2},"title":"Project SnowRSS","author":"gubatron","date":"March 18, 2006","format":false,"excerpt":"SnowRSS is a GPL RSS Aggregator engine I wrote in python. Currently it's been under use in wedoit4you.com and its stable. It can read RSS and ATOM feeds. It uses the feedparser python module, and the MySQLdb python module to do the job. DOWNLOAD You can only download the code\u2026","rel":"","context":"In "Gubatron"","block_context":{"text":"Gubatron","link":"https:\/\/www.gubatron.com\/blog\/category\/gubatron\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":273,"url":"https:\/\/www.gubatron.com\/blog\/snowrss\/","url_meta":{"origin":3686,"position":3},"title":"SnowRSS","author":"gubatron","date":"March 18, 2006","format":false,"excerpt":"SnowRSS SnowRSS is an RSS Aggregator engine I wrote in python (Licensed under the GPL). Currently it's been under use in wedoit4you.com and its stable. It can read RSS and ATOM feeds. It uses the feedparser python module, and the MySQLdb python module to do the job. DOWNLOAD You can\u2026","rel":"","context":"In "Gubatron"","block_context":{"text":"Gubatron","link":"https:\/\/www.gubatron.com\/blog\/category\/gubatron\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3591,"url":"https:\/\/www.gubatron.com\/blog\/upgrading-your-wordpress-blog-to-php-7-0-on-ubuntu-xenial\/","url_meta":{"origin":3686,"position":4},"title":"Upgrading your wordpress blog to PHP 7.0 on Ubuntu Xenial","author":"gubatron","date":"September 7, 2016","format":false,"excerpt":"If you're about to upgrade your Ubuntu server to 16.04 (Xenial) you might want to take advantage of the new PHP 7.0 which is as fast or more than Facebook's HHVM, or perhaps during the upgrade process a few things may have broken and perhaps that's why you're here Make\u2026","rel":"","context":"In "Linux"","block_context":{"text":"Linux","link":"https:\/\/www.gubatron.com\/blog\/category\/linux\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":516,"url":"https:\/\/www.gubatron.com\/blog\/ejemplo-de-automatizacion-entre-2-maquinas-remotas-con-bash-scripting-y-python\/","url_meta":{"origin":3686,"position":5},"title":"Ejemplo de automatizacion entre 2 maquinas remotas con bash scripting y Python","author":"gubatron","date":"May 10, 2007","format":false,"excerpt":"Para los amigos que se inician en el mundo *nix, ya sea con su nueva Mac, o con una PC corriendo Linux, les recomiendo que aprendan a manejar bien los siguientes lenguajes, y el mundo sera suyo: - bash scripting (aliases, variables, exports, iteraciones, condicionales) - python (para programar logica\u2026","rel":"","context":"In "Code"","block_context":{"text":"Code","link":"https:\/\/www.gubatron.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts\/3686","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/comments?post=3686"}],"version-history":[{"count":3,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts\/3686\/revisions"}],"predecessor-version":[{"id":3711,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts\/3686\/revisions\/3711"}],"wp:attachment":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/media?parent=3686"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/categories?post=3686"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/tags?post=3686"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nSELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';<\/code><\/p>\nexplain<\/code>
\nexplain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';<\/code>![\"\"](\"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2017\/06\/Screen-Shot-2017-06-15-at-8.55.20-AM.png?resize=640%2C85\")
<\/p>\nALTER TABLE wp_options ADD INDEX (`autoload`);<\/code><\/p>\nexplain<\/code> again![\"\"](\"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2017\/06\/Screen-Shot-2017-06-15-at-8.57.49-AM.png?resize=640%2C87\")
<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2017\/06\/Screen-Shot-2017-06-15-at-9.00.31-AM.png?resize=640%2C21\")
<\/p>\n","protected":false},"excerpt":{"rendered":"