Pertanyaan Mengapa permintaan sisipan terkadang butuh waktu lama untuk diselesaikan?


Ini masalah yang cukup sederhana. Memasukkan data ke dalam tabel biasanya berfungsi dengan baik, kecuali untuk beberapa kali di mana permintaan insert membutuhkan waktu beberapa detik. (Saya tidak mencoba memasukkan data massal.) Jadi saya menyiapkan simulasi untuk proses penyisipan untuk mencari tahu mengapa permintaan sisipan kadang-kadang membutuhkan waktu lebih dari 2 detik untuk dijalankan. Joshua menyarankan bahwa file indeks mungkin sedang disesuaikan; Saya menghapus id (field primary key), tetapi penundaan masih terjadi.

Saya memiliki tabel MyISAM: daniel_test_insert (tabel ini dimulai sama sekali kosong):

create table if not exists daniel_test_insert ( 
    id int unsigned auto_increment not null, 
    value_str varchar(255) not null default '', 
    value_int int unsigned default 0 not null, 
    primary key (id) 
)

Saya memasukkan data ke dalamnya, dan kadang-kadang permintaan insert membutuhkan waktu> 2 detik untuk dijalankan. Tidak ada yang dibaca di atas meja ini - Hanya menulis, secara serial, dengan program berulir tunggal.

Saya menjalankan kueri yang sama 100.000 kali untuk mencari tahu mengapa peluang kueri memerlukan waktu yang lama. Sejauh ini, tampaknya kejadian acak.

Kueri ini misalnya membutuhkan 4.194 detik (waktu yang sangat lama untuk menyisipkan):

Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str='afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f' - ran for 4.194 seconds
status               | duration | cpu_user  | cpu_system | context_voluntary | context_involuntary | page_faults_minor
starting             | 0.000042 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
checking permissions | 0.000024 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
Opening tables       | 0.000024 | 0.001000  | 0.000000   | 0                 | 0                   | 0                
System lock          | 0.000022 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
Table lock           | 0.000020 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
init                 | 0.000029 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
update               | 4.067331 | 12.151152 | 5.298194   | 204894            | 18806               | 477995           
end                  | 0.000094 | 0.000000  | 0.000000   | 8                 | 0                   | 0                
query end            | 0.000033 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
freeing items        | 0.000030 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
closing tables       | 0.125736 | 0.278958  | 0.072989   | 4294              | 604                 | 2301             
logging slow query   | 0.000099 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
logging slow query   | 0.000102 | 0.000000  | 0.000000   | 7                 | 0                   | 0                
cleaning up          | 0.000035 | 0.000000  | 0.000000   | 7                 | 0                   | 0

(Ini adalah versi singkat dari perintah SHOW PROFILE, saya membuang kolom yang semuanya nol.)

Sekarang pembaruan memiliki sejumlah besar sakelar konteks dan kesalahan halaman kecil. Opened_Tables meningkat sekitar 1 per 10 detik pada database ini (tidak kehabisan ruang table_cache)

Statistik:

  • MySQL 5.0.89

  • Hardware: 32 Gigs of ram / 8 core @ 2.66GHz; menyerang 10 SCSI harddisk (SCSI II ???)

  • Saya telah memiliki hard drive dan pengendali serangan bertanya: Tidak ada kesalahan yang dilaporkan. CPU sekitar 50% tidak aktif.

  • iostat -x5 (melaporkan kurang dari 10% penggunaan untuk harddisk) load laporan teratas rata-rata sekitar 10 untuk 1 menit (normal untuk mesin db kami)

  • Ruang swap memiliki 156k digunakan (32 gigs of ram)

Saya bingung untuk mencari tahu apa yang menyebabkan kelambatan kinerja ini. Ini TIDAK terjadi pada budak beban rendah kami, hanya pada master beban tinggi kami. Ini juga terjadi dengan tabel memory dan innodb. Apakah ada yang punya saran? (Ini adalah sistem produksi, jadi tidak ada yang eksotis!)


32
2017-09-15 23:05


asal


Jawaban:


