Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…

Image by …

So you thought your database is in good shape, but suddenly your CPU usage spiked and reached 100% at 2AM. How would you go about tracking the root cause and fix it?

1. Locate the root cause

If you are using CloudWatch or any infrastructure monitoring solution, you can start your investigation there.
To understand what happened in your database, you should enable the slow query log feature on your database. This will capture all the slow log queries into a log, based on a threshold that you define.
If you need, we created a step-by-step guide on how to enable the slow query log.

Once you enabled it, you can use slowquerylog.com to …

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on November 26, 2019.

It’s not uncommon these days for us to use a high availability stack for MySQL consisting of Orchestrator, Consul and ProxySQL. You can read more details about this stack by reading Matthias Crauwels’ blog post How to Autoscale ProxySQL in the Cloud as well as Ivan Groenwold’s post on MySQL High Availability With ProxySQL, Consul and Orchestrator. The high-level concept is simply that Orchestrator will monitor the state of the MySQL replication topology and report changes to Consul which in turn can update ProxySQL hosts using …

Kubernetes operators are meant to simplify the deployment and management of applications. Our Percona Kubernetes Operator for Percona XtraDB Cluster serves the purpose, but also provides users the flexibility to fine-tune their MySQL and proxy services configuration.

The document Changing MySQL Options describes how to provide custom

my.cnf

configuration to the operator. But what would happen if you made a mistake and specified the wrong parameter in the configuration?

Apply Configuration

I already deployed my Percona XtraDB Cluster and deliberately submitted the wrong

my.cnf

  configuration in

cr.yaml

 :

spec:
...
  pxc:
    configuration: | …

At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?”

As a developer, I just want to automatically identify and isolate slow queries!

Where I work, we do have SolarWinds Database Performance Monitor aka Vividcortex to find slow queries, so that helps. But that collects data at the database, which means you get to see slow queries, but maybe not application context.

There is also work done by a few developers which instead collects query strings, query execution times and query counts at the application. This has access to the call stack, so it can tell you which code generated the query that was slow.

It …

Now available on demand: Watch the Continuent Tungsten MySQL Use Case Webinar Series for 2020 with Eero Teerikorpi, Founder & CEO of Continuent.

Tags:  MySQL tungsten clustering Webinar use case High Availability data protection geo-scale geo-distributed …

All together now

Let’s now put together all that we’ve learned in InnoDB Data Locking – Part 2 “Locks” about table and record locks to understand following situation:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t FOR SHARE;
+----+
| id |
+----+
|  5 |
| 10 |
| 42 |
+----+
3 rows in set (0.00 sec)

mysql> DELETE FROM t WHERE id=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE
       FROM performance_schema.data_locks

… Facebook Twitter LinkedIn

Oracle Live.

On Wednesday, December 2, the world’s most popular open source database takes a leap forward in the cloud. Edward Screven, Oracle’s chief corporate architect, will share MySQL innovation that enables you to reduce cost and complexity while accelerating business insights. 

We have really exciting news for MySQL users, including some amazing testimonials from early adopters who will share their experiences. And, yes of course, there will be benchmarks!

Join us on this very special day in the history of MySQL. Watch the live event, and following the announcement, join us for a conversation in a live Q&A with MySQL product experts. 

Don’t miss this major announcement.   …

Oracle Live. On Wednesday, December 2, the world’s most popular open source database takes a leap forward in the cloud. Edward Screven, Oracle’s chief corporate architect, will share MySQL innovation that enables you to reduce cost and complexity while accelerating business insights. We have really ...

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:

 …