Poprawienie wydajności bazy Opcje

[Gruchol]

Witam,
Na wstępie chciałbym powiedzieć że jestem zielony jeżeli chodzi o linuxa i prosiłbym o pisanie jak najbardziej zrozumiale.
Posiadam sporą bazę MySQL która strasznie przymula.
Uruchomiłem MySQLTuner który pokazał mi coś takiego :
http://wklej.org/id/1633653/
Szukałem tych linijek w my.cnf
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
Jednak ich nie znalazłem.
Proszę o wyjaśnienie jak najprościej się da co mogę zrobić aby poprawić wydajność.
Pozdrawiam.

@edit
Dopisałem to do my.cnf
tmp_table_size = 16M
max_heap_table_size = 16M
I teraz ten komunikat na samym dole zniknął.
Co jeszcze mogę zrobić ?

Ten post edytował Gruchol 12.02.2015, 22:26:37

[aniolekx]

tak naprawdę to nie wiadomo co oznacza przymula, wiesz jak masz bazę na kiepskiej maszynie to może tu jest problem
pyzatym zawsze możesz sprawdzić jakie zapytania trwają najdłużej, może brakuje indeksów
ograniczyć liczbę zapytań do bazy stosując rożne formy cachowania

[Gruchol]

A czy istnieje jakaś aplikacja która pozwoli mi sprawdzić które zapytanie wykonuje się najdłużej ?
Co do słabej maszyny, baza stała na maszynie 6gb ramu 4x3.0 ghz i tam było praktycznie to samo.
Teraz zrobiłem tak że mysql stoi na osobnej maszynie a na osobnej stoi php5, nginx itp.
Jest trochę lepiej jednak nadal strona strasznie wolno chodzi.

[mmmmmmm]

Przejrzyj slow.log.
W pliku my.cnf masz zdefiniowane, gdzie leży:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

[Gruchol]

A co muszę zrobić aby te błędy naprawić ?
[] InnoDB is enabled but isn't being used
[] Total fragmented tables: 2

[Pyton_000]

Olać, to mówi że masz odpalone InnoDB ale nie używasz tego nigdzie i że 2 tabele mozna zoptymalizować (zdrefragmentować)

[Gruchol]

Kupiłem lepszego VPSa pod bazę (3gb ramu, 2x2,5 GHZ) i cały czas muli tak samo.
A mysqltuner pokazuje : http://wklej.org/id/1634295/
Już nie wiem co mam zrobić :/

[Pyton_000]

a co zwraca slow.log o które prosił @mmmmmmm

[b4rt3kk]

Kupiłem lepszego VPSa pod bazę (3gb ramu, 2x2,5 GHZ) i cały czas muli tak samo.
A mysqltuner pokazuje : http://wklej.org/id/1634295/
Już nie wiem co mam zrobić :/

Muli, bo musi mulić - widocznie aplikacja, która korzysta z bazy jest mało optymalnie napisana. Przedstaw jakieś przykładowe zapytania z podstrony, która najbardziej muli.

Zabrałeś się do tego, za przeproszeniem, od d... strony, kupując od razu nowy vps. Najpierw optymalizujesz zapytania, triggery, indexy, funkcje jak to nie pomaga to lepszy sprzęt.

[Gruchol]

slowlog pokazuje :
http://wklej.org/id/1634306/

Ten post edytował Gruchol 13.02.2015, 15:57:17

[Pyton_000]

To teraz zopytmalizuj te zapytanie a serwis zacznie latać.

[Gruchol]

Niestety nie znam się aż tak bardzo aby optymalizować skrypty :/
W moim pliku to zapytanie wygląda tak :

