Overview

Determine how long it will take to migrate the database by checking the size of each table.

SELECT
    table_name AS `Table`,
    ROUND(data_length / 1024 / 1024, 2) AS `Data Size (MB)`,
    ROUND(index_length / 1024 / 1024, 2) AS `Index Size (MB)`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Total Size (MB)`
FROM
    information_schema.tables
WHERE
    table_schema = 'jod-v2-prod'
ORDER BY
    `Total Size (MB)` DESC;

Database Size

SELECT
    table_name AS `Table`,
    ROUND(data_length / 1024 / 1024, 2) AS `Data Size (MB)`,
    ROUND(index_length / 1024 / 1024, 2) AS `Index Size (MB)`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Total Size (MB)`

Database	Size (MB)
information_schema	0.20
jod-v1-prod	9440.69
jod-v2-demo	14.78
jod-v2-prod	1209306.53
mysql	13.19
performance_schema	0.00
sys	0.02
TOTAL	1218775.41

Table Sizes

Top 4 tables by size:

Table	Data Size (MB)	Index Size (MB)	Total Size (MB)
notifications_20240325	786200.00	139757.86	925957.86
notifications	242404.00	27080.95	269484.95
audits	9147.00	2815.91	11962.91
jod_jobs	548.98	71.56	620.55

Data in the tables (except jod_jobs) are not used by the system or reporting.

• We can migrate only the schema (without row data)

Total Migration Size 1,209,306.53 - 925,957.86 - 269,484.95 - 11,962.91 = 1,900.81 MB (approx. 1.9 GB) to migrate.

Small enough to use mysqldump in an EC2 instance with access to the rds instances:

• rds-jod-prod
• jodgig-qa
• jodgig

The dump and load into QA took about 15 minutes.

rds-jod.prod.jod-v2-prod tables by size

As of 31 Oct 2024, 12pm GMT +7:

Table	Data Size (MB)	Index Size (MB)	Total Size (MB)
notifications_20240325	786200.00	139757.86	925957.86
notifications	242404.00	27080.95	269484.95
audits	9147.00	2815.91	11962.91
jod_jobs	548.98	71.56	620.55
email_sms_notifications	150.67	117.19	267.86
failed_jobs	242.58	1.52	244.09
slot_user	85.69	59.06	144.75
job_user	83.66	53.95	137.61
oauth_access_tokens	55.50	37.80	93.30
users	68.63	22.81	91.44
payments	53.59	29.58	83.17
billings	34.56	36.91	71.47
slots	29.55	9.52	39.06
qr_code_slot_users	12.52	17.58	30.09
jod_job_certificates	13.55	4.52	18.06
user_badges	7.52	5.52	13.03
applicant_experiences	6.52	3.98	10.50
wishlists	3.52	4.80	8.31
company_badge_assignments	1.52	2.89	4.41
partner_events	2.52	1.50	4.02
slot_user_badges	1.52	1.94	3.45
job_templates	2.52	0.13	2.64
friends	1.52	0.42	1.94
company_role_permissions	1.52	0.31	1.83
credits	1.52	0.27	1.78
user_settings	1.52	0.23	1.75
files	1.52	0.00	1.52
eber_points_logs	0.34	0.38	0.72
locations	0.52	0.17	0.69
user_certificates	0.50	0.14	0.64
job_batches	0.27	0.00	0.27
account_suspensions	0.19	0.00	0.19
slot_user_transaction_logs	0.09	0.06	0.16
track_incidents	0.11	0.05	0.16
role_permissions	0.08	0.05	0.13
ct_reposts	0.11	0.00	0.11
user_unsuspension_request	0.09	0.00	0.09
companies	0.08	0.00	0.08
notification_templates	0.08	0.00	0.08
payment_adjustment_approvals	0.05	0.03	0.08
permissions	0.06	0.00	0.06
migrations	0.05	0.00	0.05
user_company	0.02	0.03	0.05
oauth_clients	0.02	0.02	0.03
address	0.02	0.02	0.03
role_features	0.02	0.02	0.03
configurations	0.02	0.02	0.03
oauth_auth_codes	0.02	0.02	0.03
jobs	0.02	0.02	0.03
versions	0.02	0.02	0.03
features	0.02	0.02	0.03
feature_permissions	0.02	0.02	0.03
user_roles	0.02	0.02	0.03
password_resets	0.02	0.02	0.03
banks	0.02	0.02	0.03
menus	0.02	0.02	0.03
menu_roles	0.02	0.02	0.03
settings	0.02	0.02	0.03
oauth_refresh_tokens	0.02	0.02	0.03
job_template_certificates	0.02	0.02	0.03
reason_templates	0.02	0.00	0.02
feature_modules	0.02	0.00	0.02
certificates	0.02	0.00	0.02
job_types	0.02	0.00	0.02
educational_institutes	0.02	0.00	0.02
badges	0.02	0.00	0.02
roles	0.02	0.00	0.02
oauth_personal_access_clients	0.02	0.00	0.02

Stored procedures, events and TRIGGERS

Database contains stored procedures, events and triggers.

Determine if we need to migrate them by checking if there is any data in them.

We only had a COUNT_MIGRATE stored procedure, so we can ignore tha

• the rest returned 0 rows

Commands

Stored procedures:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, CREATED, LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'jod-v2-prod';

Columns with blob, varbinary, binary data types:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'jod-v2-prod'
AND DATA_TYPE IN ('blob', 'varbinary', 'binary');

Triggers:

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT, ACTION_TIMING, CREATED
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'jod-v2-prod';

Events:

SELECT EVENT_SCHEMA, EVENT_NAME, EVENT_DEFINITION, STATUS, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'your_database_name';