MinervaDB Athena 2020 – Profiling Linux Operations for Performance and Troubleshooting by Tanel Poder

In Athena 2020, Tanel Poder talked about troubleshooting Linux operations performance based on the detailed forensics and evidence collection through0x.tools (Linux Process Snapper from Tanel), Which is a free, open source /proc file system sampling tool which annotates Linux thread handling activities more intuitively. The talk was really interesting for Open Source Database Systems folks who attended the conference because they spend most of their professional hours troubleshooting performance of their Database Infrastructure Operations for optimal performance and also in this talk Tanel’s approach was bottom-up (interpreting performance metrics with Linux probes) compared top-down approach (depending on Database Systems metadata views), You can download the PDF of the talk …

In this article, we will see the steps to create a snapshot of the existing RDS MySql Instance. Then we will see the steps to restore the snapshot which creates a new RDS Instance. At last, we will clean up the instances and snapshots.

I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.

In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:

SELECT   f.title AS film
,        fp.title AS prequel
FROM     film f LEFT JOIN film fp
ON       f.prequel_id = fp.film_id
WHERE    f.series_name = 'Harry Potter'
AND      fp.series_name = 'Harry Potter'
ORDER BY f.series_number;

It returns the following result set:

 …

While I’m switching labs next term after more than a decade with more comprehensive lab set, I’m hoping the new exercises build the students’ core SQL skill set. Next term, I hope to see whether the change is successful. I’ve opted for using Alan Beaulieu’s Learning SQL: Generate, Manipulate, and Retrieve Data, 3rd Edition, because it’s a great book and uses the MySQL database.

One exercise that the students will lose is a data migration exercise from a badly designed common_lookup table to a well designed common_lookup table. The starting point is shown below on the left and the fixed version is on the right.

         …

It’s always interesting when I upgrade from one release to the next. I learn new things, and in the case of MySQL’s installation and maintenance I become more grateful for the great team of developers working to produce MySQL 8.

A warning that caught my eye in MySQL 8 (8.0.21) was this one on Unicode with the utf8 character code:

Warning (code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

Currently, a character alias for utf8mb3 is an alias for the deprecated utf8mb3 (a 3-byte character set) until it is removed. When the utf8mb3 character set is …

One of the questions you often will be faced with operating a Linux-based system is managing memory budget. If a program uses more memory than available you may get swapping to happen, oftentimes with a terrible performance impact, or have Out of Memory (OOM) Killer activated, killing process altogether.

Before adjusting memory usage, either by configuration, optimization, or just managing the load, it helps to know how much memory a given program really uses.

If your system runs essentially a single user program (there is always a bunch of system processes) it is easy.  For example, if I run a dedicated MySQL server on a system with 128GB of RAM I can use “used” as a good proxy of what is used and “available” as what can still be used.

root@rocky:/mnt/data2/mysql# free -h …

A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.

I thought it would be interesting to put the idea to a test. Below is a Node.js script that acts as a utility that queries the MySQL database with substitution variables in query. It also returns a standard out (stdout) stream of the MySQL query’s results. It also supports three flag and value pairs as arguments, and optionally writes the results of the MySQL query to a log file while still returning result as the stdout value. All errors are written to the standard error (stderr) stream.

The Node.js solution is completely portable between Windows …

Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:

1. Configure your Express.js and MySQL development in a single Node.js application.
2. How to convert the list of RowDataPacket objects as elements of data, which is really just simple JavaScript knowledge.
3. How to bind variables into the query.

Like the other blog post, this one assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this …

These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:

1. Configure your Node.js development directory.
2. Build a small application to test a MySQL connection.
3. Build a small application that connects to the MySQL database and queries data.

This blog post assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.

Before you write the Node.js applicaiton, you need to setup a db developer directory. Then, create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory. You can use the following command …

When trying to do some Linux tuning for MySQL, there are a few options that will greatly influence the speed of MySQL.  Below are some of the most important of these settings to help you get started.

Swappiness

The first thing to look at is what swappiness is set to.  This will determine the tendency of the kernel to swap out memory pages.  In may cases, you will want to set this to “1” to keep the swapping to a minimum.  A value of “0” will disable it entirely.

You can determine the current value with the following command:

cat /proc/sys/vm/swappiness

If this is not set to “1”, you should consider making the change by using one of the following options:

# Make sure you are root and set swappiness to 1 
echo 1 > /proc/sys/vm/swappiness

# Or, you can use sysctl to do the same sysctl 
vm.swappiness vm.swappiness = 1

If the change helps, you will want to …