loading...

Question pivoting for faster results

strredwolf profile image STrRedWolf ・2 min read

In my job, as the subject matter expert of the monitoring system we've brought on and use, I get the odd question from our support team. Some are obvious to me, some are worthy of an article in an internal knowledge base, and some... well...

"Hey Red, maybe this will be quicker. I need a list of all Linux servers that have under four gigs of RAM and MySQL installed. Can you query the monitoring system database quickly?"

I said I'll check into it. With some time on my hands I did...

And this is where I curse H.P. Lovecraft and fight great old eldritch horrors.

You see, the monitoring system's database layout isn't all that great. It's optimized for fast writing, a choice the vendor of said system made a long time ago. Plus, their reporting features only get you so far, and the use of MySQL in the past and MariaDB now makes me want to file bug reports for features in MS SQL Server (at least MariaDB has "WITH alias AS (SELECT statement)" now!). Plus, data I need isn't in a normal structure -- no, it's captured by convoluted means and placed in the fast write structure tables.

Which means to get the list, I can't really use MySQL or MariaDB's SQL language. It's not that capable. It took me 2.5 hours of frustration to realize that.

I give up, talk to the guy who asked the question... and realized an easier way. I was initially looking for Linux servers with 2 gigs of RAM. I could of said "What Linux servers have MySQL on them?" The monitoring system has a fast query page that lets me ask that, without going through the database or tearing my hair out. I was able to say "Okay, do this, you get the list of 130 servers to check the RAM on." The support guy said "I'll take that!" and can easily dig through them to get a shorter list.

The point here is that sometimes, you have to reorder the question and do a bit of optimization yourself. SQL servers do this all the time, gathering some info on the query, reordering the searches needed, and producing a fast execution path.

Posted on by:

strredwolf profile

STrRedWolf

@strredwolf

Furry software engineer. I solve digital problems. Code tailored "WHILE U WAIT". Transit enthusiast & sci-fi/furry artist/author.

Discussion

pic
Editor guide