Designing a Scalable IM System: Redis Queues, MySQL Sharding, and Real‑Time Unread Message Handling

Scenario : An instant‑messaging (IM) application with PC and mobile clients must provide unread message counts and full message content when users reconnect after being offline.

Required Java Interfaces

Retrieve a list of unread messages for a user.

Given a sender ID and receiver ID, return the message body.

Message Flow

1. The sender client sends (srcid, destid, msg) to the web server.

2. The server looks up the conversation’s auto‑incrementing mid (based on srcid and destid).

3. The tuple (srcid, destid, mid, msg) is placed into a persistent queue, and the Redis unread‑message list is updated.

Redis Unread‑Message Structure

Key: a device‑specific prefix (e.g., pc- or mobile-) followed by the receiver ID.

Value: a HashMap where each field is a sender ID and the value is the count of unread messages.

When a user logs in, the entire HashMap is fetched and displayed. Entries expire after prolonged inactivity and are removed.

If the Redis entry is missing, the system falls back to the database to reconstruct the unread list.

Redis Operations Example

127.0.0.1:6379> HINCRBY pc-1000 1001 1   # PC side unread ++
127.0.0.1:6379> HINCRBY mobile-1000 1001 1   # Mobile side unread ++
# ... repeat for additional messages ...
127.0.0.1:6379> HDEL mobile-1000 1001   # User reads on mobile, delete entry

After reading, the maximum read mid is asynchronously written back to the database every 30 seconds.

Database Storage Design

Four MySQL shards are used; the shard is selected by (srcid + destid) mod 64. This ensures that messages between two users reside on the same physical database, allowing a single query to retrieve their conversation.

The chat_msg table is partitioned by timestamp:

create table chat_msg(
  id bigint auto_increment,
  srcid bigint not null,
  destid bigint not null,
  mid bigint not null,
  msg TEXT,
  ts timestamp not null default current_timestamp,
  hashvalue tinyint not null,
  primary key (id,ts)
) partition by range (UNIX_TIMESTAMP(ts)) (
  partition p201511 values less than (UNIX_TIMESTAMP('2015-11-01 00:00:00')),
  partition p201512 values less than (UNIX_TIMESTAMP('2015-12-01 00:00:00')),
  partition p201601 values less than (UNIX_TIMESTAMP('2016-01-01 00:00:00'))
);

The read_chat_mid table tracks the maximum read mid per device:

create table read_chat_mid(
  id bigint primary key auto_increment,
  uid bigint not null,
  sessionId varchar(45) not null,
  pcMid bigint not null default 0,
  mobileMid bigint not null default 0,
  hashvalue tinyint not null,
  ts timestamp not null default current_timestamp on update current_timestamp
);

Indexes for fast lookup:

create index inx_1 on chat_msg(ts,srcid,destid,mid);
create unique index inx_2 on read_chat_mid(uid,sessionId);

Sample SQL to fetch recent messages between users 1 and 2, filtering out already‑read messages:

select mid,srcid,destid,msgpb,ts from im_0.chat_msg_3 where id in (
  select t.id from(
    select t1.id from (
      (select id from im_0.chat_msg_3 where srcid=1 and destid=2
        and ts>now()-interval '1' month
        and mid>ifnull((select mobileMid from im_0.read_chat_mid_3 where sessionId='1,2' and uid=1),0) order by ts desc limit 200)
      union all
      (select id from im_0.chat_msg_3 where srcid=2 and destid=1
        and ts>now()-interval '1' month
        and mid>ifnull((select mobileMid from im_0.read_chat_mid_3 where sessionId='1,2' and uid=1),0) order by ts desc limit 200)
    ) as t1 order by id desc limit 200
  ) as t
) order by mid desc;

MySQL Performance Tuning & Results

Key InnoDB settings:

innodb_buffer_pool_size=512m
innodb_flush_log_at_trx_commit=0
sync_binlog=0
innodb_support_xa=0
log_bin=master

On production hardware with RAID‑10, the system achieves:

~17,000 inserts/second per server up to 2 million rows (using LOAD FILE).

~11,000 inserts/second per server up to 8 million rows.

Overall insert capacity >40,000 rows/second across four servers.

Theoretical maximum >640,000 rows/second.

With this capacity, the platform is expected to handle five years of growth without needing additional scaling, illustrating the benefit of involving DBAs early in architecture design.