[MySQL] Langzame query door filesort

[Olaf van der Spek]

Deze MySQL (5.0.51a-24+lenny2) query is veel te graag, soms kost het 30+ s. De disk is wel heel druk, maar ik had verwacht dat Linux (Debian Lenny) de tmp files wel in het geheugen zou houden.
Hoe kan ik dit verbeteren?

http://dev.mysql.com/doc/...rder-by-optimization.html

sort_buffer_size en read_rnd_buffer_size zijn defaults maar dat zou voldoende moeten zijn.

SQL:

mysql> explain select id from torrents WHERE category != 8 ORDER BY id DESC LIMIT 1050,35;
+----+-------------+-------+-------+---------------------+------------+---------+------+-------+------------------------------------------+
| id | select_type | table | type  | possible_keys       | key        | key_len | ref  | rows  | Extra                                    |
+----+-------------+-------+-------+---------------------+------------+---------+------+-------+------------------------------------------+
|  1 | SIMPLE      | t     | range | category,category_2 | category_2 | 4       | NULL | 88040 | Using where; Using index; Using filesort | 
+----+-------------+-------+-------+---------------------+------------+---------+------+-------+------------------------------------------+


mysql> show index from torrents;
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| torrents |          0 | PRIMARY      |            1 | id           | A         |       97658 |     NULL | NULL   |      | BTREE      |         | 
| torrents |          1 | category     |            1 | category     | A         |          31 |     NULL | NULL   |      | BTREE      |         | 
| torrents |          1 | category_2   |            1 | category     | A         |          31 |     NULL | NULL   |      | BTREE      |         | 
| torrents |          1 | category_2   |            2 | id           | A         |       97658 |     NULL | NULL   |      | BTREE      |         | 
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

[pedorus]

Zou je niet een stuk beter af zijn als gewoon de primary key werd gebruikt, al dan niet door een hint te geven?

En probeer anders een index (id, category) ipv omgekeerd, om de boel bij elkaar in het geheugen te krijgen.

[ Voor 24% gewijzigd door pedorus op 17-12-2009 23:15 ]

[Voutloos]

Dat kan je inderdaad testen, of maak juist een (id,category) index ipv je huidige (category,id).

[ Voor 19% gewijzigd door Voutloos op 17-12-2009 23:16 ]

[Olaf van der Spek]

Dan wordt toch een table scan gebruikt (aangezien de where wel een keer uitgevoerd moet worden)?

[ Voor 70% gewijzigd door Olaf van der Spek op 17-12-2009 23:30 ]

[pedorus]

offtopic:
Ha, Voutloos met seconden verslagen

Olaf van der Spek schreef op donderdag 17 december 2009 @ 23:29:
Dan wordt toch een table scan gebruikt (aangezien de where wel een keer uitgevoerd moet worden)?

Het probleem is dat je nu een index hebt die niet correct gesorteerd is; nadat die ene categorie er tussenuit is gefilterd blijven nodes over waarvan het geheel op category, id is gesorteerd. Als je die sortering ook prima vind, kun je dat beter aanpassen in je query. Stukjes van dat resultaat zijn natuurlijk wel op id gesorteerd, maar het geheel niet. Waarschijnlijk is het vanwege de boomstructuur waar in het zit ook het makkelijkst om maar compleet overnieuw te sorteren, en dat doet MySQL dan ook.

Met een index die op id begint heb je dat sorteerprobleem niet. Als category er ook bij zit, kan de complete selectie met die index afgehandelt worden, anders zal er af en toe onnodig naar het complete record worden gesprongen. Vooral als er zeer weinig category 8 is of de gehele tabel toch al in het geheugen staat boeit dat niet veel, en is de primary key het handigst (minder indexruimte nodig). Verder hangt het van tal van details af, maar ik zou zeggen: probeer het gewoon uit...

[WouZz]

Deze filesort moet je er uit kunnen indexen. Als je het goed doet leest ie alles zelfs uit de index aangezien je alleen het id opvraagt. Ik ben dus ook voor een compound id, category index.

[cariolive23]

Hoeveel KB/MB gebruikt de index category_2 ? Je kunt zien dat er een 88000 records zijn die aan de voorwaarde voldoen, wanneer hierop moet worden gesorteerd, moet de index wel in RAM passen. Wanneer dat niet het geval is, zie ook jouw configuratie, dan zal de database gewoon een filesort toepassen.

>> LIMIT 1050,35;
Dit is ook relatief langzaam, de eerste 1049 records worden overgeslagen, maar kosten dus wel tijd om te "verwerken". Wellicht is het gebruik van een CURSOR handiger, geen idee welke mogelijkheden MySQL hiervoor in huis heeft.

[ACM]

cariolive23 schreef op vrijdag 18 december 2009 @ 08:30:
moet de index wel in RAM passen. Wanneer dat niet het geval is, zie ook jouw configuratie, dan zal de database gewoon een filesort toepassen.

Vziw heeft wel of niet in RAM passen van de index weinig met het gebruik van filesort te maken bij MySQL. En bovendien betekent - wederom vziw - het feit dat ie een filesort aangeeft nog niet dat ie ook daadwerkelijk files (naar disk) schrijft.

Wat voor type tabel gaat het trouwens om? Als het MyISAM is kan het best zijn dat je op writes zit te wachten, ipv op de query zelf.

[Voutloos]

ACM schreef op vrijdag 18 december 2009 @ 09:31:
Vziw heeft wel of niet in RAM passen van de index weinig met het gebruik van filesort te maken bij MySQL. En bovendien betekent - wederom vziw - het feit dat ie een filesort aangeeft nog niet dat ie ook daadwerkelijk files (naar disk) schrijft.

Klopt volledig, filesort betekent puur dat er nog een aparte sorteerstop nodig is. En ja, die is vaak de boosdoener bij trage queries.

Wat voor type tabel gaat het trouwens om? Als het MyISAM is kan het best zijn dat je op writes zit te wachten, ipv op de query zelf.

Engine vermelden kan nooit kwaad, maar dan nog is de huidige explain uberhaupt niet optimaal, want het kan best zonder filesort.

PK of die (id,cat) key proberen is iets dat je binnen een minuutje moet kunnen doen.

[cariolive23]

cariolive23 schreef op vrijdag 18 december 2009 @ 08:30:
>> LIMIT 1050,35;
Dit is ook relatief langzaam, de eerste 1049 records worden overgeslagen, maar kosten dus wel tijd om te "verwerken". Wellicht is het gebruik van een CURSOR handiger, geen idee welke mogelijkheden MySQL hiervoor in huis heeft.

Inmiddels even in de handleiding gekeken, MySQL ondersteunt dit nauwelijks,
cursors in 5.0:

Cursors are supported inside stored procedures and functions and triggers. The syntax is as in embedded SQL. Cursors in MySQL have these properties:
* Asensitive: The server may or may not make a copy of its result table
* Read only: Not updatable
* Nonscrollable: Can be traversed only in one direction and cannot skip rows

Dat gaat 'em dus niet worden. Met versie 5.1 is het niet veel beter, al zijn events dan wel toegevoegd aan het rijtje.

[GlowMouse]

Als categorie 8 niet heel vaak voorkomt, moet je een index op id gebruiken (forceren in jouw geval). InnoDB is door zijn clustered index ideaal voor zulke queries.

De filesort is extra erg omdat je zo ontzettend veel records hebt.

http://www.mysqlperforman...performance-optimization/

[ Voor 33% gewijzigd door GlowMouse op 24-12-2009 21:45 ]