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
- mengurangi kompleksitas. Meminta satu tabel lebih sederhana daripada N tabel
- dorong sebanyak mungkin kerumitan ke database. Pembungkus di sekitar logika sharding bulan di Rails lambat dan bermasalah
- meningkatkan kinerja. Juga terkait dengan satu kueri tabel yang lebih sederhana daripada N
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
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
- gunakan sesedikit mungkin indeks
- memuat data secara berurutan tidak hanya membuat pemuatan lebih cepat, tetapi tabel yang dihasilkan akan lebih cepat
- jika Anda dapat memuat data apa pun dari MySQL (bukan perantara file datar), itu akan jauh lebih cepat. Anda dapat menggunakan `INSERT INTO. Pernyataan SELECT' untuk menyalin data antar tabel dengan cepat
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