Millions of entities when logging

Question

I know there is a lot written on Drupal and tables that have tens of thousands and even millions of records but everyone has a different solution and I want to bring up a specific case.

I am using the log module (https://www.drupal.org/project/log) for Drupal 7 to save detailed custom log messages about a process that I have created for an internal system running on a small ubuntu VM in the Azure cloud. My bundle for the log entity has three custom fields. At 1.5 Million records with 4 custom fields that is 6 Million Records and we are growing every time the process iterates.

As you can imagine the site is slowed down to a near stop when making queries against the log table.

I am thinking of writing a little module to put the entities into a table that is not part of Drupal or exporting the data into a CSV file.

Is there a better way?

Thanks, Josh

Answer 1

First thing I would look at is creating indices for your tables. Look at the queries you are making and see if you can't optimize them with an SQL index. E.g. if you are querying against node id and user id with joins, try creating three indices, one for nid, one for id, one for nid and id.

Having the right indices can make a long running query fly.

Answer 2

Regarding your general approach, you're on the right track- avoid logging to Drupal's watchdog table; avoid writing to even a custom table in Drupal. Write logs to file(s) instead. Additionally, you'll want to determine a retention plan and prune the logs on a regular basis.

As @darvanen commented, there are some modules available for Drupal 7, but they vary in stability/support. Here's a synopsis of what you'll find:

• File Logger (https://www.drupal.org/project/flog): has a stable release, but has not been updated in 5 years; supports logging of any php variable, a label, and dump of php stack at time of log entry; limited to logging to just one log file

• File logger action (https://www.drupal.org/sandbox/martin_klima/2257705): sandbox module that extends above to allow support of writing a customizable message (with tokens) to the log file

• Write Logs In File (https://www.drupal.org/sandbox/vikrantr/2688987): sandbox module that could be used as a starting point for a custom module that supports logging to multiple files

• File Log (https://www.drupal.org/project/filelog): only a dev version / initial port to D7 exists besides the D6 versions, hasn't been updated in 6 years; a watchdog interface implementation which records system events directly into files as an alternative to the Syslog core module

Answer 3

Depending on your exact use case and needs you can use core's syslog module in place of the default database logging.

Answer 4

I was trying to archive entities rather than system log entities from the log module (drupal.org/project/log). I wrote a small module that on cron finds up to 25K log entities that are 30 days old and then saves them to a CSV file and deletes them. http://dropbucket.org/node/9182

I hope this helps someone else.