How to Build a Scalable User Online Duration System with Redis and MySQL

Why Track User Online Duration?

In business systems, tracking user online time is a common requirement. Different scenarios have different meanings: IM systems need to know if a user is online and for how long; learning platforms track daily study time; game systems record daily online time for anti‑cheat or reward calculations; SaaS systems use it for activity analysis.

How to design a flexible, extensible, high‑performance “user online duration” system?

Business Scenario Analysis

From a business perspective, online duration can be measured in several dimensions:

Daily statistics: total time per day.

Session statistics: time between each login and logout.

Real‑time status: whether the user is online at this moment.

Cross‑device: user may be logged in on multiple devices.

Additional considerations include network disconnections, abnormal exits, and data storage for later analysis.

Technical Options

Based on experience, we can choose among the following schemes:

1. Heartbeat + Redis Cache

Frontend sends a heartbeat request every 30 seconds.

Redis stores the latest heartbeat timestamp.

Online status is determined by “current time – last heartbeat < timeout”.

Pros: good real‑time performance.

Cons: requires frontend cooperation and fault‑tolerance for disconnections.

2. Login/Logout Logging

Record login and logout timestamps.

Each session generates a data record.

Pros: simple and easy to analyze.

Cons: difficult to handle abnormal exits.

3. Hybrid Approach (Recommended)

Combine login/logout logging with heartbeat supplementation.

Periodically aggregate Redis heartbeat logs into MySQL.

Core Data Structure Design

We use a Redis + MySQL combination.

Redis Structure Example

Key: online:user:{userId}
Value: timestamp (last heartbeat)
Type: String or Hash
TTL: 5 minutes (auto‑expire)

MySQL Table Design

CREATE TABLE user_online_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    session_id VARCHAR(64),
    login_time DATETIME,
    logout_time DATETIME,
    duration_seconds INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Java Core Implementation

1. Heartbeat API

@RestController
@RequestMapping("/api/online")
public class OnlineController {
    private static final String ONLINE_KEY_PREFIX = "online:user:";
    private static final long HEARTBEAT_EXPIRE_SECONDS = 300; // 5 minutes

    @Autowired
    private StringRedisTemplate redisTemplate;

    @PostMapping("/heartbeat")
    public ResponseEntity<String> heartbeat(@RequestParam Long userId) {
        String key = ONLINE_KEY_PREFIX + userId;
        redisTemplate.opsForValue().set(key, String.valueOf(System.currentTimeMillis()),
                                      HEARTBEAT_EXPIRE_SECONDS, TimeUnit.SECONDS);
        return ResponseEntity.ok("heartbeat received");
    }
}

2. Login/Logout Logging

@Service
public class UserSessionService {
    @Autowired
    private UserOnlineLogRepository repository;

    private final Map<Long, UserOnlineLog> sessionMap = new ConcurrentHashMap<>();

    /** Record login time */
    public void login(Long userId, String sessionId) {
        UserOnlineLog log = new UserOnlineLog();
        log.setUserId(userId);
        log.setSessionId(sessionId);
        log.setLoginTime(LocalDateTime.now());
        sessionMap.put(userId, log);
    }

    /** Record logout time and calculate duration */
    public void logout(Long userId) {
        UserOnlineLog log = sessionMap.remove(userId);
        if (log != null) {
            log.setLogoutTime(LocalDateTime.now());
            long seconds = Duration.between(log.getLoginTime(), log.getLogoutTime()).getSeconds();
            log.setDurationSeconds((int) seconds);
            repository.save(log);
        }
    }
}

3. Scheduled Job to Aggregate Online Time

@Component
public class OnlineStatisticsJob {
    @Autowired
    private StringRedisTemplate redisTemplate;
    @Autowired
    private UserDailyOnlineRepository dailyRepository;

    @Scheduled(cron = "0 0 1 * * ?") // daily at 1 am
    public void collectDailyOnlineTime() {
        Set<String> keys = redisTemplate.keys("online:user:*");
        if (keys == null) return;

        for (String key : keys) {
            Long userId = Long.valueOf(key.split(":")[2]);
            // Simulate a 5‑minute online duration
            UserDailyOnline online = new UserDailyOnline();
            online.setUserId(userId);
            online.setDate(LocalDate.now().minusDays(1));
            online.setDurationSeconds(300);
            dailyRepository.save(online);
        }
    }
}

Summary and Optimization Tips

Heartbeat interval: 30–60 seconds balances real‑time accuracy and performance.

Redis TTL: automatically detects offline users.

Abnormal exit handling: supplement with scheduled jobs.

Cross‑device support: Redis keys can store multiple session IDs.

Final Thoughts

Tracking user online duration may seem simple, but it involves many technical points and business decisions. As an experienced Java developer, I recommend starting from business requirements and combining Redis’s high performance with MySQL’s persistence to build a stable, scalable system.