1023555 - Please create a new mysql database for production mapper

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Pete Moore [:pmoore][:pete]]

We would like a new mysql database for mapper, which we hope to deploy to production in bug 1023541.

Database size is likely to be ~ 1GB or less.

Backup requirements are not very strict - it should be possible to create database from scratch relatively automatically within an hour.

It will be accessed primarily from production relengapi application cluster (web1.releng.webapp.scl3.mozilla.com and web2.releng.webapp.scl3.mozilla.com).

Please let me know if you need any further details.

Comment 1

[Sheeri Cabral [:sheeri]]

Is this db a tree-closing one? should it be on its own db instance or a shared one? What's the approximate volume of querying expected? What's the expected data size/growth rate?

Comment 2

[Pete Moore [:pmoore][:pete]]

(In reply to Sheeri Cabral [:sheeri] from comment #1)

> Is this db a tree-closing one?

Yes; the mapper application is a dependency of the b2g_build.py python script in mozharness that builds b2g. If the mapper database goes down, it will prevent new b2g builds from running, and b2g trees will need to be closed.

> Should it be on its own db instance or a shared one?

I would say it can be on the already existing relengapi db instance (mysql://[e-mail]/), since both the relengapi database and the (new) mapper database will be required by mapper application (since mapper is written in the relengapi framework as a relengapi blueprint) - therefore if either of these two databases go down, then mapper will fail. Therefore the two databases naturally sit together in terms of availability requirements, and therefore I would be happy for them to be on the same physical instance.

> What's the approximate volume of querying expected?

The querying level I expect to be extremely low. Developers *may* query it from time to time, and pull back all rows of the hashes table, which could be around 500,000 rows - but that is likely to be only a handful of devs, and I think at most once per hour (if they do more than this, we should block them, since there are much more efficient ways to pull back this data by only getting new rows).

The b2g build script for building b2g will only pull back a row at a time, maybe once per 5 mins(?) - basically one query for every b2g build that is run, which I think is usually every few minutes on average.

VCS Sync is the only other client of the database - but it will be populating the database rather than reading from it. The activity for this I have included in the answer to the following question.

> What's the expected data size/growth rate?

The schemas has two tables: hashes and projects.

hashes table
============

CREATE TABLE `hashes` (
  `hg_changeset` varchar(40) NOT NULL,
  `git_commit` varchar(40) NOT NULL,
  `project_id` int(11) NOT NULL,
  `date_added` int(11) NOT NULL,
  UNIQUE KEY `project_id__hg_changeset` (`project_id`,`hg_changeset`),
  UNIQUE KEY `project_id__git_commit` (`project_id`,`git_commit`),
  KEY `project_id` (`project_id`),
  KEY `hg_changeset` (`hg_changeset`),
  KEY `project_id__date_added` (`project_id`,`date_added`),
  KEY `git_commit` (`git_commit`),
  CONSTRAINT `hashes_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This table will initially have around 500,000 rows, and should grow at around 200 rows per day.

projects table
==============

CREATE TABLE `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8

This table will start with around 30 rows, and grow with maybe 50 rows per year (probably in bursts).

I hope I haven't been too verbose - I wanted to make sure I provided all information, in case it is useful. Let me know if you have other questions though.

Many thanks in advance, and many thanks for all your help thus far! :)

Pete

Comment 3

[Matt Pressman [:mpressman]]

prod database named mapper created on same host as existing releng db instance

Comment 4

[Pete Moore [:pmoore][:pete]]

Great, thanks!

Comment 5

[Matt Pressman [:mpressman]]

Ok, credentials created, but I wasn't able to test due to the vip listed in Comment 2 (generic-rw-vip.db.scl3.mozilla.com) is unresolved

[[e-mail]3 ~]$ host generic-rw-vip.db.scl3.mozilla.com

However, I am fairly confident as I used the same grants as the relengapi user connecting to relengapi database switching only the user, database and password.

Comment 6

[Pete Moore [:pmoore][:pete]]

Thanks Matthew. I'm testing now. :)

Comment 7

[Pete Moore [:pmoore][:pete]]

Hi Matthew, Dustin,

I've hit a problem that is probably related to comment 5 above - *but* it is not so straightforward to understand, because it seems that the relengapi framework is (at least, partly) running in production (https://api.pub.build.mozilla.org/), and this is also using the same mysql host... So I haven't quite got my head around this yet, since if the mysql host was not available, I would have thought relengapi app would not be able to run (however it is running as you see from the link).

The log files for relengapi show some celery problems (which is something I'm not using, so might be harmless):

e.g.:

2014-05-19 09:19:26,794 [relengapi.app] Exception on /base/temp/test-celery [GET]
Traceback (most recent call last):
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1379, in handle_user_exception
    return handler(e)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/lib/api.py", line 96, in exc_handler
    return h.handle_exception(exc_type, exc_value, tb)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/blueprints/base/__init__.py", line 82, in test_celery
    answer = add.delay(1, 2, 3).get()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/celery/result.py", line 152, in get
    interval=interval)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/celery/backends/amqp.py", line 153, in wait_for
    meta = self.consume(task_id, timeout=timeout)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/celery/backends/amqp.py", line 222, in consume
    no_ack=True, accept=self.accept) as consumer:
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/messaging.py", line 359, in __init__
    self.revive(self.channel)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/messaging.py", line 371, in revive
    self.declare()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/messaging.py", line 381, in declare
    queue.declare()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/entity.py", line 504, in declare
    self.exchange.declare(nowait)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/entity.py", line 166, in declare
    nowait=nowait, passive=passive,
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/channel.py", line 613, in exchange_declare
    self._send_method((40, 10), args)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/abstract_channel.py", line 56, in _send_method
    self.channel_id, method_sig, args, content,
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/method_framing.py", line 221, in write_method
    write_frame(1, channel, payload)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/transport.py", line 173, in write_frame
    frame_type, channel, size, payload, 0xce,
  File "/usr/lib/python2.7/socket.py", line 224, in meth
    return getattr(self._sock,name)(*args)
error: [Errno 32] Broken pipe


So I can't work out if relengapi is:
    a) surviving despite its database not being available; or
    b) is able to connect to its own database.

