چگونه میلیون‌ها رکورد را در MySQL به روز کنیم؟

مقدمه

هنگام به روز رسانی تعداد زیادی رکورد در یک پایگاه داده OLTP، مانند MySQL، باید مراقب قفل کردن رکوردها باشید. اگر آن رکوردها قفل شوند، توسط سایر تراکنش ‌ها در پایگاه داده، قابل دسترسی برای ویرایش (به روز رسانی یا حذف) نخواهند بود. یکی از روش ‌های رایج که برای به ‌روز رسانی تعداد زیادی رکورد استفاده می ‌شود، اجرای چندین به ‌روز رسانی کوچک ‌تر به صورت دسته ‌ای است. یعنی برای به روز رسانی تعداد بالای رکورد، رکوردها را به دسته‌ های کوچک ‌تر تقسیم کرده و به روز رسانی روی دسته‌ های کوچک انجام شود. به این ترتیب، تنها رکوردهایی که در هر نقطه در حال به روز رسانی هستند قفل می‌ شوند.
اگر سوالات زیر ذهن شما را به خود مشغول داشته است این مقاله دقیقا مناسب شماست.

• چگونه میلیون ‌ها رکورد را بدون تأثیر قابل توجهی بر تجربه کاربری به روز رسانی کنیم؟
• چطور دستور به روز رسانی باعث قفل شدن رکوردها می‌ شود؟

توجه داشته باشید که راه حل ارائه شده تنها راه حل نیست. رویکردهای دیگری مانند جابه‌جایی جداول، اجرای به روز رسانی استاندارد بسته به سطوح ایزوله‌ سازی تراکنش‌ ها و غیره وجود دارد. موارد استفاده از این رویکردها به شرایط پروژه بستگی دارد. برای مثال ارائه شده در این مقاله، فرض می ‌کنیم در حال به روز رسانی جدول user هستیم که اگر برای مدت زمان قابل توجهی قفل شود (مثلاً ۱۰ ثانیه)، می ‌تواند به طور قابل توجهی بر تجربه کاربری ما تأثیر بگذارد و ایده‌آل نیست.

نصب

ما از داکر برای اجرای کانتینر MySQL و کتابخانه فِیک پایتون برای تولید داده‌ های فِیک استفاده می ‌کنیم. ابتدا پوشه پروژه را ایجاد می ‌کنیم.

mkdir lock_update && cd lock_update

سپس یک اسکریپت پایتون تولید کننده داده فیک با نام gen_fake.py اجرا می ‌کنیم.

#!/usr/bin/env python3
import argparse
import random
from faker import Faker
def gen_user_data(file_name: str, num_records: int, seed: int = 1) -> None:
    fake = Faker("en_US")
    with open(file_name, "w") as file1:
        for i in range(1, num_records + 1):
            file1.write(
                f"{seed + i},{fake.name()},{random.randint(0,1)},{fake.state()},{fake.country()}\n"
            )
if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Generate some fake data")
    parser.add_argument(
        "--file-name",
        type=str,
        default="fake_user.csv",
        help="file name to store fake data",
    )
    parser.add_argument(
        "--num-records", type=int, default=100, help="Num of records to generate"
    )
    parser.add_argument("--seed", type=int, default=0, help="seed")
    args = parser.parse_args()
    gen_user_data(
        file_name=args.file_name, num_records=args.num_records, seed=args.seed
    )

اسکریپت gen_fake.py داده‌های فیک را با فرمت (id, name, is_active flag, state, country)  در هر ردیف تولید می‌ کند. به تولید کننده داده‌ های فیک حق دسترسی بدهید و ۱۰ میلیون ردیف مطابق شکل زیر ایجاد کنید.

chmod u+rwx gen_fake.py
# run 5 parallel processes, each generating 2million records as shown below
./gen_fake.py --file-name user_data_1.csv --num-records 2000000 --seed 0 & ./gen_fake.py --file-name user_data_2.csv --num-records 2000000 --seed 2000000 & ./gen_fake.py --file-name user_data_3.csv --num-records 2000000 --seed 4000000 & ./gen_fake.py --file-name user_data_4.csv --num-records 2000000 --seed 6000000 & ./gen_fake.py --file-name user_data_5.csv --num-records 2000000 --seed 8000000
mkdir data
cat user_data_1.csv user_data_2.csv user_data_3.csv user_data_4.csv user_data_5.csv >> ./data/user_data_fin.csv # combine data
rm user_data_1* user_data_2* user_data_3* user_data_4* user_data_5*

