| apollo13 | zed, ys76 around? | 10:13 |
|---|---|---|
| ys76 | apollo13: Sleeping... | 10:17 |
| apollo13 | ys76: grr, SELECT `wiki_page`.`name`, `wiki_revision`.`deleted`, `wiki_revision`.`attachment_id` FROM `wiki_page` LEFT OUTER JOIN `wiki_revision` ON (`wiki_page`.`last_rev_id` = `wiki_revision`.`id`); | 10:17 |
| apollo13 | why would this query lock the db for ages | 10:17 |
| apollo13 | those are just 19k rows :þ | 10:18 |
| ys76 | Phew, I am all but an MySQL expert.... | 10:19 |
| ys76 | Check for indexes first.... | 10:20 |
| ys76 | Joins without indexes may cause those "slowdowns"... | 10:20 |
| zed | hi | 10:21 |
| apollo13 | http://paste.pocoo.org/show/333606/ | 10:21 |
| apollo13 | zed: hi | 10:21 |
| apollo13 | since yesterday this query never runs… | 10:21 |
| zed | on which server ? | 10:22 |
| apollo13 | tilo itself | 10:22 |
| zed | errr i don't see any query running like this one | 10:22 |
| apollo13 | zed: gimme a sec | 10:23 |
| apollo13 | zed: now | 10:23 |
| zed | nothing.. | 10:23 |
| apollo13 | zed: mind executing it yourself in ubuntu_de_inyoka db? | 10:24 |
| zed | yep | 10:24 |
| zed | mmh | 10:25 |
| zed | this query might parse 4339295469 rows.... | 10:25 |
| apollo13 | there aren't that much rows :þ | 10:26 |
| apollo13 | mysql> select count(*) from wiki_page; | 10:26 |
| apollo13 | +----------+ | 10:26 |
| apollo13 | | count(*) | | 10:26 |
| apollo13 | mysql> select count(*) from wiki_revision; | 10:26 |
| apollo13 | +----------+ | 10:26 |
| apollo13 | | count(*) | | 10:26 |
| zed | yes but a join will have to parse ab rows | 10:26 |
| zed | a*b | 10:26 |
| apollo13 | yikes | 10:27 |
| zed | (without indexes of course) | 10:27 |
| apollo13 | how to fix that :þ | 10:27 |
| zed | that's pretty bad | 10:27 |
| apollo13 | well the explain indicates an index on wiki_revision, doesn't it? | 10:27 |
| zed | try adding an index on wiki_page.last_rev_id | 10:27 |
| zed | or I can do it if you want | 10:28 |
| apollo13 | I can do that, thx | 10:28 |
| apollo13 | | wiki_page | 1 | wiki_page_1ec47986 | 1 | last_rev_id | A | 10:28 |
| apollo13 | there is one | 10:28 |
| apollo13 | odd thing is: db is doing nothing at all | 10:29 |
| zed | weird | 10:30 |
| apollo13 | mysql> SELECT `wiki_page`.`name`, `wiki_revision`.`deleted`, `wiki_revision`.`attachment_id` FROM `wiki_page` LEFT OUTER JOIN `wiki_revision` ON (`wiki_page`.`last_rev_id` = `wiki_revision`.`id`) into outfile '/tmp/test.csv'; | 10:30 |
| apollo13 | Query OK, 19094 rows affected (0.18 sec) | 10:30 |
| apollo13 | zed: looool | 10:31 |
| apollo13 | the query works fine if I add a limit 50k | 10:31 |
| apollo13 | ah no, got i hanging | 10:32 |
| apollo13 | 15k still worked | 10:32 |
| apollo13 | but I get output then at least | 10:32 |
| apollo13 | now it finished | 10:33 |
| apollo13 | it's hanging in the read there | 10:36 |
| apollo13 | zed: any ways to reorganize those two tables? | 10:36 |
| zed | yep... | 10:38 |
| zed | try again ? | 10:39 |
| apollo13 | wtf | 10:40 |
| apollo13 | what did you do? | 10:40 |
| apollo13 | (let me recheck if it really works again) | 10:42 |
| apollo13 | zed: what did you do :) | 10:44 |
| zed | optimize both tqbles | 10:46 |
| zed | tables | 10:46 |
| apollo13 | k, is there a shortcut to run it over the whole db? | 10:46 |
| apollo13 | + how long would it take | 10:46 |
| apollo13 | zed: does it hurt if I run that over all tables? | 10:47 |
| apollo13 | zed: and where the hell is the cherokee testpage coming @ubuntuusers.de | 10:48 |
| apollo13 | we had a nice error page there *gg* | 10:49 |
| apollo13 | take to me zed :þ | 10:50 |
| apollo13 | talk* | 10:51 |
| ys76 | apollo13: It would affect performance during the run... | 10:54 |
| ys76 | and no, I don't know a shortcut | 10:54 |
| apollo13 | ys76: I took ubuntuusers.de down completly | 10:54 |
| ys76 | Just saw it. | 10:54 |
| apollo13 | na ubuntuusers is down since a while… | 10:54 |
| apollo13 | I have no idea where cherokee is coming from | 10:54 |
| apollo13 | apperently a server outside of my control | 10:54 |
| ys76 | where did you get that page? | 10:55 |
| apollo13 | oh I am so stupid | 10:55 |
| ys76 | *smile* | 10:55 |
| apollo13 | localhost | 10:55 |
| apollo13 | ys76: how long do you think an optimize over all tables will take? | 10:55 |
| ys76 | Phew now idea... | 10:57 |
| ys76 | Depends on the size... | 10:57 |
| apollo13 | 7 gig | 10:57 |
| apollo13 | +- 1 or 2 | 10:57 |
| ys76 | MIght take a while... | 10:58 |
| apollo13 | define while, hours days… | 10:58 |
| apollo13 | years :þ | 10:58 |
| ys76 | It took half an hour on our 6GB OTRS DB | 10:59 |
| apollo13 | great, I'll throw it over all then | 10:59 |
| apollo13 | zed: thanks for your help | 10:59 |
| ys76 | Just start with a tiny little table.... | 11:00 |
| EnTeQuAk | my last OPTIMIZE took about an hour locally (with my old laptop) | 11:03 |
| apollo13 | running it over all now, we have till this evenining ;) | 11:03 |
| EnTeQuAk | :) | 11:03 |
| EnTeQuAk | hey, it's the first downtime since months | 11:03 |
| EnTeQuAk | its totally overdue | 11:04 |
| apollo13 | EnTeQuAk: btw I read the postgres performance ml a while ago, people actually disable auto vacuum and run it manually | 11:05 |
| apollo13 | just so it doesn't kick in during day and kills the io performance :) | 11:05 |
| apollo13 | + other crazy stuff *gg* | 11:05 |
| EnTeQuAk | makes sense | 11:10 |
| zed | appsorry i was eating | 11:43 |
| apollo13 | 37 rows in set (1 hour 46 min 24.81 sec) | 12:55 |
| apollo13 | yeha | 12:55 |
| zed | wow :) | 13:53 |
| apollo13 | optimize over the whole db | 13:54 |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!