Citat:
Originally posted by SkyNet@Dec 14 2004, 09:15
Körningsinformationen som du skickade med ger inte så mycket information, men 18 queries/sekund är inte alls mycket.
Skulle jag optimera skulle jag börja med att;
a) Ladda hem mytop från http://jeremy.zawodny.com/mysql/mytop/ för att kolla vilka queries som körs samt hur lång tid de tar.
B) Slå på log-slow-queries + long_query_time=5 i my.cnf (antar att du kör MySQL 4.x ?).
c) Definitivt använda query cachen.
Sedan kan man ha en lång utläggning om olika inställningar i konfigurationen osv. Du kan även kolla vilka tabeller som används mest, går dessa att optimera? Kanske det inte går, utan du behöver mer minne i servern, tuggar den mycket swap minne?
|
Får en ENORM logfil med slow_queries...
Citat:
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 0 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='2493' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 2 *Rows_examined: 22482
SELECT * FROM commenttable WHERE name='2607' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 0 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='3815' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 0 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='18490' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 0 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='17112' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 2 *Rows_examined: 13301
SELECT * FROM commenttable WHERE name='122' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 0 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='8664' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 26 *Lock_time: 0 *Rows_sent: 1 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='20057' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 15 *Lock_time: 0 *Rows_sent: 1 *Rows_examined: 26506
SELECT * FROM commenttable WHERE name='92' ORDER BY id DESC LIMIT 2;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 18 *Lock_time: 0 *Rows_sent: 3 *Rows_examined: 11036
select url, id from imagetable where name = 'sandra i (y)' order by average desc;
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 619 Lock_time: 611 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 619 Lock_time: 611 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 560 Lock_time: 552 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 558 Lock_time: 550 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 555 Lock_time: 547 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 551 Lock_time: 543 Rows_sent: 10 Rows_examined: 12549
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE category = 'Tjejer' and total >= 20 and status = 'active' group
by name order by average DESC LIMIT 0,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 529 Lock_time: 521 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 522 Lock_time: 514 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 605 Lock_time: 597 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 600 Lock_time: 592 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 634 Lock_time: 626 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 625 Lock_time: 617 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 605 Lock_time: 597 Rows_sent: 11021 Rows_examined: 11035
SELECT id FROM imagetable WHERE status = 'active';
# Query_time: 384 Lock_time: 376 Rows_sent: 11019 Rows_examined: 11036
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 383 Lock_time: 375 Rows_sent: 10 Rows_examined: 12684
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE category = 'Tjejer' and status = 'active' group by name order b
y id DESC LIMIT 60,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 381 Lock_time: 374 Rows_sent: 11019 Rows_examined: 11036
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 374 Lock_time: 367 Rows_sent: 10 Rows_examined: 12674
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE category = 'Tjejer' and status = 'active' group by name order b
y id DESC LIMIT 50,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 370 Lock_time: 363 Rows_sent: 10 Rows_examined: 16509
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE status = 'active' and total >= 20 group by name order by averag
e DESC LIMIT 0,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 369 Lock_time: 362 Rows_sent: 10 Rows_examined: 16519
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE status = 'active' and total >= 20 group by name order by averag
e DESC LIMIT 10,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 367 Lock_time: 360 Rows_sent: 10 Rows_examined: 11866
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE category = 'Nakna killar' and status = 'active' group by name o
rder by id DESC LIMIT 0,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 367 Lock_time: 360 Rows_sent: 10 Rows_examined: 16519
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE status = 'active' and total >= 20 group by name order by averag
e DESC LIMIT 10,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 363 Lock_time: 356 Rows_sent: 10 Rows_examined: 12700
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE category = 'Tjejer' and total >= 20 and status = 'active' group
by name order by average DESC LIMIT 150,10;
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 361 Lock_time: 354 Rows_sent: 11019 Rows_examined: 11036
SELECT id FROM imagetable WHERE status = 'active';
# User@Host: fabmos[fabmos] @ localhost []
# Query_time: 359 Lock_time: 352 Rows_sent: 10 Rows_examined: 11059
SELECT name, id, url, MAX(average) as average, total FROM imagetable WHERE category = 'Naken.ORG' and total >= 20 and status = 'active' gr
oup by name order by average DESC LIMIT 0,10;
# User@Host: fabmos[fabmos] @ localhost []
|
Där är ett litet utkast ur den... är det någor speciellt jag skall leta efter? |