Saya telah memperhatikan fenomena yang sama pada sistem saya. Pertanyaan yang biasanya membutuhkan waktu milidetik akan membutuhkan waktu 1-2 detik. Semua kasus saya sederhana, satu tabel INSERT / UPDATE / REPLACE pernyataan --- tidak pada SELECT. Tidak ada beban, penguncian, atau penumpukan benang yang terbukti.

Saya menduga itu karena membersihkan halaman-halaman kotor, menyiram perubahan ke disk, atau beberapa mutex tersembunyi, tapi saya belum mempersempitnya.

Juga Diperintah

  • Beban server - tidak ada korelasi dengan beban tinggi
  • Engine - terjadi dengan InnoDB / MyISAM / Memori
  • MySQL Query Cache - terjadi apakah aktif atau tidak aktif
  • Rotasi log - tidak ada korelasi dalam kejadian

Satu-satunya pengamatan lain yang saya miliki saat ini berasal dari fakta saya menjalankan db yang sama pada beberapa mesin. Saya memiliki aplikasi membaca yang berat jadi saya menggunakan lingkungan dengan replikasi - sebagian besar beban ada pada budak. Saya telah memperhatikan bahwa meskipun ada beban minimal pada master, fenomena ini terjadi lebih banyak di sana. Meskipun saya tidak melihat masalah penguncian, mungkin itu Innodb / Mysql mengalami masalah dengan konkurensi (thread)? Ingat bahwa pembaruan pada slave akan menjadi single threaded.

Verion MySQL 5.1.48

Memperbarui

Saya pikir saya punya petunjuk untuk masalah pada kasus saya. Pada beberapa server saya, saya melihat fenomena ini lebih dari yang lain. Melihat apa yang berbeda antara server yang berbeda, dan mengutak-atik hal-hal di sekitar, saya mengarah ke Variabel sistem innodb MySQL  innodb_flush_log_at_trx_commit.

Saya menemukan dokter agak canggung untuk membaca, tapi innodb_flush_log_at_trx_commit dapat mengambil nilai 1,2,0:

  • Untuk 1, buffer log disiram file log untuk setiap commit, dan log file di-flush ke disk untuk setiap commit.
  • Untuk 2, buffer log dibilas ke file log untuk setiap commit, dan log file di-flush ke disk kira-kira setiap 1-2 detik.
  • Untuk 0, buffer log dibilas ke file log setiap detik, dan log file di-flush ke disk setiap detik.

Secara efektif, dalam urutan (1,2,0), seperti yang dilaporkan dan didokumentasikan, Anda seharusnya mendapatkan peningkatan kinerja dalam perdagangan untuk peningkatan risiko.

Karena itu, saya menemukan bahwa server dengan innodb_flush_log_at_trx_commit=0 berkinerja buruk (yaitu memiliki 10-100 kali lebih banyak "pembaruan panjang") daripada server dengan innodb_flush_log_at_trx_commit=2. Selain itu, segala sesuatunya segera membaik pada kejadian buruk ketika saya mengubahnya menjadi 2 (perhatikan Anda dapat mengubahnya dengan cepat).

Jadi, pertanyaan saya adalah, apa tujuan Anda? Perhatikan bahwa saya tidak menyalahkan parameter ini, tetapi menyoroti bahwa konteksnya terkait dengan masalah ini.


19
2017-11-09 22:38



Saya memiliki masalah ini menggunakan tabel INNODB. (dan indeks INNODB bahkan lebih lambat untuk ditulis ulang daripada MYISAM)

Saya kira Anda melakukan beberapa pertanyaan lain pada beberapa tabel lain, sehingga masalahnya adalah MySQL harus menangani penulisan disk dalam file yang semakin besar dan perlu mengalokasikan ruang tambahan untuk file-file tersebut.

Jika Anda menggunakan tabel MYISAM, saya sangat menyarankan untuk menggunakannya

LOAD DATA INFILE 'file-on-disk' INTO TABLE `tablename` 

perintah; MYISAM sangat cepat dengan ini (bahkan dengan primary key) dan file dapat diformat sebagai csv dan Anda dapat menentukan nama kolom (atau Anda dapat menempatkan NULL sebagai nilai untuk bidang autoincrement).

Lihat dokumen MYSQL di sini.


1
2017-09-16 08:35



