pengembangan-web-mp.com

Mengapa MySQL InnoDB disisipkan sangat lambat?

Saya menggunakan angka acak besar sebagai kunci (datang dari sistem lain). Sisipan dan pembaruan pada tabel yang cukup kecil (seperti dalam beberapa juta baris) membutuhkan waktu lebih lama daripada yang saya pikir masuk akal.

Saya telah menyaring tes yang sangat sederhana untuk digambarkan. Di tabel tes saya sudah mencoba membuatnya sesederhana mungkin; kode asli saya tidak memiliki tata letak yang sederhana dan memiliki hubungan dan indeks tambahan dan semacamnya. Namun, pengaturan yang lebih sederhana menunjukkan kinerja yang setara.

Inilah hasilnya:

creating the MyISAM table took 0.000 seconds
creating 1024000 rows of test data took 1.243 seconds
inserting the test data took 6.335 seconds
selecting 1023742 rows of test data took 1.435 seconds
fetching 1023742 batches of test data took 0.037 seconds
dropping the table took 0.089 seconds
creating the InnoDB table took 0.276 seconds
creating 1024000 rows of test data took 1.165 seconds
inserting the test data took 3433.268 seconds
selecting 1023748 rows of test data took 4.220 seconds
fetching 1023748 batches of test data took 0.037 seconds
dropping the table took 0.288 seconds

Memasukkan baris 1M ke dalam MyISAM membutuhkan waktu 6 detik; ke InnoDB memakan waktu 433 detik!

Apa yang saya lakukan salah? Apa yang salah konfigurasi? (MySQL adalah instalasi Ubuntu normal dengan default)

Inilah kode tes:

import sys, time, random
import MySQLdb as db

# usage: python script db_username db_password database_name

db = db.connect(Host="127.0.0.1",port=3306,user=sys.argv[1],passwd=sys.argv[2],db=sys.argv[3]).cursor()

def test(engine):

    start = time.time() # fine for this purpose
    db.execute("""
CREATE TEMPORARY TABLE Testing123 (
k INTEGER PRIMARY KEY NOT NULL,
v VARCHAR(255) NOT NULL
) ENGINE=%s;"""%engine)
    duration = time.time()-start
    print "creating the %s table took %0.3f seconds"%(engine,duration)

    start = time.time()
    # 1 million rows in 100 chunks of 10K
    data = [[(str(random.getrandbits(48)) if a&1 else int(random.getrandbits(31))) for a in xrange(10*1024*2)] for b in xrange(100)]
    duration = time.time()-start
    print "creating %d rows of test data took %0.3f seconds"%(sum(len(rows)/2 for rows in data),duration)

    sql = "REPLACE INTO Testing123 (k,v) VALUES %s;"%("(%s,%s),"*(10*1024))[:-1]
    start = time.time()
    for rows in data:
        db.execute(sql,rows)
    duration = time.time()-start
    print "inserting the test data took %0.3f seconds"%duration

    # execute the query
    start = time.time()
    query = db.execute("SELECT k,v FROM Testing123;")
    duration = time.time()-start
    print "selecting %d rows of test data took %0.3f seconds"%(query,duration)

    # get the rows in chunks of 10K
    rows = 0
    start = time.time()
    while query:
        batch = min(query,10*1024)
        query -= batch
        rows += len(db.fetchmany(batch))
    duration = time.time()-start
    print "fetching %d batches of test data took %0.3f seconds"%(rows,duration)

    # drop the table
    start = time.time()
    db.execute("DROP TABLE Testing123;")
    duration = time.time()-start
    print "dropping the table took %0.3f seconds"%duration


test("MyISAM")
test("InnoDB")
55
Will

InnoDB tidak dapat menangani kunci primer 'acak' dengan baik. Coba kunci berurutan atau kenaikan otomatis, dan saya yakin Anda akan melihat kinerja yang lebih baik. Bidang kunci 'nyata' Anda masih dapat diindeks, tetapi untuk memasukkan massal Anda mungkin lebih baik menjatuhkan dan menciptakan kembali indeks itu dalam satu pukulan setelah memasukkannya secara lengkap. Akan tertarik melihat tolok ukur Anda untuk itu!

Beberapa pertanyaan terkait

42
Paul Dixon

InnoDB memiliki dukungan transaksi, Anda tidak menggunakan transaksi eksplisit sehingga innoDB harus melakukan komit setelah setiap pernyataan ( "melakukan log flush ke disk untuk setiap sisipan" ).

