This task is to pick a database for Nodepool

For continuous integration purposes, we are setting up a python based daemon named Nodepool. It maintains a pool of instances on the labs infrastructure and refill the pool as instances are consumed by Jenkins jobs.

To track status of instances and images in labs, Nodepool relies on a database backend. During the proof of concept build, I manually installed a MySQL database on the machine (labnodepool1001.eqiad.wmnet). When reinstalling fully from puppet, I found out I forgot about the database part and the service is obviously no more working.

I do not expect much queries per seconds to happen though I have no metric to back up my claim. It is definitely less consuming than Phabricator or Gerrit might be.

Nodepool hold a database connection for its internal process as well for each instances spawned in labs. Seems we might want up to 100 connections for a start. I am not sure how much of an issue it can be for our databases.

Once we have a database and credentials, the Nodepool services takes care of populating the database schema and performing schema changes. The suggested installation process is:

CREATE USER 'nodepool'@'localhost' IDENTIFIED BY '<password>';
CREATE DATABASE nodepooldb;
GRANT ALL ON nodepooldb.* TO 'nodepool'@'localhost';
FLUSH PRIVILEGES;

Nodepool uses a pure python MySQL client library (PyMysql). The credentials are configured in Nodepool file in operations/puppet.git:

modules/nodepool/templates/nodepool.yaml.erb:
    dburi: 'mysql+pymysql://nodepool:nodepool@localhost/nodepool'

Nodepool administrators can thus reuse those credentials if they need to manually interact with the db though anything we need is provided by the Nodepool command line client.

Potentially we can either:

• colocate the database with the nodepool service (on labnodepool1001.eqiad.wmnet)
• host the DB on one of our misc db hosts

The server is labnodepool1001.eqiad.wmnet, might want to have the DB as close to it as possible if that matters.

In T110693#1592130, @jcrespo wrote:

@hashar is this related to the other OpenStack-related databases that normally @Andrew works with?

Unrelated. It is a standalone daemon which is hosted in the labs host network and uses our OpenStack infrastructure just like any end users. The Nodepool schema is not related with whatever OpenStack database backend we might.

Maybe we can collocate it with one of the labs DB host supporting OpenStack since the host is in the same network segment.

@Andrew, @hashar -- I would think we treat nodepool as we do other openstack services? Why would it be treated differently from a db perspective?

I think everyone is right. It's a separate database, strictly speaking, but it should be hosted on the same server (m5-master) as the other labs db services, with the same security policy, but different passwords. Jaime should be able to set something up in a flash, when he has a flash to spare.

I'm on it, assigning space on the m5 shard.

BTW, the FLUSH PRIVILEGES; of the Openstack documentation is a bug: http://dbahire.com/stop-using-flush-privileges/

In T110693#1593380, @jcrespo wrote:

BTW, the FLUSH PRIVILEGES; of the Openstack documentation is a bug: http://dbahire.com/stop-using-flush-privileges/

Proposed upstream with https://review.openstack.org/219324 . Thank you it was a great read :-}

I am not sure how much of an issue it can be for our databases.

I had to ask for 2 reasons: usually, misc servers are not dedicated servers, which means they could be impacted or impact other issues. For example, phabricator is separate from puppet or other services due to its load.

Second, production "misc" databases have the same level of support than wiki databases: I will wake up if something is wrong with it, so I have to make sure that nothing will go wrong. That is why understanding its usage is important to me. Thanks for the extended details.

Deploying right now. Restricting access to libnodepool1001.

Change 235412 had a related patch set uploaded (by Jcrespo):
Add nodepooldb mysql database to m5 and grants from libnodepool1001

https://gerrit.wikimedia.org/r/235412

Change 235412 merged by Jcrespo:
Add nodepooldb mysql database to m5 and grants from libnodepool1001

https://gerrit.wikimedia.org/r/235412

Change 235416 had a related patch set uploaded (by Jcrespo):
Fix mysql grant issues on m5 (Followup to gerrit:235412)

https://gerrit.wikimedia.org/r/235416

Change 235416 merged by Jcrespo:
Fix mysql grant issues on m5 (Followup to gerrit:235412)

https://gerrit.wikimedia.org/r/235416

Access has been granted to m5-master only from labnodepool1001:

root@labnodepool1001:~$ mysql -h m5-master -u nodepool -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2158236
Server version: 5.5.5-10.0.16-MariaDB-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| nodepooldb         |
+--------------------+
2 rows in set (0.00 sec)

In need to add nodepooldb to the backups now.

Change 235424 had a related patch set uploaded (by Hashar):
Nodepool database user/pass

https://gerrit.wikimedia.org/r/235424

Change 235427 had a related patch set uploaded (by Hashar):
nodepool: adjust database configuration

https://gerrit.wikimedia.org/r/235427

Change 235424 merged by Hashar:
Nodepool database pass placeholder

https://gerrit.wikimedia.org/r/235424

Change 235427 merged by Jcrespo:
nodepool: adjust database configuration

https://gerrit.wikimedia.org/r/235427

In T110693#1593380, @jcrespo wrote:

BTW, the FLUSH PRIVILEGES; of the Openstack documentation is a bug: http://dbahire.com/stop-using-flush-privileges/

I proposed a change upstream to amend the documentation and referencing the above link. https://review.openstack.org/#/c/219324/ has been merged and the flush privileges command disappeared from the Nodepool online documentation http://docs.openstack.org/infra/nodepool/installation.html#database

Thanks @jcrespo !