Table of contents of the article:
While providing assistance to one of our clients, www.viktec.net (a technology review site), who was complaining about extremely slow browsing, and finding that everything on the server side was properly configured with an excellent software stack featuring Varnish Cache that responded correctly, and even an additional cache state with CloudFlare , we asked ourselves what could possibly be wrong with the site and we did further 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.
There could be many reasons for this—a poorly configured sitemap, a cache that doesn't update, a robots.txt with the wrong disallow—but in this article I want to talk to you about how important it is to have a fast site and how damaging a slow site is for search engines.
Slowness 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, free WordPress plugin designed to make it easy for Amazon Associates members to find Amazon products to include on their sites. It lets you create direct links in just a few clicks, without leaving 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 discontinued on March 9, 2020 , but if you have it installed, be 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.
Crawling sites with 100, 1000, or 10000 pages can be particularly demanding and, above all, time-consuming, given that there are billions of sites that need to be periodically checked for any new updates or changes to their content.
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 free way is to use your Search Console account in the Crawl > Crawl Stats area, where you will find a graph of Google activity over the last 90 days , showing the average, highs and lows for the number of pages crawled each day.
It is obvious that if there are many pages to crawl and the site is slow, Google Crawlers will not stay there for hours to retrieve the page and index the contents, but will move on to the next site without retrieving your contents and not indexing them, leaving 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 capable system administrators like us who also take care of monitoring your performance and not just selling you the hosting package for 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 by accessing the database with the phpMyAdmin utility it was impossible not to notice that something was not going right: 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 replaced it with the less famous but much better and higher performing WP Money Click, which we highly recommend to anyone working in the affiliation 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.
For guidance on what to choose and how to choose a plugin, we refer you to this article, "Best Practices for Choosing Reliable Plugins," which will help you carefully consider the best solution.
As always, if you have any performance issues, feel free to contact us. You are always welcome.
