MySQL Optymalizacja - Kończy procesor

[maciej92 0]

Witam,

ostatnio zakupiłem do zobaczenia jak będzie działał RPS I w OVH. Skonfigurowałem go i postanowiłem postawić na nim jedną ze stron. I zaczęły się problemy, pierwsze atak DoS i apache nie wyrabiał i się restartował, ale to się udało opanować i jak weszli użytkownicy zaczeły się kłopoty z MySQL. Baza danych na początku miała ~250MB zmiejszyłem ja poprzez usunięcie rekodów niewidocznych dla użytkowników. Teraz ma już tylko 40MB, a i tak mysql zżera procesor.

Średnio na stronie jest ok 40-50 userów.

Tak wygląda top:

top - 13:33:21 up 1 day,  1:19,  5 users,  load average: 2.90, 3.11, 3.32
Tasks: 105 total,   1 running, 102 sleeping,   0 stopped,   2 zombie
Cpu(s): 83.1%us, 15.8%sy,  0.0%ni,  1.2%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:	499868k total,   383808k used,   116060k free,	20088k buffers
Swap:  1952692k total,	10708k used,  1941984k free,   195324k cached

USER	  PR  NI  VIRT  RES  SHR S %CPU %MEM	TIME+  COMMAND
mysql	 20   0  398m  48m 5744 S 191.2 10.0  81:41.15 mysqld
apache	20   0 36880  15m 3356 S  1.3  3.2   0:00.88 httpd
root	  20   0 95896  74m 4936 S  1.0 15.3   5:16.81 httpd

MySQL status ( troche krótko chodził ale przezprzypadek go zrestartowałem)

Uptime: 4232 Threads: 1  Questions: 60516  Slow queries: 0  Opens: 56  Flush tables: 1  Open tables: 49  Queries per second avg: 9.299

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket		  = /var/lib/mysql/mysql.sock
skip-locking
user=mysql
port			= 3306
old_passwords=1
max_connections=30
key_buffer_size = 196M
max_allowed_packet = 1M
table_open_cache = 96
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 16M
query_cache_size= 96M
query_cache_type =1
query_cache_limit=2M
thread_cache_size=16M
log-bin=mysql-bin
binlog_format=mixed
server-id	   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 48M
sort_buffer_size = 48M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[ndbd]
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
connect-string="host=localhost:1186"

Witam czy jest jakieś rozwiązanie poza rezygnacją z serwera?

[m.p 0]

1 - dodaj skip-innodb do konfiguracji mysql

2 - http://wiki.mysqltuner.com/MySQLTuner i http://www.day32.com/MySQL/tuning-primer.sh

[BlueMan 69]

3. Pamiętaj, że dyski RPS w OVH mają średnio ~2MB/s transfer :]

[maciej92 0]

BlueMan pamiętam choć chyba mam jakieś szczęscie bo za każdym testem mam ok 4MB/s przy ściąganiu wgetem srednio wychodzi 5,5MB/s

IO wait na procesorze 0,5-4%

Po zastosowaniu rad użycie procesora ok 5% choć zbyt dużo nie zrobiłem zmniejszylem tylko troche query_cache_size i dodalem skip-innodb. Tylko, że narazie te wyniki nie są miarodajne bo wtej chwili jest tylko 10os na stronie ;]

[benzydamina 1]

Mam podobny problemik z tym, że przy odkomentowaniu skip_innodb serwis mi się wysypuje (zgaduje, że korzysta z tej opcji) więc jako tako zrobić tego nie mogę. Konfiguracja standardowa podobna do konfiga macieja92. mysqltuner przeszedł mi za pierwszym i drugim razem teraz coś mu się odwidziało i nie pasuje mu rootużytkownik chociaż normalnie mogę się na niego logować choćby przez phpmyadmina. Tuning.primer wygenerował plik i w zasadzie niewiele zmienił (aby go odpalić czekałem 48 godzin po restarcie serwera mysql). Na moim sprzęcie mam akurat 2gb ramu i 4 procesory po 2.0 więc raczej nieczęsto dociąga do 50-60% jednak wykonywanie dużej ilości zapytań przez serwis trwa strasznie długo.

Sugestie?

[riklaunim 0]

Jakie zapytania? (popatrz w slowlogu). Jak coś wali LIKE lub zapytania bez indeksów to nic dziwnego że może się z tym męczyć i RAMu nie starcza.

[benzydamina 1]

top - 18:45:51 up 2 days, 19:16,  3 users,  load average: 1.67, 1.86, 2.04
Tasks: 225 total,   2 running, 223 sleeping,   0 stopped,   0 zombie
Cpu(s): 31.4%us,  1.6%sy,  0.0%ni, 64.6%id,  2.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2061592k total,  1960216k used,   101376k free,   205704k buffers
Swap:  3124600k total,     6928k used,  3117672k free,  1351056k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
26659 mysql     20   0  263m  76m 5848 S  129  3.8 464:53.66 mysqld
3834 www-data  20   0 33844 7756 3368 S    5  0.4   0:01.63 apache2

 

Tak to wygląda w chwili obecnej czyli po pamięci nie jedzie aż jakoś tragicznie (3.8%), po procku też nie, a wykonywanie zapytań trwa około50 sekund. Zaznaczam, że na innym dużo słabszym serwie do którego konfiguracji akurat nie mam dostępu przy podobnym obciążeniu wykonanie nawet większej ilości zapytań trwa 15s.

[riklaunim 0]

Jak zapytanie robi full-scan to może np. jechać po danych na dysku a nie w RAMie itd. Zobacz slowloga jakie zapytania najwolniej idą i sprawdź explainem dlaczego tak mulą (brakujący indeks, indeks się posypał itd.)

[benzydamina 1]

Nie bardzo rozumiem w tym wypadku jak sprawdzić explainem ;P

Może opisze troszkę dokładniej jak to u mnie wygląda:

- normalnie zapytanie wykonuje się szybko, problem zaczyna się przy wielokrotnym wykonaniu zapytania. Zauważyłem, że serwer tak jakby zbiera wielokrotne zapytania od userów i wywala je na raz (testowałem w ten sposób, że wywoływałem wielokrotne zapytania wielokrotnie z jakimś opóźnieniem, a wynik dostawałem prawie jednocześnie). Możliwe to jest, że maszyna zbiera z jakiegoś czasu odwołania i stara się wykonać hurtem?

Przy tej ilości zapytań ciężko mi coś wychwycić z slow loga.

Time: 090803 22:00:32

Powiedzcie mi czy 090803 to czas wykonywania?

[Miłosz 2311]

dodaj do /etc/mysql/my.cnf

 

log_slow_queries		= /var/log/mysql/mysql-slow.log

 

i przeladuj mysqla, za jakiś czas sprawdź ten plik z logiem, tam bedą zapytania które się wolno wykonują, mozesz je zoptymalizować

 

a co do Time: 090803 22:00:32 to: 09 - 2009, 08- sierpień, 03 dzień

[benzydamina 1]

To to ja akurat mam ale z tego pliku by wynikalo, ze wszystkie zapytania sa o kant dypy rozbic Przestawilem na level 4 ale to i tak daje mi od diabła zapytań. Zgaduje, że im wyższy level wpisu do loga tym dłużej się wykonuje?

Oznaczało by to, że ewidentnie coś mam do ubicia. Najwyraźniej indeksy. Na drugiej bazie mam prawie identyczny skrypt i on zdecydowanie szybciej działa. Posprawdzam i dam znać.

W każdym bądź razie wielkie dzięki za pomoc

[Miłosz 2311]

sprawdź indexy, potem długie zapytania z like i join'ami