11 odpowiedzi na ten temat

[mke]

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"  mojtop.png   148,16K   1 Ilość pobrań (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]

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...

Cytuj

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]

• po pierwsze zrób indeksy, które będą wykorzystywane przy tym zapytaniu
• 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....tymalizacja.pdf

[mke]

ahes, o 14 sierpień 2010 - 10:56, powiedział:

• 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?

Cytuj

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]

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.

[squeezer]

mke, o 14 sierpień 2010 - 12:48, powiedział:

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.

mke, o 14 sierpień 2010 - 12:48, powiedział:

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]

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]

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]

Cytuj

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

Cytuj

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

[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]

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.

[squeezer]

mke, o 15 sierpień 2010 - 13:30, powiedział:

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;

mke, o 15 sierpień 2010 - 13:30, powiedział:

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.