Tip pertama yang akan saya berikan kepada Anda, adalah menonaktifkan fungsi autocommit dan melakukan secara manual.

LOCK TABLES a WRITE;
... DO INSERTS HERE
UNLOCK TABLES;

Ini menguntungkan kinerja karena buffer indeks hanya disiram ke disk, setelah semua laporan INSERT selesai. Biasanya, akan ada banyak buffer penyangga indeks karena ada pernyataan INSERT.

Tetapi sebaiknya Anda dapat melakukannya, dan jika itu mungkin dalam aplikasi Anda, Anda melakukan penyisipan massal dengan satu pilihan.

Ini dilakukan melalui Vector Binding dan ini adalah cara tercepat yang bisa Anda lakukan.

Instead
of:
"INSERT INTO tableName values()"
DO
"INSERT INTO tableName values(),(),(),().......(n) " ,

Tetapi pertimbangkan opsi ini hanya jika pengikatan vektor parameter dimungkinkan dengan driver mysql yang Anda gunakan.

Kalau tidak, saya akan cenderung ke kemungkinan pertama dan MENGUNCI meja untuk setiap 1000 sisipan. Jangan menguncinya untuk sisipan 100k, karena Anda akan mendapatkan buffer overflow.


1
2017-09-16 08:46



Dapatkah Anda membuat satu tabel lagi dengan 400 (bukan null) kolom dan menjalankan tes Anda lagi? Jika jumlah sisipan lambat menjadi lebih tinggi ini bisa menunjukkan MySQL membuang-buang waktu menulis catatan Anda. (Saya tidak tahu cara kerjanya, tetapi ia mungkin lebih banyak menggerakkan blok, atau memindahkan sesuatu untuk menghindari fragmentasi .... benar-benar tidak tahu)


1
2017-09-22 14:37



Kami menemukan masalah yang persis sama dan dilaporkan di sini: http://bugs.mysql.com/bug.php?id=62381

Kami menggunakan 5.1.52 dan belum memiliki solusi. Kita mungkin perlu mematikan QC untuk menghindari pukulan sempurna ini.


1
2017-09-22 10:28



Baca ini di Myisam Performance: http://adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html

Pencarian untuk:

'Ukuran blok kunci MyISAM Ukuran blok kunci penting' (minus tanda kutip tunggal), ini bisa menjadi apa yang terjadi. Saya pikir mereka memperbaiki beberapa jenis masalah ini dengan 5.1


0
2017-09-21 19:49



Bisakah Anda memeriksa statistik pada subsistem disk? Apakah I / O sama? Ini terdengar seperti pekerjaan DB internal yang dilakukan pembilasan barang ke disk / log.


0
2017-09-24 15:10



Untuk memeriksa apakah disk Anda berperilaku buruk, dan jika Anda berada di Windows, Anda dapat membuat file cmd batch yang menciptakan 10.000 file:

@echo OFF
FOR /L %%G IN (1, 1, 10000) DO TIME /T > out%%G.txt

simpan dalam dir sementara, seperti test.cmd

Aktifkan ekstensi perintah yang menjalankan CMD dengan parameter / E: ON

CMD.exe /E:ON

Kemudian jalankan batch Anda dan lihat apakah waktu antara file pertama dan terakhir keluar berbeda dalam hitungan detik atau menit.

Di Unix / Linux Anda dapat menulis skrip shell similare.


0
2017-09-27 15:55



Kebetulan ada drive SSD di server? Beberapa SSD drive menderita 'studder', yang dapat menyebabkan gejala Anda.

Dalam hal apapun, saya akan mencoba untuk mengetahui apakah penundaan itu terjadi di MySQL atau di subsistem disk.

OS apa yang server Anda, dan file sistem apa yang merupakan data MySQL?


0
2017-10-02 05:29



Kami mengupgrade ke MySQL 5.1 dan selama acara ini cache Query menjadi masalah dengan banyak "Membebaskan item?" keadaan benang. Kami kemudian menghapus cache query.

Baik upgrade ke MySQL 5.1 atau menghapus cache query menyelesaikan masalah ini.

FYI, untuk pembaca selanjutnya.

-daniel


0
2017-12-09 23:02