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