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