Optymalizacja skryptu forum - mail od dhosting

[SN2010]

Witam

Jakiś czas temu dokonałem konwersji forum z phpbb2 do IPB. Po konwersji okazało się, że obecny serwer 1/4 dedykowany jest nie wystarczający do obsługi forum i zalecono mi zakupienie nowej usługi hostingowej - serwera dedykowanego.

Przez jakiś czas był spokój (rok, może 1.5) aż w chyba w kwietniu tego roku forum strasznie wolno się ładowało, pomimo niskich czasów generowania strony na poziomie: 0,1850 - 0,3513

Otrzymałem wówczas informację, że niektóre zapytania są niezoptymalizowane i zlecili mi ponowną optymalizację. Oczywiście, tego nie zrobiłem, twierdząc że nie poto płacę 740 zł miesięcznie za serwer, by marne forum nie mogło na nim pracować. Doszedłem do wniosku, że czas zmienić serwer - ale tutaj po rozmowie z osobami obsługującymi serwer - wykonali jakiś zabiegi i znów hulało to kolejny czas.

Po dwóch miesiącach to samo, zignorowałem już kolejną wypowiedź o nieoptymalnych zapytaniach - usterka znikneła.

W dniu wczorajszym ponownie niskie czasy generowania strony - a strona ładuje się jakby chodziła na conajmniej mizernym serwerze typu shared. - tragedia otrzymałem kilkanascie maili od Użytkowników iź nie da się pracować.

Po głębokiej analizje ze strony serwerowni otrzymałem ponownie listę zapytań które obciążają forum :

1) Nieoptymalne zapytania SQL, które powodują generowanie wysokiego obciążenia procesora oraz wiele operacji IO ze względu na użycie wielokrotnych LIKEów albo wybieranie po polu, na którym nie ma założonego poprawnie indeksu. Takie zapytania są wolne, wykonują się nawet kilka- kilkanaście sekund. Przykłady:

SELECT c . * , m . * , pc . * , pp . * , rep_index.rep_rating AS has_given_rep, rep_cache.rep_points

FROM downloads_comments c

LEFT JOIN members m ON ( m.member_id = c.comment_mid )

LEFT JOIN pfields_content pc ON ( pc.member_id = m.member_id )

LEFT JOIN profile_portal pp ON ( pp.pp_member_id = m.member_id )

LEFT JOIN reputation_index rep_index ON ( rep_index.app = 'downloads'

AND rep_index.type = 'comment_id'

AND rep_index.type_id = c.comment_id

AND rep_index.member_id =0 )

LEFT JOIN reputation_cache rep_cache ON ( rep_cache.app = 'downloads'

AND rep_cache.type = 'comment_id'

AND rep_cache.type_id = c.comment_id )

WHERE c.comment_fid =19

AND comment_open =1

ORDER BY comment_date

LIMIT 51290 , 10

(wykonanie powyższego zapytania SQL: ponad 8s)

SELECT t . * , f.name AS forum_name, f.forum_allow_rating, f.name_seo, ms.member_group_id AS starter_group_id, mss.member_group_id AS last_poster_group_id

FROM topics t

LEFT JOIN forums f ON ( f.id = t.forum_id )

LEFT JOIN members ms ON ( ms.member_id = t.starter_id )

LEFT JOIN members mss ON ( mss.member_id = t.last_poster_id )

WHERE t.tid <>26000

AND t.approved =1

AND t.forum_id

IN ( 153, 15, 16, 136, 169, 25, 20, 21, 22, 58, 33, 19, 24, 52, 208, 26, 49, 162, 50, 54, 155, 48, 55, 29, 53, 51, 27, 28, 215, 47, 30, 39, 17, 18, 57, 31, 45, 154, 42, 44, 35, 43, 41, 56, 46, 174, 32, 36, 37, 38, 60, 59, 226, 62, 61, 64, 63, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 100, 101, 102, 103, 104, 105, 124, 125, 225, 126, 127, 128, 129, 82, 83, 84, 85, 86, 87, 71, 72, 73, 74, 75, 106, 107, 108, 109, 110, 111, 130, 131, 224, 132, 133, 134, 135, 65, 66, 67, 68, 69, 70, 94, 95, 96, 97, 98, 99, 76, 77, 78, 79, 80, 81, 88, 89, 90, 91, 92, 93, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 222, 223, 156, 157, 158, 159, 160, 161, 163, 164, 165, 166, 167, 168, 170, 171, 173, 172, 209, 210, 211, 212, 213, 214, 216, 217, 218, 219, 220, 221 )

