3

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

2
  • 1
    There's a few modules that have been started to write logs to files instead of db, you can probably start with one of those.
    – Darvanen
    Dec 8, 2017 at 2:32
  • Are you familiar with the Message module ? It depends on what kind of data you want to track / perform logging about. But I imagine that if you'd use an appropriate set of "Message types", you wouldn't need any extra fields to create relevant "Message entities". Possibly something like CSV-type record layouts could work. Refer to my answer here for way more details. PS: Should I convert this comment to an actual anwer (with more details)? Jan 26, 2018 at 20:58

4 Answers 4

0

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.

1
  • Is there documentation on adding indices for entities created by another module? I have the log table form the log module and four tables for fields. All queries against this table are done using the Entity API.
    – Josh
    Dec 19, 2017 at 3:24
0

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

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.