Apakah beberapa indeks pada tabel MYSQL menjadi penyebab UPDATE dan INSER yang lambat?

Dec 10 2020

Kinerja situs (LAMP stack) saya telah menurun secara signifikan selama beberapa hari terakhir meskipun tidak ada pembaruan kode. Tampaknya hanya menyisipkan, memperbarui, dan menghapus tabel MySQL tertentu yang menyebabkan masalah. Setiap halaman yang memperbarui, menyisipkan, atau menghapus entri "tabel" pekerjaan membutuhkan waktu sekitar 10 detik untuk dimuat. (EG UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT kueri tampaknya dijalankan seperti sebelumnya, meskipun tampaknya lebih lambat jika pembaruan dilakukan pada waktu yang sama.

Tabel tersebut memiliki sekitar 180.000 entri. Saya perhatikan di tampilan PHPMyAdmin, bahwa selain indeks "normal" di kolom utama, ada indeks di kolom "tanggal_masuk" (lihat gambar). Mungkinkah itu menjadi masalah dalam kasus ini? Saya tidak tahu mengapa indeks di bidang itu dibuat.

Jika tidak, apa lagi yang bisa menjadi sumber masalahnya? Saya sudah memeriksa ruang di disk, yang tampaknya OK. (7 GB tersedia) menurut df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

UPDATE - Terima kasih kepada semua kontributor. Saya sangat menghargai itu. Jadi selama beberapa hari terakhir, masalah berhenti, yang membuat upaya untuk menemukan apa masalahnya mungkin menjadi lebih sulit. Tapi sekarang sepertinya sudah kembali lagi. Mari saya mulai dengan menyediakan jenis mesin yang dihosting di google cloud: g1-small (1 vCPU, memori 1,7 GB). Saya akan terus memperbarui ini dengan informasi lebih lanjut yang diminta oleh mereka yang berkomentar.

Jawaban

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

Untuk menjawab pertanyaan spesifik:

Tidak , indeks tambahan pada (entry_date)atau dua tidak akan mengganggu kinerja pembaruan titlekolom yang berbeda .

Selain itu:

Tidak , versi MySQL, 5.6, tidak terlalu kuno, meskipun beberapa fitur modern (seperti fungsi jendela) tidak ada. Anda harus memiliki kinerja yang layak dengan perangkat keras yang layak.

Kami hanya dapat berspekulasi tentang masalah yang ada tetapi apa yang mungkin salah atau menjelaskannya:

tua, perangkat keras tidak efisien: periksa spesifikasi disk, ukur dalmnya.
indeks / tabel terfragmentasi. Periksa dokumen MySQL dan pertanyaan / jawaban lama di sini tentang cara mendefrag tabel MyISAM (OPTIMALKAN TABEL).

Terakhir tapi bukan yang akhir:

Meja Anda menggunakan mesin MyISAM , yang merupakan berita lama. InnODB telah menggantikannya sebagai mesin default di MySQL beberapa tahun yang lalu. Tidak ada pengembangan aktif dari mesin ini. Itu kekurangan beberapa fitur dibandingkan dengan InnoDB (transaksi, batasan kunci asing, dll) dan kinerja di sebagian besar beban kerja . Saya sangat menyarankan Anda mengubah tabel Anda (setelah pengujian tentu saja bahwa aplikasi dan prosedur Anda tidak rusak) untuk menggunakan InnoDB.
Yang penting untuk Pertanyaan Anda, InnoDB dapat bekerja SELECTdan UPDATEpada saat yang sama (biasanya). MyISAM mengunci meja sepenuhnya; pembaruan atau pilih harus selesai sebelum pemilihan atau pembaruan bahkan dapat dimulai.
Saat beralih dari MyISAM ke InnoDB, pastikan untuk menyesuaikan key_buffer_sizedan innodb_buffer_pool_size.

3 J.D. Dec 10 2020 at 19:20

Indeks sedikit memengaruhi kinerja operasi INSERT dan DELETE, tetapi umumnya hal ini dapat diabaikan terutama jika indeks Anda direncanakan dengan baik dan Anda tidak berlebihan dengan meletakkan 30 indeks pada tabel yang sama masing-masing dengan 30 kombinasi bidang yang berbeda. (Umumnya saya tetap berpegang pada pedoman 5 kali 5 - maks 5 indeks, 5 bidang per indeks maks. Tentu saja ini hanya pedoman dan bukan aturan yang sulit).

Dengan demikian, indeks sebenarnya digunakan untuk meningkatkan kinerja kueri UPDATE dan SELECT karena indeks digunakan untuk menemukan baris ke UPDATE atau SELECT.

Perubahan drastis yang Anda lihat diragukan terkait dengan indeks entry_date yang Anda temukan (apakah Anda tahu apakah itu baru saja ditambahkan atau sudah ada sebelum perubahan kinerja?).

Dua hal yang harus Anda perhatikan adalah:

Jika indeks sebelumnya untuk kueri Anda masih digunakan, terutama untuk kueri UPDATE dan SELECT (dan jika mereka sedang dicari atau operasi pemindaian sedang terjadi sekarang). Ini dapat berubah karena perubahan statistik tabel data dari waktu ke waktu. Anda dapat menggunakan pernyataan ANALYZE untuk memeriksa statistik suatu tabel.
Hal lain yang dapat Anda perhatikan adalah fragmentasi indeks , yang merupakan kejadian alami dari waktu ke waktu. Ini terjadi karena lebih banyak data ditambahkan ke tabel. (Biasanya hal ini seharusnya tidak menjadi perhatian di meja kecil seperti milik Anda, tetapi tetap ada baiknya memeriksanya.)

Saya akan terus memperbarui jawaban saya dengan lebih banyak hal untuk dilihat saat saya memikirkannya. Menjalankan PENJELASAN atas pertanyaan Anda dapat membantu memberi petunjuk kepada Anda tentang masalah tersebut juga. Apakah mencoba untuk memisahkan pernyataan UPDATE dan INSERT yang berjalan lambat dan mencoba untuk memahami apakah mereka dipengaruhi oleh akar penyebab yang sama (karena sekali lagi mereka bertindak agak terbalik dengan indeks, INSERT sangat lambat tetapi UPDATE biasanya mendapat manfaat dari indeks yang benar dan harus lebih cepat.)