Updating access timestamps without wrecking the database

PHP Performance

When you want to know the last time some user was seen or some resource was accessed/viewed, then usually you reach to a timestamp column and update this to the current time whenever it’s seen or accessed.

You may have written this kind of SQL statement (or the equivalent) in your ORM yourself a few times:

$this->connection->executeUpdate(
    'UPDATE user SET last_seen = ? WHERE id = ?',
    [new DateTime('now'), $user->getId()],
    [Types::DATETIME, Types::INTEGER],
);

It looks innocent, but even on reasonably small tables, this can cause slow UPDATE statements regularly.

In our Tideways code, this was not an issue for the longest time on the user table with its last_seen column. But a while ago we started seeing the query getting slower in Tideways own Slow SQL Query Log:

The query took 980 ms in this instance, and there were 18,000 previous occurrences where it also took at least 100 ms or more in the past two weeks.

Unacceptable, given this is affecting real customers’ experience. Not critical, because it did not happen to hundreds of thousands of requests where the update was fast. But the chance it impacted a user during a longer session was almost 100%.

I want to show two solution patterns that you can use to work around this problem.

Solution 1: Only update the timestamp every few minutes

Do you really need to know the exact time of the last access, or is a bucket of time enough? For our case, we only need to know the last seen time in a five-minute window. So we can update the code to only UPDATE when the User::$lastSeen time is older than five minutes:

$fiveMinutesAgo = new DateTime('-5 minute');
if ($user->getLastSeen() >= $fiveMinutesAgo) {
    return;
}
$this->connection->executeUpdate(
    'UPDATE user SET last_seen = ? WHERE id = ? AND last_seen < ?',
    [new DateTime('now'), $user->getId(), $fiveMinutesAgo],
    [Types::DATETIME, Types::INTEGER],
);

This is what we did for the User::$lastSeen timestamp and the slow queries were gone.

You might wonder why we have plain SQL for this in our code when User is clearly a Doctrine ORM entity.

The reason is that we made a conscious decision in the UserListener to not call EntityManager::flush to save the timestamp changes for two reasons:

  1. It’s not clear how many other entities might already be loaded at the time, causing changesets to be computed when we know only the User::$lastSeen is to be saved.
  2. It’s not clear if there are other changes made to the user or other entities at that point, and flush would store them.

Using an UPDATE circumvents these two problems in a way that is also future-proof, when someone adds code loading new/more entities before this code runs. Then they don’t need to know about this code here.

Solution 2: Write time to Redis and synchronize asynchronously

If you need the exact time of the last access, but maybe you don’t need to know this information in real time, then you can temporarily store the last access in Redis in a sorted set. And synchronize these times to the database asynchronously every few minutes.

This is how we do it in Tideways for a different data type with the same pattern: the transaction (page type). Every minute, Tideways processes thousands of records for thousands of transactions. We want to store the last recorded time in the database. But updating this immediately in the worker failed for our scale years ago. Thousands of update statements on thousands of rows per second is not a pattern that scales well.

Since then we store the last recorded time in Redis as a sorted set with code similar to this:

$this->redis->zadd(
    "transaction_last_recorded-" . $project->getId(),
    $now->getTimestamp(),
    $transaction->getId(),
);

Then we have a service that synchronizes the times per project every 15 minutes using a cronjob:

public function synchronize(int $projectId)
{
    $key = "transaction_last_recorded-" . $projectId;
    $timestamps = $this->redis->zrange($key, 0, -1, ['WITHSCORES' => 1]);
    
    $maxTimestamp = 0;
    foreach ($timestamps as $id => $timestamp) {
         $maxTimestamp = max($maxTimestamp, $timestamp);
         $this->connection->executeUpdate(
				    'UPDATE transaction SET last_recorded_at = ? WHERE id = ?',
				    [date('Y-m-d H:i:s', $timestamp), $id],
				    [Types::DATETIME, Types::INTEGER],
				);
    }
    
    // do the query again, during this time new transaction could have a new
    // score or ones processed here could have been updated their score.
    $ids = $this->redis->zrange($key, 0, $maxTimestamp);

    // only delete the ones that still have a time up to the one we have processed.
    $this->redis->zrem($key, ...$ids);
}ƒ

And in the user interface, when a user navigates to a page that requires the up-to-date times, then we synchronize immediately.

This cleans up the sorted set by removing the processed timestamp IDs. But this implementation can be improved even further.

Because it updates every transaction individually, it might still be slow and become a bottleneck fast. You can improve the code more if you aren’t concerned with seconds by bucketing them into minutes and doing a mass update for several transactions that each get the same minute. Careful though: Depending on the size of entries, you might need to implement chunking these updates in batches of 100 rows or so.

If you have some actions in your application that need the times up-to-date, then you can call the synchronize method before running these actions. Or you can implement some code that fetches times from both the database and Redis.

We have generalized this pattern in the Tideways codebase and wrapped it in a service LastSeenStore that performs this work across multiple entities, tables, and columns.

Conclusion

I hope this blog post inspires you to think about workarounds for slow SQL queries and database load: It’s always good to challenge the assumptions; do we really need to store and update this information immediately and all the time?

Often, you can make small adjustments to the precision of a value and solve a bottleneck in a clever way.

Do you have stories of similar solutions to improve database performance? Let me know; maybe it’s worthwhile to turn it into another blog post.

About the author

  • Benjamin

Benjamin
Founder & CEO

I am the founder and CEO of Tideways. I started the company over 10 years ago with the mission to move the PHP ecosystem forward, starting with performance. As managing director, I work across product, strategy, and the day-to-day of building a developer-focused SaaS business.

I’m a core contributor to the Doctrine open-source project and a founding board member of the PHP Foundation, which reflects my long-standing commitment to the PHP ecosystem. I particularly enjoy working at the intersection of application performance, developer experience, and the open-source community that makes PHP what it is. Outside of work, I enjoy board games, hiking, and coffee.