Jak czytac "mysqldumpslow", problem z szybkoscia

[mke 0]

Hejka, na wstepie powiem ze jestem swiezy wiec prosze o lopatologiczny dobor slow

 

Mam vps, testuje jedna strone oparta na Joomli i komponencie ogloszeniowym. Wszystko dzialalo w miare ok dopoki nie wrzucilem do bazy wiekszej ilosci ogloszen. Uzycie CPU przez mysqld skacze momentami znacznie(patrz rezultat "top") kiedy proboje np wyswietlic prosta strone z kilkunastoma rezulatatami. Byc moze jest to wina zle napisanego komponentu, byc moze cos jest nie tak z baza (probowalem juz optymalizacji i naprawy wszystkich tabel w phpmyadmin), byc moze servera lub konfiguracji.

 

 

rezulatat polecenia "top" (przy niemal zerowym ruchu, tylko ja)

 

postanowilem zapisywac w logach wolne zapytania (powyzej 1 sek) oto wynik mysqldumpslow ktorego do konca nie rozumiem, tzn nie wiem jakie wnioski mam z tego wyciagnac, zastanawiam sie czy np Rows_sent: 49 Rows_examined: 161357 to cos normalnego w sensie liczbowym.

 

 

[root@vps ~]# mysqldumpslow /var/log/wolne_zapytania.log

 

Reading mysql slow query log from /var/log/wolne_zapytania.log

Count: 34 Time=2.18s (74s) Lock=0.00s (0s) Rows=49.0 (1666), uk146[uk146]@localhost

