In an IEEE Software “Adventures in Code” column titled Modular Data Analytics I describe the benefits and use of simple-rolap, a tool suite for relational online analytical processing. I have built simple-rolap based on the Unix make tool and a few shell scripts. With make approaching its 50th birthday, before writing the column I looked for possible modern and better alternatives I might be ignoring.
Continue reading "Unix make vs Apache Airflow"You may be familiar with the use of a database upsert of MERGE operation to insert a record into a table or update an existing record, if that record already exists. This evaluates the condition for finding the record only once, and is therefore more efficient than other alternatives. How can you efficiently handle a reverse operation of updating a record and deleting it if some condition holds?
Continue reading "Fast database UPDATE/DELETE operations"The progress indicator of MySQL or MariaDB long-running commands and queries is extremely extremely and frustratingly coarse. In an index update I’m running now it was stuck in the same state for more than three hours. Thankfully, the pmonitor tool allows us to precisely monitor the progress of many commands. Here’s an example of its application on MariaDB.
Continue reading "How to monitor MySQL / MariaDB query progress"I was trying to run a simple join query on MariaDB (MySQL) and its performance was horrendous. Here’s how I cut down the query’s run time from over 380 hours to under 12 hours by executing part of it with two simple Unix commands.
Continue reading "How I slashed a SQL query runtime from 380 hours to 12 with two Unix commands"I’m sure I’m not the only person on earth facing a complex and expensive analytical processing task. The one I’ve been working on for the past couple of years, runs on the GHTorrent 98.5 GB data set of GitHub process data. It comprises 99 SQL queries (2599 lines of SQL code in total) and takes more than 20 hours to run on a hefty server. To make the job’s parts run efficiently and reliably I implemented simple-rolap, a bare-bones relational online analytical processing tool suite. To ensure the queries produce correct results, I wrote RDBUnit, a unit testing framework for relational database queries. Here is a quick overview on how to use the two.
Continue reading "Modular SQL Queries with Unit Tests"This column is about a tool we no longer have: the continuous rise of the CPU clock frequency. We were enjoying this trend for decades, but in the past few years, progress stalled. CPUs are no longer getting faster because their makers can’t handle the heat of faster-switching transistors. Furthermore, increasing the CPU’s sophistication to execute our instructions more cleverly has hit the law of diminishing returns. Consequently, CPU manufacturers now package the constantly increasing number of transistors they can fit onto a chip into multiple cores—processing elements—and then ask us developers to put the cores to good use.
Continue reading "Faking it"
Last update: Tuesday, October 15, 2024 2:19 pm
Unless otherwise expressly stated, all original material on this page created by Diomidis Spinellis is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.