یک کانتینر MySQL را با داده‌ هایی که در بالا ایجاد کردیم راه اندازی کرده و وارد آن شوید.

docker run -d -p 3306:3306 --name mysql-updates -v "$(pwd)"/data:/var/lib/data -e MYSQL_ROOT_PASSWORD=Password1234 mysql:8.0
docker exec -it mysql-updates bash # open docker shell
mysql -h 127.0.0.1 -P 3306 -u root --local-infile=1 -p # password is Password1234

یک پایگاه داده به نام updates و یک جدولuser  ایجاد کنید. داده‌ های تولید شده را مطابق شکل زیر در جدول وارد کنید.

create database updates;
use updates;
drop table if exists user;
CREATE TABLE user (
    user_id int,
    name varchar(100),
    is_active boolean,
    st varchar(100),
    country varchar(100),
    PRIMARY KEY (user_id)
);
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
-- time taken to load 1 min 22.36 sec
select count(*) from user;
-- should be 10 m

مشکلات به روز رسانی با تعداد رکورد زیاد

فرض کنید برای یک وب سایت تجارت الکترونیک کار می ‌کنیم. یک باگ باعث شده فیلد st(state) را به اشتباه برای کاربرانی با id های بین ۳ میلیون (۳۰۰۰۰۰۰) تا ۸ میلیون (۸۰۰۰۰۰۰) تنظیم گردد. ما باید ۵ میلیون رکورد از مجموع ۱۰ میلیون رکورد را به روز کنیم تا مقدار st با مقدار NY تنظیم شود.
اجازه دهید ببینیم که چگونه تجربه کاربری ممکن است تحت تأثیر یک به روز رسانی بزرگ که روی رکورد آنها قفل نگه داشته شده، تأثیر گذار باشد.
در ترمینال sql خود این دستور به روز رسانی را اجرا کنید.

update user set st = 'NY' where user_id between 3000000 and 8000000;
-- ۲ min 13.46 sec

 هم زمان در یک ترمینال SQL دیگر سعی کنید یک رکورد کاربری که توسط به روز رسانی فوق قفل شده است را به روز رسانی کنید. ما از Where user_id = 3300000 برای انتخاب ردیفی استفاده می‌ کنیم که توسط به روز رسانی بالا قفل شده است.

docker exec -it mysql-updates bash
mysql -h 127.0.0.1 -P 3306 -u root -p # password is Password1234

update updates.user set name = 'Amos Burton' where user_id = 3300000;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 
این خطا به این دلیل رخ می ‌دهد که به روز رسانی بزرگ ما رکورد را با user_id = 3300000 قفل کرده است و این مانع از تغییر داده ‌های قفل شده توسط تراکنش ‌های دیگر می ‌شود.
به روز رسانی دوم به طور پیش‌ فرض ۵۰ ثانیه صبر می‌ کند. با استفاده از عبارت زیر می ‌توانید این تنظیم را بررسی کنید.

show variables like 'innodb_lock_wait_timeout';

 چه اتفاقی خواهد افتاد در صورتی که به جای به‌ روز رسانی ۵ میلیون رکورد در یک دستور به روز رسانی، فقط تعداد کمی از رکوردهای کاربران را در یک زمان به ‌روز رسانی کنیم؟ به این ترتیب، می‌توانیم تعداد رکوردهای قفل‌شده در هر زمان معین را کم نگه داریم و زمان انتظار برای سایر تراکنش‌های به روز رسانی را کاهش دهیم.

به روز رسانی به صورت دسته ‌ای