[PHP] pobierz, plaintext 
	$qry = $db->prepare ( "SELECT name, honor, guild_id, 
				(SELECT user_data.user_name FROM user_data WHERE user_data.user_id = leader_id) AS leader, 
				(SELECT Count(user_data.guild_id) FROM user_data WHERE user_data.guild_id = guilds.guild_id) AS members 
				FROM guilds 
				ORDER BY honor DESC, members DESC LIMIT :pos, 15" );
		$qry->bindParam ( ':pos', $posFrom, PDO::PARAM_INT );
		$qry->execute ();
[PHP] pobierz, plaintext 

Był bym bardzo wdzięczny gdybyś pomógł mi to zoptymalizować.

[Pyton_000]

Zerknij to:

[SQL] pobierz, plaintext 
SELECT
    g.name, g.honor, g.guild_id, u.user_name AS leader, COUNT(ug.guild_id) AS members
FROM 
    guilds g
    JOIN user_data u ON(u.user_id = g.leader_id)
    JOIN uesr_data ug USING(guild_id)
ORDER BY 
    honor DESC, members DESC
GROUP BY ug.guild_id
LIMIT :pos,15
[SQL] pobierz, plaintext 

A jak to nie zadziała to sprawdź czy masz indeksy na polach:
user_data.guild_id
guilds.guild_id
user_data.user_id

[Gruchol]

Nadal jest tak samo :/
(Tzn. aktualnie nie mam możliwość sprawdzenia czy kod działa jednak po wgraniu go muli cały czas tak samo :/ )

Ten post edytował Gruchol 13.02.2015, 16:51:24

[sazian]

[SQL] pobierz, plaintext 
EXPLAIN
SELECT name, honor, guild_id, 
(SELECT user_data.user_name FROM user_data WHERE user_data.user_id = leader_id) AS leader, 
(SELECT Count(user_data.guild_id) FROM user_data WHERE user_data.guild_id = guilds.guild_id) AS members 
FROM guilds 
ORDER BY honor DESC, members DESC LIMIT 0, 15;
[SQL] pobierz, plaintext 

pokaż wynik

[b4rt3kk]

Zerknij to:

[SQL] pobierz, plaintext 
SELECT
    g.name, g.honor, g.guild_id, u.user_name AS leader, COUNT(ug.guild_id) AS members
FROM 
    guilds g
    JOIN user_data u ON(u.user_id = g.leader_id)
    JOIN uesr_data ug USING(guild_id)
ORDER BY 
    honor DESC, members DESC
GROUP BY ug.guild_id
LIMIT :pos,15
[SQL] pobierz, plaintext 

A jak to nie zadziała to sprawdź czy masz indeksy na polach:
user_data.guild_id
guilds.guild_id
user_data.user_id

Wg mnie ten join, tylko po to, żeby zrobić counta jest tu niepotrzebny. Podzapytanie sobie lepiej poradzi.

[SQL] pobierz, plaintext 
SELECT
    g.name, g.honor, g.guild_id, u.user_name AS leader, (SELECT COUNT(ug.guild_id) FROM user_data WHERE guild_id = guilds.guild_id) AS members
FROM 
    guilds g
    JOIN user_data u ON(u.user_id = g.leader_id)
ORDER BY 
    honor DESC, members DESC
GROUP BY ug.guild_id
LIMIT :pos,15
[SQL] pobierz, plaintext 

W ogóle to trochę bez sensu, żeby robić joina tylko po to, żeby zrobić counta. Już lepszym rozwiązaniem by było:

[SQL] pobierz, plaintext 
SELECT
    g.name, g.honor, g.guild_id, u.user_name AS leader, ct AS members
FROM 
    guilds g
    JOIN user_data u ON(u.user_id = g.leader_id)
    JOIN (SELECT guild_id, COUNT(*) ct FROM user_data GROUP BY guild_id) gc ON gc.guild_id = g.guild_id
ORDER BY 
    honor DESC, members DESC
GROUP BY ug.guild_id
LIMIT :pos,15
[SQL] pobierz, plaintext 

[Pyton_000]

Może i tak, nie robiłem testów Nie chciało mi się, na szybko pisałem. Czasami wyniki mogą być różne więc każde rozwiązanie można przetestować.