Vraag MySQL slow query - "Wachten op blokkering van query-cache"


Ik voer een eenvoudige query uit op een eenvoudige tabel op dezelfde computer als de server met 5.5. Het kost 22sec om ~ 7000 rijen terug te geven uit een tabel met 20 miljoen rijen. Na profilering wordt het grootste deel van de tijd ingenomen door meerdere "wachten op cachevergrendeling query". Wat is 'Wacht op quarklock voor query's' en waarom duurt deze query zo lang? Is het iets met de manier waarop ik de server installeer?

Hier is het profiel (merk op dat de tijd voor de bewerking eigenlijk uit de rij hieronder is zoals vermeld hier):

mysql> show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000015 |
| Waiting for query cache lock   | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000027 |
| System lock                    | 0.000007 |
| Waiting for query cache lock   | 0.000032 |
| init                           | 0.000018 |
| optimizing                     | 0.000008 |
| statistics                     | 0.033109 |
| preparing                      | 0.000019 |
| executing                      | 0.000002 |
| Sending data                   | 4.575480 |
| Waiting for query cache lock   | 0.000005 |
| Sending data                   | 5.527728 |
| Waiting for query cache lock   | 0.000005 |
| Sending data                   | 5.743041 |
| Waiting for query cache lock   | 0.000004 |
| Sending data                   | 6.191706 |
| end                            | 0.000007 |
| query end                      | 0.000005 |
| closing tables                 | 0.000028 |
| freeing items                  | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000182 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000004 |
| logging slow query             | 0.000001 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

Hier is de tabel:

mysql> SHOW CREATE TABLE prvol;

"Table","Create Table"
"prvol","CREATE TABLE `prvol` (
  `ticker` varchar(10) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `close` float unsigned DEFAULT NULL,
  KEY `Index 1` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"

Hier is de vraag:

mysql> select close from prvol where date = '20100203';

BEWERK: Na het uitvoeren met SQL_NO_CACHE is de hele tijd bezig met de uitvoering. Zou dit normaal kunnen zijn voor een tafel van deze grootte op een 2.4GHz, 3GB rammachine?

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000052 |
| checking permissions |  0.000007 |
| Opening tables       |  0.000027 |
| System lock          |  0.000008 |
| init                 |  0.000019 |
| optimizing           |  0.000008 |
| statistics           |  0.034766 |
| preparing            |  0.000011 |
| executing            |  0.000002 |
| Sending data         | 22.071324 |
| end                  |  0.000012 |
| query end            |  0.000005 |
| closing tables       |  0.000020 |
| freeing items        |  0.000170 |
| logging slow query   |  0.000001 |
| logging slow query   |  0.000003 |
| cleaning up          |  0.000004 |
+----------------------+-----------+

BEWERK: Voeg de resultaten van uitleg toe.

mysql> explain extended select cp from prvol where date = '20100208';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   |rows  | filtered | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | prvol | ref  | Index 1       | Index 1 | 4       | const |6868  |   100.00 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.08 sec)

20
2018-03-18 19:35


oorsprong


antwoorden:


Ik heb mijn probleem met langzame query opgelost. Om het probleem samen te vatten, kostte het 22sec om 7000 rijen te ondervragen vanuit een 20mln rij, 1,7GB geïndexeerde tabel. Het probleem was dat de cache te klein was en de query voor elke query naar de schijf moest gaan. Ik zou denken dat de toegang tot de schijf sneller zou zijn dan wat ik zag, omdat ik van een geïndexeerde kolom afgaf, zodat de hoeveelheid gegevens die van schijf was gelezen klein had moeten zijn. Maar ik vermoed dat er veel overhead is met toegang tot de InnoDB-opslag op schijf.

Zodra ik klaar ben innodb_buffer_pool_size=1024M in het bestand my.ini zou de eerste query lang duren, maar alle volgende query's zouden binnen een seconde zijn voltooid.

Helaas heeft de profilering niet echt geholpen.


11
2018-03-22 15:08



Dit is een bekend probleem met MySQL. Het staat hier heel goed beschreven:

https://web.archive.org/web/20160129162137/http://www.psce.com/blog/kb/how-query-cache-can-cause-performance-problems/

Querycache kan u veel helpen, maar kan tegelijkertijd een knelpunt worden.


9
2018-05-29 22:47