Search API indexing stops with "pending server tasks could not be executed" or "out of memory" errors due to large search_api_task table

Issue

If you have problems indexing with Search API and see these symptoms:

• Seeing PHP "Out Of Memory" errors when running Search API indexing operations.

• ... or seeing errors like these:

  copy

  Could not index items on index '[index name]' because pending server tasks could not be executed.
  
  .. or ..
  
  SearchApiException: Could not index items since important pending server tasks could not be performed.

• And having a large amount of items in the search_api_task MySQL table, for example:

  copy

  # EXAMPLE: Get number of rows in search_api_task table.
  MYSITE@web-12345:/var/www/html/MYSITE/docroot$ echo "select count(1) FROM search_api_task" |drush --uri=MYSITE.COM sql-cli
  count(1)
  5361339

Resolution

• Ensure Search API, Search API Solr and related modules are updated.
• Ensure that the Search API indexes' configuration have the "index immediately" option DISABLED.
• Make a database backup.
• Truncate the search_api_task table with a MySQL query. Here is an example:
  copy

  # Run this within your Acquia server. EDIT THE --uri=XXX option to match your site.
  # WARNING: THIS is a destructive operation. You should make a DB backup first!
  echo TRUNCATE search_api_task |drush --uri=MYSITE.COM sql-cli

• After these steps, you will need to tell Search API to reindex your site.

Cause

One possible cause is that the Search API "tasks" table (which queues Search API operations for later processing) is too large.

The Search API "tasks" table balloons when...

• Using older Search API versions. The task system has been improved in newer releases and should be used.
• Having "index immediately" enabled in the index configuration could trigger immediate indexing of many items even with simple site changes, which could overload your server resources.
  • Acquia Support recommends disabling this option, and instead letting indexing happen automatically via cron runs and/or via manual indexing via Drush commands for various reasons (processing will then happen outside an HTTP request and often has more PHP memory available).