pengembangan-web-mp.com

Bagaimana cara memperbarui 10 juta + baris dalam tabel tunggal MySQL secepat mungkin?

Menggunakan MySQL 5.6 dengan mesin penyimpanan InnoDB untuk sebagian besar tabel. Ukuran buffer pool InnoDB adalah 15 GB dan indeks Innodb DB + sekitar 10 GB. Server memiliki 32GB RAM dan menjalankan Cent OS 7 x64.

Saya punya satu meja besar yang berisi sekitar 10 juta + catatan.

Saya mendapatkan file dump yang diperbarui dari server jarak jauh setiap 24 jam. File ini dalam format csv. Saya tidak memiliki kendali atas format itu. File tersebut ~ 750 MB. Saya mencoba memasukkan data ke baris tabel MyISAM demi baris dan butuh 35 menit.

Saya hanya perlu mengambil 3 nilai per baris dari 10-12 dari file dan memperbaruinya dalam database.

Apa cara terbaik untuk mencapai sesuatu seperti ini?

Saya perlu melakukan ini setiap hari.

Saat ini Flow seperti ini:

  1. mysqli_begin_transaction
  2. Baca file Dump baris demi baris
  3. Perbarui setiap catatan Baris demi Baris.
  4. mysqli_commit

Operasi di atas membutuhkan waktu -40 menit untuk menyelesaikan dan saat melakukan ini, ada pembaruan lain yang terjadi yang memberi saya

Batas waktu tunggu tunggu melebihi; coba mulai kembali transaksi

Perbarui 1

memuat data dalam tabel baru menggunakan LOAD DATA LOCAL INFILE. Di MyISAM butuh 38.93 sec saat berada di InnoDB, butuh 7 menit 5,21 detik. Lalu saya lakukan:

UPDATE table1 t1, table2 t2
SET 
t1.field1 = t2.field1,
t1.field2 = t2.field2,
t1.field3 = t2.field3
WHERE t1.field10 = t2.field10

Query OK, 434914 rows affected (22 hours 14 min 47.55 sec)

Perbarui 2

pembaruan yang sama dengan permintaan bergabung

UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4

(14 hours 56 min 46.85 sec)

Klarifikasi dari pertanyaan dalam komentar:

  • Sekitar 6% dari baris dalam tabel akan diperbarui oleh file, tetapi kadang-kadang bisa sebanyak 25%.
  • Ada indeks pada bidang yang sedang diperbarui. Ada 12 indeks di atas meja, dan 8 indeks menyertakan bidang pembaruan.
  • Tidak diperlukan untuk melakukan pembaruan dalam satu transaksi. Ini bisa memakan waktu tetapi tidak lebih dari 24 jam. Saya ingin menyelesaikannya dalam 1 jam tanpa mengunci seluruh tabel, karena nanti saya harus memperbarui indeks sphinx yang bergantung pada tabel ini. Tidak masalah jika langkah-langkah memakan waktu lebih lama selama database tersedia untuk tugas-tugas lain.
  • Saya bisa memodifikasi format csv dalam langkah preprocess. Satu-satunya hal yang penting adalah pembaruan cepat dan tanpa mengunci.
  • Tabel 2 adalah MyISAM. Ini adalah tabel yang baru dibuat dari file csv menggunakan memuat data infile. Ukuran file MYI adalah 452 MB. Tabel 2 diindeks pada kolom field1.
  • MYD dari tabel MyISAM adalah 663MB.

Pembaruan 3:

berikut ini rincian lebih lanjut tentang kedua tabel.

