INSERT hàng triệu bản ghi vào cơ sở dữ liệu mà không làm treo máy

Khi bạn insert 1000 hoặc 2000 bản ghi vào database thì hệ thống ruby on rails vẫn chạy bình thường. Nhưng đã bao giờ bạn đã thử với việc insert vài triệu bản ghi vào database chưa. Tôi chắc chắn với cách insert thông thường thì 1 triệu bản ghi sẽ làm cho máy bạn bị treo do không đủ bộ nhớ để cấp phát cho việc insert cả triệu bản ghi. Chính vì vậy mà hôm nay tôi xin đưa ra 4 phương pháp giúp cải thiện hiệu năng khi bạn chạy câu truy vấn và cách chia ra nhiều luồng khi insert

Tối ưu hóa câu truy vấn

Trước đấy bạn phải tạo trước một bảng UserNodeScore đơn giản chỉ với 3 trường để có thể insert dữ liệu vào

class CreateUserNodeScores < ActiveRecord::Migration
  def change
      create_table :user_node_scores do |t|
      t.integer :score
      t.integer :node_id
      t.integer :user_id

      t.timestamps null: false
    end
  end
end

Phương pháp 1: Sử dụng duy nhất một transactions

Trước khi đến với phương pháp này chúng ta phải hiểu transaction trong cơ sở dữ liệu là gì? Transaction (giao dịch) là một nhóm, có thứ tự, các hoạt động thao tác trên cơ sở dữ liệu, nhưng được xem như một đơn vị thao tác duy nhất.

Đây là cách dễ nhất và cơ bản nhất để insert 1000 bản ghi dữ liệu vào model, trong đó options là các giá trị của một bản ghi mà bạn muốn insert vào

  1000.times {Model.create options}

Nhưng với cách này bạn phải gọi đến 1000 transaction. Tương ứng với việc gọi 1000 câu lệnh INSERT trong SQL

INSERT INTO models (...) VALUES (...);
INSERT INTO models (...) VALUES (...);
INSERT INTO models (...) VALUES (...);
INSERT INTO models (...) VALUES (...);

Tuy nhiên hiệu quả sẽ tốt hơn nhiều nếu bạn insert thông qua ActiveRecord

  ActiveRecord::Base.transaction do
    1000.times {Model.create options}
  end

Việc insert 1000 bản ghi được thực hiện trong 1 transaction duy nhất, 1 câu lệnh INSERT duy nhất trong SQL, cụ thể là:

INSERT INTO models (...) VALUES
  (...),
  (...),
  (...),
  (...),
  ...

Phương pháp 2: Bỏ qua validations trong khi insert vào SQL

Nếu bạn biết rằng dữ liệu của bạn phải kiểm tra hợp lệ (valid) khi tạo khi đó bạn có thể bỏ qua validations, bạn có thể tiết kiệm thời gian nếu truyền thẳng dữ liệu vào SQL. Ví dụ, bạn chạy lệnh create bên dưới thì dữ liệu bạn tạo ra sẽ bị kiểm tra bởi validations

  1000.times {|i| Foo.create(:counter => i)}

Khi mà bạn tạo 1000 ActiveRecord objects, chạy validations, insert vào SQL và đổ nó vào cơ sở dữ liệu. Bạn sẽ nhận ra hiệu năng sẽ được cải thiện nếu nhảy trực tiếp đến generated SQL

1000.times do |i|
  Foo.connection.execute "INSERT INTO foos (counter) values (#{i})"
end

Phương pháp 3: Chèn vào một khối

Nhiều cơ sở dữ liệu hỗ trợ chèn vào một khối dữ liệu (hay có thể gọi là một mảng các giá trị, mỗi giá trị là một chuỗi string "(3.0, '2009-01-23 20:21:13', 2, 1)") bằng lệnh INSERT. Hệ cơ sở dữ liệu có khản năng tối ưu hóa nhất trong quá trình thực hiện này.

inserts = []
TIMES = 5

TIMES.times do
  inserts.push "(3.0, '2009-01-23 20:21:13', 2, 1)"
end
sql = "INSERT INTO user_node_scores (`score`, `updated_at`, `node_id`, `user_id`) VALUES #{inserts.join(", ")}"

Không có khối transaction nào là cần thiết ở đây, vì nó chỉ là một câu lệnh và DB đã bao gồm cả transaction. Tôi tạo ra một mảng các giá trị có tên là inserts và đưa vào database bằng câu lệnh INSERT

Phương pháp 4: Sử dụng method import

columns = [:score, :node_id, :user_id]
values = []
TIMES = 5

TIMES.times do
    values.push [3, 2, 1]
end

UserNodeScore.import columns, values

Nếu các trường trong model của bạn có định nghĩa validations thì để tăng hiệu năng import bạn có thể bỏ qua bước validations bằng cách thêm validate: false vào câu lệnh import. Cụ thể là