Jalankan perintah ini sebelum loop Anda:

START TRANSACTION

dan ini setelah Anda loop

COMMIT
61
flo

Saya perlu melakukan pengujian aplikasi insert-heavy di MyISAM dan InnoDB secara bersamaan. Ada satu pengaturan yang menyelesaikan masalah kecepatan yang saya alami. Coba atur yang berikut ini:

innodb_flush_log_at_trx_commit = 2

Pastikan Anda memahami risikonya dengan membaca tentang pengaturan di sini .

Juga lihat https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2/12612 dan - https://dba.stackexchange.com/a/29974/9405

21
Philip Koshy

Saya mendapatkan hasil yang sangat berbeda pada sistem saya, tetapi ini tidak menggunakan standar. Anda kemungkinan mengalami bottleneck pada ukuran innodb-log-file, yaitu 5M secara default. Pada innodb-log-file-size = 100M saya mendapatkan hasil seperti ini (semua angka dalam hitungan detik):

                             MyISAM     InnoDB
create table                  0.001      0.276
create 1024000 rows           2.441      2.228
insert test data             13.717     21.577
select 1023751 rows           2.958      2.394
fetch 1023751 batches         0.043      0.038
drop table                    0.132      0.305

Meningkatkan innodb-log-file-size akan mempercepat ini beberapa detik. Menjatuhkan jaminan ketahanan dengan menetapkan innodb-flush-log-at-trx-commit=2 atau 0 akan meningkatkan angka sisipan juga.

6
Andrew

Nilai default untuk InnoDB sebenarnya sangat buruk. InnoDB sangat RAM tergantung, Anda mungkin menemukan hasil yang lebih baik jika Anda men-tweak pengaturan. Berikut panduan yang saya gunakan dasar pengoptimalan InnoDB

5
Kien Truong

Ini adalah topik lama tetapi sering dicari. Selama Anda mengetahui adanya risiko (seperti yang dinyatakan oleh @philip Koshy di atas) dari kehilangan transaksi yang dilakukan dalam satu detik terakhir, sebelum pembaruan besar-besaran, Anda dapat menetapkan parameter global ini

innodb_flush_log_at_trx_commit=0
sync_binlog=0

kemudian hidupkan kembali (jika diinginkan) setelah pembaruan selesai.

innodb_flush_log_at_trx_commit=1
sync_binlog=1

untuk kepatuhan ACID penuh.

Ada perbedaan besar dalam kinerja penulisan/pembaruan ketika keduanya dimatikan dan dihidupkan. Dalam pengalaman saya, hal-hal lain yang dibahas di atas membuat perbedaan tetapi hanya sedikit.

Satu hal lain yang berdampak update/insert sangat indeks teks lengkap. Dalam satu kasus, tabel dengan dua bidang teks yang memiliki indeks teks lengkap, menyisipkan baris 2mil membutuhkan waktu 6 jam dan yang sama hanya membutuhkan waktu 10 menit setelah indeks teks lengkap dihapus. Lebih banyak indeks, lebih banyak waktu. Jadi indeks pencarian selain kunci utama dan unik dapat dihapus sebelum memasukkan/memperbarui secara besar-besaran.

2
Allen King

Berapa ukuran buffer-pool innodb Anda? Pastikan Anda mengaturnya ke 75% dari RAM Anda. Biasanya memasukkan lebih baik ketika dalam urutan kunci utama untuk InnoDB. Tetapi dengan ukuran kolam besar, Anda harus melihat kecepatan yang baik.

2
Ajay

hal-hal yang mempercepat sisipan:

  • saya telah menghapus semua kunci dari sebuah tabel sebelum memasukkan besar ke dalam tabel kosong
  • kemudian menemukan saya punya masalah bahwa indeks tidak muat di memori.
  • juga menemukan saya memiliki sync_binlog = 0 (harus 1) bahkan jika binlog tidak digunakan.
  • juga menemukan saya tidak mengatur innodb_buffer_pool_inances
1
Shimon Doodkin

Larutan

  1. Buat kunci UNIK baru yang identik dengan kunci PRIMARY Anda saat ini
  2. Tambahkan kolom baru id adalah bilangan bulat yang tidak ditandatangani, auto_increment
  3. Buat kunci utama pada kolom id baru

Bam, peningkatan insert 10x + segera.

0
William Entriken