Re: Why does this query take so long?
Hi René, everybody! René Fournier wrote: [[...]] However, even if the Index can't fit in memory (4GB of RAM, lots free), just reading it from disk should allow sub-millisecond response, no? No chance! Of course, performance of disk drives varies, but AFAIK typical values are in the range of 7 - 10 ms per random access. Assuming the (very unlikely) optimum case of one index access and one data access, this would put you into the range of 15 - 20 ms just for fetching the stuff from disk, not including any CPU time to traverse the data structures etc. Just do some math: A disk with 7,200 rpm has 120 revolutions per second, so it needs a bit more than 8 milliseconds per revolution. Random access means you have to wait (on average) for half a revolution (4 ms) until the desired block passes the disk head, and before that the head needs to be positioned at the proper cylinder (the drive's data sheet might give that time). I guess that even with SSD you will not reach sub-millisecond response times if the data is not in RAM. Strange thing is that I've used my laptop for benchmarking for the past five years and it's always produced results fairly typical or at least consistent in relation to our servers. This new thing is... new. IMO, the most influential factor in single-user database benchmarks are - disk performance - RAM size for caches, cache replacement - history, cache preloading Their relative importance will vary, depending especially on data size. As long as your data size is small enough that RAM differences between server and laptop don't matter too much, performance on the laptop may be a good prediction of that on the server. With multi-user benchmarks, CPU performance, number of cores etc becomes another important factor, again the relative weights will vary. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why does this query take so long?
Even weirder, I came back to my laptop a couple hours later. And now the same queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing this? On 2009-12-28, at 1:19 PM, René Fournier wrote: Hmm, weird. I just re-imported the data (after drop/create table, etc.), and now the spatial queries run fast. Has anyone seen this sort of thing happen? Maybe the Index got corrupted somehow, and then MySQL had to do a full table scan (even though EXPLAIN indicated it would use the Spatial Index)? On 2009-12-28, at 9:28 AM, René Fournier wrote: So just to clarify (hello?), the index which *should* be used (EXPLAIN says so) and *should* make the query run faster than 4 seconds either isn't used (why?) or simply doesn't speed up the query (again, why?). ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) 8 rows in set (3.87 sec) On 2009-12-27, at 3:59 PM, René Fournier wrote: So... there is an index, and it's supposedly used: mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates); ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ 1 row in set (0.00 sec) But when I run the query: mysql SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) - ; ++--+-+---+--+--++ | id | province | latitude| longitude | AsText(coordinates) | s_ts_r_m | quartersection | ++--+-+---+--+--++ | 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228 -114.79282412) | 04-031-06 W5 | N4 | | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 -114.79283278) | 09-031-06 W5 | N4 | | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 -114.81645649) | 05-031-06 W5 | N4 | | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 -114.81643801) | 08-031-06 W5 | N4 | | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 -114.80475858) | 05-031-06 W5 | E4 | | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 -114.80479925) | 05-031-06 W5 | NE | | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 -114.80478262) | 08-031-06 W5 | E4 | | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 -114.80476596) | 08-031-06 W5 | NE | ++--+-+---+--+--++ 8 rows in set (3.87 sec) So, there are ~2.6 million rows in the table, and coordinates is spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing wrong? ...REne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why does this query take so long?
It sounds like your laptop might be paging mysql's memory to disk or something like that. Your laptop may not be the most reliable source for benchmarks. Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Monday, December 28, 2009 2:16 AM To: René Fournier Cc: mysql Subject: Re: Why does this query take so long? Even weirder, I came back to my laptop a couple hours later. And now the same queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing this? On 2009-12-28, at 1:19 PM, René Fournier wrote: Hmm, weird. I just re-imported the data (after drop/create table, etc.), and now the spatial queries run fast. Has anyone seen this sort of thing happen? Maybe the Index got corrupted somehow, and then MySQL had to do a full table scan (even though EXPLAIN indicated it would use the Spatial Index)? On 2009-12-28, at 9:28 AM, René Fournier wrote: So just to clarify (hello?), the index which *should* be used (EXPLAIN says so) and *should* make the query run faster than 4 seconds either isn't used (why?) or simply doesn't speed up the query (again, why?). ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) 8 rows in set (3.87 sec) On 2009-12-27, at 3:59 PM, René Fournier wrote: So... there is an index, and it's supposedly used: mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates); ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ 1 row in set (0.00 sec) But when I run the query: mysql SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) - ; ++--+-+---+--+--++ | id | province | latitude| longitude | AsText(coordinates) | s_ts_r_m | quartersection | ++--+-+---+--+--++ | 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228 -114.79282412) | 04-031-06 W5 | N4 | | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 -114.79283278) | 09-031-06 W5 | N4 | | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 -114.81645649) | 05-031-06 W5 | N4 | | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 -114.81643801) | 08-031-06 W5 | N4 | | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 -114.80475858) | 05-031-06 W5 | E4 | | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 -114.80479925) | 05-031-06 W5 | NE | | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 -114.80478262) | 08-031-06 W5 | E4 | | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 -114.80476596) | 08-031-06 W5 | NE | ++--+-+---+--+--++ 8 rows in set (3.87 sec) So, there are ~2.6 million rows in the table, and coordinates is spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing wrong? ...REne -- MySQL General Mailing List For list archives: http://lists.mysql.com
Re: Why does this query take so long?
I think you might be right. The good-to-poor performance I'm seeing is so intermittent. And I see now that it's also with other queries, though not as extremely obvious as the spatial queries. However, even if the Index can't fit in memory (4GB of RAM, lots free), just reading it from disk should allow sub-millisecond response, no? Strange thing is that I've used my laptop for benchmarking for the past five years and it's always produced results fairly typical or at least consistent in relation to our servers. This new thing is... new. On 2009-12-29, at 3:18 AM, Gavin Towey wrote: It sounds like your laptop might be paging mysql's memory to disk or something like that. Your laptop may not be the most reliable source for benchmarks. Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Monday, December 28, 2009 2:16 AM To: René Fournier Cc: mysql Subject: Re: Why does this query take so long? Even weirder, I came back to my laptop a couple hours later. And now the same queries are taking 3-10 seconds instead of 0.01 seconds. What could be causing this? On 2009-12-28, at 1:19 PM, René Fournier wrote: Hmm, weird. I just re-imported the data (after drop/create table, etc.), and now the spatial queries run fast. Has anyone seen this sort of thing happen? Maybe the Index got corrupted somehow, and then MySQL had to do a full table scan (even though EXPLAIN indicated it would use the Spatial Index)? On 2009-12-28, at 9:28 AM, René Fournier wrote: So just to clarify (hello?), the index which *should* be used (EXPLAIN says so) and *should* make the query run faster than 4 seconds either isn't used (why?) or simply doesn't speed up the query (again, why?). ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) 8 rows in set (3.87 sec) On 2009-12-27, at 3:59 PM, René Fournier wrote: So... there is an index, and it's supposedly used: mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates); ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ 1 row in set (0.00 sec) But when I run the query: mysql SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) - ; ++--+-+---+--+--++ | id | province | latitude| longitude | AsText(coordinates) | s_ts_r_m | quartersection | ++--+-+---+--+--++ | 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228 -114.79282412) | 04-031-06 W5 | N4 | | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 -114.79283278) | 09-031-06 W5 | N4 | | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 -114.81645649) | 05-031-06 W5 | N4 | | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 -114.81643801) | 08-031-06 W5 | N4 | | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 -114.80475858) | 05-031-06 W5 | E4 | | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 -114.80479925) | 05-031-06 W5 | NE
Re: Why does this query take so long?
So just to clarify (hello?), the index which *should* be used (EXPLAIN says so) and *should* make the query run faster than 4 seconds either isn't used (why?) or simply doesn't speed up the query (again, why?). ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) 8 rows in set (3.87 sec) On 2009-12-27, at 3:59 PM, René Fournier wrote: So... there is an index, and it's supposedly used: mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates); ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ 1 row in set (0.00 sec) But when I run the query: mysql SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) - ; ++--+-+---+--+--++ | id | province | latitude| longitude | AsText(coordinates) | s_ts_r_m | quartersection | ++--+-+---+--+--++ | 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228 -114.79282412) | 04-031-06 W5 | N4 | | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 -114.79283278) | 09-031-06 W5 | N4 | | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 -114.81645649) | 05-031-06 W5 | N4 | | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 -114.81643801) | 08-031-06 W5 | N4 | | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 -114.80475858) | 05-031-06 W5 | E4 | | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 -114.80479925) | 05-031-06 W5 | NE | | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 -114.80478262) | 08-031-06 W5 | E4 | | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 -114.80476596) | 08-031-06 W5 | NE | ++--+-+---+--+--++ 8 rows in set (3.87 sec) So, there are ~2.6 million rows in the table, and coordinates is spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing wrong? ...REne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why does this query take so long?
Hmm, weird. I just re-imported the data (after drop/create table, etc.), and now the spatial queries run fast. Has anyone seen this sort of thing happen? Maybe the Index got corrupted somehow, and then MySQL had to do a full table scan (even though EXPLAIN indicated it would use the Spatial Index)? On 2009-12-28, at 9:28 AM, René Fournier wrote: So just to clarify (hello?), the index which *should* be used (EXPLAIN says so) and *should* make the query run faster than 4 seconds either isn't used (why?) or simply doesn't speed up the query (again, why?). ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) 8 rows in set (3.87 sec) On 2009-12-27, at 3:59 PM, René Fournier wrote: So... there is an index, and it's supposedly used: mysql EXPLAIN SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates); ++-+---+---+---+---+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ | 1 | SIMPLE | qs| range | coord | coord | 27 | NULL | 5260 | Using where | ++-+---+---+---+---+-+--+--+-+ 1 row in set (0.00 sec) But when I run the query: mysql SELECT id, province, latitude, longitude, AsText(coordinates), s_ts_r_m, quartersection FROM qs WHERE MBRContains(GeomFromText('POLYGON((51.62582589 -114.82248918,51.65126254 -114.82248918,51.65126254 -114.78150333,51.62582589 -114.78150333,51.62582589 -114.82248918))'), coordinates) - ; ++--+-+---+--+--++ | id | province | latitude| longitude | AsText(coordinates) | s_ts_r_m | quartersection | ++--+-+---+--+--++ | 444543 | AB | 51.63495228 | -114.79282412 | POINT(51.63495228 -114.79282412) | 04-031-06 W5 | N4 | | 444564 | AB | 51.64941120 | -114.79283278 | POINT(51.6494112 -114.79283278) | 09-031-06 W5 | N4 | | 444548 | AB | 51.63497789 | -114.81645649 | POINT(51.63497789 -114.81645649) | 05-031-06 W5 | N4 | | 444561 | AB | 51.64943119 | -114.81643801 | POINT(51.64943119 -114.81643801) | 08-031-06 W5 | N4 | | 444547 | AB | 51.62775680 | -114.80475858 | POINT(51.6277568 -114.80475858) | 05-031-06 W5 | E4 | | 444549 | AB | 51.63498028 | -114.80479925 | POINT(51.63498028 -114.80479925) | 05-031-06 W5 | NE | | 444560 | AB | 51.64220442 | -114.80478262 | POINT(51.64220442 -114.80478262) | 08-031-06 W5 | E4 | | 444562 | AB | 51.64942854 | -114.80476596 | POINT(51.64942854 -114.80476596) | 08-031-06 W5 | NE | ++--+-+---+--+--++ 8 rows in set (3.87 sec) So, there are ~2.6 million rows in the table, and coordinates is spatially-indexed. Yet the query requires nearly 4 seconds. What am I doing wrong? ...REne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why does 1st query take so long?
- Original Message - From: [EMAIL PROTECTED] To: "mySQL list" [EMAIL PROTECTED] Sent: Tuesday, January 16, 2001 3:57 PM Subject: Why does 1st query take so long? I've noticed that when developing queries involving complex joins on tables I sometimes seem to get appallingly long times the first time I run a query, but the second and subsequent time I run it - even substituting a different value for a field value I'm matching on - the query runs quickly. Does mySQL do some behind-the-scenes reindexing the first time, resulting in the speed differences I see? If so is there some way I can force it to re-build indices periodically so I can ensure that queries are generally fast? Is this the point I should be going out and buying a book about mySQL? ;-) I think its because the Operating System is caching the data the query are run on - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php