CREATE TABLE `content` (
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `og_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `keywords` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
  `files` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `category` smallint(3) unsigned NOT NULL DEFAULT '600',
  `size` bigint(19) unsigned NOT NULL DEFAULT '0',
  `downloaders` int(11) NOT NULL DEFAULT '0',
  `completed` int(11) NOT NULL DEFAULT '0',
  `uploaders` int(11) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `vote_up` int(11) unsigned NOT NULL DEFAULT '0',
  `vote_down` int(11) unsigned NOT NULL DEFAULT '0',
  `comments_count` int(11) NOT NULL DEFAULT '0',
  `imdb` int(8) unsigned NOT NULL DEFAULT '0',
  `video_sample` tinyint(1) NOT NULL DEFAULT '0',
  `video_quality` tinyint(2) NOT NULL DEFAULT '0',
  `audio_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
  `subtitle_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `uploader` int(11) unsigned NOT NULL DEFAULT '0',
  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
  `scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`record_num`),
  UNIQUE KEY `hash` (`hash`),
  KEY `uploaders` (`uploaders`),
  KEY `tfile_size` (`tfile_size`),
  KEY `enabled_category_upload_date_verified_` (`enabled`,`category`,`upload_date`,`verified`),
  KEY `enabled_upload_date_verified_` (`enabled`,`upload_date`,`verified`),
  KEY `enabled_category_verified_` (`enabled`,`category`,`verified`),
  KEY `enabled_verified_` (`enabled`,`verified`),
  KEY `enabled_uploader_` (`enabled`,`uploader`),
  KEY `anonymous_uploader_` (`anonymous`,`uploader`),
  KEY `enabled_uploaders_upload_date_` (`enabled`,`uploaders`,`upload_date`),
  KEY `enabled_verified_category` (`enabled`,`verified`,`category`),
  KEY `verified_enabled_category` (`verified`,`enabled`,`category`)
) ENGINE=InnoDB AUTO_INCREMENT=7551163 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED


CREATE TABLE `content_csv_dump_temp` (
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) unsigned NOT NULL DEFAULT '0',
  `uploaders` int(11) unsigned NOT NULL DEFAULT '0',
  `downloaders` int(11) unsigned NOT NULL DEFAULT '0',
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

dan di sini adalah permintaan pembaruan yang memperbarui content tabel menggunakan data dari content_csv_dump_temp

UPDATE content a JOIN content_csv_dump_temp b 
ON a.hash = b.hash 
SET 
a.uploaders = b.uploaders,
a.downloaders = b.downloaders,
a.verified = b.verified

pembaruan 4:

semua pengujian di atas dilakukan pada mesin uji., tapi sekarang saya melakukan tes yang sama pada mesin produksi, dan permintaan sangat cepat.

mysql> UPDATE content_test a JOIN content_csv_dump_temp b
    -> ON a.hash = b.hash
    -> SET
    -> a.uploaders = b.uploaders,
    -> a.downloaders = b.downloaders,
    -> a.verified = b.verified;
Query OK, 2673528 rows affected (7 min 50.42 sec)
Rows matched: 7044818  Changed: 2673528  Warnings: 0

saya minta maaf atas kesalahan saya. Lebih baik menggunakan join daripada setiap pembaruan catatan. sekarang saya mencoba untuk meningkatkan mpre menggunakan indeks yang disarankan oleh rick_james, akan diperbarui setelah bench-marking selesai.

35
AMB

Berdasarkan pengalaman saya, saya akan menggunakan LOAD DATA INFILE untuk mengimpor File CSV Anda.

Pernyataan LOAD DATA INFILE membaca baris dari file teks ke dalam tabel dengan kecepatan yang sangat tinggi.

Contoh yang saya temukan di internet Memuat contoh Data . Saya menguji contoh ini di kotak saya dan bekerja dengan baik

Tabel Contoh

