The latest from the MySQL community

I recently got into a bit of a debate about standardizing MySQL 8.4’s internal temporary table configuration. We wanted to cap memory usage efficiently, but relying on “rules of thumb” isn’t enough when production stability is at stake.

The main confusion was about how per-query limits fight with global limits. So, rather than guessing, I decided to break a sandbox environment to see exactly where the bytes go—and confirm the findings against the official documentation.

The Theory: Individual vs. Collective

Before I run the scripts, let’s establish the rules based on the MySQL 8.4 Reference Manual. There is a critical distinction between “individual” and “collective” limits.

1. tmp_table_size (The Cup)

This is the limit for a single table. According to the …

What is Schema Tracking? # In a distributed relational database system, like Vitess, a central component is responsible for serving queries across multiple shards. For Vitess, it is VTGate. One of the challenges this component faces is being aware of the underlying SQL schema being used. This awareness facilitates query planning. Table schemas are stored in MySQL’s information_schema, meaning that they are located in a VTTablet’s MySQL instance and not in VTGate.

Query planning is hard # Have you ever wondered what goes on behind the scenes when you execute a SQL query? What steps are taken to access your data? In this article, I'll talk about the history of Vitess's V3 query planner, why we created a new query planner, and the development of the new Gen4 query planner. Vitess is a horizontally scalable database solution which means that a single table can be spread out across multiple database instances.

Originally posted at Andres's blog. Traditional query optimizing is mostly about two things: first, in which order and from where to access data, and then how to then combine it. You have probably seen the tree shapes execution plans that are produced from query planning. I’ll use an example from the MySQL docs, using FORMAT=TREE which was introduced in MySQL 8.0: mysql>EXPLAINFORMAT=TREE->SELECT*->FROMt1->JOINt2->ON(t1.c1=t2.c1ANDt1.c2<t2.c2)->JOINt3->ON(t2.c1=t3.c1)\G***************************1.row***************************EXPLAIN:->Innerhashjoin(t3.c1=t1.c1)(cost=1.05rows=1)->Tablescanont3(cost=0.35rows=1)->Hash->Filter:(t1.c2<t2.c2)(cost=0.70rows=1)->Innerhashjoin(t2.c1=t1.c1)(cost=0.70rows=1)->Tablescanont2(cost=0.35rows=1)->Hash->Tablescanont1(cost=0.35rows=1)Here we can see that the MySQL optimizer thinks the best plan is to start reading from t1 using a table scan.

Overview

Hi, it’s too hot summer in Korea. Today I want to talk about an interesting and exciting topic. Try to making your own exporter in Go language.

If you register a specific query, it is a simple program that shows the result of this query as an exporter result metrics. Some of you may still be unfamiliar with what Expoter is.

I will explain about Exporter step by step in today’s post.

Exporter?

You can think of an Exporter as an HTTP server for pulling data from a time series database like Prometheus. Prometheus periodically calls the specific URL of the exporter and saves the result of metrics as a time series.

There are many exporters exist in the everywhere.

Typically, there is mysqld_expoter, which is Prometheus’s Offcial projects, and …

Computers are dumb. And they will do exactly what you ask them to do.  The trick often is to think as dumb as the computer. Sadly it is all to easy to assume that the computer is 'thinking' like you are and blunder into a head scratching puzzle.  Recently there was a post on MySQL Community Space Groundbreakers Developer Community site that shows that sometimes what is intended is not what you want but you are getting exactly what you asked. 

Quiz -- What happens if you run the following query?

SELECT concat('CREATE TABLE if does not exists sakila1.', 
    TABLE_NAME, 
    ' like sakila.', 
    TABLE_NAME,  ';') 
FROM information_schema.`TABLES` 
WHERE TABLE_SCHEMA = 'sakila'

A) You will …

Both MongoDB and the MySQL Document Store are JSON document stores.  The syntax differences in the two products are very interesting.  This long will be a comparison of how commands differ between these two products and may evolve into a 'cheat sheet' if there is demand.

I found an excellent Mongo tutorial Getting Started With MongoDB that I use as a framework to explore these two JSON document stores.
The DataI am using the primer-dataset.json file that MongoDB has been using for years  in their documentation, classes, and examples. MySQL has created the world_x data set based on the world database used for years in documentation, classes and examples.  The data set is a collection of JSON documents filled with restaurants around Manhattan.
…

In this blog post,  I’ll look at how to catch slow and frequent queries with ProxySQL.

More and more people are using ProxySQL because it is a great tool and it can help DBAs a lot. But many people do not realize that it is more powerful than it looks. It has many features and possibilities. I am going to show you one of my favorite “tricks” / use cases.

There are plenty of blog posts explaining how ProxySQL works. I am not going to that again. Instead, let’s jump straight to the point. There is a table in ProxySQL called “stats.stats_mysql_query_digest”. It is one of my favorite tables because it basically records all the queries that were running against ProxySQL. Without collecting any queries on the MySQL server, I can find …

Occasionally at conference or a Meetup, someone will approach me and ask me for help with a MySQL problem.  Eight out of ten times their difficulty includes a sub query. "I get an error message about a corrugated or conflabugated sub query or some such,"  they say, desperate for help.  Usually with a bit of fumbling we can get their problem solved.  The problem is not a lack of knowledge for either of us but that sub queries are often hard to write. 

MySQL 8 will be the first version of the most popular database on the web with Common Table Expressions or CTEs.  CTEs are a way to create temporary tables and then use that temporary table for queries. Think of them as easy to write sub queries!

WITH is The Magic WordThe new CTE magic is indicated with the WITH clause.

mysql> WITH myfirstCTE 
      AS (SELECT * FROM world.city WHERE …

Welcome to another post in the series of Percona Live featured tutorial speakers blogs! In these blogs, we’ll highlight some of the tutorial speakers that will be at this year’s Percona Live conference. We’ll also discuss how these tutorials can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured tutorial, we’ll meet Øystein Grøvlen, Senior Principal Software Engineer at Oracle. His tutorial is on How to Analyze and Tune MySQL Queries for Better Performance. SQL query …