[10:13] <apollo13> zed, ys76 around?
[10:17] <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:18] <apollo13> those are just 19k rows :þ
[10:19] <ys76> Phew, I am all but an MySQL expert....
[10:20] <ys76> Check for indexes first....
[10:20] <ys76> Joins without indexes may cause those "slowdowns"...
[10:21] <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:22] <zed> on which server ?
[10:22] <apollo13> tilo itself
[10:22] <zed> errr i don't see any query running like this one
[10:23] <apollo13> zed: gimme a sec
[10:23] <apollo13> zed: now
[10:23] <zed> nothing..
[10:24] <apollo13> zed: mind executing it yourself in ubuntu_de_inyoka db?
[10:24] <zed> yep
[10:25] <zed> mmh
[10:25] <zed> this query might parse 4339295469 rows....
[10:26] <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:27] <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:28] <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:29] <apollo13> odd thing is: db is doing nothing at all
[10:30] <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:31] <apollo13> zed: looool
[10:31] <apollo13> the query works fine if I add a limit 50k
[10:32] <apollo13> ah no, got i hanging
[10:32] <apollo13> 15k still worked
[10:32] <apollo13> but I get output then at least
[10:33] <apollo13> now it finished
[10:36] <apollo13> it's hanging in the read there
[10:36] <apollo13> zed: any ways to reorganize those two tables?
[10:38] <zed> yep...
[10:39] <zed> try again ?
[10:40] <apollo13> wtf
[10:40] <apollo13> what did you do?
[10:42] <apollo13> (let me recheck if it really works again)
[10:44] <apollo13> zed: what did you do :)
[10:46] <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:47] <apollo13> zed: does it hurt if I run that over all tables?
[10:48] <apollo13> zed: and where the hell is the cherokee testpage coming @ubuntuusers.de
[10:49] <apollo13> we had a nice error page there *gg*
[10:50] <apollo13> take to me zed :þ
[10:51] <apollo13> talk*
[10:54] <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:55] <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:57] <ys76> Phew now idea...
[10:57] <ys76> Depends on the size...
[10:57] <apollo13> 7 gig
[10:57] <apollo13> +- 1 or 2
[10:58] <ys76> MIght  take a while...
[10:58] <apollo13> define while, hours days…
[10:58] <apollo13> years :þ
[10:59] <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
[11:00] <ys76> Just start with a tiny little table....
[11:03] <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:04] <EnTeQuAk> its totally overdue
[11:05] <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:10] <EnTeQuAk> makes sense
[11:43] <zed> appsorry i was eating
[12:55] <apollo13> 37 rows in set (1 hour 46 min 24.81 sec)
[12:55] <apollo13>  yeha
[13:53] <zed> wow :)
[13:54] <apollo13> optimize over the whole db