- Posts: 49
- Thank you received: 0
Flexi table size in SQL getting to large
- emanuelusa
- Topic Author
- Offline
- Senior Member
-
11 years 9 months ago #40139
by emanuelusa
Flexi table size in SQL getting to large was created by emanuelusa
Hello there!
Lately our dev site has slowed down to a crawl and has started to give us this error message;
getItems(): SQL QUERY ERROR:
Incorrect key file for table '/tmp/#sql_144d_7.MYI'; try to repair it SQL=SELECT i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as categoryslug, GROUP_CONCAT(rel.catid SEPARATOR ",") as itemcats FROM pxrj3_content AS i JOIN pxrj3_flexicontent_items_ext AS ie on ie.item_id = i.id JOIN pxrj3_flexicontent_types AS ty on ie.type_id = ty.id JOIN pxrj3_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN pxrj3_categories AS c ON c.id = rel.catid JOIN pxrj3_categories AS mc ON mc.id = i.catid WHERE c.published = 1 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2013-09-18 05:54:54' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2013-09-18 05:54:54' ) AND ty.access IN (0,1,1) AND mc.access IN (0,1,1) AND i.access IN (0,1,1) AND i.id <> 0 AND i.state IN ( 1, -5 ) AND c.id IN (51) GROUP BY i.id ORDER BY i.created DESC, i.title LIMIT 0, 7
so we did some trouble shooting with Rochen and they gave us this response.
"You should definitely have your developer review your script/db though as you are running queries constantly that do not fit in RAM, resulting in tmp tables being written to disk. That's going to be 500-1000X slower than handling the query in RAM which is why you're seeing delays. I've provided a processlisting showing two such queries running for 13 and 2 seconds, respectively, below:
+
+
+
+
+
+
+
+
+
| Id | User | Host | db | Command | Time | State | Info |
+
+
+
+
+
+
+
+
+
| 692144 | uigvtyaz_racer12 | localhost | uigvtyaz_racer1 | Query | 13 | Copying to group table | SELECT i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.al |
| 692191 | uigvtyaz_racer12 | localhost | uigvtyaz_racer1 | Query | 2 | Copying to tmp table | SELECT i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.al |
+
+
+
+
+
+
+
+
+
That typically indicates that you have data in a table that has recently grown to a size that no longer fits in RAM (a tmp table over >16MB). "
Can you suggest a fix for this? we have already corrected the scripts best we can but still get the error message and slow speed.
Thank you!
Robert
Lately our dev site has slowed down to a crawl and has started to give us this error message;
getItems(): SQL QUERY ERROR:
Incorrect key file for table '/tmp/#sql_144d_7.MYI'; try to repair it SQL=SELECT i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as categoryslug, GROUP_CONCAT(rel.catid SEPARATOR ",") as itemcats FROM pxrj3_content AS i JOIN pxrj3_flexicontent_items_ext AS ie on ie.item_id = i.id JOIN pxrj3_flexicontent_types AS ty on ie.type_id = ty.id JOIN pxrj3_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN pxrj3_categories AS c ON c.id = rel.catid JOIN pxrj3_categories AS mc ON mc.id = i.catid WHERE c.published = 1 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2013-09-18 05:54:54' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2013-09-18 05:54:54' ) AND ty.access IN (0,1,1) AND mc.access IN (0,1,1) AND i.access IN (0,1,1) AND i.id <> 0 AND i.state IN ( 1, -5 ) AND c.id IN (51) GROUP BY i.id ORDER BY i.created DESC, i.title LIMIT 0, 7
so we did some trouble shooting with Rochen and they gave us this response.
"You should definitely have your developer review your script/db though as you are running queries constantly that do not fit in RAM, resulting in tmp tables being written to disk. That's going to be 500-1000X slower than handling the query in RAM which is why you're seeing delays. I've provided a processlisting showing two such queries running for 13 and 2 seconds, respectively, below:
+
+
+
+
+
+
+
+
+
| Id | User | Host | db | Command | Time | State | Info |
+
+
+
+
+
+
+
+
+
| 692144 | uigvtyaz_racer12 | localhost | uigvtyaz_racer1 | Query | 13 | Copying to group table | SELECT i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.al |
| 692191 | uigvtyaz_racer12 | localhost | uigvtyaz_racer1 | Query | 2 | Copying to tmp table | SELECT i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.al |
+
+
+
+
+
+
+
+
+
That typically indicates that you have data in a table that has recently grown to a size that no longer fits in RAM (a tmp table over >16MB). "
Can you suggest a fix for this? we have already corrected the scripts best we can but still get the error message and slow speed.
Thank you!
Robert
Please Log in or Create an account to join the conversation.
- emanuelusa
- Topic Author
- Offline
- Senior Member
-
- emanuelusa
- Topic Author
- Offline
- Senior Member
-
Time to create page: 0.371 seconds
Last FLEXI-site
... loading images 
