Table of contents of the article:
Providing help to one of our clients www.viktec.net (a technology review site) who complained of extreme slow navigation and found that on the server side everything was properly configured with a software stack of excellence with Varnish cache which responded correctly, and even an additional state of Cache with CloudFlare, we asked ourselves what could not go on the site and we deepened the research by analyzing the application and the database.
It can happen not too infrequently that a site that was giving good results and excellent satisfaction in terms of positioning on search engines that could lose the positioning of many words indexed and shortly thereafter see it literally disappear from search engines.
The reasons can be many, a not well configured sitemap, a cache that does not update, a robots.txt with an incorrect disallow, but in this article I want to talk to you about how important it is to have a fast site and how harmful a slow site is for search engines.
Slow this time due to an official Amazon WordPress plugin: Amazon Associates Link Builder.
The Amazon Associates Link Builder WordPress plugin allows you to integrate Amazon products into your site in a simple and attractive way, with just a few clicks.
Amazon Associates Link Builder is the official plugin for WordPress, free and designed to make it easy for members of the Amazon Affiliate Program to search for Amazon products to place on their site. It gives the possibility to create direct links, with a few clicks and without having to exit the WordPress editor.
Using the Amazon Associates Link Builder allows you to avoid having to manually integrate important product information, such as product names, descriptions and previews.
The Link Builder plugin generates shortcodes that you can use to select Amazon product data in different display formats, then display it on your website. Below we will look at the features of the Link Builder and some examples of how affiliates use it to improve their sites.
Fortunately this plugin was decommissioned on March 9, 2020, but if you have installed it, be really VERY careful.
The problem
The main problem comes from the fact that this plugin adds entries in the WordPress database in the wp_options table, entries for several million rows.
The fact of not using a separate table already speaks volumes about how improvised this plugin can be and the bad job that the Amazon engineers have done (yes, it's their official plugin) but the fact that it can fill the table by going to dramatically increasing the query time of the DB is really more than very serious.
Slow queries on the database equates to slow pages that are harmful both for the visitor who gets impatient, and for the Google crawlers who, having billions of sites to crawl, also get impatient and move on to the next.
What is crawling budget?
The commonly given definition of the crawl budget is the number of pages that Google crawls in a given period of time.
Put simply, Google's crawler bot takes care of crawling web pages, adding them to the index later. During the operation, the software searches for other links to follow independently or by following the URLs of a sitemap.xml to always analyze new contents.
To be identified during login, bots typically use a user agent, which is an acknowledgment string. By consulting the Apache log file on our server, we can see visits from Googlebot, but there are also Microsoft's BingBot, Baiduspider for the Chinese market and YandexBot for Russia.
Carry out the site crawl task of 100, 1000 or 10000 pages can be particularly onerous and above all expensive in terms of hardware resources, but also of time taken, given that there are billions of sites that must be periodically checked to monitor any new updates or changes to the contents.
Hence the need to limit the time and resources dedicated to each site based on a series of factors that reward some sites with a more frequent scan, while others will be checked only rarely.
To get a concrete idea of the situation of your site you must necessarily rely on a Googlebot access monitoring system and there are various solutions.
The easiest and most free way is to use your Search Console account in the Crawl> Crawl Stats area, where you will find a graph with Google's activity in the past 90 days, showing the average, highs and lows for the number of pages scanned each day.
It is obvious that if the pages are many to crawl and the site is slow, the Google Crawlers will not stay there for hours to retrieve the page and index the contents, but will go over to the next site not recovering your contents and not indexing them. letting you literally disappear from Google.
The speech wants to examine Google, but it is valid for practically all search engines.
Problem analysis
The first step to take when a site becomes extremely slow to death is to profile the application to understand bottlenecks.
This can be done in several ways, using third party applications like New Relic, using profiling plugins like Query Monitor, or going to the MySQL DB shell and doing a SHOW FULL PROCESSLIST; to see which queries pass most frequently and the timing used by each of them.
A healthy way if you have root access to the system or have smart systems engineers who like us also follow you on the performance and not just sell you the hosting package at 50 euros a year is to use Percona Toolkit as we wrote in this article.
However, in this case it was even much easier, since accessing the database with the utility phpMyAdmin it was impossible not to notice that something was wrong: the wp_options table weighed 20GB.
An inhuman, senseless value that exceeds a few tens of megabytes (a maximum of one hundred) proves and proves beyond all reasonable doubt that some plugins are writing nefarious things on the table.
In fact it was full of entries with an aalb_update_table_batch identifier
Someone had already noticed the problem as we can read in this plugin support thread: https://wordpress.org/support/topic/the-wordpress-db-has-increased-by-600mb-2/
The solution as recommended also in the thread listed above was to delete all those entries using the following SQL query:
DELETE FROM wp_options WHERE option_name LIKE ('% aalb_update_table_batch%')
The table started to empty up to a few tens of megabytes and the site started opening pages in less than two seconds versus over 10 seconds before cleaning.
Replacing the Amazon Associates Link Builder plugin with WP Money Click
Once the customer understood the problem that the official Amazon plugin was literally killing his site, he proceeded to replace it with the less famous but much better and more performing WP Money Clicks which we highly recommend to all those who work in the affiliations and product comparison field.
Furthermore, the replacement of the plugin allowed to convert all the shortcodes present on the site (we are talking about hundreds of articles) to the WP Money Click format, therefore not having to take a lot of work or time to migrate to the new plugin.
Results and benefits obtained
The results obtained are those of having returned to a fast and snappy site with many keyworks and contents finally indexed by Google as we can see from the following screenshot obtained through SeoZoom.
As you can see from the end of March to today (beginning of April) from about 1000 indexed pages we have reached over 20000.
Conclusions
Although in this specific case we wanted to deal with this plugin that was giving big problems, this does not mean that it is the only plugin to generate harmful situations and consequences as described above.
There are simply plugins written more or less badly that tend to slow down the site in an embarrassing way, obtaining the same harmful effects that you have read above.
So be careful every time you install a plugin, and pay attention to the growth of the database if you notice an excessive slowdown.
About what to choose and with what criteria to choose a plugin, we refer you to this article Best Practice for Choosing Plugins certain that it will help you to carefully consider the best solution.
As always, if you have any performance issues, feel free to contact us. You are always welcome.
