Tag: MySQL

How MySQL and PostgreSQL handle concurrency under the hood

DatabaseSQL
Andy Wu

When choosing a relational database to use for a new project, MySQL and PostgreSQL are nearly the two most popular open-source database options that come to mind. Although there isn’t much difference for developers to tell when writing SQL or using ORM, there is a slight difference in performance potentially since MySQL and Postgres have their own implementations on records locking when dealing with concurrent operations. Today we will mock a simple scenario and run some SQL queries to demonstrate how MySQL and Postgres use lock mechanisms differently to handle concurrency under the hood.

Read More

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

Database
Andy Wu

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
# User@Host: 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)
Read More