Project

General

Profile

Support #16021

test mariadb server

Added by Arthur Kreymer over 3 years ago. Updated almost 3 years ago.

Status:
Resolved
Priority:
Normal
Start date:
03/29/2017
Due date:
06/15/2017
% Done:

100%

Estimated time:
20.00 h
Duration: 79

Description

Test the mariadb service which will replace mysql on minos-db1

The test server is mariadb-dev

Testing procedures are documented in
https://cdcvs.fnal.gov/redmine/projects/database/wiki/MINOSDBTEST

Minos_DB_status_053017.pdf (606 KB) Minos_DB_status_053017.pdf Arthur Kreymer, 05/31/2017 09:09 AM

History

#1 Updated by Arthur Kreymer over 3 years ago

PLAN

TASK DONE COMMENT
Monitoring 03/29 http://minos.fnal.gov/database/topdb/mariadb-dev/
Accounts 03/29 tested reader, writer, writer_old
primary keys rhatcher - dbi tables, then DBAs will add the rest
load dev DBAs after primary keys are added
dbu predator pending table loading
beamdbu
conn limits by DBAs
fd limits by DBAs
keepup by Minerva
minos-dcs02 ahabig, badgett
Date: Fri, 24 Mar 2017 16:31:44 -0500
From: Svetlana G Lebedeva <svetlana@fnal.gov>
...
We created a new MariaDB-Galera cluster on mariadb-dev,
also we decided to keep your 3306 port with data directory in non-default location,
and I started testing schema modification.  

Will update you next week when you can connect to your databases start testing.
I do not plan to load data for offline database, just offline_dev, is it ok?!

From: Svetlana G Lebedeva
Sent: Friday, March 17, 2017 2:35 PM
Date: Mon, 27 Mar 2017 16:14:23 -0500
From: Olga Vlasova <ovlasova@fnal.gov>
...
The following users had been created in mariadb-dev:
| argoneut  | %                    
| guest         | %                   
| minerva    | %                    
| nova          | %                    
| reader       | %                     
| writer        | %.fnal.gov           
| writer_old | %.fnal.gov           
| writer         | %.minos-soudan.org   
| dbmetrics  | dbweb2.fnal.gov      

These users permissions/grants are the same as in ifdb05.
No data was loaded yet and no databases created except offline_dev.

Please test you can connect to offline_dev using VIP mariadb-dev as following:
mysql -u user_name -p -h mariadb-dev -P 3306 offline_dev
Date: Mon, 27 Mar 2017 16:31:53 -0500
From: Jiyeon Han <jyhan@fnal.gov>
I tested the connection using "reader" account only since I don't know the password for "minerva" account.
The connection worked with "reader" account.
I will check the production team if they know the password or the authorization method for "minerva" account.
Date: Tue, 28 Mar 2017 10:45:33 -0500
From: Jiyeon Han <jyhan@fnal.gov>

 I checked our production team about "minerva" account, but they also didn't know
 any password for this account. It seems that "minerva" account uses the different
 authorization method.

 As I said, I could access "reader" account and found these databases :

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| offline_dev        |
+--------------------+
2 rows in set (0.01 sec)

I also tried "describe offline_dev", but it said that Table 'offline_dev.offline_dev" doesn't exist,
 which might be true since you didn't upload any data.

This is all which you wanted to test? 
If there is any further test which I need to do, please let me know.

#2 Updated by Arthur Kreymer over 3 years ago

The mysql upgrade was managed under a Major Change CHG000000012509
Presumably because this involves move than one experiment, potentially
Argoneut, Minera, Minos, Nova.

We should have an appropriate CHG go cover the Mariadb migration.
------------
The MariaDB Change is CHG000000012510 targeting 04/20

#3 Updated by Arthur Kreymer over 3 years ago

I tested accounts and montoring :

    ssh mindata@minosdatagpvm01

NEW=mariadb-dev

Accounts

./conndb ${NEW}
Tue Mar 28 09:00:12 CDT 2017

STATUS
--------------
mysql  Ver 14.14 Distrib 5.6.19, for Linux (x86_64) using  EditLine wrapper

Connection id:        168727
Current database:    
Current user:        writer@minosdatagpvm01.fnal.gov
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.5.5-10.1.16-MariaDB MariaDB Server
Protocol version:    10
Connection:        mariadb-dev via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            4 days 21 hours 7 min 35 sec

Threads: 4  Questions: 191  Slow queries: 0  Opens: 33  Flush tables: 2  Open tables: 13  Queries per second avg: 0.000
--------------

MAX CONNECTIONS
Variable_name    Value
max_connections    110
max_user_connections    100

TESTING USERS IN /opt/mindata 

         reader
Current user:        reader@minosdatagpvm01.fnal.gov

         writer
Current user:        writer@minosdatagpvm01.fnal.gov

     writer_old
