## Module 1: Size The TiDB Storage Layer Based On The Existing MySQL Database Volume

### Step 1. Log into a Linux machine

+ Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the lab machine, then, connect to a MySQL server and run queries to retrieve data size of the `sbtest` database on it.

### Step 2. Get a data volume overview of the existing MySQL database

+ Connect to MySQL server node. If prompted, answer `yes`.

   ```bash
   ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}
   ```

   ```output
   Last login: Thu Aug  1 11:58:47 2024 from 10.90.4.90
   [rocky@ip-10-90-1-235 ~]$
   ```

+ Connect to MySQL server as database user `root`.

   ```bash
   mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password
   ```

   ```output
   mysql> 
   ```

+ Verify the tables and data in `sbtest` database. The `sbtest` database was already populated by Sysbench.

   ```sql
   SHOW DATABASES;
   ```

   ```output
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | sbtest             |
   | sys                |
   +--------------------+
   5 rows in set (0.01 sec)
   ```

   ```sql
   SHOW TABLES FROM sbtest;
   ```

   ```output
   +------------------+
   | Tables_in_sbtest |
   +------------------+
   | sbtest1          |
   | sbtest2          |
   | sbtest3          |
   | sbtest4          |
   | sbtest5          |
   | sbtest6          |
   +------------------+
   6 rows in set (0.00 sec)
   ```

+ Review the schema and row count of table `sbtest.sbtest1`, it's similar to `sbtest2`, `sbtest3`, `sbtest4`, `sbtest5`, and `sbtest6`. It should take you about 1 minute.

   ```sql
   DESC sbtest.sbtest1;
   ```

   ```output
   +-------+-----------+------+-----+---------+----------------+
   | Field | Type      | Null | Key | Default | Extra          |
   +-------+-----------+------+-----+---------+----------------+
   | id    | int       | NO   | PRI | NULL    | auto_increment |
   | k     | int       | NO   | MUL | 0       |                |
   | c     | char(120) | NO   |     |         |                |
   | pad   | char(60)  | NO   |     |         |                |
   +-------+-----------+------+-----+---------+----------------+
   4 rows in set (0.00 sec)
   ```

   ```sql
   SELECT COUNT(id) FROM sbtest.sbtest1;
   ```

   ```output
   +-----------+
   | COUNT(id) |
   +-----------+
   |  10000000 |
   +-----------+
   1 row in set (32.49 sec)
   ```

+ Execute `ANALYZE` command on database `sbtest`.

   ```sql
   ANALYZE TABLE sbtest.sbtest1, sbtest.sbtest2, sbtest.sbtest3, sbtest.sbtest4, sbtest.sbtest5, sbtest.sbtest6;
   ```

   ```output
   +----------------+---------+----------+----------+
   | Table          | Op      | Msg_type | Msg_text |
   +----------------+---------+----------+----------+
   | sbtest.sbtest1 | analyze | status   | OK       |
   | sbtest.sbtest2 | analyze | status   | OK       |
   | sbtest.sbtest3 | analyze | status   | OK       |
   | sbtest.sbtest4 | analyze | status   | OK       |
   | sbtest.sbtest5 | analyze | status   | OK       |
   | sbtest.sbtest6 | analyze | status   | OK       |
   +----------------+---------+----------+----------+
   6 rows in set (40.95 sec)
   ```

+ Get data size. The data size of `sbtest` database should be around `13.43GB`.

   ```sql
   SELECT CONCAT(ROUND(SUM((data_length + index_length)/1024/1024/1024),2), 'GB') AS data
   FROM information_schema.tables
   WHERE table_schema='sbtest';
   ```

   ```output
   +---------+
   | data    |
   +---------+
   | 13.43GB |
   +---------+
   1 row in set (0.00 sec)
   ```

+ Verify the data size by using OS commands. Retrieve the `datadir` path of `sbtest` database.

   ```sql
   SHOW GLOBAL VARIABLES LIKE "%datadir%";
   ```

   ```output
   +---------------+-----------------+
   | Variable_name | Value           |
   +---------------+-----------------+
   | datadir       | /var/lib/mysql/ |
   +---------------+-----------------+
   1 row in set (0.00 sec)
   ```

+ Exit to OS prompt.

   ```sql
   EXIT
   ```

   ```output
   bye
   ```

+ List the data files of tables in `sbtest` database. The total data size should be around `14GB`.

   ```bash
   sudo ls -lh /var/lib/mysql/sbtest
   ```

   ```output
   total 14G
   -rw-r-----. 1 mysql mysql 2.3G Jul 23 06:22 sbtest1.ibd
   -rw-r-----. 1 mysql mysql 2.3G Jul 23 06:22 sbtest2.ibd
   -rw-r-----. 1 mysql mysql 2.3G Jul 23 06:22 sbtest3.ibd
   -rw-r-----. 1 mysql mysql 2.3G Jul 23 06:22 sbtest4.ibd
   -rw-r-----. 1 mysql mysql 2.3G Jul 23 06:22 sbtest5.ibd
   -rw-r-----. 1 mysql mysql 2.3G Jul 23 06:22 sbtest6.ibd
   ```

### Step 3. Estimate the storage volume for TiDB

+ We use the result of the SQL query as the size of `sbtest` in MySQL database. This is the data volume input of the sizing tool, which is `13.43GB`.

+ Estimate the storage size of `sbtest` database in TiDB by using the Sizing Tool.

+ Calculate data size by using the formula: `TiKV Data Volume = (Existing Data Size) x (Compression Ratio) x (Number of Replicas in TiKV)`. The compression ratio varies depending on different data types and content. The compression ratio cannot be calculated in advance. It can only be determined after the data is actually imported into the TiKV cluster. Remember to consider using a range of 35% to 50% as a starting point.

  + Estimated TiKV Data Volume = `(Existing Data Size) x (Compression Ratio) x (Number of Replicas in TiKV)`
  + Estimated TiKV Data Volume = `(13.43 GB) x (35% ~ 50%) x (3)`
  + Estimated TiKV Data Volume = `14.10 GB ~ 20.15 GB`

+ The data storage required in TiKV is estimated to be between 14.10 GB and 20.15 GB. You will verify it later after the migration steps.