UserNodeScore.import columns, values, validate: false

So sánh hiệu năng của các phương pháp

Tôi sử dụng một hàm tính thời gian measure của Benchmarks để kiểm tra từng method ở dưới đây.

require "ar-extensions"

CONN = ActiveRecord::Base.connection
TIMES = 10000

def do_inserts
  TIMES.times {UserNodeScore.create :user_id => 1, :node_id => 2, :score => 3}
end

def raw_sql
  TIMES.times {CONN.execute "INSERT INTO `user_node_scores` (`score`, `updated_at`, `node_id`,  `user_id`) VALUES(3.0, '2009-01-23 20:21:13', 2, 1)"}
end

def mass_insert
  inserts = []
  TIMES.times do
    inserts.push "(3.0, '2009-01-23 20:21:13', 2, 1)"
  end
  sql = "INSERT INTO user_node_scores (`score`, `updated_at`, `node_id`, `user_id`) VALUES #{inserts.join(", ")}"
  CONN.execute sql
end

def activerecord_extensions_mass_insert validate = true
  columns = [:score, :node_id, :user_id]
  values = []
  TIMES.times do
    values.push [3, 2, 1]
  end

  UserNodeScore.import columns, values, {validate: validate}
end

puts "Testing various insert methods for #{TIMES} inserts\n"
puts "ActiveRecord without transaction:"
puts base = Benchmark.measure {do_inserts}

puts "ActiveRecord with transaction:"
puts bench = Benchmark.measure {ActiveRecord::Base.transaction{ do_inserts }}
puts sprintf("%2.2fx faster than base", base.real / bench.real)

puts "Raw SQL without transaction:"
puts bench = Benchmark.measure {raw_sql}
puts sprintf("%2.2fx faster than base", base.real / bench.real)

puts "Raw SQL with transaction:"
puts bench = Benchmark.measure {ActiveRecord::Base.transaction {raw_sql }}
puts sprintf("%2.2fx faster than base", base.real / bench.real)

puts "Single mass insert:"
puts bench = Benchmark.measure {mass_insert}
puts sprintf("%2.2fx faster than base", base.real / bench.real)

puts "ActiveRecord::Extensions mass insert:"
puts bench = Benchmark.measure {activerecord_extensions_mass_insert}
puts sprintf("%2.2fx faster than base", base.real / bench.real)

puts "ActiveRecord::Extensions mass insert without validations:"
puts bench = Benchmark.measure {activerecord_extensions_mass_insert(true)}
puts sprintf("%2.2fx faster than base", base.real / bench.real)

Và kết quả xuất ra là. Theo kết quả này thì single mass insert có hiệu năng cao nhất là nhanh gấp 70.35 lần cách bình thường

Testing various insert methods for 10000 inserts

ActiveRecord with transaction:
  1.29x faster than base
Raw SQL without transaction:
  5.07x faster than base
Raw SQL with transaction:
  11.46x faster than base
Single mass insert:
  70.35x faster than base
ActiveRecord::Extensions mass insert:
  2.01x faster than base
ActiveRecord::Extensions mass insert without validations:
  2.00x faster than base

Chia ra nhiều luồng insert

Cụ thể chúng ta sẽ không lấy tất cả các bản ghi để insert duy nhất trong một lần. Mà chúng ta sẽ chia là nhiều lần insert, mỗi lần chúng ta sẽ insert một lượng nhất định số bản ghi tùy vào hiệu năng xử lý của mỗi thiết bị. Như vậy chức năng insert sẽ chạy ổn định mà không xảy ra nguy cơ treo máy khi cùng một lúc chúng ta insert quá nhiều bản ghi vào máy. Để làm được điều này chúng ta có 2 hàm xử lý trong ruby hỗ trợ, cụ thể là:

find_in_batches(options = {})
Các tùy chọn là:

• batch_size : Xác định kích thước của một batch. Mặc định là 1000
• start: Chỉ định điểm bắt đầu của tất cả các batch
• finish: Chỉ định điểm kết thúc của tất cả các batch

# Mỗi một tiến trình sẽ lấy liên tiếp 2000 bản ghi
Person.find_in_batches(start: 2000, batch_size: 2000) do |group|
  group.each { |person| person.party_all_night! }
end

Ngoài ra chúng ta cũng có thể dùng hàm in_batches trong ruby cũng có tác dụng tương tự

Kết luận

Ngoài những phương pháp kể ở trên rất có thể có những phương pháp mà tôi chưa biết đến, rất mong nhận được sự đóng góp của các bạn để bài bài viết cảu tôi có thể hoàn chỉnh hơn Bài viết này tôi có tham khảo ở các trang web

https://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/

http://weblog.jamisbuck.org/2015/10/10/bulk-inserts-in-activerecord.html