SELECT cat.title, cat.id, (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N

 

Count: 3 Time=2.00s (6s) Lock=0.00s (0s) Rows=3291.0 (9873), uk146[uk146]@localhost

SELECT a.*,concat(a.id,'S',a.alias) as id,c.title as country,cat.title as category,r.title as region,u.email as uemail,u.username,u.id as uid,(select image from jos_gbl_ads_images where published='S' and ad_id=a.id order by ordering asc limit N) as image,(select extension from jos_gbl_ads_images where published='S' and ad_id=a.id order by ordering asc limit N) as extension

from jos_gbl_ads as a

LEFT JOIN jos_gbl_countries as c on c.id=a.country_id LEFT JOIN jos_gbl_categories as cat on cat.id=a.category_id LEFT JOIN jos_gbl_regions as r on r.id=a.region_id LEFT JOIN jos_users as u on u.id=a.user_id

WHERE a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND a.country_id in (N) AND a.title RLIKE 'S' AND cat.access<=N AND cat.published=N GROUP BY a.id ORDER BY a.created_date desc ,a.ordering

 

 

a moj my.cnf

 

 

[mysqld]

 

#moje zmiany

long_query_time=1

log_slow_queries=/var/log/wolne_zapytania.log

 

 

set-variable=local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

 

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment

# these lines and adjust the connectstring as needed.

#ndbcluster

#ndb-connectstring="nodeid=4;host=localhost:1186"

 

skip-bdb

 

set-variable = innodb_buffer_pool_size=2M

set-variable = innodb_additional_mem_pool_size=500K

set-variable = innodb_log_buffer_size=500K

set-variable = innodb_thread_concurrency=2

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

skip-bdb

 

set-variable = innodb_buffer_pool_size=2M

set-variable = innodb_additional_mem_pool_size=500K

set-variable = innodb_log_buffer_size=500K

"/etc/my.cnf" 48L, 1340C 6,0-1 Top

[kafi 2425]

Wykonaj sobie te zapytania przez monitor SQL / phpMyAdmin, poprzedzając SELECT słówkiem EXPLAIN

Tam zobaczysz, czemu MySQL robi ci fullrowsearch.

 

Najprawdopodobniej warto było by na klucze obce ponakładać jakieś indeksy...

 

Rows_sent: 49 Rows_examined: 161357 to cos normalnego w sensie liczbowym.

Oznacza to mniej więcej tyle, że silnik bazy danych do wyplucia niecałych 50 rekordów musiał przeczesać w pełni analizując ponad 161 tysięcy rekordów.

 

Za bardzo optymalne to nie jest.

Może warto zrobić kilka prostych zapytań zamiast takiej kolumbryny?

[ahes 83]

1. po pierwsze zrób indeksy, które będą wykorzystywane przy tym zapytaniu
2. po drugie skonfiguruj sobie bufory na indeksy: key_buffer dla MyISAM i innodb_buffer_pool_size dla innodb.

Poczytaj też moje wypociny uczelniane o optymalizacji. Przyda ci się.

http://marcinhlybin.com/docs/Badanie_wydajnosci_i_optymalizacja.pdf

[mke 0]

1. po pierwsze zrób indeksy, które będą wykorzystywane przy tym zapytaniu

 

Wlasnie przeczytalem ten rozdzial i mysle ze rozumiem dlaczego powinienem go miec, tylko jak zrobic te indexy?

 

Oznacza to mniej więcej tyle, że silnik bazy danych do wyplucia niecałych 50 rekordów musiał przeczesać w pełni analizując ponad 161 tysięcy rekordów

 

 

Bardzo dziwne bo np. polecenie

 

SELECT cat.title, cat.id, (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N

korzysta z dwoch tabel jos_gbl_ads as i jos_gbl_categories w ktorych lacznie jest nie wiecej niz 3500 rekordow

[kafi 2425]

Ad2. Pewnie dlatego, że dla każdego selecta nadrzędnego

SELECT cat.title, cat.id ... FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N

wykonuje ci pełny rowsearch - czyli dla każdego wiersza mieli od nowa skorelowane zapytanie

 (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount 

Bo musisz pamiętać, że dla każdego wiersza NOW() będzie zwracało inną wartość (różniącą się o bardzo niewiele) i nijak tego się nie zoptymalizuje.

 

Tak jak pisałem - EXPLAIN SELECT powinno pokazać ci, jakie operacje wykonuje silnik, żeby wypluć ci rekordy.

[Gość squeezer]

Wlasnie przeczytalem ten rozdzial i mysle ze rozumiem dlaczego powinienem go miec, tylko jak zrobic te indexy?

 

 

Przykład tego, jak możesz to zrobić znajdziesz na przykład na poniższej stronie:

 

http://blog.ksiazek....rk-i-profiling/

 

Znajdziesz tam też wyjaśnienie, dlaczego wszyscy proszą Cię o podesłanie wyniku EXPLAIN SELECT.... Bez konkretnego planu dla Twojego zapytania, można tylko teoretyzować na temat ewentualnych zmian.

 

 

Bardzo dziwne bo np. polecenie

 

SELECT cat.title, cat.id, (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N

 

korzysta z dwoch tabel jos_gbl_ads as i jos_gbl_categories w ktorych lacznie jest nie wiecej niz 3500 rekordow

 

Dlaczego tak się dzieje, napisał Fercio. Nie zgodzę się tylko z nim w tym, że nie da się tego zoptymalizować. Są szanse, że uda się na tyle przyspieszyć podzapytanie, że tego typu konstrukcja będzie miała sens pod względem wydajności. Podeślij wyniki:

 

EXPLAIN SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N);

 

EXPLAIN SELECT cat.title, cat.id, (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N;

[mke 0]

wynik mysqltuner

 

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

 

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.79-log

[OK] Operating on 32-bit architecture with less than 2GB RAM

 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 100M (Tables: 1232)

[--] Data in InnoDB tables: 2M (Tables: 153)

[--] Data in MEMORY tables: 0B (Tables: 1)

[!!] Total fragmented tables: 97

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 20h 17m 55s (1M q [24.856 qps], 17K conn, TX: 961M, RX: 302M)

[--] Reads / Writes: 97% / 3%

[--] Total buffers: 27.0M global + 2.7M per thread (100 max threads)

[OK] Maximum possible memory usage: 295.7M (57% of installed RAM)

[OK] Slow queries: 0% (55/1M)

[OK] Highest usage of available connections: 8% (8/100)

[OK] Key buffer size / total MyISAM indexes: 8.0M/28.8M

[OK] Key buffer hit rate: 99.7% (41M cached / 133K reads)

[!!] Query cache is disabled

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 492K sorts)

[!!] Joins performed without indexes: 5185

[OK] Temporary tables created on disk: 20% (4K on disk / 24K total)

[!!] Thread cache is disabled

[!!] Table cache hit rate: 0% (64 open / 21K opened)

[OK] Open file limit used: 11% (121/1K)

[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

[!!] InnoDB data size / buffer pool: 2.5M/2.0M

 

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Adjust your join queries to always utilize indexes

Set thread_cache_size to 4 as a starting value

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (>= 8M)

join_buffer_size (> 128.0K, or always use indexes with joins)

thread_cache_size (start at 4)

table_cache (> 64)

innodb_buffer_pool_size (>= 2M)

 

 

a teraz wynik mysqlreport

 

Use of uninitialized value in multiplication (*) at ./mysqlreport line 829.

Use of uninitialized value in formline at ./mysqlreport line 1227.

MySQL 5.0.79-log uptime 0 20:43:30 Sat Aug 14 19:07:10 2010

 

__ Key _________________________________________________________________

Buffer used 2.45M of 8.00M %Used: 30.61

Current 2.87M %Usage: 35.94

Write hit 90.89%

Read hit 99.67%

 

__ Questions ___________________________________________________________

Total 1.85M 24.8/s

DMS 1.76M 23.6/s %Total: 95.15

Com_ 81.14k 1.1/s 4.38

COM_QUIT 17.88k 0.2/s 0.97

-Unknown 9.21k 0.1/s 0.50

Slow 1 s 56 0.0/s 0.00 %DMS: 0.00 Log: ON

DMS 1.76M 23.6/s 95.15

SELECT 1.71M 22.9/s 92.46 97.18

UPDATE 20.93k 0.3/s 1.13 1.19

DELETE 12.87k 0.2/s 0.70 0.73

REPLACE 10.11k 0.1/s 0.55 0.57

INSERT 5.84k 0.1/s 0.32 0.33

Com_ 81.14k 1.1/s 4.38

set_option 43.16k 0.6/s 2.33

change_db 23.97k 0.3/s 1.29

admin_comma 9.59k 0.1/s 0.52

 

__ SELECT and Sort _____________________________________________________

Scan 316.73k 4.2/s %SELECT: 18.51

Range 170.16k 2.3/s 9.94

Full join 5.33k 0.1/s 0.31

Range check 0 0/s 0.00

Full rng join 0 0/s 0.00

Sort scan 451.61k 6.1/s

Sort range 43.64k 0.6/s

Sort mrg pass 0 0/s

 

__ Table Locks _________________________________________________________

Waited 79 0.0/s %Total: 0.00

Immediate 1.77M 23.7/s

 

__ Tables ______________________________________________________________

Open 64 of 64 %Cache: 100.00

Opened 26.31k 0.4/s

 

__ Connections _________________________________________________________

Max used 8 of 100 %Max: 8.00

Total 17.88k 0.2/s

 

__ Created Temp ________________________________________________________

Disk table 5.00k 0.1/s

Table 19.98k 0.3/s Size: 32.0M

File 5 0.0/s

 

__ Threads _____________________________________________________________

Running 1 of 1

Cached 0 of 0 %Hit: 0.01

Created 17.88k 0.2/s

Slow 0 0/s

 

__ Aborted _____________________________________________________________

Clients 5 0.0/s

Connects 88 0.0/s

 

__ Bytes _______________________________________________________________

Sent 981.93M 13.2k/s

Received 309.51M 4.1k/s

 

__ InnoDB Buffer Pool __________________________________________________

Usage 2.00M of 2.00M %Used: 100.00

Read hit 97.70%

Pages

Free 0 %Total: 0.00

Data 127 99.22 %Drty: 0.00

Misc 1 0.78

Latched 0.00

Reads 70.51k 0.9/s

From file 1.62k 0.0/s 2.30

Ahead Rnd 0 0/s

Ahead Sql 0 0/s

Writes 5.65k 0.1/s

Flushes 159 0.0/s

Wait Free 0 0/s

 

__ InnoDB Lock _________________________________________________________

Waits 0 0/s

Current 0

Time acquiring

Total 0 ms

Average 0 ms

Max 0 ms

 

__ InnoDB Data, Pages, Rows ____________________________________________

Data

Reads 1.63k 0.0/s

Writes 1.23k 0.0/s

fsync 1.13k 0.0/s

Pending

Reads 0

Writes 0

fsync 0

 

Pages

Created 18 0.0/s

Read 1.62k 0.0/s

Written 159 0.0/s

 

Rows

Deleted 28 0.0/s

Inserted 45 0.0/s

Read 19.05k 0.3/s

Updated 1.01k 0.0/s

 

 

 

Jak dobrze rozumiem wg tego przewodnika http://hackmysql.com/mysqlreportguide

 

1) wyglada ze poziom "key buffer" jest ok

2) w sekcji "Questions" brakuje mi "QC Hits", rezulat niewlaczonego cache

3) widac ze moj sever jest "SELECT heavy"

4) wysoki poziom "scan" czyli czesto przeszukiwana jest cala tabela, "full join" tez tak sobie, nie wiem co oznacza duza wartosc "range"

