Mengapa menggunakan innodb_file_per_table?

Menurut saya ini bukan soal performa, tapi soal manajemen.

Dengan file terpisah per tabel, Anda dapat menyimpan database yang berbeda di perangkat penyimpanan yang berbeda misalnya.

Anda dapat menangani kasus database yang sangat besar dalam sistem file yang tidak dapat menangani file besar (setidaknya tunda masalah hingga satu tabel mencapai batas ukuran file).

Anda tidak memiliki pertumbuhan tablespace yang tidak terkontrol. Jika Anda memiliki beberapa tabel besar yang Anda jatuhkan, ibdata file tetap kecil.

Salah satu aspek yang mungkin berpengaruh pada kinerja adalah fragmentasi data tabel dan indeks, yang akan dibatasi per tabel. Tapi itu perlu pengujian untuk dikonfirmasi.

Mengapa menggunakan innodb_file_per_table?

Karena lebih mudah mengelola individu karena dapat dilakukan di tingkat file. Ini berarti bahwa meskipun server mati, Anda masih dapat menyalin data dengan menyalin file tabel sedangkan menggunakan ruang tabel bersama berarti menyalin semuanya yang bisa sangat besar, atau mencari cara agar server berjalan untuk mengekstrak data (Anda benar-benar tidak ingin mengekstrak data secara manual dengan hex-editor).

Seseorang memperingatkan bahwa Anda tidak bisa begitu saja menyalin dan menempel .ibd file dari satu server ke server lainnya. Ini mungkin benar, tetapi seharusnya tidak berlaku untuk cadangan di server yang sama (saya menggunakan istilah cadangan di sini dalam pengertian tradisional membuat salinan; yaitu, tidak secara drastis mengubah semuanya). Apalagi ibdata1 secara otomatis dibuat ulang saat startup (seperti yang terlihat di delete ibdata1 langkah dari sebagian besar panduan "mengonversi ke file-per-tabel"). Dengan demikian, Anda tidak perlu menyalin ibdata1 selain .ibd Anda file (dan .frm yang sesuai , dll. file).

Jika mencoba memulihkan tabel yang hilang, cukup menyalin .ibd-nya dan .frm file, serta information_schema (yang banyak lebih kecil dari ibdata1 ). Dengan begitu, Anda dapat menempatkannya di server dummy dan mengekstrak tabel Anda tanpa harus menyalin semuanya, hal yang sangat besar.

Namun, klaim untuk kinerja yang lebih baik dipertanyakan. … Dengan innodb_file_per_table, diperlukan lebih banyak operasi I/O disk; dan ini penting dalam batasan JOIN dan FOREIGN KEY yang rumit.

Tidak mengherankan, kinerja akan bergantung sepenuhnya pada basis data tertentu yang digunakan. Satu orang akan memiliki (bahkan sangat) hasil yang berbeda dari yang lain.

Benar bahwa akan ada lebih banyak operasi I/O disk dengan file-per-tabel, tetapi hanya sedikit lagi. Pikirkan tentang cara kerja sistem.

• Untuk database monolitik:

  1. Server dimulai
  2. ibdata1 dibuka
  3. Header dan meta-data telah dibaca
  4. Struktur dan meta-data di-cache di memori
  5. Kueri terjadi
     1. Server mengakses disk dan membaca data dari ibdata1 yang telah dibuka
     2. Server dapat meng-cache data di memori

• Untuk basis data per tabel:

  1. Server dimulai
  2. ibdata1 dibuka
  3. Header dan meta-data telah dibaca
  4. Setiap individu .ibd file dibuka
  5. Header dan meta-data dibaca dari setiap .ibd berkas
  6. Struktur dan meta-data di-cache di memori
  7. Kueri terjadi
     1. Server mengakses disk dan membaca data dari .ibd yang telah dibuka berkas
     2. Server dapat meng-cache data di memori

Anda akan melihat bahwa ketika server sedang berjalan, Anda tidak dapat memindahkan file data karena server telah membuka pegangannya. Ini karena ketika dinyalakan, itu membukanya dan membiarkannya terbuka. Itu tidak membuka dan menutupnya untuk setiap kueri individual.

