mysql AuditDriver - prune experienced slow query issue

[zebastian]

PHP Version

8.2.6

Laravel Version

10.48

Package Version

13.6.8

Description

sometimes really rarely the prune method hangs as the delete takes really really long to finish (almost an hour).

our use case is using laravel-audits as an audit for contact changes, the audits table contains ~200million records of some million contacts.

slow query log excerpt:

SET timestamp=1745938126;
delete `audits` from `audits` left join (select `id` from `audits` where `audits`.`auditable_type` = 'App\\cfab9d1953' and `audits`.`auditable_id` = 3210790 and `audits`.`auditable_id` is not null order by `created_at` desc limit 30) as `audit_threshold` on `audits`.`id` = `audit_threshold`.`id` where `audits`.`auditable_type` = 'App\\cfab9d1953' and `audits`.`auditable_id` = 3210790 and `audits`.`auditable_id` is not null and `audit_threshold`.`id` is null;
# Time: 250429 10:48:50
# User@Host: mydb-live[mydb-live] @  [172.17.12.195]
# Thread_id: 5651235  Schema: mydb-live  QC_hit: No
# Query_time: 2.003077  Lock_time: 0.000026  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 80
--
# User@Host: mydb-live[mydb-live] @  [172.17.12.195]
# Thread_id: 5667915  Schema: mydb-live  QC_hit: No
# Query_time: 3103.636623  Lock_time: 0.000022  Rows_sent: 0  Rows_examined: 146805805
# Rows_affected: 1  Bytes_sent: 11
SET timestamp=1745942497;
delete `audits` from `audits` left join (select `id` from `audits` where `audits`.`auditable_type` = 'App\\cfab9d1953' and `audits`.`auditable_id` = 10254368 and `audits`.`auditable_id` is not null order by `created_at` desc limit 30) as `audit_threshold` on `audits`.`id` = `audit_threshold`.`id` where `audits`.`auditable_type` = 'App\\cfab9d1953' and `audits`.`auditable_id` = 10254368 and `audits`.`auditable_id` is not null and `audit_threshold`.`id` is null;
# Time: 250429 12:02:00
# User@Host: mydb-live[mydb-live] @  [172.17.12.195]
# Thread_id: 5651235  Schema: mydb-live  QC_hit: No
# Query_time: 18.598989  Lock_time: 0.000036  Rows_sent: 0  Rows_examined: 11414403
# Rows_affected: 0  Bytes_sent: 8508

Steps To Reproduce

see also description with many audits and high load this locking behaviour does arise really rarely

Possible Solutions

rewriting the prune logic to not lock with a potential complex query.
This runs first a query to find out the ids of all matching audits and then optionally in a second step deletes the records, if need be by the id reference.
Note: Even though the operation is not atomic anymore compared to the original query this is still logically fine. Even when racing with simultanious pruning of more audit changes, then end result is that all to-be-wiped records are gone.

public function prune(Auditable $model): bool
    {
        if (($threshold = $model->getAuditThreshold()) > 0) {
            $auditClass = get_class($model->audits()->getModel());
            $auditModel = new $auditClass;
            $auditIds = $model->audits()->pluck($auditModel->getKeyName())->toArray();
            if(count($auditIds) <= $threshold) return false;
            $oldIds = array_slice($auditIds, 0, count($auditIds) - $threshold);
            $auditClass::whereIn('id', $oldIds)->delete();
            return true;
        }

        return false;
    }

[angeljqv]

rewriting the prune logic to not lock with a potential complex query.

Feel free to make a PR

[willpower232]

Please beware this part of the code has already been changed relatively recently to deal with various issues (#933 and #948) so not sure how your proposed version compares. I'm not sure how well plucking a single column works and how likely this would be to run out of memory because it was hydrating many models or whatever. Were you able to try out your changes in your system?

[erikn69]

If I remember correctly, the original problem was that in some cases there were too many IDs, which caused the error "too long query",
Also, if many records already existed, it would bring them all just to make the slice, and a memory problem would occur.
maybe it would be better to return to the original functionality, but use chunks to do a "delete in portions", or some approach like that
what do you think?

[zebastian]

@willpower232

Please beware this part of the code has already been changed relatively recently to deal with various issues (#933 and #948) so not sure how your proposed version compares.

compared to the first pull request: i will make sure to base my pr on latest code. Once decided this issue/pr is actually wortwhile and beneficial.
compared to the second pull request: the approach above actually eliminates the need for any direct/complex query handling.

I'm not sure how well plucking a single column works and how likely this would be to run out of memory because it was hydrating many models or whatever. Were you able to try out your changes in your system?

This works actually pretty well. In fact we do this approach almost anywhere where we need to delete in a complex manner (2 step with a complex select for the ids with pluck and to array / second step deletion of the records).
We will deploy the custom pruning today, i will get back with actual metrics then.

@erikn69

If I remember correctly, the original problem was that in some cases there were too many IDs, which caused the error "too long query",

This arises when more than 65K fields are PDO bound to the whereIn(). see also: https://laracasts.com/discuss/channels/laravel/too-many-placeholders
Actually this should not happen, as the pruning is run on any audited model change, so the deletion should only cover exactly one id (maybe consider a handful ids to be on the safe side). Anyway a "whereIntegerInRaw" instead would work in this case instead if needed.

Also, if many records already existed, it would bring them all just to make the slice, and a memory problem would occur.

IMHO not really, see also above the records should in worst case be only a handful, since the pruning always runs against the id/type on change. Note the actual ids once plucked and converted to array do not take much memory.

maybe it would be better to return to the original functionality, but use chunks to do a "delete in portions", or some approach like > that what do you think?

Deletion in chunks does not remove the issue mentioned. (Still a potential complex query inside a delete query). Though an alternative would be selecting into a temporary table and deleting based on ids in that temporary table. This would actually move the implementation stated above into the database.

[erikn69]

Following your instructions with whereIntegerInRaw I think it is better to revert this in v13: 50d6a61
And cherry pick to v14

[iasjennen]

@erikn69 thanks for the commit, looks good

btw: we deployed today to the live box:

• The issue mentioned in this ticket did not happen since
• we did not run into any other issues
• the stats monitoring does not indicate any negative effect (cpu and mem usage on the application and database server did not increase)

[zebastian]

resolved then with #1021
Thanks @erikn69 @willpower232