5) brakuje "Query Cache Report"

6) Locks Report - ok

7) tables report - zwiekszyc table_cache system ?

8) Connections Report - wyglada ok

9) Created Temp Report - wyglada ok

10) InnoDB Buffer Pool Report -

buffer pool space - uzycie 100% niedobrze

Buffer Pool Read hit - 97,7%, za malo?

11) InnoDB Lock Report - ok

12) inoDB Data, Pages, Rows Report - ok?

 

 

 

Mozna probowac optymalizowac config, jakie ustawienia i wartosci radzicie?

 

Problem z tymi wynikami polega na tym ze dane pochodza z zapytan z innych stron na tym samym serwerze ktore chodza wydaje mi sie w miare przyzwoicie. Zeby ugryzc ta testowa o ktora mi chodzi trzeba jednak pewnie przeanalizowac EXPLAIN dla wybranych zapytan.

[mke 0]

EXPLAIN SELECT cat.title, cat.id, (

 

SELECT count( * )

FROM jos_gbl_ads AS a

WHERE a.category_id = cat.id

AND a.status =1

AND (

a.expiry_date > NOW( ) || a.expiry_date = 'S'

)

AND (

a.country_id =77

)

) AS adCount

FROM jos_gbl_categories AS cat

WHERE published =1

AND cat.access <=0

 

 

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY cat ALL NULL NULL NULL NULL 49 Using where

