Eşzamanlı senaryolarda MySQL sorunları ve çözümleri
1. Arkaplan
Veri tabanı sistemleri için, veri tutarlılığını sağlarken çok kullanıcılı eşzamanlılık koşullarında eşzamanlılığı iyileştirmek her zaman veri tutarlılığını sağlamak olmuştur.Yalnızca çok sayıda eşzamanlı erişimin ihtiyaçlarını karşılamakla kalmamalı, aynı zamanda bu koşul altında verilerin güvenliğini de sağlamalıdır. Bu hedefe ulaşmak için, çoğu veritabanı kilitler ve işlem mekanizmaları aracılığıyla uygulanır ve MySQL veritabanları istisna değildir. Buna rağmen, iş geliştirme sürecinde yine de çeşitli zor sorunlarla karşılaşacağız.Bu makale, ortak eşzamanlılık sorunlarını gösterecek ve vaka çalışmaları yoluyla çözümleri analiz edecektir.
2. Tablo kilidinin neden olduğu yavaş sorgu sorunu
İlk olarak, bir kullanıcı bilgisini kimliğe göre sorgulamak için basit bir duruma bakalım:
mysql > id = 6 olan kullanıcıdan * seçin;
Bu tablodaki toplam kayıt sayısı 3'tür, ancak yürütülmesi 13 saniye sürdü.
Bu tür bir sorun ortaya çıktığında, ilk düşüncemiz mevcut MySQL işlem durumuna bakmaktır:
İşlemden, select deyiminin bir tablo kilidi için beklediği görülebilir, bu durumda bu tablo kilidini hangi sorgu oluşturdu? Bu sonuç doğrudan bir ilişki göstermez, ancak çoğunun güncelleme ifadesi tarafından oluşturulduğu sonucuna varabiliriz (çünkü işlemde başka şüpheli SQL yoktur). Tahminimizi onaylamak için önce kullanıcı tablosu yapısını kontrol edin:
Yeterince, kullanıcı tablosu MyISAM depolama motorunu kullanıyor, MyISAM işlemi gerçekleştirmeden önce bir tablo kilidi oluşturacak ve işlem tamamlandıktan sonra otomatik olarak kilidi açacaktır. İşlem bir yazma işlemi ise, masa kilidi tipi bir yazma kilididir ve işlem bir okuma işlemi ise, masa kilidi tipi bir okuma kilididir. Anladığınız gibi, yazma kilidi tüm işlemleri seri hale getiren diğer işlemleri (okuma ve yazma dahil) engelleyecektir ve okuma kilidi durumunda okuma-okuma işlemleri paralel olabilir, ancak okuma-yazma işlemleri hala seridir. Aşağıdaki örnek, tablo kilidi (okuma kilidi), paralel okuma-okuma ve seri okuma-yazma özelliğinin açıkça belirtildiği durumu gösterir.
Tablo kilitlerini açıkça açmak / kapatmak için, kilit tablosu kullanıcı okuma / yazma, tabloların kilidini açma;
sezon 1:
oturum2:
1. oturumun okuma işlemlerini gerçekleştirmek için tablo kilidinin (okuma kilidi) etkinleştirdiğini görebilirsiniz Bu anda, 2. oturum okuma işlemlerini paralel olarak gerçekleştirebilir, ancak yazma işlemleri engellenmiştir. Ardından şuna bakın:
sezon 1:
oturum2:
Oturum1'in kilidi açıldığında, seesion2 hemen yazma işlemlerini yani seri okuma-yazma işlemlerini gerçekleştirmeye başlar.
sonuç olarak:
Şimdiye kadar sorunun nedenini net bir şekilde analiz ettik ve özetledik - MyISAM depolama motoru, işlemleri gerçekleştirirken tablo kilitleri oluşturacak ve bu da diğer kullanıcıların tablodaki işlemlerini etkileyecektir. Tablo kilidi bir yazma kilidi ise, diğer kullanıcıların bir dizeyi çalıştırmasına neden olacaktır. Tamam, eğer bu bir okuma kilidi ise, diğer kullanıcıların okuma işlemleri paralel olabilir. Bu yüzden bazen durumun böyle olup olmadığını görmek uzun süren basit bir sorguyla karşılaşırız.
Çözüm:
1) MyISAM depolama motorunu kullanmamaya çalışın.MySQL 8.0'da, MyISAM depolama motorunun tüm tabloları kaldırılmıştır. InnoDB depolama motoru önerilir.
2) MyISAM depolama motorunu kullanmanız gerekiyorsa, işlemlerin yazılma süresini azaltın;
3. Tablo yapısının çevrimiçi olarak değiştirilmesinin riskleri nelerdir?
Bir gün iş sisteminin bir alanın uzunluğunu artırması gerekiyorsa, doğrudan çevrimiçi olarak değiştirilebilir mi? Bu soruyu cevaplamadan önce bir vakaya bakalım:
Yukarıdaki ifade, kullanıcı tablosunun ad alanının uzunluğunu değiştirmeye çalışır ve ifade engellenir. Her zamanki gibi, mevcut süreci kontrol ediyoruz:
İşlemden, alter ifadesinin bir meta veri kilidi beklediği ve bu meta veri kilidinin büyük olasılıkla yukarıdaki select ifadesinden kaynaklandığı görülebilir. DML (seçme, güncelleme, silme, ekleme) işlemlerini gerçekleştirirken, tabloya bir meta veri kilidi eklenir.Bu meta veri kilidi, tablo yapısının sorgu sırasında değiştirilmemesini sağlamak içindir, böylece yukarıdaki alter ifadesi engellenecektir. Öyleyse, yürütme sırası tersine çevrilirse, önce alter ifadesini yürütün, ardından DML ifadesini yürütün. DML ifadeleri engellenecek mi? Örneğin, bir çevrimiçi ortamda tablo yapısını değiştiriyorum Çevrimiçi DML ifadeleri engellenecek mi? Cevap: emin değilim.
Çevrimiçi ddl işlevi, MySQL 5.6'dan beri sağlanmıştır ve bazı DDL ifadelerinin ve DML ifadelerinin eşzamanlı olmasına izin verir Mevcut sürüm 5.7'de, çevrimiçi ddl, çoğu DDL işleminin çevrimiçi olarak gerçekleştirilmesine izin verecek şekilde geliştirilmiştir. Ayrıntılar için lütfen şu adrese bakın: https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
Bu nedenle, belirli bir senaryo için DDL yürütme sürecinde, DML'nin engellenip engellenmeyeceği senaryoya bağlıdır.
Özet: Bu örnek aracılığıyla, meta veri kilidi ve çevrimiçi ddl hakkında temel bir anlayışa sahibiz. İş geliştirme sürecinde tablo yapısını çevrimiçi olarak değiştirmemiz gerekirse, aşağıdaki çözümlere başvurabiliriz:
1. İş hacminin küçük olduğu zamanlarda yapmaya çalışın;
2. Resmi belgeleri kontrol edin ve yapılacak tablo değişikliğinin DML ile eşzamanlı olabileceğini ve çevrimiçi işi engellemeyeceğini onaylayın;
3. Resmi çevrimiçi ddl tarafından daha güçlü olan percona'nın pt-çevrimiçi-şema değiştirme aracının kullanılması önerilir. Temel ilkesi: insert ... select ... ifadeleri aracılığıyla tam bir kopya yapılır ve tablo yapısı tetikleyiciler aracılığıyla kaydedilir Tablo yapısı değişikliğinin amacına ulaşmak için değişim sürecinde üretilen artış.
Örneğin, Tablo A'da değişiklik yapmak için ana adımlar şunlardır:
Hedef tablo yapısının boş bir tablosunu oluşturun, A_new;
Tetikleyicileri ekleme, silme ve değiştirme dahil tablo A'da tetikleyiciler oluşturun;
Verileri hedef tabloya kopyalayın: insert ... select ... limit N deyimi
Kopyalama tamamlandıktan sonra, A_new tablosunu tablo A olarak yeniden adlandırın.
4. Bir kilitlenme sorununun analizi
Çevrimiçi ortamda ara sıra kilitlenme sorunu ortaya çıkar Bir kilitlenme, iki veya daha fazla işlemin birbirinin kilidi açmasını beklediği ve işlemin asla sonlandırılamayacağı bir durumla sonuçlanan bir durumdur. Sorunu analiz etmek için basit bir kilitlenme durumunu simüle edeceğiz ve ardından ondan bazı analiz fikirlerini özetleyeceğiz.
Demo ortamı: MySQL5.7.20 İşlem yalıtım düzeyi: RR
Tablo kullanıcısı:
TABLO OLUŞTUR "KULLANICI" (
`ID` INT (11) NULL DEĞİL AUTO_INCREMENT,
`NAME` VARCHAR (300) VARSAYILAN NULL,
"AGE" INT (11) VARSAYILAN NULL,
BİRİNCİL ANAHTAR ("ID")
) MOTOR = INNODB AUTO_INCREMENT = 5 DEFAULT CHARSET = UTF8
Aşağıda işlem 1, işlem 2'nin çalışması gösterilmektedir:
Bu basit bir kilitlenme senaryosudur. İşlem 1 ve işlem 2, birbirlerinin kilidi açmasını bekler. InnoDB depolama motoru, kilitlenmenin gerçekleştiğini algılar ve işlem 2'yi geri alır. Bu, işlem 1'in artık B işleminin kilidini beklememesini sağlar ve devam edebilir gerçekleştirillen. Peki InnoDB depolama motoru, kilitlenmeleri nasıl tespit eder? Bu sorunu anlamak için öncelikle şu anda InnoDB'nin durumunu kontrol ediyoruz:
motor innodb durumunu göster
------------------------
EN SON TESPİT EDİLEN DEADLOCK
------------------------
2018-01-1412:17:130x70000f1cc000
*** (1) İŞLEM:
TRANSACTION 5120, ACTIVE 17 sn başlatma indeksi okuma
kullanımda mysql tabloları 1, kilitli 1
LOCK WAIT 3 kilit yapı (lar), yığın boyutu 1136, 2 sıra kilit (ler)
MySQL iş parçacığı kimliği 10, işletim sistemi iş parçacığı tutamacı 123145556967424, sorgu kimliği 2764 localhost kök güncellemesi
kullanıcı seti adını güncelle = 'haha' burada id = 4
*** (1) BU KİLİDİN VERİLMESİNİ BEKLEME:
KAYIT KİLİTLERİ boşluk kimliği 94 sayfa no 3 n bit 80 dizin PRIMARY 'test`.`user` trx id 5120 lock_mode X kaydı kilitler ancak boşluk beklemiyor
Kayıt kilidi, 5 numaralı yığın FİZİKSEL KAYIT: n_fields 5; kompakt format; bilgi bitleri 0
0: len 4; hex 80000004; artan ;;
1: len 6; onaltılık 0000000013fa; artan ;;
2: len 7; hex 520000060129a6; asc R) ;;
3: len 4; hex 68616861; asc haha ;;
4: len 4; altıgen 80000015; artan ;;
*** (2) İŞLEM:
TRANSACTION 5121, ACTIVE 12 sn başlangıç indeksi okuma
kullanımda mysql tabloları 1, kilitli 1
3 kilit yapısı, yığın boyutu 1136, 2 sıralı kilit (ler)
MySQL iş parçacığı kimliği 11, işletim sistemi iş parçacığı tutamacı 123145555853312, sorgu kimliği 2765 localhost kök güncellemesi
kullanıcı seti adını güncelle = 'hehe' burada id = 3
*** (2) KİLİTLERİ TUTAR:
KAYIT KİLİTLERİ boşluk kimliği 94 sayfa no 3 n bit 80 dizin PRIMARY 'test`.`user` trx id 5121 kilit_modu X kaydı kilitler ancak boşluğu değil
Kayıt kilidi, 5 numaralı yığın FİZİKSEL KAYIT: n_fields 5; kompakt format; bilgi bitleri 0
0: len 4; hex 80000004; artan ;;
1: len 6; onaltılık 0000000013fa; artan ;;
2: len 7; hex 520000060129a6; asc R) ;;
3: len 4; hex 68616861; asc haha ;;
4: len 4; altıgen 80000015; artan ;;
*** (2) BU KİLİDİN VERİLMESİNİ BEKLEME:
KAYIT KİLİTLERİ boşluk kimliği 94 sayfa no 3 n bit 80 dizin PRIMARY 'test`.`user` trx id 5121 kilit_modu X kaydı kilitler ancak boşluk beklemiyor
Kayıt kilidi, 7 numaralı yığın FİZİKSEL KAYIT: n_fields 5; kompakt format; bilgi bitleri 0
0: len 4; hex 80000003; artan ;;
1: uzunluk 6; onaltılık 0000000013fe; artan ;;
2: len 7; hex 5500000156012f; artan U V / ;;
3: len 4; altıgen 68656865; asc hehe ;;
4: len 4; altıgen 80000014; artan ;;
*** GERİ DÖNÜŞÜM İŞLEMİ (2)
InnoDB durumunun birçok göstergesi var Burada kilitlenme ile ilgili bilgileri yakalıyoruz. InnoDB'nin en son kilitlenme bilgilerini verebildiği görülebilir.Aslında, birçok kilitlenme izleme aracı da bu fonksiyona göre geliştirildi.
Kilitlenme bilgilerinde, kilitleri bekleyen iki işlemle ilgili bilgiler görüntülenir (mavi işlem 1'i temsil eder, yeşil işlem 2'yi temsil eder), şu konulara odaklanır: BU KİLİT VERİLMESİ BEKLENİR ve KİLİT (LER) TUTULUR.
BU KİLİDİN VERİLMESİ BEKLENMESİ mevcut işlemin beklediği kilit bilgisini gösterir.İşlem 1'in 5 yığın no'lu bir satır kilidi beklediği ve işlem 2'nin 7 yığın no.lu bir satır kilidini beklediği çıkıştan görülebilir;
KİLİT (LER) TUTAR: Mevcut işlem tarafından tutulan kilit bilgisini temsil eder.Çıktıdan işlem 2'nin 5 satırlı kilit için bir yığın no tuttuğu görülebilir.
InnoDB'nin sonunda 2. işlemi geri aldığı çıktıdan görülebilir.
Peki InnoDB kilitlenmeleri nasıl tespit ediyor?
En basit yol, bir işlemin kilit beklemesi durumunda, bekleme süresinin belirlenen eşiği aşması durumunda işlemin başarısız olması, birden fazla işlemin uzun süre birbirini beklemesi durumunun önüne geçmektir. İnnodb_lock_wait_timeout parametresi, kilit bekleme süresini ayarlamak için kullanılır.
Bu yöntemi uygularsanız, kilitlenmeyi çözmek zaman alır (yani, innodb_lock_wait_timeout tarafından belirlenen eşikten daha fazlasını bekleyin) Bu yöntem biraz pasiftir ve sistem performansını etkiler InnoDB depolama motoru, kilitlenme sorununu çözmek için daha iyi bir algoritma sağlar. - grafik algoritması için. Basitçe ifade etmek gerekirse, birden fazla işlem birbirini beklemeye başladığında, bekleme grafiği algoritması etkinleştirilir.Algoritma bunun bir kilitlenme olduğunu belirledikten sonra, işlemlerden biri anında geri alınır ve kilitlenme kaldırılır. Bu yöntemin avantajı, denetimin daha aktif olması ve bekleme süresinin kısa olmasıdır.
Bekleme grafiği algoritmasının temel ilkesi aşağıdadır:
Anlama kolaylığı için, kilitlenmeyi 4 arabanın birbirini engellediği bir senaryo olarak görüyoruz:
4 araba birbirinin kilitlenmesini bekleyen 4 işlem olarak kabul edilir ve kilitlenmeye neden olur. Bekleme grafiği algoritmasının prensibi, işlemleri düğümler olarak ele almaktır ve işlemler arasındaki kilit bekleme ilişkisi yönlendirilmiş kenarlarla temsil edilir.Örneğin, A işlemi B işleminin kilidini beklerse, A düğümünden B düğümüne yönlendirilmiş bir kenar çizin. A, B, C ve D'den oluşan yönlendirilmiş bir grafik bir halka oluşturur ve bir kilitlenme olarak değerlendirilir. Bekle grafik algoritmasının temel ilkesi budur.
sonuç olarak:
1. İş geliştirmemizde bir kilitlenme olup olmadığını nasıl kontrol edebilirim? InnoDB durumunu izleyerek, daha sonra kolay gözden geçirme için kilitlenme kayıtlarını toplamak için küçük bir araç yapabileceğinizi yeni tanıttım.
2. Bir kilitlenme olursa, iş sistemi nasıl yanıt vermelidir? Yukarıdan, InnoDB bir kilitlenme tespit ettiğinde, kilitlenmeye çalışırken tespit edilen bir Kilitlenme rapor ettiğini görebiliriz; işlem mesajını istemciye yeniden başlatmayı deneyin ve işlemi geri alır.Bu bilgi için uygulama tarafının işlemi yeniden başlatması gerekir. Ve sonraki kilitlenmeyi önlemek için daha sonra daha fazla analiz için site günlüğünü kaydedin.
5. Kilit bekleme probleminin analizi
İş geliştirmede, kilitlenme olasılığı nispeten küçüktür, ancak kilit bekleme olasılığı yüksektir.Kilit bekletme, bir işlemin kilit kaynaklarını uzun süre kullanması ve diğer işlemlerin kilidi açmak için önceki işlemin beklemesidir.
Yukarıdan, işlem 1'in id = 3 ile satır kilidini uzun süre tuttuğu, işlem 2'nin kilit bekletme ürettiği ve bekleme süresi innodb_lock_wait_timeout'u geçtikten sonra işlemin kesildiği, ancak işlemin geri alınmadığı görülebilir. İş geliştirmemizde kilit bekleme ile karşılaşırsak, bu sadece performansı etkilemekle kalmayacak, aynı zamanda iş sürecinizi de zorlayacaktır, çünkü iş tarafınızın işlemi yeniden denemek veya işlemi geri almak için kilit bekleme durumunun mantığını uyarlaması gerekir. .
MySQL meta veri tablosunda, information_schema veritabanında bulunan INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WAITS gibi işlemler ve kilit bekleme durumları hakkında bilgiler toplanır ve bu tablolar üzerinden iş sisteminizin kilit bekleme durumunu gözlemleyebilirsiniz. İşlem ve kilit beklemesi arasındaki ilişkiyi rahatça sorgulamak için aşağıdaki ifadeyi de kullanabilirsiniz:
R.TRX_ID WAITING_TRX_ID SEÇİN,
R.TRX_MYSQL_THREAD_ID WAITING_THREAD,
R.TRX_QUERY WATING_QUERY,
B.TRX_ID BLOCKING_TRX_ID,
B.TRX_MYSQL_THREAD_ID BLOCKING_THREAD,
B.TRX_QUERY BLOCKING_QUERY
INFORMATION_SCHEMA.INNODB_LOCK_WAITS W'DEN
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX B ON B.TRX_ID = W.BLOCKING_TRX_ID
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX R ON R.TRX_ID = W.REQUESTING_TRX_ID;
sonuç:
wait_trx_id: 5132
wait_thread: 11
wating_query: kullanıcı seti adını güncelle = 'hehe' burada id = 3
blocking_trx_id: 5133
blocking_thread: 10
blocking_query: NULL
sonuç olarak:
1. Lütfen mevcut veritabanı kilit durumunu anlamanıza ve iş prosedürlerini optimize etmenize yardımcı olacak iş sistemi kilit bekleme durumunuzu izleyin;
2. İş sisteminde kilit bekleme zaman aşımı için uygun mantıksal kararlar verilmelidir.