I tried from relengwebadm and also from the production web heads directly, e.g.:

From a web head
===============
[[e-mail]3 ~]# mysql -u mapper -pXXXXXXXXXXXX -h generic-rw-vip.db.scl3.mozilla.com mapper
ERROR 2005 (HY000): Unknown MySQL server host 'generic-rw-vip.db.scl3.mozilla.com' (1)

From admin node
===============
[[e-mail]3 relengapi]# mysql -u mapper -pXXXXXXXXXXXX -h generic-rw-vip.db.scl3.mozilla.com mapper
ERROR 2005 (HY000): Unknown MySQL server host 'generic-rw-vip.db.scl3.mozilla.com' (1)

also when trying to generate the schema for production mapper from the admin node:

[[e-mail]3 relengapi]# RELENGAPI_SETTINGS=/data/releng/src/relengapi/settings.py /data/releng/src/relengapi/virtualenv/bin/relengapi createdb
2014-06-13 04:59:54,981 registering blueprint docs
2014-06-13 04:59:55,027 registering blueprint base
2014-06-13 04:59:55,028 registering blueprint tokenauth
2014-06-13 04:59:55,029 registering blueprint auth
2014-06-13 04:59:55,029 registering blueprint mapper
2014-06-13 04:59:55,032 registering blueprint docs
2014-06-13 04:59:55,033 creating tables for database mapper
Traceback (most recent call last):
  File "/data/releng/src/relengapi/virtualenv/bin/relengapi", line 9, in <module>
    load_entry_point('relengapi==0.2.1', 'console_scripts', 'relengapi')()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/subcommands.py", line 56, in main
    args._subcommand.run(parser, args)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/blueprints/base/__init__.py", line 49, in run
    meta.create_all(bind=engine)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 3291, in create_all
    tables=tables)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1546, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1539, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1729, in contextual_connect
    self.pool.connect(),
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 332, in connect
    return _ConnectionFairy._checkout(self)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 626, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 433, in checkout
    rec = pool._do_get()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 945, in _do_get
    return self._create_connection()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 278, in _create_connection
    return _ConnectionRecord(self)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 404, in __init__
    self.connection = self.__connect()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 527, in __connect
    connection = self.__pool._creator()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 95, in connect
    connection_invalidated=invalidated
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 89, in connect
    return dialect.connect(*cargs, **cparams)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 376, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (2005, "Unknown MySQL server host 'generic-rw-vip.db.scl3.mozilla.com' (1)") None None
[[e-mail]3 relengapi]# 


So the mystery to me is how relengapi is running at all in production, if this host can't be reached or is not known about, since the same database host is used by mapper and relengapi, i.e. (with passwords hidden):

[[e-mail]3 relengapi]# cat settings.py 
SQLALCHEMY_DATABASE_URIS = {
    'relengapi': 'mysql://relengapi:[e-mail]/relengapi',
    'mapper': 'mysql://mapper:[e-mail]/mapper',
}

## Celery
# Any Celery configuration option can be included here; see
# http://docs.celeryproject.org/en/master/configuration.html#configuration
CELERY_BACKEND='amqp'
CELERY_ACCEPT_CONTENT=['json']
CELERY_TASK_SERIALIZER='json'
CELERY_RESULT_SERIALIZER='json'

CELERY_BROKER_URL='amqp://relengapi:[e-mail]:5672//relengapi-celery'