2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 3292 Using where

[kafi 2425]

w tym przypadku, nawet bez tuneup'u mysql widac jak na dloni ze zapytanie jest zmasakrowane...

Ale to widać było od razu po slowlogu

 

5) powyzsze zapytanie wygalda na skopane a poza tym zgadujac co one moze robic wydaje mi sie ze jest za bardzo pokrecone

Widać autor tego dodatku nie słyszał o czymś takim jak GROUP BY/HAVING

[Gość squeezer]

Dodaj indeksy:

 

ALTER TABLE jos_gbl_ads ADD INDEX idx_cat_id_country_id (category_id, country_id);

ALTER TABLE jos_gbl_ads ADD INDEX idx_country_id (country_id);

ALTER TABLE jos_gbl_categories ADD INDEX idx_id (id);

 

Po dodaniu podeślij jeszcze raz wynik EXPLAIN dla tego zapytania. Zobaczymy co optimizer wymyśli dla Twoich danych, najwyżej skasujemy te indeksy, które nie są wykorzystywane.

 

Pewnie, że wiedząc dokładnie co poeta miał na myśli możnaby takie zapytanie przepisać. Zazwyczaj jednak albo nie wiemy, albo nie mamy czasu sprawdzać. Dlatego też dobrze zacząć od podstaw, czyli oprawnego indeksowania. Tu nawet tego nie ma.

