MySQL variables store data, label data, and let developers create more accurate and efficient code by turning long and complicated strings of characters into one simple variable. This article will explore user-defined variables. User-defined variables let us execute various data sets with one command and use this data whenever needed. Mastering variables in MySQL is […]

The post MySQL Variables – Definition and Examples appeared first on Devart Blog.

Tweet

The slow query log is the trusted old method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been overshadowed by the Performance Schema which has lower overhead and thus allows collecting statistics about all queries. The slow query log has one major advantage though: the data is persisted. In MySQL 8.0.14 which was recently released, there is an improvement for the slow query log: additional statistics about the recorded queries.

The slow query log with log_slow_extra enabled.

Contribution

Thanks for Facebook for …

Tweet

MySQL 8.0 introduced a new feature that allows you to persist configuration changes from inside MySQL. Previously you could execute SET GLOBAL to change the configuration at runtime, but you needed to update your MySQL configuration file in order to persist the change. In MySQL 8.0 you can skip the second step. This blog discuss how this works and how to backup and restore the configuration.

Using SET PERSIST to set a variable and the persisted_variables table in the Performance Schema to get a list of persisted variables.Persisting Variables

You persist changes with either the SET PERSIST or SET PERSIST_ONLY …

In this blog post, we’ll look at some tips on how to configure Aurora RDS parameters.

I was recently deploying a few Aurora RDS instances, a process very similar to configuring a regular RDS instance. I noticed a few minor differences in the way you configure Aurora RDS parameters, and very few articles on how the commands should be structured (for RDS as well as Aurora). The only real literature available is the official Amazon RDS documentation.

This blog provides a concise “how-to” guide to quickly change Aurora RDS parameters using the AWS CLI. Aurora retains the parameter group model introduced with RDS, with new instances having the default read only parameter groups. For a new instance, you need to create and allocate a new parameter group (this requires a DB reboot). After that, you can apply changes to …

Introduction

This is the final installment of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to cover variables that relate to enforcing data consistency, and how index statistics are handled and stored.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that while tuning recommendations are provided, this objective of this blog post series was NOT meant to be a tuning primer, but instead to explore the mechanics that each variable interacts with. As such I would like to advise against reading this guide and trying to fine tune all of the available InnoDB variables. System variable tuning is an exercise in diminishing returns, the most benefit you’ll get out of tuning your MySQL server will occur within the first 15 minutes of configuration. In …

Introduction

This is part four of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to explore the mechanics that impact CPU resourcing and how InnoDB handles concurrent threads. You’ll notice that a lot of the variables covered in section relate to features that are now disabled but were a lot more prevalent in previous versions of MySQL, particularly those that were released at a time where system context switching had a greater cost than it does today. These variables are still worth discussing as you may run into older systems that utilize these mechanics, and there are even modern systems I’ve worked with that have implemented these features to tune performance to its highest potential.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that …

Introduction

A couple of months ago I decided to give myself a refresher on the mechanics of InnoDB. Having a high level understanding of what’s going on under the hood can help provide the context needed in order to resolve issues you may encounter as well as assist you in ensuring that your MySQL instance is running efficiently. Everyone can stand to go back to basics every now and then, as it can help you pick up concepts that you may have missed the last time you researched the topic.

This time around I decided to give myself a refresher by re-reading the MySQL 5.6 reference manual, chapter 14, which covers the InnoDB engine. Despite having a wealth of documented knowledge and insights, I found that a lot of the points in the documentations were unclear, leading me to do more research and experimentation in order to get a bit of clarity on some of the specifics that I felt were missing. In order to help make the information …

Welcome to MySQL QA Episode 3: “Debugging: GDB, Backtraces, Frames and Library Dependencies”

In this episode you’ll learn how to use debugging tool GDB. The following debugging topics are covered:

1. GDB Introduction
2. Backtrace, Stack trace
3. Frames
4. Commands & Logging
5. Variables
6. Library dependencies
7. c++filt
8. Handy references
– GDB Cheat sheet (page #2): https://goo.gl/rrmB9i
– From Crash to testcase: https://goo.gl/3aSvVW

Also expands on live debugging & more. In HD quality (set your player to 720p!)

The post MySQL QA Episode 3: How to use the debugging tool GDB appeared …

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

To do so, we can use the "OUT" parameter mode while defining stored procedures.

In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.



Stored Procedure Definition:

Store the below stored procedure in a file named my_sqrt.sql and save it.

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
BEGIN
    SET op_number=SQRT(inp_number);
…

I got sidetracked today playing with Magic Squares and thought it might be a good chance to give an example of using MySQL Routines , Loops and IF checks.

So If you are unaware of what a Magic Square is I have included a few links. It might save you a Google search but otherwise think Sudoku as an example.

• http://en.wikipedia.org/wiki/Melencolia_I
• http://en.wikipedia.org/wiki/File:Albrecht_D%C3%BCrer_-_Melencolia_I_%28detail%29.jpg
• http://www.mathematische-basteleien.de/magsquare.htm
• …