[HydraTeam] Fw: epscidb-b status

[Thomas Britton]

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 🙂

Thomas Britton
Staff Scientist in Scientific Computing
Jefferson Lab
x7624
________________________________
From: Marty Wise <wise at jlab.org>
Sent: Friday, February 2, 2024 2:02 PM
To: Thomas Britton <tbritton at jlab.org>
Subject: epscidb-b status


Thomas,



Things continue to look pretty good. Utilization looks normal, etc. BUT,



there is a long-pending transaction that started 2024-02-01 04:25:03. It is PID 33839 which is:



33839 | clasuser        | clonfarm1.jlab.org:59224 | CLAS_Hydra         | Sleep   |     96 |                             | NULL



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.



On clonfarm1, the connection above corresponds to the process –



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



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.





One other minor 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…



SELECT ID FROM Plot_Types where Name="CTOF_tdc" && IsChunked =1 && FileType="png";
# User at Host: clasuser[clasuser] @ ifarm1901.jlab.org [129.57.70.18]
# Thread_id: 57051  Schema: CLAS_Hydra  QC_hit: No
# Query_time: 0.000111  Lock_time: 0.000023  Rows_sent: 1  Rows_examined: 109
# Rows_affected: 0  Bytes_sent: 90



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.



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 😊. 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.



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.



Marty Wise

JLab CST/CNI


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.jlab.org/pipermail/hydrateam/attachments/20240202/82c47f22/attachment.html>