<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">
Hi Mark et al.,
<div class=""><br class="">
</div>
<div class=""> It is probably a little late, but I thought I should point out a feature in the DANA library</div>
<div class="">I put in some time ago to solve the SQLite issue for the online monitoring. If you set the</div>
<div class="">SQLITE_TO_LOCAL config parameter to a file name on the local disk AND you specify</div>
<div class="">using an sqlite file in you JANA_CALIB_URL, then it will copy the file first and use the</div>
<div class="">copy. The benefit of copying is that it is read-only so you don’t have the file lock contention</div>
<div class="">for a network mounted SQLite file. It looks like you’ve solved this by randomly choosing</div>
<div class="">from 100 copies of the sqlite which will work too.</div>
<div class=""><br class="">
</div>
<div class="">I just thought I’d mention it in case you run into a similar situation in the future and this</div>
<div class="">could be useful.</div>
<div class=""><br class="">
</div>
<div class="">Also, FWIW: The jobs we run at NERSC all copy the SQLite file from cvmfs to the local</div>
<div class="">disk at start up to avoid lock contention.</div>
<div class=""><br class="">
</div>
<div class="">
<div class=""><br class="">
Regards,<br class="">
-David<br class="">
<br class="">
-------------------------------------------------------------<br class="">
David Lawrence Ph.D.<br class="">
Staff Scientist, Thomas Jefferson National Accelerator Facility<br class="">
Newport News, VA<br class="">
<a href="mailto:davidl@jlab.org" class="">davidl@jlab.org</a><br class="">
(757) 269-5567 W<br class="">
(757) 746-6697 C<br class="">
<br class="">
</div>
<div><br class="">
<blockquote type="cite" class="">
<div class="">On Apr 29, 2019, at 11:57 AM, Mark Ito <<a href="mailto:marki@jlab.org" class="">marki@jlab.org</a>> wrote:</div>
<br class="Apple-interchange-newline">
<div class="">
<div text="#000000" bgcolor="#FFFFFF" class="">
<p class="">Folks,</p>
<p class="">You will recall that we switched the default choice for the database engine for CCDB at JLab from SQLite to MySQL back on March 12 (<a class="moz-txt-link-freetext" href="https://mailman.jlab.org/pipermail/halld-offline/2019-March/003561.html" moz-do-not-send="true">https://mailman.jlab.org/pipermail/halld-offline/2019-March/003561.html</a>).
Since then we have been studying usage patterns and CCDB performance on MySQL. And, not unexpectedly, we have experienced "storms" where the MySQL server is overloaded with CCDB requests from farm jobs and performance is severely degraded. Jobs don't crash
but they take a long time to read in constants before starting to analyze events, up to two hours in the worst cases. These have been occurring once every day or two and last for a few hours.<br class="">
<br class="">
We have learned a lot. The storms are definitely coming from farm jobs when many of them start at the same time. The queries have been analyzed (no big inefficiencies found). These things were invisible to us when we were running with the SQLite default. As
a result IT is putting up a new beefier server for us to use for the farm.<br class="">
<br class="">
Until the new server comes online (a week or two), <b class="">I am proposing that we switch back to using SQLite as the default for CCDB</b>, but with two differences from how we used SQLite at JLab in the past.<br class="">
<br class="">
(1) SQLite will be used <b class="">for the farm only</b>. Interactive users at JLab will continue to use the MySQL server.<br class="">
(2) Rather than accessing the ccdb.sqlite file on the group disk, farm jobs will access one of 100 copies of ccdb.sqlite, located on the work disk, chosen randomly for each job.<br class="">
<br class="">
The idea is to separate farm demand for CCDB constants from other functions of our main database server while we wait for the new server that will do just that. Other functions include the writing of calibration constants to the CCDB, RCDB access, and MCwrapper
job control functions.<br class="">
<br class="">
Note again that this change only affects processes using the defaults from the build_scripts-based environment set-up. Those that set their own JANA_CALIB_URL environment variable explicitly will get what they ask for, as always.<br class="">
<br class="">
<b class="">The proposed switch-over time is COB today.</b> I will make the change unless I hear objections.<br class="">
</p>
<p class=""> -- Mark<br class="">
</p>
</div>
_______________________________________________<br class="">
Halld-offline mailing list<br class="">
<a href="mailto:Halld-offline@jlab.org" class="">Halld-offline@jlab.org</a><br class="">
https://mailman.jlab.org/mailman/listinfo/halld-offline</div>
</blockquote>
</div>
<br class="">
</div>
</body>
</html>