AND (

LOWER( t.title ) LIKE '%kody%'

OR LOWER( t.description ) LIKE '%kody%'

OR LOWER( t.title ) LIKE '%bdw%'

OR LOWER( t.description ) LIKE '%bdw%' OR

LOWER( t.title ) LIKE '%czyli%' OR

LOWER( t.description ) LIKE '%czyli%' OR

LOWER( t.title ) LIKE '%wstrtna%' OR LOWER( t.description ) LIKE '%wstrtna%' OR LOWER(

t.title

) LIKE '%ta%' OR LOWER(

t.description

) LIKE '%ta%' OR LOWER(

t.title

) LIKE '%kontrolka%' OR LOWER(

t.description

) LIKE '%kontrolka%' OR LOWER(

t.title

) LIKE '%silnika%' OR LOWER(

t.description

) LIKE '%silnika%') ORDER

BY rand( )

LIMIT 0 , 8

(wykonanie powyższego zapytania SQL: ponad 1s)

Oba powyższe zapytania nie używają w przynajmniej jednym wyszukaniu wydajnych indeksów co powoduje użycie wolnych tabel tymczasowych na dysku twardym.

oraz

Otrzymałem informacje, że administratorom udało się zidentyfikować przykład zapytania SQL, które wykonuje się dość długo z wykorzystaniem tabel tymczasowych (co znacznie spowalnia wykonanie zapytania):

SELECT t.*,f.name as forum_name, f.forum_allow_rating, f.name_seo,ms.member_group_id as starter_group_id,mss.member_group_id as last_poster_group_id FROM topics t LEFT JOIN forums f ON ( f.id=t.forum_id ) LEFT JOIN members ms ON ( ms.member_id=t.starter_id ) LEFT JOIN members mss ON ( mss.member_id=t.last_poster_id ) WHERE t.tid <> 4548 AND t.approved = 1 AND t.forum_id IN (153,15,16,136,169,25,20,21,22,58,33,19,24,52,208,26,49,162,50,54,155,48,55,29,53,51,27,28,215,47,30,39,17,18,57,31,45,154,42,44,35,43,41,56,46,174,32,36,37,38,60,59,226,62,61,64,63,112,113,114,115,116,117,118,119,120,121,122,123,100,101,102,103,104,105,124,125,225,126,127,128,129,82,83,84,85,86,87,71,72,73,74,75,106,107,108,109,110,111,130,131,224,132,133,134,135,65,66,67,68,69,70,94,95,96,97,98,99,76,77,78,79,80,81,88,89,90,91,92,93,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,222,223,156,157,158,159,160,161,163,164,165,166,167,168,170,171,173,172,209,210,211,212,213,214,216,217,218,219,220,221) AND (LOWER(t.title) LIKE '%astra%' OR LOWER(t.description) LIKE '%astra%' OR LOWER(t.title) LIKE '%dti%' OR LOWER(t.description) LIKE '%dti%' OR LOWER(t.title) LIKE '%ekonomia%' OR LOWER(t.description) LIKE '%ekonomia%' OR LOWER(t.title) LIKE '%jazdy%' OR LOWER(t.description) LIKE '%jazdy%') ORDER BY rand() LIMIT 0,8

Strona - http://forum.opel24.com

serwer - dhosting.pl

Proszę o Waszą opinie jako osoby biegłe w skrypcie czy taka optymalizacja w ogóle jest możliwa na tyle, że będzie ona efektywna ? Czy po prostu zmienić serwer na inną maszynę - czytałem o hetzner.de

Proszę również Waszę opinie jakiego rzędu kwota może być potrzebna na optymalizację skryptu.

Pozdrawiam

Lukasz

[Portek]

Umówmy się, to zapytanie do najlżejszych i najmniejszych nie należy. 740 PLN i nie wytrzymuje? Serio? W tej cenie polecał bym się przenieść gdzieś indziej, zapłacić jakiemuś administratorowi za konfiguracje i zabezpieczenie (usługa 100-150 PLN) i mieć spokój. Do dH w zasadzie nic nie mam, prócz faktu że nie masz dostępu do maszyny, a można by się pobawić w optymalizację serwera MySQL, czy nawet PHP. Zapytanie właściwie też można by nazwijmy to "skrócić", tyle że to zewnętrzna app, bez kodu nikt nie da rady.