Current user:        writer_old@minosdatagpvm01.fnal.gov

Monitoring

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

See http://minos.fnal.gov/database/topdb/mariadb-dev/

#4 Updated by Arthur Kreymer over 3 years ago

Before replication to Mariadb with Galera clustering,
all tables need to have primary keys.

Tables accessed via the Minos dbi ( database interface )
should already have such keys, but Robert Hatcher has found that a few do not.
The dbi carefully tests that tables have only the expected columns,
so Robert will add these properly, probably this week.

After that is done, DBA's can add arbitrary autoincrement keys to
the remaining tables ( not accessed via the dbi ),
and load mariadb-dev for testing,

Then Minerva can use mariadb-dev for load testing,
to address the issues described in INC000000835983.

#5 Updated by Arthur Kreymer over 3 years ago

  • Due date set to 06/15/2017
  • Status changed from Assigned to Work in progress
  • % Done changed from 0 to 30
  • Estimated time set to 20.00 h

Date: Tue, 2 May 2017 17:23:55 +0000
From: Olga Vlasova <>

1) After Robert have done big chunk of job creating PKs on Minos tables
we are down to just several tables with no PKs:

 caldet:    PlexPixelSpotToStripEnd
            PlexPixelToRawChannel
            PlexRawChannelReadoutType
           PlexRawChannelToPinDiodesource

hardware:   FABPLNINSTALL
FABPLNINSTALLVLD

offline:    DCS_HV

production:  CALPMTDRIFT

Once Robert will finish creating PKs on the rest of the tables,
we will load them in mariadb-dev.

2)  Due to amount of work and testing that needs to be done,
we found it reasonable to postpone Minos migration to mariadb-prd
until June.

The implementation of this change will also take more than one day. 
Preliminary we plan to start Tuesday night, June 13
and finish Thursday afternoon, June 15.
After we will be done with migration Thursday afternoon,
network team will point `minos-db1` alias to VIP mariadb-prd.
Please, Minos team, give us your feedback on this plan.

#6 Updated by Arthur Kreymer over 3 years ago

Noted cancelled Minos Migration Project meeting Thu 05/04 13:00-14:00

During this meeting we would like to discuss the following:
1) Review the migration plan and the timeframe for certain tasks.
2) Review the list of users DBAs need to create in mariadb-prd.
3) Decide on what databases will
stay in mariadb-dev (alignment_dev, offline_dev, etc.)
and what databases will be migrated to mariadb-prd.

There are may be additional items on agenda related to Minos migration project.
Thank you,
Olga.

#8 Updated by Arthur Kreymer about 3 years ago

  • % Done changed from 30 to 50

Notes from jyhan

MINOS Meeting on July 6th, 2017

  • MINERvA test update: *
    + DCS_MAG_NEAR table is copied into minervamc table removing unnecessary information
    +
    o 1500 jobs are tested via ifdb04, mariadb-dev (VIP)
    o The change speeded up the job
    o CPU efficiency calculated using condor log file is 80% in average, peak of CPU efficiency is 88%
    o
    o FIFEmon underestimated CPU efficiency when the job duration is less than 1 hour
    o The performance was similar between ifdb04 and mariadb-dev
    o Another test for scalability was done with 3000 jobs via mariadb-dev
    o
    o CPU efficiency is similar and CPU usage is less than 50% in average
    + mariadb-dev1 vs. mariadb-dev2 test was done before the change to check parameter differences and there was not much
    difference
    + Fixing time period of DCS_MAG_NEARVLD table (setting up to 2020) is not done yet, but there is much necessary to
    implement it now. 
    + MINERvA experiment is happy for the performance improvement for the production
  • Final migration plan *
    + Missing component before final migration is adding index in DCS_HV table
    +
    o Robert worked on cleaning up this table before adding index
    o Jiyeon and Phil will check the status of this work and try to add index in table next week in ifdb04
    o Test is required via ifdb04 (development) after adding index
    + Request the conditional approval for final mariadb migration (depending on DCS_HV status)
    + Tentative date would be Aug. 1st
    +
    o The beam will be down this Friday and no data until October, so it is good time for migration
  • Additional work in future *
    + offline_dev includes several tables which should be copied to offline 
    +
    o It will be done after final mariadb migration project
    o After cleaning up offline_dev, table name of “offline_dev” will be changed to “offline_int” or so
  • Other item discussed *
    + DBAs are to archive minos databases in /pnfs before final migration.
    + There is suspicious connection tried every week, which will be checked next week after cleaning up accounts

#9 Updated by Arthur Kreymer about 3 years ago

  • % Done changed from 50 to 70

Notes regarding DCS_HV table primary keys.
Sounds like this is ready to go.
Cleanup can be done later under Mariadb.

