mysql load problemi

[captainzap]

Problem: mysql load sorunu, mysql in cpu kullanımı %100 lere %200 lere çıkıyor. Sık aralıklarlada restart oluyor. Ram kullanımıda genelde %10 civarında. Aynı sunucu günde 150k hiti kaldırıyordu. Kodlamayı baştan yaptım. Yeni kodlamada gereksiz ve sorgularda vardı optimize etmemiştim, neyse bu şekilde yaklaşık 1 hafta sorun olmadan sayfa açılmalarında hiç problem yaşamadım. Daha sonra kodları biraz optimize edip tekrar sunucuya attım. Gece yarısına kadar problem olmamışken gece yarısı sunucunun loadı arttı. O günden bugünede inmiyor yapmadığım test kalmadı. Tek sorgu ile denemeler yaptım. Yine olmadı yine cpu %10-%40-%100 değerlerine kadar inip çıkıyor. Sorgularda gereksiz alanları almıyor, her sorgudan sonra free_result yapıyorum, sayfa sonunda bağlantıyı kapatıyorum. Her türlü şeyi denedim. Olmuyor. Daha iyi bir sunucudada test yaptık yine aynı şekilde oldu. Sorgularda en çok kullanılan alanlar indexli. Eski kodlamada kullanılan ve yeni kodlamada kullanmadığım indexleri bile temizledim. Bu da hiç fayda vermedi. Yani ne olduysa o gece bir şey oldu. Tabloları repair, optimize ettim sql dan ama yine olmadı. Bir sorunda görünmüyor zira. Sorun ne olabilir sizce. İçinden çıkamadım kafayı yiyeceğim.

Sayfada sadece şu örnekteki gibi bi sorgu kullanmama rağmen ne num_rows ne fetch_array çalıştırmamama rağmen yine aynı.

PHP Kodu:

$query = "SELECT `id` FROM `tablo_adi` WHERE `baslik`='$deger' AND `durum`=1";
$calistir = mysql_query($query); 


tablolarda toplam 1,5 milyona yakın kayıt var. ana tabloların birinde 800bin, diğerinde 180bin civarında kayıt var.

:/ ne olabilir problem. fikri olan var mı ?

[mustaine]

MySQL'in log dosyalarına ulaşabilirseniz oradaki mesajlara göre daha sağlıklı yorum yapabiliriz.

Ayrıca kodlarda değişiklik yaptıktan sonra problemler olmaya başladı diyorsun. Bu durumda şöyle bir yol izlenebilir;

1. MySQL server'ın konfigürasyonunda herhangi bir değişiklik yapılmış mı? Bu durumu kodlarından bağımsız olarak düşün. En son ne zaman nasıl bir değişiklik yapılmış MySQL'de.