[mke 0]

EXPLAIN SELECT cat.title, cat.id, (

 

SELECT count( * )

FROM jos_gbl_ads AS a

WHERE a.category_id = cat.id

AND a.status =1

AND (

a.expiry_date > NOW( ) || a.expiry_date = 'S'

)

AND (

a.country_id =77

)

) AS adCount

FROM jos_gbl_categories AS cat

WHERE published =1

AND cat.access <=0

 

 

po dodaniu powyzszych indeksow mam

 

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY cat ALL NULL NULL NULL NULL 49 Using where

2 DEPENDENT SUBQUERY a ref idx_cat_id_country_id,idx_country_id idx_cat_id_country_id 10 uk146.cat.id,const 127 Using where

 

zauwazanla zmiana poprawy dzialania, zuzycie CPU przez mysql juz nie skacze powyzej 100%

 

byc moze jest to czesciowo zwiazane z wlaczeniem cachu

 

moj obecny my.cnf w oparciu o rady z mysqltuner

 

 

[mysqld]

 

long_query_time=1

log_slow_queries=/var/log/wolne_zapytania.log

set-variable=local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment

# these lines and adjust the connectstring as needed.

#ndbcluster

#ndb-connectstring="nodeid=4;host=localhost:1186"

skip-bdb

#skip-innodb

query_cache_size=8M

query_cache_limit=4M

thread_cache_size=4

join_buffer_size=1M

key_buffer_size=24M

table_cache=64

set-variable = innodb_buffer_pool_size=2M

set-variable = innodb_additional_mem_pool_size=500K

set-variable = innodb_log_buffer_size=500K

set-variable = innodb_thread_concurrency=2

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

 

efekt? Prawdopodobnie dzieki dodaniu indexow w moim pliku do ktergo zapisywane sa wolne zapytania nie widze zadnych wolnych zapytan powyzej 1 sekundy

Mysle ze jeszcze mozna zmienic troche, zoptymalizowac zapytania, ale glowny cel jest juz blisko. Teraz moja kolej skontaktowac sie z Tworcami tego komponentu i zasugerowac co maja poprawic. Dla potomnosci, moze komus sie przyda, powiem ze rozpatrywalismy komponent o nazwie Listbingo.

Aha niezaleznie jak dalej potoczy sie ten temat dziekuje wszystkim za pomoc.

[Gość squeezer]

po dodaniu powyzszych indeksow mam

 

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY cat ALL NULL NULL NULL NULL 49 Using where

2 DEPENDENT SUBQUERY a ref idx_cat_id_country_id,idx_country_id idx_cat_id_country_id 10 uk146.cat.id,const 127 Using where

 

zauwazanla zmiana poprawy dzialania, zuzycie CPU przez mysql juz nie skacze powyzej 100%

 

 

W takim razie możesz usunąć index idx_country_id, bo w tym konkretnym zapytaniu nie jest wykorzystywany (co nie znaczy, że gdzie indziej nie będzie).

 

ALTER TABLE jos_gbl_ads DROP INDEX idx_country_id;

 

byc moze jest to czesciowo zwiazane z wlaczeniem cachu

 

Nie bardzo. W zapytaniu masz funkcję NOW(), która rozwija się w timestamp, czyli treść zapytania zmienia się co sekundę. W efekcie, jeśli do bazy nie leci takich zapytań na sekundę kilkadziesiąt bądź więcej, to stosowanie cache przyniesie więcej strat niż pożytku i równie dobrze można je wyłączyć. Oczywiście, zakładamy że nie przydaje się do innych zapytań, to już kwestia analizy całokształtu ruchu idącego do bazy.