<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
We'll look into both the indicies and these weird NULL queries. With all the improvements and DB changes it is reasonable to assume some indicies were missed
<span id="🙂">🙂</span></div>
<div class="elementToProof">
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature">
<div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Thomas Britton</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Staff Scientist in Scientific Computing</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Jefferson Lab</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
x7624<br>
</div>
</div>
</div>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Marty Wise <wise@jlab.org><br>
<b>Sent:</b> Friday, February 2, 2024 2:02 PM<br>
<b>To:</b> Thomas Britton <tbritton@jlab.org><br>
<b>Subject:</b> epscidb-b status</font>
<div> </div>
</div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Calibri}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
span.x_EmailStyle17
{font-family:"Calibri",sans-serif;
color:windowtext}
span.x_ui-provider
{}
.x_MsoChpDefault
{font-family:"Calibri",sans-serif}
@page WordSection1
{margin:1.0in 1.0in 1.0in 1.0in}
div.x_WordSection1
{}
-->
</style>
<div lang="EN-US" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="x_WordSection1">
<p class="x_MsoNormal">Thomas,</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Things continue to look pretty good. Utilization looks normal, etc. BUT,
</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">there is a long-pending transaction that started <span class="x_ui-provider">
2024-02-01 04:25:03. It is PID 33839 which is:</span></p>
<p class="x_MsoNormal"><span class="x_ui-provider"> </span></p>
<p class="x_MsoNormal" style="margin-left:.5in"><span class="x_ui-provider">33839 | clasuser | clonfarm1.jlab.org:59224 | CLAS_Hydra | Sleep | 96 | | NULL</span></p>
<p class="x_MsoNormal"><span class="x_ui-provider"> </span></p>
<p class="x_MsoNormal"><span class="x_ui-provider">That “NULL” at the end is where the query would be shown normally. I haven’t yet been able to find any information about why a transaction might have a null query.</span></p>
<p class="x_MsoNormal"><span class="x_ui-provider"> </span></p>
<p class="x_MsoNormal"><span class="x_ui-provider">On clonfarm1, the connection above corresponds to the process –</span></p>
<p class="x_MsoNormal"><span class="x_ui-provider"> </span></p>
<p class="x_MsoNormal" style="margin-left:.5in"><span class="x_ui-provider">hydra 7942 1 99 Feb01 ? 1-08:50:57 python3 /group/clas12/hydra/scripts/hydra_feeder.py -i /local/hydra/input// -o /home/hydra/hydra_in_converted// -M auto --config
/group/clas12/hydra/Hydra.cfg</span></p>
<p class="x_MsoNormal"><span class="x_ui-provider"> </span></p>
<p class="x_MsoNormal">I do not see any ill effects of this… The ibdata1 file is still only 76MB and the CLAS_Hydra INNODB files are only about 15GB and do not seem to be growing excessively. There has been some decrease in available disk space since the server
started, but I’d expect a bit of that. Nevertheless, I will keep an eye on it. With things behaving normally, the individual per-table INNODB files (like CLAS_Hydra/RunTime.ibd) can supposedly be cleaned/shrunk/etc. by an “optimize” operation. I need to see
this actually work to be sure, but if it turns out that some of your table files have a tendency to grow excessively, we should be able to manage it. On epscidb-a, that specific file was about 110GB, I think. So far, on epscidb-b, it is only about 14GB. Aside
from the main ibdata1 file, it is this one that was the second largest.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">One other <i>minor</i> issue is the diagnostics I’ve seen indicating that there are lots of queries/joins that are not using indexes… I found I am able to enable logging of such queries, so I turned that on briefly yesterday… The log
was filled with LOTS (tens of thousands) of lines logging queries with no indexes…
</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal" style="margin-left:.5in"><span class="x_ui-provider">SELECT ID FROM Plot_Types where Name="CTOF_tdc" && IsChunked =1 && FileType="png";</span><br>
<span class="x_ui-provider"># User@Host: clasuser[clasuser] @ ifarm1901.jlab.org [129.57.70.18]</span><br>
<span class="x_ui-provider"># Thread_id: 57051 Schema: CLAS_Hydra QC_hit: No</span><br>
<span class="x_ui-provider"># Query_time: 0.000111 Lock_time: 0.000023 Rows_sent: 1 Rows_examined: 109</span><br>
<span class="x_ui-provider"># Rows_affected: 0 Bytes_sent: 90</span></p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Note that Rows Examined is 109. The documentation says that some small queries are no big deal… And, has a means to ignore queries with no indexes that examine fewer than x rows… and suggests values for x like 10,000. So, it is very likely
that this is a non-problem. I will set the ignore value to something reasonable (maybe 1,000, 10,000?) on MD.
</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">There are actually still a couple of other small tweaks I want to make at that time as well. Another item is there is apparently a way to limit how big the ibdata1 file is allowed to become. Seems like something less than infinite would
be a good idea <span style="font-family:"Segoe UI Emoji",sans-serif">😊</span>. Unfortunately, the documentation is unclear whether this option exists or not for our version. I suspect it does, though the reference docs do not include the option in their description.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">There will probably be other things that come up as we have an opportunity to analyze the servers performance over time. I will compile my “wish list” and touch base with you ahead of MD to discuss.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"><span style="">Marty Wise</span></p>
<p class="x_MsoNormal"><span style="">JLab CST/CNI</span></p>
<p class="x_MsoNormal"> </p>
</div>
</div>
</body>
</html>