@Edit

Hetzner jest spoko, ostatnio wprawdzie zaliczyli dużą wtopę, ale nadal dają radę - choć jakość juz nie ta co kiedyś.

Edytowane 31 Sierpnia 2012 przez Portek

[Macsch15]

Mógłbyś jeszcze spróbować zrobić aktualizację do najnowszej wersji, może IPS zrobił jakieś optymalizacje (w co wątpię).

Również powinieneś sprawdzić jak ma się sprawa obciążenia w trybie bezpiecznym, gdzie są wyłączone wszystkie dodatki.

Jeśli chodzi o serwer dedykowany w innej firmie, poleciłbym nephax.com

[Łukasz]

Za 300 zł masz

Procesor :

Intel Core™ i7-920 Quadcore

Pamięć :

8 GB DDR3 RAM

Dysk (HDD) :

2 x 750 GB SATA-II HDD

Transfer :

10000 GB / miesiąc

290 PLN netto / miesiąc

Albo

Procesor :

Intel Core™ i7-2600 Quadcore

Pamięć :

16 GB DDR3 RAM

Dysk (HDD) :

2 x 3000 GB SATA-III HDD 6Gb/s

Transfer :

10000 GB / miesiąc

300 PLN netto / miesiąc

Polecam strone http://serveradmin.pl/ oni Ci wszystko przeniosa z starego serwera nie masz sie o co martwic.

Ja mam u nich dedyka od ponad 2 lat zero problemów i usterek.

Edytowane 31 Sierpnia 2012 przez Łukasz

[SN2010]

Przede wszystkim dziękuje wszystkim za udział w dyskusji. Nie miałem na celu żalić się, że koszt miesięczny serwera jest za drogi, czy tani. Gdyż osobiście pełni świadom przyjąłem ofertę i zaakceptowałem tą cenę. Bardziej zależało mi na próbie optymalizacji skryptu o ile takowa jest w ogóle możliwa.

Co do serwerów - daje sobie ostatnią szansę dla DH.

Jeśli chodzi o serveradmin.pl w internecie są mieszane opinie, w dniu wczorajszym trafiłem na taką jedną i troszkę się boje ich. Generalnie szczerze mówiąc mam dość już polskich serwerowni i nie chodzi mi o kwestię ceny - gdyż wymagam więc płacę ale oto by to wszystko się kalkulowało.

[Macsch15]

Przy okazji możesz zmniejszyć sesję na np. 5 minut żeby tabela sesji trochę schudła.

Generalnie szczerze mówiąc mam dość już polskich serwerowni i nie chodzi mi o kwestię ceny - gdyż wymagam więc płacę ale oto by to wszystko się kalkulowało.

Moim zdaniem trochę przesadzasz, w PL jest dużo DC dobrej jakości - ATM, 3SF, Beyond i inne.

To, że dH nie spełnia Twoich oczekiwań, nie znaczy że wszystkie PL firmy powinieneś wykreślać.

[Portek]

Przede wszystkim dziękuje wszystkim za udział w dyskusji. Nie miałem na celu żalić się, że koszt miesięczny serwera jest za drogi, czy tani. Gdyż osobiście pełni świadom przyjąłem ofertę i zaakceptowałem tą cenę. Bardziej zależało mi na próbie optymalizacji skryptu o ile takowa jest w ogóle możliwa.

Tutaj nie chodzi o fakt że płacisz dużo czy mało, tylko że przepłacasz, nie mając też żadnego większego dostępu. Niech nawet 200zł za administracje sobie liczą, to w cenie 540zł masz w Warszawskim Atmanie ciekawą i dużo mocniejszą maszynę (oferta dla firm, przedstawiciel dla os. prywatnych http://www.onededyk.pl/ )

Jeśli chodzi o serveradmin.pl w internecie są mieszane opinie, w dniu wczorajszym trafiłem na taką jedną i troszkę się boje ich. Generalnie szczerze mówiąc mam dość już polskich serwerowni i nie chodzi mi o kwestię ceny - gdyż wymagam więc płacę ale oto by to wszystko się kalkulowało.

To Ci sami ludzie którzy obsługują linuxpl.com - opinie różne, więcej chyba negatywnych, niestety.

Wracając do optymalizacji, samo zapytanie można napisać ciut inaczej, pytanie tylko czy to coś da - o ile w ogóle coś da.