Dengan demikian, hanya ada beberapa operasi I/O lagi di awal, saat server dinyalakan; bukan saat sedang berjalan. Selanjutnya, sementara masing-masing individu .ibd file memiliki overhead terpisah (tanda tangan file, struktur, dll.), mereka di-cache dalam memori dan tidak dibaca ulang untuk setiap permintaan. Selain itu, struktur yang sama dibaca bahkan dengan ruang-tabel bersama, jadi hampir tidak ada (jika ada) lebih banyak memori yang diperlukan.

Apakah innodb_file_per_table berpengaruh pada kinerja mysql yang lebih baik?

Sebenarnya, jika ada, kinerjanya mungkin sebenarnya lebih buruk .

Saat menggunakan ruang-tabel bersama, operasi baca dan tulis terkadang/sering digabungkan sehingga server membaca sebagian data dari beberapa tabel sekaligus dari ibdata .

Namun, jika data tersebar di antara beberapa file, maka harus melakukan operasi I/O terpisah untuk masing-masing file.

Tentu saja ini sekali lagi sepenuhnya bergantung pada database yang bersangkutan; dampak kinerja dunia nyata akan bergantung pada ukuran, frekuensi kueri, dan fragmentasi internal ruang-tabel bersama. Beberapa orang mungkin melihat perbedaan besar sementara yang lain mungkin tidak melihat dampak apa pun.

Tablespace dibagikan pada ibdata tunggal; bagaimana tablespace khusus untuk tabel terpisah dapat menghemat ruang disk?

Itu tidak. Jika ada, itu meningkat penggunaan disk beberapa.

Saya tidak memiliki database 60GB untuk diuji, tetapi database pribadi "kecil" saya yang berisi instalasi WordPress saya dan beberapa tabel kecil untuk penggunaan pribadi dan pengujian pengembangan berbobot ~30MB saat menggunakan ruang tabel bersama. Setelah mengubahnya menjadi file-per-tabel, membengkak menjadi ~85MB. Bahkan dengan membuang semuanya dan mengimpor ulang, itu masih>60MB.

Peningkatan ini disebabkan oleh dua faktor:

• minimum absolut ukuran untuk ibdata1 adalah—untuk beberapa alasan—10 MB, bahkan jika Anda tidak memiliki apa-apa selain information_schema disimpan di dalamnya.

• Dengan ruang meja bersama, hanya ibdata1 memiliki overhead seperti tanda tangan file, meta-data, dll., tetapi dengan per-tabel, masing-masing .ibd file memiliki semua itu. Ini berarti total (bahkan dengan hipotetis <10MB ibdata1 ) akan sedikit lebih besar setidaknya:

  GetTotalSizeofOverhead() * GetNumTables()
  

Jelas ini tidak akan menjadi besar meningkat (kecuali jika Anda menggunakan host yang membatasi ukuran database Anda atau menyimpannya di flash-drive, dll.), tetapi tetap meningkat, dan sementara dengan beralih (setiap ) tabel ke file-per-tabel Anda dapat menyusutkan ibdata1 hingga 10 MB, total keseluruhan akan selalu lebih dari sebelumnya.

Inilah alasan saya SELALU menggunakan innodb_file_per_table:

Tanpa file per tabel, file ibdata tidak pernah memampatkan atau menyusut atau mengurangi ruang. Tidak saat Anda menghapus baris, menjatuhkan tabel, atau database. Data 2GB bisa menjadi file 20GB dalam waktu singkat jika Anda memiliki sistem antrian yang aktif.

Katakanlah Anda ingin membuat cadangan dari tabel 1GB Anda saat ini sebelum diubah, lalu lepas setelahnya. Anda terjebak dengan GB ruang yang sekarang tidak terpakai di ibdata Anda. Nyebelin.

Mungkin ada banyak contoh contoh di mana tindakan sementara mengembang file data tunggal, tetapi cukup untuk mengatakan bahwa menurut pendapat saya, tidak pernah ada alasan untuk TIDAK menggunakan innodb_file_per_table

Juga, inilah pos yang bagus untuk dibaca:http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table