SESSION_COOKIE_NAME='s'
SECRET_KEY="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

RELENGAPI_AUTHENTICATION = {
    'type': 'external',
    'environ': 'AUTHENTICATE_MAIL',
}

admin = [
    'base.tokens.view',
    'base.tokens.issue',
    'base.tokens.revoke',
],
# support for ldap-groups isn't landed yet
future_RELENGAPI_ACTIONS = {
    'type': 'ldap-groups',
    'group-actions': {
        'team_relops': admin,
        'team_releng': admin,
    },
    # Base LDAP URI
    'uri': "ldap://ldap.db.scl3.mozilla.com/",

    # This needs to be a user that has sufficient rights to read users and groups
    'login_dn': "uid=bind-relengapi,ou=logins,dc=mozilla",
    'login_password': "XXXXXXXXXXXXXX",

    # The search bases for users and groups, respectively
    'user_base': 'dc=mozilla',
    'group_base': 'dc=mozilla',

    # set this to True for extra logging
    #'debug': True,
}
RELENGAPI_ACTIONS = {
   'type': 'static',
   'actions': {
       '[e-mail]': [
           'base.tokens.issue',
           'base.tokens.revoke',
           'base.tokens.view',
           #'mapper.mapping.insert',
           #'mapper.project.insert',
       ],
   },
}

P.S. (For Dustin) I still have to migrate some of the other settings from staging settings.py to production settings.py - but that is independent from this problem.

Comment 8

[Matt Pressman [:mpressman]]

Looks like I didn't put the full output for the vip, only the command, here it is in full:

[[e-mail]3 ~]$ host host generic-rw-vip.db.scl3.mozilla.com
host: couldn't get address for 'generic-rw-vip.db.scl3.mozilla.com': not found

Comment 9

[Matt Pressman [:mpressman]]

Let's try this one last time, third times a charm:

[[e-mail]3 ~]$ host generic-rw-vip.db.scl3.mozilla.com
Host generic-rw-vip.db.scl3.mozilla.com not found: 3(NXDOMAIN)

Comment 10

[Pete Moore [:pmoore][:pete]]

Dustin,

Are you sure the mysql host is correct in the releng production config ('generic-rw-vip.db.scl3.mozilla.com')?

    'relengapi': 'mysql://relengapi:[e-mail]/relengapi',

Thanks,
Pete

Comment 11

[Dustin J. Mitchell [:dustin] (he/him)]

It's wrong :)

Some DB vips are named foo-rw-vip, some are named foo-rw-zeus, and some are named foo-rw-zlb.  I probably copied the config from staging (where we've been doing most of our work, and where the vip is named staging-rw-vip), changed the name, and never looked at the logs.

Relengapi will happily start up and, since most of the stuff it does is not DB-related, serve pages, even if its DB is misconfigured.

Anyway, try it with generic-rw-zeus.db.scl3.mozilla.com

Comment 12

[Pete Moore [:pmoore][:pete]]

Thanks Dustin, unfortunately I can't seem to access generic-rw-zeus.db.scl3.mozilla.com either. :(

[[e-mail]3 relengapi]# mysql -u mapper -pXXXXXXXXXXX -h generic-rw-zeus.db.scl3.mozilla.com mapper
ERROR 2003 (HY000): Can't connect to MySQL server on 'generic-rw-zeus.db.scl3.mozilla.com' (110)
[[e-mail]3 relengapi]#

Comment 13

[Pete Moore [:pmoore][:pete]]

Seems to exist this time, though, so maybe either a flow problem, or service is down?

[[e-mail]3 ~]# host generic-rw-zeus.db.scl3.mozilla.com
generic-rw-zeus.db.scl3.mozilla.com has address 10.22.70.74

Comment 14

[Dustin J. Mitchell [:dustin] (he/him)]

Indeed, we need a flow.  I could swear we were already using DBs on the generic cluster..

Comment 15

[Pete Moore [:pmoore][:pete]]

Thanks Dustin!

Comment 16

[Pete Moore [:pmoore][:pete]]

Hey Dustin,

Should we also ask for a flow from relengwebadm too (e.g. to run relengapi createdb from there)?

Thanks Dustin!
Pete

Comment 17

[Dustin J. Mitchell [:dustin] (he/him)]

Oh, good point.

Comment 18

[Pete Moore [:pmoore][:pete]]

By the way, I also noticed we have mysql client installed on production web heads, but not on staging webhead - should we add that to the IT puppet repo? Although maybe no need to have it - just thought it good to be consistent.

Comment 19

[Pete Moore [:pmoore][:pete]]

Sorry, wrong bug! Ignore comment above.

Comment 20

[Pete Moore [:pmoore][:pete]]

Closing this bug, as the database has been created, and is working correctly! Thanks Matt and Dustin!