Ngày 30 tháng 1 năm 2021 · 7 phút Đọc
- Giới thiệu
- Thành lập
- Các vấn đề với một bản cập nhật lớn duy nhất
- Cập nhật theo đợt
- Sự kết luận
- đọc thêm
Giới thiệu
Thành lập
Các vấn đề với một bản cập nhật lớn duy nhất
Cập nhật theo đợt
Sự kết luận
đọc thêm
Thành lập
Các vấn đề với một bản cập nhật lớn duy nhất
- Cập nhật theo đợt
- Sự kết luận
- đọc thêm
Khi cập nhật một số lượng lớn các bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu tâm đến việc khóa hồ sơ. Nếu các hồ sơ đó bị khóa, chúng sẽ không thể chỉnh sửa [cập nhật hoặc xóa] bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một cách tiếp cận phổ biến được sử dụng để cập nhật một số lượng lớn hồ sơ là chạy nhiều bản cập nhật nhỏ hơn theo từng lô. Bằng cách này, chỉ các hồ sơ đang được cập nhật tại bất kỳ điểm nào đều bị khóa.
mkdir lock_update && cd lock_update
Nếu bạn đang tự hỏi
#!/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
]
Làm thế nào để cập nhật hàng triệu hồ sơ mà không ảnh hưởng đáng kể đến trải nghiệm người dùng?
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*
Làm thế nào để bản ghi lệnh khóa cập nhật?
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
Sau đó, bài viết này là dành cho bạn. Lưu ý rằng đây không phải là cách duy nhất. Có các cách tiếp cận khác như hoán đổi bảng, chạy bản cập nhật tiêu chuẩn tùy thuộc vào mức cách ly giao dịch của bạn, v.v ... Việc sử dụng các phương pháp này phụ thuộc vào trường hợp sử dụng của bạn. Đối với trường hợp sử dụng của chúng tôi, hãy để giả sử chúng tôi đang cập nhật bảng người dùng, nếu bị khóa trong một khoảng thời gian đáng kể [giả sử> 10s], có thể ảnh hưởng đáng kể đến trải nghiệm người dùng của chúng tôi và không lý tưởng.
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 million
Các vấn đề với một bản cập nhật lớn duy nhất
Cập nhật theo đợt
Sự kết luận
đọc thêm
update user set st = 'NY' where user_id between 3000000 and 8000000;
-- 2 min 13.46 sec
Khi cập nhật một số lượng lớn các bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu tâm đến việc khóa hồ sơ. Nếu các hồ sơ đó bị khóa, chúng sẽ không thể chỉnh sửa [cập nhật hoặc xóa] bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một cách tiếp cận phổ biến được sử dụng để cập nhật một số lượng lớn hồ sơ là chạy nhiều bản cập nhật nhỏ hơn theo từng lô. Bằng cách này, chỉ các hồ sơ đang được cập nhật tại bất kỳ điểm nào đều bị khóa.
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
Nếu bạn đang tự hỏi
Làm thế nào để cập nhật hàng triệu hồ sơ mà không ảnh hưởng đáng kể đến trải nghiệm người dùng?
show variables like 'innodb_lock_wait_timeout';
Làm thế nào để bản ghi lệnh khóa cập nhật?
Cập nhật theo đợt
Sự kết luận
đọc thêm
Khi cập nhật một số lượng lớn các bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu tâm đến việc khóa hồ sơ. Nếu các hồ sơ đó bị khóa, chúng sẽ không thể chỉnh sửa [cập nhật hoặc xóa] bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một cách tiếp cận phổ biến được sử dụng để cập nhật một số lượng lớn hồ sơ là chạy nhiều bản cập nhật nhỏ hơn theo từng lô. Bằng cách này, chỉ các hồ sơ đang được cập nhật tại bất kỳ điểm nào đều bị khóa.
Nếu bạn đang tự hỏi
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 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
]
1Bản cập nhật này thực hiện nhanh chóng. Mặc dù bản cập nhật lớn mất nhiều thời gian hơn vì chúng tôi đang thực hiện theo các đợt, chúng tôi có thể thấy cách tiếp cận này cho phép các giao dịch khác chỉnh sửa dữ liệu.
Sự kết luận
Bạn có thể phá bỏ các thùng chứa Docker của bạn bằng các lệnh sau.
#!/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
]
2Tóm lại, chúng tôi đã thấy
- Làm thế nào một bản cập nhật lớn có thể khóa các bản cập nhật khác và xóa các hoạt động và gây ra lỗi
0.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 million
- Làm thế nào việc cập nhật theo các lô nhỏ hơn thực hiện nhanh hơn mỗi đợt và do đó khóa sẽ chỉ phải đợi một vài giây, nếu có.
Một điểm quan trọng cần lưu ý ở đây là thứ tự cập nhật. Khi chạy các bản cập nhật hàng loạt và cập nhật đơn trên cùng một cột của bản ghi, bản ghi mới nhất sẽ được áp dụng. Bạn cũng có thể thực hiện các bản cập nhật được ghép đôi này song song, nhưng bạn phải cẩn thận không gặp bất kỳ bế tắc nào.
Nếu logic cập nhật phức tạp hơn và bảng của bạn có chỉ mục bạn, có thể sử dụng
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 million
1 với các tính năng 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 million
2 hoặc 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 million
3 như được hiển thị ở đây.Lần tới khi bạn thực hiện một bản cập nhật lớn trên bảng, hãy xem xét chạy các bản cập nhật trong các phần để giữ cho các giao dịch khác không bị ảnh hưởng bởi các lỗi thời gian chờ khóa.
đọc thêm
- Lập chỉ mục ảnh hưởng đến cập nhật
References:
- Mức cách cô lập giao dịch
- Thủ tục lưu trữ
- Cập nhật với sự bỏ qua
- Khóa mysql