Kami memiliki sistem lawas di lingkungan produksi kami yang melacak saat pengguna melakukan tindakan pada Penyebab. com (bergabung dengan Penyebab, merekrut teman, dll). Saya mengatakan warisan, tetapi yang saya maksud adalah sistem yang dioptimalkan secara prematur yang ingin saya buat kurang pintar. Basis data rekaman sepanjang 500m ini dibagi menjadi beberapa tabel pecahan bulanan. Sepertinya solusi yang bagus untuk penskalaan (dan memang demikian) - kecuali bahwa kita tidak membutuhkannya. Dan berdasarkan pola penggunaan kami (mis. g. untuk menghitung jumlah total tindakan pengguna, kita perlu melakukan kueri N tabel), ini menyebabkan masalah penurunan kinerja yang cukup parah. Bahkan dengan lapisan memcache duduk di depan tabel bulan lama, fitur baru terus menemukan masalah kinerja N-query baru. Menyadari bahwa kami memiliki database lain yang dengan senang hati menampung 900 juta catatan, saya memutuskan untuk memigrasikan sistem yang ada ke dalam satu pengaturan tabel. Tujuannya adalah Show
Alternatif Solusi yang Diusulkan Partisi MySQL. ini adalah yang paling mirip dengan penyiapan kami yang sudah ada, karena MySQL secara internal menyimpan data ke dalam tabel yang berbeda. Kami memutuskan untuk tidak melakukannya karena sepertinya itu tidak akan jauh lebih cepat daripada solusi kami saat ini (walaupun MySQL secara internal dapat melakukan beberapa pengoptimalan untuk memastikan Anda hanya melihat tabel yang mungkin memiliki data yang Anda inginkan). Dan itu masih kompleksitas yang sama yang ingin kami kurangi (dan selanjutnya akan menjadi satu-satunya basis data yang diatur di sistem kami menggunakan partisi) Redis. Tidak benar-benar diusulkan sebagai alternatif karena kumpulan data lengkap tidak akan masuk ke dalam memori, tetapi sesuatu yang kami pertimbangkan untuk memuat subkumpulan data untuk menjawab pertanyaan yang kami buat banyak yang tidak terlalu baik di MySQL (e. g. 'teman saya yang mana yang telah mengambil tindakan' dengan cepat menggunakan fungsi SET UNION bawaan Redis). Tabel MySQL yang baru mungkin cukup berkinerja sehingga tidak masuk akal untuk membuat versi Redis yang cepat, jadi kami menghindari hal ini karena pengoptimalan yang terlalu dini, terutama dengan teknologi yang belum kami kenal Membuang data lama MySQL menyediakan utilitas `mysqldump' untuk memungkinkan pembuangan cepat ke disk msyqldump -T /var/lib/mysql/database_data nama_database Ini akan menghasilkan file TSV untuk setiap tabel dalam database, dan ini adalah format yang `LOAD INFILE 'akan dapat dimuat dengan cepat nanti Memasang Percona 5. 5 Kami akan membangun sistem baru dengan database Percona terbaru dan terhebat di CentOS 6. 2 rpm -Uhv http. // www. percona. com/downloads/percona-release/percona-release-0. 0-1. x86_64. rpm yum instal Percona-Server-shared-compat Percona-Server-client-55 Percona-Server-server-55 -y [buka bug dengan paket compat. https. //bug. landasan peluncuran. net/percona-server/+bug/908620] Tentukan direktori untuk data InnoDB Ini bukan tip kinerja, tetapi saya harus melakukan beberapa penggalian untuk membuat MySQL menyimpan data di partisi yang berbeda. Langkah pertama adalah memanfaatkan my. cnf berisi a datadir = /jalur/ke/data pengarahan. Pastikan /path/to/data dimiliki oleh mysql. mysql (chown -R mysql. mysql /path/to/data) dan jalankan mysql_install_db --user=mysql --datadir=/path/ke/data Ini akan mengatur struktur direktori yang digunakan InnoDB untuk menyimpan data. Ini juga berguna jika Anda membatalkan pemuatan data yang gagal dan ingin membersihkan slate (jika Anda tidak menentukan direktori, /var/lib/mysql digunakan secara default). Hanya rm -rf * direktori data dan jalankan perintah mysql_install_db [* http. //dev. mysql. com/doc/refman/5. 5/en/mysql-install-db. html] Perintah SQL untuk Mempercepat LOAD DATA Anda dapat memberi tahu MySQL untuk tidak menerapkan batasan kunci asing dan keunikan SET FOREIGN_KEY_CHECKS = 0; dan jatuhkan jaminan isolasi transaksi ke UNCOMMITTED SET SESSION tx_isolation='READ-UNCOMMITTED' dan matikan binlog dengan SET sql_log_bin = 0 Dan setelah selesai, jangan lupa untuk menyalakannya kembali SET UNIQUE_CHECKS = 1; Perlu dicatat bahwa banyak sumber daya akan meminta Anda untuk menggunakan direktif "DISABLE KEYS" dan membuat semua indeks dibuat setelah semua data dimuat ke dalam tabel. Sayangnya, InnoDB tidak mendukung ini. Saya mencobanya, dan meskipun hanya butuh beberapa jam untuk memuat 500m baris, datanya tidak dapat digunakan tanpa indeks apa pun. Anda dapat menghapus indeks sepenuhnya dan menambahkannya nanti, tetapi dengan ukuran tabel sebesar ini, saya pikir itu tidak akan banyak membantu Herring merah lainnya mematikan autocommit dan melakukan setelah setiap pernyataan `LOAD DATA '. Ini secara efektif adalah hal yang sama dengan komitmen otomatis, dan komitmen secara manual menyebabkan pelambatan `LOAD DATA' seperempat jalan di [http. //dev. mysql. com/doc/refman/5. 1/en/ubah-tabel. html, cari "NONAKTIFKAN KUNCI"] [ http. // www. mysqlperformanceblog. com/2007/11/01/innodb-performance-optimization-basics/] Penyesuaian kinerja dibuat untuk saya. cnf -- http. //dev. mysql. com/doc/refman/5. 5/en/innodb-parameter. html#sysvar_innodb_flush_log_at_trx_commit -- ini melonggarkan frekuensi pemindahan data ke disk -- kemungkinan kehilangan satu atau dua detik data dengan cara ini jika terjadi -- sistem crash, tetapi ini dalam keadaan yang sangat terkontrol innodb_flush_log_at_trx_commit . // www. mysqlperformanceblog. com/2007/11/01/innodb-performance-optimization-basics/ innodb_flush_method=O_DIRECT -- jangan menulis data dua kali -- http. //dev. mysql. com/doc/refman/5. 5/en/innodb-parameter. html#sysvar_innodb_doublewrite innodb_doublewrite = 0 Gunakan LOAD DATA INFILE Ini adalah jalur yang paling optimal untuk memuat data terstruktur secara massal ke MySQL. 8. 2. 2. 1. Pernyataan Kecepatan INSERT memprediksi ~20x percepatan selama INSERT massal (i. e. INSERT dengan ribuan baris dalam satu pernyataan). Lihat juga 8. 5. 4. Pemuatan Data Massal untuk Tabel InnoDB untuk beberapa tips lainnya Tidak hanya lebih cepat, tetapi dalam pengalaman saya dengan migrasi ini, metode INSERT akan melambat lebih cepat daripada yang dapat memuat data dan secara efektif tidak pernah selesai (perkiraan terakhir yang saya buat adalah 60 hari, tetapi masih melambat) INFILE harus ada di direktori tempat InnoDB menyimpan informasi basis data itu. Jika MySQL ada di /var/lib/mysql, maka mydatabase akan ada di /var/lib/mysql/mydatabase. Jika Anda tidak memiliki akses ke direktori tersebut di server, Anda dapat menggunakan LOAD DATA LOCAL INFILE. Dalam pengujian saya, meletakkan file di tempat yang tepat dan menggunakan `LOAD DATA INFILE' meningkatkan kinerja pemuatan sekitar 20% [http. //dev. mysql. com/doc/refman/5. 5/en/memuat-data. html] Lakukan transformasi data Anda langsung di MySQL Sistem kredit tindakan lama kami unik pada (BULAN(dibuat), id), tetapi sistem baru akan menghasilkan ID penambahan otomatis baru untuk setiap rekaman saat dimuat dalam urutan kronologis. Masalahnya adalah data TSV 50 GB saya tidak cocok dengan skema baru. Beberapa skrip yang saya miliki yang akan menggunakan Ruby untuk mengubah baris lama menjadi baris baru sangat lambat. Saya melakukan beberapa penggalian dan menemukan bahwa Anda dapat memberi tahu MySQL untuk (dengan cepat) membuang data yang tidak Anda inginkan dalam pernyataan beban itu sendiri, menggunakan pengikatan parameter LOAD DATA INFILE 'data. csv' INTO TABLE FIELDS mytable DIHENTIKAN oleh '\t' DITUTUP OLEH '\"' (@throwaway), user_id, tindakan, dibuat_at Pernyataan ini memberi tahu MySQL bidang mana yang diwakili dalam data. csv. @throwaway adalah parameter yang mengikat; . Jika kami ingin memasukkan awalan, kami dapat mengeksekusi LOAD DATA INFILE 'data. csv' INTO TABLE mytable FIELDS TERMINATED by '\t' ENCLOSED BY '\"' (id, user_id, @action, created_at SET action=CONCAT('prefix_', action) dan kolom `tindakan' setiap baris yang dimuat akan dimulai dengan string 'awalan' Memeriksa kemajuan tanpa mengganggu impor Jika Anda memuat file data besar dan ingin memeriksa progresnya, Anda pasti tidak ingin menggunakan `SELECT COUNT(*) FROM table'. Kueri ini akan menurun seiring dengan bertambahnya ukuran tabel dan memperlambat proses LOAD. Sebagai gantinya, Anda dapat meminta mysql> PILIH table_rows DARI information_schema. tabel WHERE nama_tabel = 'tabel'; . table_rows. +-----------+. 27273886. +-----------+ 1 baris dalam set (0. 23 detik) Jika Anda ingin menonton/mencatat kemajuan dari waktu ke waktu, Anda dapat membuat perintah shell cepat untuk mengumpulkan jumlah baris $sementara. ; . tabel WHERE nama_tabel = 'tabel' \G ; . grep baris. potong -d'. ' -f2. xargs echo `tanggal +"%F %R"` ,. beban tee. masuk && tidur 30; . 16 , 32267244 29-05-2012 18. 16 , 32328002 29-05-2012 18. 17 , 32404189 29-05-2012 18. 17 , 32473936 29-05-2012 18. 18 , 32543698 29-05-2012 18. 18 , 32616939 29-05-2012 18. 19 , 32693198 `tee' akan bergema ke STDOUT dan juga ke `file. log', '\G' memformat kolom dalam hasil yang ditetapkan sebagai baris, dan sleep memberikan jeda antara pemuatan MUAT skrip chunking DATA Saya segera menemukan bahwa melempar file TSV baris 50m di LOAD DATA adalah cara yang baik untuk menurunkan kinerja hingga tidak selesai. Saya memilih menggunakan `split' untuk membagi data menjadi satu juta baris per file <. [CDATA[// <. [CDATA[ // ]]]]><. [CDATA[>]]> Bungkus Selama durasi skrip ini, saya melihat waktu pemuatan potongan meningkat dari 1 menit 40 detik menjadi sekitar satu jam per juta sisipan. Namun ini lebih baik daripada tidak menyelesaikan sama sekali, yang tidak dapat saya capai sampai membuat semua perubahan yang disarankan dalam posting ini dan menggunakan `load yang disebutkan di atas. skrip dia. Kiat lainnya
Terima kasih telah membaca draf ini untuk Greg dan Lann, dua rekan kerja super cerdas saya di Causes. Lihat penyebabnya. com/joinus jika pekerjaan semacam ini menarik minat Anda Mengapa memuat data lebih cepat daripada INSERT?Alasannya cukup sederhana. LOAD DATA INFILE mengganggu MySQL lebih sedikit daripada pernyataan INSERT . misalnya, pernyataan LOAD DATA INFILE dapat melewati baris, kolom, atau, jika kita mau, memuat data hanya ke kolom tertentu, melewatkan yang lainnya (lihat contoh di atas. )
Manakah yang lebih efisien memuat data INSERT?Pilihan Benar. C. Di MySQL, ' LOAD DATA ' dalam segala bentuk lebih efisien daripada 'INSERT' karena memuat baris secara massal.
Apa perbedaan antara memuat data Infile dan memuat data Infile lokal di MySQL?LOAD DATA INFILE mendapatkan file dari sistem file lokal server database . File harus berada di direktori database atau memiliki izin baca dunia, dan nama pengguna klien harus memiliki hak istimewa FILE. LOAD DATA LOCAL INFILE membaca file di klien, dan mengirimkan isinya ke server.
Apa itu memuat data Infile di MySQL?Pernyataan LOAD DATA INFILE membaca baris dari file teks ke dalam tabel dengan kecepatan sangat tinggi . Jika kata kunci LOKAL ditentukan, file dibaca dari host klien. Jika LOKAL tidak ditentukan, file harus berada di server. ( LOKAL tersedia di MySQL 3. 22. |