Database not scaling well

This kind of makes sense for large sites. I'm not sure if we want to remove it completely rahter than having a checkbox : get it from solr at all times?

Updated to keep caching related columns in the underlying database table. Added option to disable caching in the configuration menu.

ok, right so the issue is basically the caching of the extracted text:

-  // Save the extracted, cleaned text to the DB.
-  db_update($indexer_table)->fields(array('hash' => $hash, 'body' => $text))->condition('entity_id', $file->fid)->execute();

An alternative here would be to save the extracted text to a series of text files (e.g. named for the file hash). That might be better than ever putting them in the DB?

Yep, pretty much. I have a project where I don't want to cache the extracted text in the database due to how large the caching table can get.

Does the caching of the extracted text help much? Is it really needed?

Caching the text is especially useful when you need to do a round-trip to the Solr server for each doc to re-extract the text, so I wouldn't want to remove it.

That's fair enough.

The patch I created in #5 makes the database caching configurable with the default set to caching enabled. Does that seem like a reasonable approach?

+++ b/apachesolr_attachments.admin.incundefined
@@ -47,6 +47,12 @@ function apachesolr_attachments_settings($form, &$form_state, $env_id) {
+    '#default_value' => variable_get('apachesolr_attachments_use_cache', FALSE),

Please keep default value to true. We should not change the default behaviour.

+++ b/apachesolr_attachments.admin.incundefined
@@ -47,6 +47,12 @@ function apachesolr_attachments_settings($form, &$form_state, $env_id) {
+    '#description' => t('Whether or not to cache attachment text in the database. If disabled, requests always go to Solr.'),

Add a description why it would be useful to disable cache rather than saying what it does.

will this patch be committed ?
in the latest version there in no such field for selecting whether or not to use cache...
I think if this is going to be committed a more detailed description should be added, so that users understand the pros and cons of using each selection.

I would prefer to have the new default option be to cache to the filesystem, so I don't think the patch is ready to be committed.

I get an SQL error on that dbupdate function in line 101.

Is it safe to remove it? Will it only affect performance of future re-indexing?

In addition to the disable caching option, perhaps offloading the cache column to a standard Drupal cache bin would bring a nice tradeoff.
Since we would be using cache_get and cache_set using the table PK as cid the site administrator could fine tune the cache bin TTL to a balance that better suits each case. Also moving the cache bin to another backend such a MongoDB would allow offload the DB from the body column space.
Would that be possible?

Using Drupal standard cache functions would also fulfill pwolanin's request in #10 easily by way of the File Cache backend module

That seems very reasonable, as long as you are able to choose how you want to keep the file extraction cache. Either on a filesystem or in a cache bin that is then regulated using the drupal cache configs. Anxiously awaiting a patch :)

I'm working in the patch for #12.
Our platform has more than 1M of documents so it's really important this for us.

First attempt for #12

Add flush all cache button

stray code commented out:

+  $cached = cache_get('entity_id:' . $file->fid . ':hash:' . $hash, 'cache_apachesolr_attachments_file_body');
+ // $cached = db_query("SELECT * FROM {{$indexer_table}} WHERE entity_id = :entity_id", array(':entity_id' => $file->fid))->fetchAssoc();
 
-  $cached = db_query("SELECT * FROM {{$indexer_table}} WHERE entity_id = :entity_id", array(':entity_id' => $file->fid))->fetchAssoc();

Also does the other table schema need to change?

• +1 to @pwolanin's drop of obsolete body field.
• +1 to @pwolanin's removal of stray code.
• Cache table description: replace 'apachesolr_attachments' by full module name 'Apache Solr Attachments'.
• Extra chars returned in return $cached . ' cache';
• Change 'The local cache of extracted text has been deleted.' message to 'The local Extracted text cache cleared.' as done in core. Also, it will not be necessarily local.
• Extra linefeeds before and after functions.

Other than these, the patch is right and solves the problem. I think that moving the extracted body cache out of the 'apachesolr_index_entity_file' table will avoid all the deadlocks I've observed there. Managing the cache via Drupal's cache API will enable even moving it out of MySQL (to MongoDB, for instance).

Well formed patch review with recent head update.

Committed a version of this to 7.x-1.x-dev. Patch attached.

Derp. Commented on the wrong issue! Setting back to needs review (see patch from #21).

New patch.

Patch in #21 had a few problems:

• Did not store the calculated hash on the indexer table.
• Left the 'body' field on the indexer table schema.
• apachesolr_attachments_solr_reindex() was still attempting to delete any rows with a NULL 'body' field.
• apachesolr_attachments_get_attachment_text() was returning the $cached_body object instead of just $cached_body->data.

I also tried this out with https://www.drupal.org/project/filecache and it seems to work with this:

$conf['cache_backends'][] = './sites/all/modules/contrib/filecache/filecache.inc';
$conf['cache_class_cache_apachesolr_attachments_file_body'] = 'DrupalFileCache';

Aaand of course I forgot the patch.

Tested the patch in #25 with the FileCache backend, and it appears to solve the issue.

Database size on a 35k document site went from >4GB to less than 200MB.

Ours went from 8.5Gb for 1.3M docs to about 300mb. It's working flwalessly on our dev, test and prod systems.
This allows Drupal's best practice of not backing up cache data.

Commited. Thanks all!

[7.x-1.x c9834f5] Issue #1936662 by escuriola, janusman, posulliv, srjosh, pwolanin, amontero, Nick_vh: Database not scaling well
Author: janusman
4 files changed, 26 insertions(+), 17 deletions(-)

This is related. Also improving the performance #2017705: Performance! Add missed indexes to {apachesolr_index_entities_file} table.

Thanks for the great job to #25 patch.

The size of our database is significantly reduced with the develop version of 1.4-3-dev.

I was aware that the cache_apachesolr_attachments_file_body table is always empty, even after I re-index all contents.

Is there anyone know why?

I just noticed that we are using Memcache module for local caching. I suppose that is why the custom cache table of cache_apachesolr_attachments_file_body is empty in our database.