971078 - Clear up allocation data in Inventory

Status: RESOLVED WONTFIX

(Infrastructure & Operations Graveyard :: WebOps: Inventory, task)

Description

[Jacques Uber [:uberj]]

Attachment: allocation_status.txt

We need to clean this up before we can start enforcing requirements for new and existing systems.

Comment 1

[Jacques Uber [:uberj]]

Attachment: updated allocation stats

On further inspection, a lot of the systems that I had marked as not having allocations *do* have allocations but have their allocation set to one of the deprecated kind. I've added a new attachment reflecting which systems are outright missing their allocation and which ones are just using a deprecated allocation.

We need to map each deprecated allocation to a new one and update the hosts marked as 'DEPRECATED ALLOCATION' in the attachment.

After rerunning the numbers, we have 856 systems outright missing their allocation and 1492 system using deprecated allocations (see attachment for more detail.)

Comment 2

[Jacques Uber [:uberj]]

:fox2mike, can you you post the mapping of old allocations to new ones?

Comment 3

[Shyam Mani [:fox2mike]]

(In reply to Jacques Uber [:uberj] from comment #2)
> :fox2mike, can you you post the mapping of old allocations to new ones?

https://infra.etherpad.mozilla.org/new-inventory-allocations I think I got all of them here.

Comment 4

[Jacques Uber [:uberj]]

Attachment: allocation import stats

I've imported the SRE-derived spread sheet which created the new allocations and set about 1000 allocation values.

Comment 5

[Jacques Uber [:uberj]]

We need to go back now and set the allocation of the hosts that still do not have their allocation set.

mysql> SELECT count(hostname) FROM systems WHERE allocation_id IS NULL;
+-----------------+
| count(hostname) |
+-----------------+
|             932 |
+-----------------+
1 row in set (0.01 sec)

Comment 6

[Shyam Mani [:fox2mike]]

(In reply to Jacques Uber [:uberj] from comment #5)
> We need to go back now and set the allocation of the hosts that still do not
> have their allocation set.
> 
> mysql> SELECT count(hostname) FROM systems WHERE allocation_id IS NULL;
> +-----------------+
> | count(hostname) |
> +-----------------+
> |             932 |
> +-----------------+
> 1 row in set (0.01 sec)

Do we have a list of these nodes?

Comment 7

[Jacques Uber [:uberj]]

Attachment: straglers

Here is a list of all hosts that still do not have their allocation set

Comment 8

[Shyam Mani [:fox2mike]]

(In reply to Jacques Uber [:uberj] from comment #7)
> Created attachment 8375814 [details]
> straglers
> 
> Here is a list of all hosts that still do not have their allocation set

Thank you!

Here's a list of the stragglers with owners - https://infra.etherpad.mozilla.org/inventory-allocations 

I did leave some out specifically, since I'm not sure they can be been allocated to any one team (like the seamicros). Let me know when this is imported, let's do one more run on what's left. 

Thanks!

Comment 9

[Sheeri Cabral [:sheeri]]

Hey folks, 

I moved the "round one" to a gdocs spreadsheet at https://docs.google.com/a/mozilla.com/spreadsheet/ccc?key=0AvGP1OghOtJSdEg2N2lRV3NkUGtFZHRoek9uX1JVOUE#gid=0 and given you edit access.

If it's in a spreadsheet or similarly-delimited form, it's WAY easier to import to the db....I have linked the "round one" part of the etherpad to the gdocs spreadsheet.

Comment 10

[Sheeri Cabral [:sheeri]]

I've put in formulas to get the correct allocation_id given the allocation description, and get the MySQL update statement from all those...making a backup now, let me know if you don't want me to import the new data.

Comment 11

[Sheeri Cabral [:sheeri]]

(looks like those changes were already made).

mysql> SELECT count(hostname) FROM systems WHERE allocation_id IS NULL;
+-----------------+
| count(hostname) |
+-----------------+
|             520 |
+-----------------+
1 row in set (0.00 sec)

Comment 12

[Sheeri Cabral [:sheeri]]

11:16 <uberj> i.e. its likely that all systems with the model='tegra' is going 
              to be in the 'release' allocation

mysql> select id,model from server_models where model like '%tegra%';
+-----+-------------------+
| id  | model             |
+-----+-------------------+
| 287 | Tegra 200 Dev Kit |
| 345 | Tegra 200 Dev Kit |
+-----+-------------------+
2 rows in set (0.00 sec)

Let's see how many are not yet allocated:
mysql> select count(*),allocation_id from systems where server_model_id in (287,345) and allocation_id IS NULL;
+----------+---------------+
| count(*) | allocation_id |
+----------+---------------+
|        0 |          NULL |
+----------+---------------+
1 row in set (0.00 sec)

mysql> select count(*),name from systems left join allocations on (allocation_id=allocations.id) where server_model_id in (287,345) group by allocation_id;
+----------+---------+
| count(*) | name    |
+----------+---------+
|      364 | Release |
|        5 | Webops  |
+----------+---------+
2 rows in set (0.01 sec)

So...that's all set. Any other shortcuts?

Comment 13

[Sheeri Cabral [:sheeri]]

62 servers match the pattern seamicro-releng-spare-seamicro-spare%.mozilla.com (% is a multiwildcard, like *)

30 servers match old-amopod-spare%

15 match seamicro-b-spare%

4 match seamicro-spare%

3 match seamicro-xeon-spare%

378 match node%seamicro - like node0.seamicro.phx1.mozilla.com, node511.seamicro.phx1.mozilla.com, etc.

That may not be  *all* the remaining ones, but it's all the groups....

Comment 14

[Jacques Uber [:uberj]]

I believe the problem with the seamicro entries are that who is responsible for the node depends on what app it is running? Can we just assign the seamicro stuff to infra?

Comment 15

[Sheeri Cabral [:sheeri]]

That's not a question I can answer. :(

Comment 16

[Shyam Mani [:fox2mike]]

(In reply to Jacques Uber [:uberj] from comment #14)
> I believe the problem with the seamicro entries are that who is responsible
> for the node depends on what app it is running? Can we just assign the
> seamicro stuff to infra?

If this is the only one that's left, assign all the spares to DCops.

Comment 17

[Sheeri Cabral [:sheeri]]

mysql> select count(*) from systems where hostname like '%spare%' and allocation_id is null;
+----------+
| count(*) |
+----------+
|      116 |
+----------+
1 row in set (0.01 sec)

Backed up just in case:
[[e-mail]1 ~]# mysqldump --skip-extended-insert inventory_mozilla_org systems > systems_backup_before_2_20_2014.sql


mysql> update systems set allocation_id=62 where hostname like '%spare%' and allocation_id IS NULL;
Query OK, 116 rows affected (0.02 sec)
Rows matched: 116  Changed: 116  Warnings: 0

Still 405 systems with no allocation.

Comment 18

[Sheeri Cabral [:sheeri]]

graphite1.private.scl3.mozilla.com
graphite2.private.scl3.mozilla.com
graphite3.private.scl3.mozilla.com
graphite4.private.scl3.mozilla.com
graphite5.private.scl3.mozilla.com
I asked corey in #systems and he said MOC, so I updated those to be MOC.

mysql> update systems set allocation_id=57 where hostname like 'graphite_.private.scl3.mozilla.com';

Comment 19

[Sheeri Cabral [:sheeri]]

Here's what's left:

08435
08449
08460
102-01c4
android-app.dmz.phx1.mozilla.com
athena
cn-vmware01.nms.nl.mozilla.com
hostname
ilossx02
jabba.scl1
KVM-101-08
KVM-103-03
lm-app-ident-stage01.labs.mozilla.org
mecha
test
vpn2b
w64-ix-slave30.winbuild.scl1.mozilla.com

I'd guess that those 17 can be looked at individually in inventory (although 'hostname' and 'test' look like entries that could be deleted/ignored, and I'd guess the labs one is going away, winbuild is probably owned by Release Eng/Ops, vmware is probably owned by Storage, and jabba probably owns jabba.scl1....etc etc these are my guesses, haven't changed any data on those.)

Then these 2 groups:

abandoned-via-p2v-node260.seamicro.phx1.mozilla.com
abandoned-via-p2v-node261.seamicro.phx1.mozilla.com
abandoned-via-p2v-node263.seamicro.phx1.mozilla.com
abandoned-via-p2v-node39.seamicro.phx1.mozilla.com
abandoned-via-p2v-pp-web03.phx.mozilla.com

So, maybe abandoned could be made into either Dcops or "Decommissioned"... (should I have made the spares from the last comment "decommissioned"?) 

And 279 entries of the format node#.seamicro.phx1.mozilla.com

Comment 20

[Rob Tucker [:rtucker]]

Inventory is to be deprecated. No additional features are being considered at this time. If you feel this bug was closed erroneously, please reopen and comment as to why.

Comment 21

[Sheeri Cabral [:sheeri]]

Not reopening, but food for thought - won't we need to clean up the information so we can import it into a new system? 

Or are we greenfielding a new inventory system?