Testing Minos Conditions Database servers¶

Tests should be done before updating the Minos conditions database server

Detailed tests should be documented in Redmine issues

Date	CHG	Issue	Comment
2017/03/16	CHG000000012509	15856	Upgrade mysql v5.1.41 to v5.6, test ifdb04, deploy ifdb05
2017/06/TBD	CHG000000012510	16021	Upgrade to Mariadb, test mariadb-dev

Tests should include

• Web monitoring of connections via topdb_log
• Connect to each account
• Run dbu on a raw data file
• Run beamdbu on a beam data file
• Show connection limits
• Verify open file descriptor limits on the server host
• Run a keepup process
• Run a client process which will generate a temp database connection

In the example below, let the new database server to NEW.
For example in November 2020,
NEW=fnalmariadbprd

Web Monitoring¶

This is done as mindata@minos-data

Test interactively with

./topdb ${NEW}

Run ongoing monitoring during tests with

set nohup ; ${HOME}/topdb_log ${NEW} &

Check the results under https://minos.fnal.gov/database/topdb

Account connections¶

Check the ability to connect using accounts listed in /opt/mindata/*.mysql

./conndb ${NEW}

dbu generation of SAM metadata from raw data files¶

mindata@minos-data  predator

mkdir -p /tmp/mindata/dbu
cd       /tmp/mindata/dbu

LOON="-r R3.11" 
FILE=/pnfs/minos/fardet_data/2005-04/F00031300_0000.mdaq.root

. /grid/fermiapp/products/common/etc/setups.sh
setup minos
setup_minos ${LOON}

export ENV_TSQL_USER=writer
export ENV_TSQL_PSWD=`cat /opt/mindata/${ENV_TSQL_USER}.mysql`

NEW=ifdb05
${HOME}/predator/run_dbux ${FILE} ${NEW}

less F00031300_0000.log
less F00031300_0000.sam.py

Expect database connections like

Successfully opened connection to: mysql:odbc://minos-db1.fnal.gov/temp
This client, and MySQL server (MySQL 5.1.41-log) does support prepared statements.
Successfully opened connection to: mysql:odbc://minos-db1.fnal.gov/offline
This client, and MySQL server (MySQL 5.1.41-log) does support prepared statements.
Successfully opened connection to: mysql:odbc://minos-db1.fnal.gov/offline_dev
This client, and MySQL server (MySQL 5.1.41-log) does support prepared statements.
DbiCascader Status:- 
Status   URL

Closed         mysql:odbc://minos-db1.fnal.gov/temp
Closed (auth)  mysql:odbc://minos-db1.fnal.gov/offline
Open           mysql:odbc://minos-db1.fnal.gov/offline_dev

and

DbiTimer:DBUDAQFILESUMMARY: Query done. 1rows,    0.2Kb Cpu   6.2 , elapse   6.5
      SubWatch Query database     : Cpu   6.0 , elapse   6.4 , Starts 2
      SubWatch Create row objects : Cpu   0.2 , elapse   0.2 , Starts 3
      SubWatch Retrieve TSQL rows : Cpu   0.0 , elapse   0.0 , Starts 2
      SubWatch Fill row objects   : Cpu   0.0 , elapse   0.0 , Starts 2
CommitDbuRunSummary(): DbuRunSummary Far Run 31300 LastSubRun 0 RunType va-calibrate (0x101)
 {2005-04-25 16:36:07.000000000Z} {2005-04-25 16:36:07.621514000Z}
 Snarls 0 (0)  NonSnarls 3 (11) [MISMATCH]
 TermCode 1 Errors 0 TimeFrames 0 Dropped 0 Consistency 0x1
 ConfigFilesText:
DbuDaqConfigFilesText SeqNo 1275 MD5 "814976c91a4ca64b9b8cc5b93b488c77" 
 Text [actual text suppressed]

DbuDaqFileModule::EndJob() 
Delete DbiStatement
DatabaseInterface shutdown not requested

beamdbu¶

connection limits¶

The mindata/conndb script will run 

mysql> show variables like "max_%connections";

+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| max_connections      | 2020   |
| max_user_connections | 2000   |
+----------------------+--------+

With Mariadb these can increase to 3020, 3000

The DBA's will need to check user limits.
Non-Minos users will typically have a 250 limit.
With the Mariadb Galera cluster, these can increase to 2000.
A value of 0 indicates no limit.
We should remove some of the obsolete users ( *_old, ) after usage review.

mysql> select User,max_user_connections from mysql.user;
 ------------ ----------------------
| User       | max_user_connections |
 ------------ ----------------------
| root       |                    0 |
| mysqladmin |                    0 |
| reader     |                    0 |
| writer     |                    0 |
| writer     |                    0 |
| mysql      |                    0 |
| nuwriter   |                    0 |
| nuwriter   |                    0 |
| reader_old |                    0 |
| writer_old |                    0 |
| writer_old |                    0 |
| writer_old |                    0 |
| reader     |                    0 |
| slvadmin   |                    0 |
| dbmetrics  |                    0 |
| minerva    |                  250 |
| argoneut   |                  250 |
| guest      |                  250 |
 ------------ ----------------------
22 rows in set (0.20 sec)

file descriptor limits¶

Ask the DBA's to verify this, with a Service Desk request.

At one time these were (on minervadb01) :

$ ulimit -n
15000
$ cat /proc/sys/fs/file-max
386543

Try 32760 for ulimit.

Keepup¶

Temp¶