[MySQL / MariaDB] Find out slow queries with slow query log

When some functions in a web service respond slower than usual when data grows and we are trying to find out what specific database queries are slowing down the service, we can give MySQL Slow Query Log a try.

Let's start with enabling it.

> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.000 sec)

Note that the SET GLOBAL VARIABLES is only effective in the current running MySQL instance. It will switch back to the default value after MySQL restarts.

By default, any queries that take above 10 seconds to run is recognized as "slow query". To change the threshold to any period besides 10, type the following command.

> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.000 sec)

Next, we also want to specify where we are going to store the slow query logs. In our case let's set it to file here.

> SET GLOBAL log_output = 'FILE';
Query OK, 0 rows affected (0.000 sec)

Take a look at the location where MySQL is storing the log file.

> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log_file | ubuntu-slow.log |
+---------------------+-----------------+
1 row in set (0.001 sec)

Now, everything is settled. Open another MySQL client and try SELECT SLEEP(6); to simulate a slow query that takes 6 seconds to response.

After that, we can open the file /var/lib/mysql/ubuntu-slow.log (location from the output of the last command) and see that the query is correctly logged:)

...
# Time: 210615 23:56:55
# [email protected]: root[root] @ localhost []
# Thread_id: 11694  Schema:   QC_hit: No
# Query_time: 6.000225  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1652003815;
SELECT SLEEP(6);

When we finish debugging, simply set the slow query log variable to OFF to turn off logging.

> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.000 sec)

Train a CNN model to identify captcha code with TensorFlow and Keras

In the last post (Automatically fill in captcha code in course selection system), we exploited the "Play Audio" button function to obtain the captcha code in the course selection system from my college. Today, we will be going through another approch to identify the captcha code by training a CNN model with TensorFlow and Keras.

A captcha code from the course selection system.

 

Install Needed Packages

Below are the environment and package versions that I perform the training in this post.

MacOS 10.14.6
Python: 3.7.3
numpy: 1.18.0
scikit-learn: 0.22
TensorFlow: 2.0.0
Pillow: 6.2.1

If lacking any of these packages, just simply install them with the following commands.

NumPy: pip install numpy
scikit-learn: pip install scikit-learn
TensorFlow: pip install tensorflow
Pillow: pip install Pillow

Keep Reading

Automatically fill in captcha code in course selection system

Captcha code is broadly used in different websites to ensure the contents are only accessible by humans rather than bots. The course selection system in my college has also been adopting captcha code in the login page to reduce momentary load of its server. (by slowing down everyone's time to fill in the code and hit log in)

Besides that, the system was kindly designed a "Play Audio" button as well for visually impaired people to "hear" the captcha code. However, it becomes a defect that we can take advantage of the "Play Audio" button function to fill in the captcha code programmatically for us.

Since I have done my last course selection in my college life, I will demonstrate how to fill in the captcha code by exploiting the "Play Audio" button function in below.


Keep Reading

Using FFmpeg concatenate multiple videos

Some cameras split a sigle video into multiple files during recoding due to the limitation of the maximum file size in the file system(E.g. maximum single file size for FAT32 is 4GB). In this post, we will take advantage of FFmpeg to merge back our video fragments into a continuous single video.

 

To begin with, we put all our video fragments into the same folder and create a new file called clips.txt. Next, we add the filenames of the video fragments into clips.txt in the order we want to concatenate and seperate them by new lines.

An example of clips.txt.

file 'first.mp4'
file 'second.mp4'
...

Last, running the following command will generate the concatenated video named output.mp4 for us:)

ffmpeg -f concat -i clips.txt -c copy output.mp4

[Matplotlib] Multiple axes with different scales

It's quite often for me to plot out the statistical data while training machine learning models. E.g., teaching models to play games.

As the records increase, the chart becomes crowded as well as difficult to see how the line grows. Therefore, a strategy come up that only plotting out the average of each 250 records in a chart.

Keep Reading

  • 1
  • 2