10:33 < sheeri> SELECT releases_history.change_id, releases_history.changed_by, releases_history.timestamp, releases_history.name, releases_history.product, releases_history.version, releases_history.data, releases_history.data_version 10:33 < sheeri> FROM releases_history ORDER BY releases_history.timestamp DESC 10:33 < sheeri> LIMIT 10 Sheeri says that an index might help. Any suggestions which columns should be indexed?

10:36 < sheeri> an index on the timestamp will help, so we can add that in. 10:36 < sheeri> since it does order by timestamp

Seems like we should change the query, too. I'm thinking that dropping the LIMIT and querying for 10 or 20 changes at a time (until we get the 10 most recent) would be better. Peter, I cc'ed you because I thought you might want to be aware of this, but I know you're officially out of Balrog at this point so don't feel obligated to grab this!

10:51 < sheeri> if you did the query with change_id, you'd need to put an index on change_id. As it is right now, that wouldn't help, because there isn't an index on change_id

(In reply to Ben Hearsum [:bhearsum] from comment #2) > Seems like we should change the query, too. I'm thinking that dropping the > LIMIT and querying for 10 or 20 changes at a time (until we get the 10 most > recent) would be better. > I am royally confused. The code that shows a page of 10 old revisions is this:: revisions = table.select( where=[ table.name == release['name'], table.data_version != None ], limit=limit, offset=offset, order_by=[table.timestamp.asc()], ) It has the same table to select from, orders by 'timestamp' and the limit looks suspicious. However, where's the `name == $name` and `data_version <> NULL`? And last but not least, where's the `OFFSET`?

Hm, I could be wrong about this being from the index page!

(In reply to Ben Hearsum [:bhearsum] from comment #5) > Hm, I could be wrong about this being from the index page! "index page"! That makes a lot more sense. I don't know why I confused myself into thinking about the revisions page. Yep, an index on the timestamp will help. If further optimization is needed I'm sure we can do something like this:: last_timestamp = releases_revisions[-1]['timestamp'] cache.set('releases_last_timestamp', last_timestamp, 60 * 60) And then used that as `where timestamp > $last_timestamp` to make the query faster.

We should resolve this before we actually have real users updating from Balrog.

Ben - do you know approximately when that will be? We have to balance priorities with goals and stuff.

We don't have a set date for that milestone (tracked in bug 832454), I would SWAG 1-3 months from now though.

OK, re-reading this bug, it looks like what we want to add is an index on releases_history (timestamp) right? And then there might be some query changes you'd do? (if it's just that on our part, we can whip that out pretty easily.

(In reply to Sheeri Cabral [:sheeri] from comment #10) > OK, re-reading this bug, it looks like what we want to add is an index on > releases_history (timestamp) right? And then there might be some query > changes you'd do? (if it's just that on our part, we can whip that out > pretty easily. Sounds good. Once you add the index we should be able to double check that it helps, and then make whatever changes we need to on our side. Should I file a separate bug for that?

The changes on your side should be a separate bug, because I don't need to be cc'd on that. Are there dev/staging servers that need this index added too?

(In reply to Sheeri Cabral [:sheeri] from comment #12) > The changes on your side should be a separate bug, because I don't need to > be cc'd on that. > > Are there dev/staging servers that need this index added too? We only have dev servers at this time AFAIK (aus4-admin-dev)

Perfect! Adding the index will take quite a while, as the releases_history table is large: [root@dev1.db.phx1 aus4_dev]# ls -lrth releases_history.* -rw-rw---- 1 mysql mysql 8.7K Sep 22 02:31 releases_history.frm -rw-rw---- 1 mysql mysql 55G Feb 13 21:21 releases_history.MYD -rw-rw---- 1 mysql mysql 3.4M Feb 13 21:21 releases_history.MYI (also, is it supposed to be MyISAM? All the tables in aus4_dev are MyISAM except for migrate_version, which is InnoDB) I can do this but I anticipate it might take a few hours for the change, and so I want to make sure I coordinate with you on that.

(In reply to Sheeri Cabral [:sheeri] from comment #14) > Perfect! > > Adding the index will take quite a while, as the releases_history table is > large: > > [root@dev1.db.phx1 aus4_dev]# ls -lrth releases_history.* > -rw-rw---- 1 mysql mysql 8.7K Sep 22 02:31 releases_history.frm > -rw-rw---- 1 mysql mysql 55G Feb 13 21:21 releases_history.MYD > -rw-rw---- 1 mysql mysql 3.4M Feb 13 21:21 releases_history.MYI > > (also, is it supposed to be MyISAM? All the tables in aus4_dev are MyISAM > except for migrate_version, which is InnoDB) I haven't thought about that much, to be honest. I think I may want to chat again with you sometime about the database as a whole :). > I can do this but I anticipate it might take a few hours for the change, and > so I want to make sure I coordinate with you on that. You can go ahead and run it right now if you want. Mid-afternoon is the slow/dead period in terms of updates to this DB. Even if some updates fail in the meantime that's fine...it's just dev!

Oof, didn't see that yesterday. Alas, today I'm on a plane from 11 am to 3 pm, which is 6 pm Eastern, so I'm not sure I'll get to run this today (though I'll try). If not, I can run it tomorrow (it's a dev machine so it's OK to change it on a Friday).

(In reply to Sheeri Cabral [:sheeri] from comment #16) > Oof, didn't see that yesterday. Alas, today I'm on a plane from 11 am to 3 > pm, which is 6 pm Eastern, so I'm not sure I'll get to run this today > (though I'll try). If not, I can run it tomorrow (it's a dev machine so it's > OK to change it on a Friday). No worries. There's no huge rush on this.

This has been completed!

Thanks! Can you echo what you did in the bug so I can translate it into SQLAlchemy-fu?

Sure! (I ran this on my tablet last night, otherwise I usually copy and paste stuff into the bug) ALTER TABLE aus4_dev.releases_history add index(timestamp); it took 27 minutes on dev2 and 28 minutes on dev1, just for my own information. Also, the table rebuild showed the table had no (or very little) fragmentation: [root@dev1.db.phx1 aus4_dev]# ls -lrth releases_history.* -rw-rw---- 1 mysql mysql 8.7K Feb 15 02:26 releases_history.frm -rw-rw---- 1 mysql mysql 55G Feb 15 02:47 releases_history.MYD -rw-rw---- 1 mysql mysql 7.7M Feb 15 02:55 releases_history.MYI

Just to follow-up, it seems like this helped quite a bit. I'm getting sub 1s page loads from https://aus4-admin-dev.allizom.org/ now.

yay! Are there pretty graphs we can show? Are we friends of the tree? :D

Hehe, no graphs unfortunately...and this is still just a dev system! You're my friend though!