Date: Thu, 6 Jul 2017 20:48:46 +0000
From: Jiyeon Han <>
To: Robert W Hatcher <>
Cc: Philip Rodrigues <>, Jiyeon Han <>, Arthur E Kreymer <>
Subject: Re: DCS_HV table
Parts/Attachments:
1 OK 49 lines Text
2 Shown 74 lines Text
----------------------------------------

O.K, then this table already has indices, but the cleaning up was not done yet.
Hew.... 70,000 is a lot...
Could you explain which part is non-trivial?

When SEQNO has two rows, then we need to cleaning up with "bad" flag.
If 'a'=='b', then it is simple since we just need to mark one bad.
But, if 'a'!='b', then how to identify the correct one?

Thanks,
Jiyeon

On 7/6/17 3:40 PM, Robert W Hatcher wrote:

On Jul 6, 2017, at 3:30 PM, Jiyeon Han &lt;&gt; wrote:
> Hi Robert,
>
> I know that you are not supposed to be involved this work now.
> But, I just want to check what was the status for DCS_HV table, so
> Phil and I can try to pursue this work before the final migration.
>
> I know that you already finished cleaning up this table. But, I guess
> that you didn't add any index in the table, yet. Is it correct?
> Could you let us know what we need to do for this table?
>
> Thanks a lot,
> Jiyeon
Actually, the reverse:
  I have added the primary key (indices), but for the older data they values in the "ROW_COUNTER" are not ideal (but
also not damaging).
What I haven't done is clean up the cases where a SEQNO has conflicting data for the same chunk i.e. SEQNO 'x' has
rows that say HV_LOGICAL channel 'z' was both set to 'a' and to 'b' during that time period.
This really should get cleaned up but marking all the cases where they disagree (some have 'a' == 'b' and thus are
just duplicates), to the case where it is flagged as BAD^tm.
But there are about 70,000 of those and it's non-trivial to script something to id and fix them.
-robert

#10 Updated by Arthur Kreymer about 3 years ago

Date: Fri, 7 Jul 2017 20:44:58 +0000
From: Olga Vlasova <>

We refreshed offline database on ifdb04 from ifdb05.

We will migrate DCS_HV table to mariadb earlier next week.

#11 Updated by Arthur Kreymer about 3 years ago

  • % Done changed from 70 to 80

Date: Thu, 13 Jul 2017 14:04:28 +0000
From: Olga Vlasova <>

DCS_HV table restore is completed.

...

From: Olga Vlasova
Sent: Wednesday, July 12, 2017 5:37 PM

We are in a progress of restoring DCS_HV table in mariadb-dev.
Restore had been running for about 7 hrs already, so hopefully it will be completed soon.
Will update you in a morning.

#12 Updated by Arthur Kreymer about 3 years ago

Date: Thu, 13 Jul 2017 16:53:57 +0000
From: Olga Vlasova <>

Let us reserve one more day just in case and schedule migration for August, 2.
Is that ok with everyone?

#13 Updated by Arthur Kreymer about 3 years ago

The migration is scheduled to take 3 days.

We should not plan a migration that completes on a Friday.
If the start needs to be delayed due to the vacation schedule,
move it to Tue Aug 8, finishing Thu Aug 10.

#14 Updated by Arthur Kreymer about 3 years ago

Date: Thu, 13 Jul 2017 20:02:57 +0000
From: Olga Vlasova <>

Sounds good!

We will schedule migration for Aug 8 - Aug 10.

#15 Updated by Arthur Kreymer about 3 years ago

DBAs recommends renaming the two databases so avoid _dev names in production.
There are two :
alignment_dev - no longer in use.
offline_dev - in daily use by the dbu process.

I have created RITM0587660 to rename alignment_dev to aligment.

I will open a separate Issue to move dbu activity from offline_dev to offline.

#16 Updated by Arthur Kreymer about 3 years ago

I created the issue for moving DBU tables to offline from offline_dev.

https://cdcvs.fnal.gov/redmine/issues/17295

#17 Updated by Arthur Kreymer almost 3 years ago

To: minos-users,minerva-software,minosdb-support

Subj: Minos database transition this week

The Minos conditions database at minos-db1 will be readonly
from Tuesday Aug 8 09:00 through Thursday Aug 10 17:00
while we move the server from Mysql to Mariadb.

This will delay keepup processing of Near Detector data for Minerva.

This is being managed under CHG000000012510 and
https://cdcvs.fnal.gov/redmine/issues/16021

There will be no changes needed in user clients.

#18 Updated by Arthur Kreymer almost 3 years ago

  • % Done changed from 80 to 90

The minos-db1 alias was incorrectly shifted from ifdb05 to mariadb-prd this morning.

This was reported in INC000000875663 around 11:45.
This was corrected around 12:20.

#19 Updated by Arthur Kreymer almost 3 years ago