برای به روز رسانی به صورت دسته ‌ای ابتدا رکوردهایی که باید به روز رسانی شوند را به دسته‌ های کوچک‌تر تقسیم می ‌کنیم. در مثال فوق ۵۰۰۰۰۰ رکورد باید به روز رسانی می ‌شد. قصد داریم این تعداد را به دسته‌ های ۵۰۰۰۰ رکوردی تقسیم کنیم و سپس به روز رسانی را انجام دهیم.
برای تقسیم رکوردها در جدول user، از شناسه id که به صورت خودکار افزایش می‌یابد استفاده می‌کنیم. فرض کنید می‌خواهیم این بار رکورد st را به NJ به روز رسانی کنیم. برای اجرای به روز رسانی ‌ها از یک حلقه while استفاده می ‌کنیم. هر دسته یک شناسه شروع به نام batch_start_id و یک شناسه پایان به نام batch_ end _id خواهد داشت. در میان این دو شناسه به تعداد batch_size رکورد خواهیم داشت کد زیر مراحل توضیح داده شده بالا را به این ترتیب پیاده‌سازی می‌ کند.

USE updates;
DROP PROCEDURE IF EXISTS BatchUpdate;
DELIMITER $$
CREATE PROCEDURE BatchUpdate(
    start_id INT,
    end_id INT,
    batch_size INT)
BEGIN
    DECLARE batch_start_id INT DEFAULT start_id;
    DECLARE batch_end_id INT DEFAULT start_id + batch_size;
    DECLARE loop_counter INT DEFAULT 0;
    WHILE batch_end_id <= end_id DO
        SELECT CONCAT('UPDATING FROM ', batch_start_id, ' TO: ', batch_end_id) as log;
        UPDATE user SET st = 'NJ' WHERE user_id BETWEEN batch_start_id and batch_end_id;
        SET batch_start_id = batch_start_id + batch_size;
        SET batch_end_id = batch_end_id + batch_size;
        SET loop_counter = loop_counter + 1;
    END WHILE;
    SELECT CONCAT('UPDATING FROM ', batch_end_id - batch_size, ' TO: ', end_id) as log, loop_counter;
    UPDATE user SET is_active = true WHERE user_id BETWEEN (batch_end_id - batch_size) and end_id;
END$$
DELIMITER ;
call BatchUpdate(3000000, 8000000, 50000);
-- takes a total of 4 min 43.49 sec

در حالی که BatchUpdate بالا در حال اجرا است، یک به روز رسانی را در یک ترمینال SQL دیگری اجرا کنید.

docker exec -it mysql-updates bash
mysql -h 127.0.0.1 -P 3306 -u root -p # password is Password1234
 

update updates.user set name = 'James Holden' where user_id = 3300000;
exit

نتیجه‌گیری

با استفاده از دستورات زیر می ‌توانید کانتینرهای docker خود را از بین ببرید.

docker stop mysql-updates
docker rm -f mysql-updates

 در این مقاله مشاهده کردید که:

•  چگونه یک به روز رسانی بزرگ می‌ تواند سایر به روز رسانی ‌ها را قفل کند و عملیات ‌ها را حذف کند و باعث ایجاد خطای Lock wait timeout شود.
•  چگونه به روز رسانی در دسته‌ های کوچک‌ تر در هر دسته سریع ‌تر اجرا می ‌شود و بنابراین قفل‌ ها در صورت وجود فقط باید چند ثانیه صبر کنند.

نکته مهمی که در اینجا باید به آن توجه کرد ترتیب به روز رسانی است. هنگام اجرای به روز رسانی ‌های دسته ‌ای و به روز رسانی‌ های تکی در همان ستون رکوردی که در حال ویرایش دسته است، آخرین به روز رسانی اعمال می‌ شود. همچنین می ‌توان این به روز رسانی‌ های دسته‌ ای را به صورت موازی نیز انجام داد، فقط باید مراقب باشید که به بن ‌بست نخورید.
اگر منطق به روز رسانی پیچیده ‌تر از مثال این مقاله بود و جدول مورد نظر دارای index بود، می ‌توانید از دستور SELECT … FOR UPDATE به همراه SKIP LOCKED یا NOWAIT استفاده کنید.
دفعه بعد که یک به روز رسانی بزرگ را روی یک جدول قصد دارید انجام دهید، به روز رسانی‌ها را به صورت تکه ‌ای اجرا کنید تا دیگر تراکنش‌ها تحت تأثیر خطاهای Lock wait timeout قرار نگیرند

منابع

https://www.startdataengineering.com/post/update-mysql-in-batch/