2. MySQL'de değişiklik yapılmadıysa kodlarından herhangi birini örneklemek lazım.
Şöyle ki; eski kodlarında problem olmadan, hızlı çalışan bir kod bloğunu ve bu kodların kullandığı sql tablolarını ve tablo yapılarıyla(index'leri, kolon tanımları vs.), aynı işi yeni kodlarda yapan kod bloğunu ve bunun kullandığı tablo ve tablo yapılarıyla karşılaştıralım derim.

[darky_dark]

hostunda yapabiliyorsan bunu yapmalisin (slow query log):
http://dev.mysql.com/doc/refman/5.0/...query-log.html

ayrica log-queries-not-using-indexes parametresine dikkat; verdigin zamandan kisa bile surse index kullanilmayan sorgulari da loglar..

ayrica mysql in diger loglarina da bakmalisin; mesela error.log; orada bir seyler soyluyor olabilir..

sevgi, saygi..
_DD_

[Eretna]

yeni bir modül eklemiş olabilrmisin
örneğin sayfadaki online üyeleri gösteren bir kod.(genelde yanlış kodlamar ile mysql i en çor yoran işlemlerdir.)
yada site saldırı yiyor olabilir.
mysql i en çok kullanan kod bloklarını yada db alanını belirleyebilmem gerekli.
çünkü ilk kodlama aşamalarında çok uzatmalı kodlar yazardım ve hosting firması bilgi verirdi.
şu alanda mysql alış verişi çok fazla ve şişiyor diyorlardı.
bir yolu olmalı yani nerden kaynaklandığınu bulunabilir.
zira hosting firması bana bu detayları iletemezdi.

[captainzap]

slow_queries.log u bende baktım where olarak kullandığım alanlar index olarak tanımlı. buradaki Rows_examined nedense hep aynı sayıda çok nadir değişmiş. Rows_sent:0 da olsa Rows_examined hep aynı ? Nedir bu Rows_examined olayı ?

# Query_time: 6 Lock_time: 0 Rows_sent: 4 Rows_examined: 713550
SELECT `field1`,`field2`,`field3`,`field4` FROM `tablo` WHERE `field5`=52530 AND `field6`=1 LIMIT 0,20;

[darky_dark]

bir sekilde index kullanmadigini dusunuyorum; oracle karsiligi neredeyse full table scan e karsilik geliyor gibi.. yani aradigi veriyi bulabilmek icin butun tabloyu dolasmis gibi sanki..

mysql de de explain gibi komutlar var, bunlar uzerine yogunlasarak sorgunun nereleri kullandigini kismen gorebilirsin. (uzun zamandir mysql kullanmiyorum; dolayisiyla eksik bilgi vermis olabilirim; oracle icin boyle bir durum soz konusu oldugunda trace acip bakariz sorgumuza)

sevgi, saygi..
_DD_

[mustaine]

captainzap, mesajından alıntı:

slow_queries.log u bende baktım where olarak kullandığım alanlar index olarak tanımlı. buradaki Rows_examined nedense hep aynı sayıda çok nadir değişmiş. Rows_sent:0 da olsa Rows_examined hep aynı ? Nedir bu Rows_examined olayı ?

# Query_time: 6 Lock_time: 0 Rows_sent: 4 Rows_examined: 713550
SELECT `field1`,`field2`,`field3`,`field4` FROM `tablo` WHERE `field5`=52530 AND `field6`=1 LIMIT 0,20;

Yukarıdaki sorguyu çalıştırdığın tablonun tablo yapısı ve index'lerini gönderebilir misin?

tablo yapısı için;

describe veritabani_adi.tablo_adi;

index yapısı için;

show index from veritabani_adi.tablo_adi;

komutlarını kullanabilirsin. Bu arada yanlış hatırlamıyorsam rows_examined sorgu için arama yapılacak (okunacak satır sayısını) ifade ediyor. rows_sent değeri ise aranan koşula uyan satırları yani bizim istediğimiz sonuçların sayısıdır.

[captainzap]

tablo yapısı:

+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| field1 | int(11) | NO | PRI | | auto_increment |
| field2 | int(11) | NO | MUL | 0 | |
| field3 | int(11) | NO | MUL | 0 | |
| field4 | varchar(15) | YES | | | |
| field5 | tinyint(1) | NO | MUL | 0 | |
| field6 | tinyint(1) | NO | MUL | 0 | |
| field7 | int(10) | YES | MUL | | |
| field8 | int(10) | YES | | | |
| field9 | tinyint(1) | NO | MUL | 0 | |
| field10 | int(10) | YES | | | |
| field11 | int(11) | YES | | | |
| field12 | varchar(255) | YES | | | |
| field13 | int(10) | YES | | | |
| field14 | varchar(255) | YES | | | |
| field15 | int(11) | YES | | | |
| field16 | mediumtext | YES | | | |
+--------------+--------------+------+-----+---------+----------------+

index yapısı

-+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+---
| tablo | 0 | PRIMARY | 1 | field1 | A | 865189 | NULL | | | BTREE | |
| tablo | 1 | field3 | 1 | field3 | A | 12016 | NULL | | | BTREE | |
| tablo | 1 | field5 | 1 | field5 | A | 4 | NULL | | | BTREE | |
| tablo | 1 | field6 | 1 | field6 | A | 1 | NULL | | | BTREE | |
| tablo | 1 | field7 | 1 | field7 | A | 865189 | NULL | | YES | BTREE | |
| tablo | 1 | field9 | 1 | field9 | A | 4 | NULL | | | BTREE | |
| tablo | 1 | field2 | 1 | field2 | A | 173037 | NULL | | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+---

üstte verdiğim örneği buna göre düzenleyeyim.

SELECT `field1`,`field7`,`field16`,`field3` FROM `tablo` WHERE `field2`=52530 AND `field5`=1 LIMIT 0,20;

[mustaine]

SELECT `field1`,`field2`,`field3`,`field4` FROM `tablo` WHERE `field5`=52530 AND `field6`=1 LIMIT 0,20;

Yukarıdaki sorgu için aşağıdaki şekilde bir index yaratıp sorguyu tekrar dener misin?
alter table `veritabani`.`tablo` add index `index_adi` (`field5`, `field6`);

Yalnız denemeyi şu şekilde yap;

Eğer MySQL'e komut satırından bağlanma imkanın varsa(mysql client programıyla mesela) index'i yaratmadan önce;

explain SELECT `field1`,`field2`,`field3`,`field4` FROM `tablo` WHERE `field5`=52530 AND `field6`=1 LIMIT 0,20;

şeklinde sorgu hakkında bilgi edin. Hangi indexleri kullanacak mesela. Daha sonra sorguyu çalıştırıp son satırda ne kadar sürede çalıştığını kontrol et.

Daha sonra yukarıdaki komutla index'i yarat ama yaratırken (`field5`, `field6`) şeklindeki sıralamaya dikkat et. Sorgundaki where ifadesinden sonraki sıralamayla aynı olmalı.( WHERE `field5`=52530 AND `field6`=1 sıralaması.) index'i yarattıktan sonra explain ile tekrar sorguya bak, sorgunun kullanacağı muhtemel index'leri incele. Sorguyu çalıştırıp ne kadar zaman aldığını öncekiyle karşılaştır.

Eğer hız farkı olursa tablolarda yapacağın sorgulara göre index yapılarına tekrar bakman gerekir gibi geliyor bana.

Ayrıca şu konudaki;

http://forum.ceviz.net/100-kisi-ayni...-t51947p2.html

açıklamalara bir bak. İşine yarayabilir.

[captainzap]

öncelikle ilgilenenlere çok teşekkürler, vakit ayırdınız saolun.

mustaine biraz garip ama load olayı biraz düştü indexleri yeniden tanımladıktan sonra mı oldu ne oldu bilemiyorum bi çok işlem yaptım (: ne yaptığı bilmiyorum ama load bi saatten fazladır çok yukarılara çıkmıyor 10 ile 50 arasında dalgalanıyor. arada 70 e falanda fırlıyor tabi.

index olayını multiple index olarakta denedim ama pek farketmedi ki sadece iki alandan başka sorgularda oluyor yani tek kriterler onlar değil. neyse şu an bahsettiğimiz tablo artık slow_queries te loglanmıyor. o problemim çözüldü slow_queriesde diğer tablonun sorgularınında entrylerde olduğu gibi tüm kayıtlarda aradığını gördüm, sorguda 2 adet where değişkeni vardı teki indexliydi diğeride indexledir demiştim ama index yokmuş onada index atayınca problem ortadan kalktı sayfalar artık çok hızlı açılıyor ve load %1 e falan düştü 2-3 günüm gitti.

tekrardan ilgilenenlere teşekkürler.