Nothing was intended to be copied from mariadb-dev to mariadb-prd.

DBAs - Minerva is asking that a new database minervamc be copied to mariadb-prd.

Jiyeon - please clarify whether the data should be copied from ifdb04 or mariadb-dev.

Date: Tue, 8 Aug 2017 16:59:34 +0000
From: Jiyeon Han <>
To: Olga Vlasova <>, 'Svetlana G Lebedeva' <>
Cc: Arthur E Kreymer <>, Jiyeon Han <>, Philip Rodrigues <>
Subject: minos db migration

Hi Olga and Svetlana,

For the final mariadb migration, I would like to make sure one thing.
For mariadb-prd, will all data base be migrated from mariadb-dev to mariadb-prd?

To improve the database performance, Phil implemented additional database, "minervamc",
in ifdb04 and mariadb-dev. But, I just found that this table doesn't exist in ifdb05.
So, if mariadb-prd will be migrated from ifdb05, then "minervamc" will be missing.

Thanks,
Jiyeon

#20 Updated by Arthur Kreymer almost 3 years ago

Last update: 2017-08-09 16:47:53 CDT - Olga Vlasova (Work notes)
mariadb-prd1 is ready for READONLY tests.
users "reader" and "minerva" can proceed with their queries.
 
Thank you,
Olga.

#21 Updated by Arthur Kreymer almost 3 years ago

Date: Mon, 7 Aug 2017 13:28:25 +0000
From: Arthur Kreymer <>
To: , ,
Subject: Minos database transition this week

The initial database copy to the new server mariadb-prd1 is complete.

minerva and reader accounts have been enabled on mariadb-prd1.

Please test Minos database clients ( readonly ) with mariadb-prd1.
Send reports of issues to minosdb-support.

Thanks !

#22 Updated by Arthur Kreymer almost 3 years ago

CHG000000012510 commented

Last update: 2017-08-10 10:23:28 CDT - Olga Vlasova (Work notes)
Hi Jiyeon,
 
Per your request we increased user connections up to 6000.
 
We are ready to switch minos-db1 alias from ifdb05 to mariadb-prd.
If no objections, it will be done around 11:30 am after Jim O. will be back from the meeting he has to attend.
 
Thank you,
Olga.

#23 Updated by Arthur Kreymer almost 3 years ago

Date: Thu, 10 Aug 2017 16:09:59 +0000
From: Fermilab Service Desk <>
To: , , , , , ,
, , , , ,
Subject: Change Request CHG000000012510 commented -- Migrate MINOS mysql databases from IFDB05 to MariaDB Galera cluster.

Last update: 2017-08-10 11:09:38 CDT - Arthur Kreymer (Work notes)
Minos approves the move of network alias minos-db1
from ifdb05 to mariadb-prd

#24 Updated by Arthur Kreymer almost 3 years ago

I have started web monitoring of mariadb-prd1, prd2, prd3

set nohup ; ${HOME}/topdb_log mariadb-prd1 &
set nohup ; ${HOME}/topdb_log mariadb-prd2 &
set nohup ; ${HOME}/topdb_log mariadb-prd3 &

See
https://minos.fnal.gov/database/topdb/mariadb-prd1/NOW.txt
https://minos.fnal.gov/database/topdb/mariadb-prd2/NOW.txt
https://minos.fnal.gov/database/topdb/mariadb-prd3/NOW.txt

#25 Updated by Arthur Kreymer almost 3 years ago

Last update: 2017-08-10 12:14:27 CDT - Olga Vlasova (Work notes)
Network alias `minos-db1`had been switched from ifdb05 to mariadb-prd.
 
Please test connection and queries.
 

#26 Updated by Arthur Kreymer almost 3 years ago

Monitoring is up to date, with an interruption during the switch.
Maybe due to local DNS caching.

#27 Updated by Arthur Kreymer almost 3 years ago

I restarted the predator script which run 'dbu' and declared files to SAM.
All seems to be running correctly.

mindata@minos-data

${HOME}/predator/predator  start
-rw-r--r-- 1 mindata e875 0 Aug  8 11:42 /minos/app/home/mindata/predator/LOG/predator/STOP
CLEARED PREDATOR STOP FLAG at Thu Aug 10 18:16:49 UTC 2017

set nohup ; ${HOME}/predator/predator 2017-08 &

#28 Updated by Arthur Kreymer almost 3 years ago

  • % Done changed from 90 to 100

Date: Thu, 10 Aug 2017 20:07:22 +0000
From: Arthur Kreymer <>
To: , ,
Subject: Re: Minos database transition this week

The new server was put in production around 12:15 today.

So far everthing seems so be working.

Report any issues to minosdb-support.

#29 Updated by Arthur Kreymer almost 3 years ago

  • Status changed from Work in progress to Resolved


Also available in: Atom PDF