CREATE TABLE example (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Column2` varchar(14) NOT NULL,
  `Column3` varchar(14) NOT NULL,
  `Column4` varchar(14) NOT NULL,
  `Column5` DATE NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB

Contoh File CSV

# more /tmp/example.csv
Column1,Column2,Column3,Column4,Column5
1,A,Foo,sdsdsd,4/13/2013
2,B,Bar,sdsa,4/12/2013
3,C,Foo,wewqe,3/12/2013
4,D,Bar,asdsad,2/1/2013
5,E,FOObar,wewqe,5/1/2013

Pernyataan Impor dijalankan dari konsol MySQL

LOAD DATA LOCAL INFILE '/tmp/example.csv'
    -> INTO TABLE example
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id, Column3,Column4, @Column5)
    -> set
    -> Column5 = str_to_date(@Column5, '%m/%d/%Y');

Hasil

MySQL [testcsv]> select * from example;
+----+---------+---------+---------+------------+
| Id | Column2 | Column3 | Column4 | Column5    |
+----+---------+---------+---------+------------+
|  1 |         | Column2 | Column3 | 0000-00-00 |
|  2 |         | B       | Bar     | 0000-00-00 |
|  3 |         | C       | Foo     | 0000-00-00 |
|  4 |         | D       | Bar     | 0000-00-00 |
|  5 |         | E       | FOObar  | 0000-00-00 |
+----+---------+---------+---------+------------+

IGNORE mengabaikan baris pertama yang merupakan tajuk kolom.

Setelah IGNORE, kami menentukan kolom (melewatkan kolom2), untuk mengimpor, yang cocok dengan salah satu kriteria dalam pertanyaan Anda.

Berikut adalah contoh lain langsung dari Oracle: contoh LOAD DATA INFILE

Ini seharusnya cukup untuk membantu Anda memulai.

17
Craig Efrein

Mengingat semua hal yang disebutkan, sepertinya bottleneck adalah join itu sendiri.

ASPECT # 1: Gabung Ukuran Buffer

Kemungkinan besar, join_buffer_size Anda mungkin terlalu rendah.

Menurut Dokumentasi MySQL pada Bagaimana MySQL Menggunakan Cache Penyangga Gabung

Kami hanya menyimpan kolom yang digunakan dalam buffer bergabung, bukan seluruh baris.

Karena itu, buat kunci buffer bergabung tetap di RAM.

Anda memiliki 10 juta baris kali 4 byte untuk setiap tombol. Itu sekitar 40 juta.

Cobalah menabraknya di sesi ke 42M (sedikit lebih besar dari 40M)

SET join_buffer_size = 1024 * 1024 * 42;
UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4;

Jika ini berhasil, lanjutkan untuk menambahkan ini ke my.cnf

[mysqld]
join_buffer_size = 42M

Restart mysqld tidak diperlukan untuk koneksi baru. Lari saja

mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 42;

ASPEK # 2: Bergabung dengan Operasi

Anda dapat memanipulasi gaya operasi gabungan dengan men-tweak pengoptimal

Menurut Dokumentasi MySQL pada Blok Nested-Loop dan Batched Key Access Joins

Ketika BKA digunakan, nilai join_buffer_size menentukan seberapa besar kumpulan kunci dalam setiap permintaan ke mesin penyimpanan. Semakin besar buffer, semakin banyak akses sekuensial akan berada di tabel sebelah kanan operasi gabungan, yang secara signifikan dapat meningkatkan kinerja.

Agar BKA dapat digunakan, flag batched_key_access dari variabel sistem optimizer_switch harus disetel ke on. BKA menggunakan MRR, jadi bendera mrr juga harus menyala. Saat ini, estimasi biaya untuk MRR terlalu pesimistis. Oleh karena itu, mrr_cost_based juga perlu dimatikan agar BKA dapat digunakan.

Halaman yang sama ini merekomendasikan untuk melakukan ini:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

ASPECT # 3: Menulis Pembaruan ke Disk (OPTIONAL)

Kebanyakan lupa untuk meningkatkan innodb_write_io_threads untuk menulis halaman kotor keluar dari kumpulan buffer lebih cepat.

[mysqld]
innodb_write_io_threads = 16

Anda harus me-restart MySQL untuk perubahan ini

COBALAH !!!

16
RolandoMySQLDBA

Anda mengatakan:

  • Pembaruan mempengaruhi 6-25% dari tabel Anda
  • Anda ingin melakukan ini secepat mungkin (<1 jam)
  • tanpa mengunci
  • tidak harus dalam satu transaksi
  • belum (dalam komentar pada jawaban Rick James), Anda menyatakan keprihatinan tentang kondisi balapan

Banyak dari pernyataan ini dapat bertentangan. Misalnya, pembaruan besar tanpa mengunci tabel. Atau menghindari kondisi balapan tanpa menggunakan satu transaksi raksasa.

Juga, karena meja Anda diindeks dengan berat, kedua sisipan dan pembaruan bisa lambat.


Menghindari kondisi balapan

Jika Anda dapat menambahkan cap waktu yang diperbarui ke meja Anda, Anda dapat menyelesaikan kondisi lomba sambil juga menghindari pencatatan setengah juta pembaruan dalam transaksi tunggal.

Ini membebaskan Anda untuk melakukan pembaruan baris-demi-baris (seperti yang Anda lakukan saat ini), tetapi dengan autocommit atau kumpulan transaksi yang lebih masuk akal.

Anda menghindari kondisi balapan (saat memperbarui baris demi baris) dengan melakukan pemeriksaan bahwa pembaruan nanti belum terjadi (UPDATE ... WHERE pk = [pk] AND updated < [batchfile date])

Dan, yang penting, ini memungkinkan Anda menjalankan paralel pembaruan.


Berjalan secepat mungkin —Paralelisasi

Dengan stempel waktu ini, sekarang sudah terpasang:

  1. Bagi file batch Anda menjadi beberapa potongan berukuran wajar (katakanlah 50.000 baris/file)
  2. Secara paralel, minta skrip dibaca di setiap file dan hasilkan file dengan 50.000 pernyataan UPDATE.
  3. Secara paralel, setelah (2) selesai, minta mysql menjalankan setiap file sql.

(mis. Dalam bash lihat split dan xargs -P untuk cara menjalankan perintah dengan banyak cara paralel. Tingkat paralelisme tergantung pada berapa banyak utas yang ingin Anda dedikasikan untuk pembaruan )

3
  1. CREATE TABLE yang cocok dengan CSV
  2. LOAD DATA ke dalam tabel itu
  3. UPDATE real_table JOIN csv_table ON ... SET ..., ..., ...;
  4. DROP TABLE csv_table;

Langkah 3 akan jauh lebih cepat daripada baris-demi-baris, tetapi masih akan mengunci semua baris dalam tabel untuk jumlah waktu yang tidak sepele. Jika waktu kunci ini lebih penting daripada berapa lama seluruh proses berlangsung, ...

Jika tidak ada yang menulis di atas meja, maka ...

  1. CREATE TABLE yang cocok dengan CSV; tidak ada indeks kecuali apa yang dibutuhkan dalam JOIN dalam UPDATE. Jika unik, buat itu PRIMARY KEY.
  2. LOAD DATA ke dalam tabel itu
  3. salin real_table hingga new_table (CREATE ... SELECT)
  4. UPDATE new_table JOIN csv_table ON ... SET ..., ..., ...;
  5. RENAME TABLE real_table TO old, new_table TO real_table;
  6. DROP TABLE csv_table, old;

Langkah 3 lebih cepat daripada pembaruan, terutama jika indeks yang tidak perlu ditinggalkan.
Langkah 5 adalah "instan".

3
Rick James

Pembaruan besar terikat I/O. Saya akan menyarankan:

  1. Buat tabel berbeda yang akan menyimpan 3 bidang Anda yang sering diperbarui. Mari kita sebut satu tabel assets_static tempat Anda menyimpan, well, data statis, dan lainnya assets_dynamic yang akan menyimpan pengunggah, pengunduh, dan diverifikasi.
  2. Jika Anda bisa, gunakan mesin MEMORY untuk tabel assets_dynamic. (cadangan ke disk setelah setiap pembaruan).
  3. Perbarui ringan dan gesit Anda assets_dynamic sesuai pembaruan Anda 4 (yaitu LOAD INFILE ... KE TEMPAT; UPDATE assets_dynamic temp GABUNGAN b pada a.id = b.id SET [apa yang harus diperbarui]. Ini harus memakan waktu kurang dari satu menit. (Pada sistem kami, assets_dynamic memiliki 95M baris dan dampak pembaruan ~ 6M baris, dalam sedikit lebih dari 40-an.)
  4. Ketika Anda menjalankan pengindeks Sphinx, GABUNG assets_static dan assets_dynamic (dengan asumsi Anda ingin menggunakan salah satu bidang ini sebagai atribut).
1
user3127882

Agar UPDATE berjalan cepat, Anda perlu

INDEX(uploaders, downloaders, verified)

Itu bisa di atas meja. Tiga bidang bisa dalam urutan apa pun.

Itu akan memudahkan UPDATE bisa dengan cepat mencocokkan baris antara dua tabel.

Dan buat tipe data yang sama di dua tabel (keduanya INT SIGNED atau keduanya INT UNSIGNED).

0
Rick James