Ten Million-Level Capacity Storage Solution of Student Management System - Examination

Background

This is the student management system for the Education Department. We have a total of 10 million students.

Requirement

Suppose that each subject takes exams twice a year, and each student takes an average of 20 courses per semester. The exams are computer-based, with 20 judgmental questions, 20 multiple-choice questions, and 4 large questions (with answers of less than 200 words). The exam results are kept permanently, and students can see their previous exam results.

Then the storage capacity of the examination result record is:

Students in school : 10 millions * 20(classes)* 2(number of exams)* 1000(answers)* 2(semesters)* 3(first three years only)= 2.4T

Graduated Students: 2.5 million per year, 0.6T of storage.

Assuming that the school's exams are all scheduled within a certain month, the exams are requested during the test, the answers will be submitted, and the intermediate answering process is completed locally in the browser. Let's also assume the test is concentrated in 4 hours in the morning and 4 hours in the afternoon, and the requested exams are concentrated before the start of the test. 1 minute, the submission of answers is concentrated in the 30 minutes before the end of the exam, so the estimates are as follows:

Fetch Exam: 10 million / 20 (number of exams) / 4 (number of exams per day) / 1 min = 50,000/S

Submit Exam: 10 million / 20 (number of exams) / 4 (number of exams per day) / 30 min = 1700/s

Storage Solution

Examination Questions

All the students who take the same examination will answer all the same questions, which is a read-heavy use case. We will use Redis.

Based on https://redis.io/topics/benchmarks, a single Redis Server can handle

• TPS：20000~50000/s

• QPS：50000~100000/s

Which is enough for our use case. But from high availability perspective, we would have another backup server act as a slave server to avoid single-point failure.

Since we are using Redis clusters (2 Redis nodes), we will use Redis Sentinel.

Set quorum as 2.

So we would have 3 server instances:

Storage Data Structure

Each exam will have a globally unique ID. Each exam will have a list of questions.

Each question will have an id, type (select, boolean, or multiple select or text), description, options (list of option id), and answer.

We will have options.

{  "exam_id": [list of questions]}
"Question": {  "id": "globally unique id",  "type": "select/mutiple-select/boolean/text",  "options": [list of option id],  "description": "the description of the question"}
"Option": {  "id": "option id",  "description": "option 1",  "value": "1"}

We can use the Redis HashTable data type, with exam_id map to a string (JSON String of all the questions).

Summary

Redis HashTable data type with exam_id -> String (JSON String)

Examination Answers & Result

There are two use cases: the teachers and the students.

For teachers, who would like to do some analytics on all the students, we recommend using a rational database like MySQL.

For students who can only view their own tests, we recommend using a non-relational database like HBase, for better extensibility.

Storage Data Structure

For MySQL

The table will like this:

For HBase

• key: School ID + Student Unique ID + Examination ID

• Column Family: test

• Column: result (JSON String), score (string)