ALU

mysql

How to Manage Alibaba MySQL Database From CLI

Alibaba Cloud offers highly-available on-demand MySQL, SQL Server, and PostgreSQL databases as part of their ApsaraDB for RDS (Relational Database Service).

RDS is easy to set up and deploy. The service handles all administrative tasks for a database server including provisioning, patching up, and recovery in case of a disaster.

Original Link

Slow MySQL Start Time in GTID? Binary Log File Size May Be the Issue

Have you been experiencing slow MySQL startup times in GTID mode? We recently ran into this issue on one of our MySQL hosting deployments and set out to solve the problem. In this article, we break down the issue that could be slowing down your MySQL restart times, how to debug for your deployment, and what you can do to decrease your start time and improve your understanding of GTID-based replication.

How We Found The Problem

We were investigating slow MySQL startup times on a low-end, disk-based MySQL 5.7.21 deployment which had GTID mode enabled. The system was part of a master-slave pair and was under a moderate write load. When restarting during a scheduled maintenance, we noticed that the database server took 5-10 minutes to start up and begin accepting connections. That kind of delay didn’t make sense, so we set out to investigate.

Original Link

MySQL High Availability Framework Explained: Part 1

In this two-part article series, we will explain the details and functionality of a High Availability (HA) framework for MySQL hosting using MySQL semi-synchronous replication and the Corosync plus Pacemaker stack. In Part I, we’ll walk you through the basics of High Availability, the components of an HA framework, and then introduce you to the HA framework for MySQL.

What Is High Availability?

The availability of a computer system is the percentage of time its services are up during a period of time. It’s generally expressed as a series of 9′s. For example, the table below shows availability and the corresponding downtime measured over one year.

Original Link

MySQL Data Type: An Overview of the Data Types in MySQL

One cannot handle the vast amount of data present in the world without a proper database management system. MySQL is one of the most popular database management systems used in the industry. In my previous blog on MySQL Tutorial, you would have got an understanding of the various SQL queries that can be executed. In this blog on MySQL Data Types, I will be discussing the different data types used in MySQL.

In this article on MySQL Data Types, I’m going to cover the following:

Original Link

MySQL Database Horizontal Scaling Solution

The constant development of cloud technologies, mature storage resources and computing resources, and gradually decreasing costs make it easier for enterprises to develop, deploy, and provide services. This benefits rapidly developing small and medium-sized enterprises that can respond to the increasing traffic by choosing to continuously add new machines to increase the application cluster.

However, with the development of enterprises, there is still a bottleneck which we cannot remove by simply piling up machines; this is the performance ceiling caused by a growing database. One of the effective measures to solve this performance ceiling is to shard the database, and to make sure the data volume of a single table is smaller than five million data entries.

Original Link

Hive Metastore Configuration After Fresh Installation

For the beginners playing around in Hive, a stoppage arises with the proper configuration. After placing Hive libraries in designated folders and updating necessary environment variables, many times the first eager execution of hive fails with the exception “HiveException java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient”. That’s when Hive Metastore needs to be configured, which is pretty simple and straightforward.

There are two ways to configure Hive Metastore. We can use ‘schematool’ or directly source the hive-schema-3.1.0.mysql.sql script provided by Hive into the Metastore database.

Original Link

How TiDB Combines OLTP and OLAP in a Distributed Database

TiDB is an open-source, cloud-native, MySQL-compatible distributed database that handles hybrid transactional and analytical processing (HTAP) workloads. It is a member of the “NewSQL” class of relational databases that are designed to be deployed at a massive scale. For those of you wondering, the “Ti” stands for Titanium.

PingCAP started building TiDB just three and a half years ago, but already the product has gathered upwards of 15,000 GitHub stars, 200 contributors, 7200 commits, 2000 forks, and 300 production users. Recently TiDB also collected InfoWorld’s 2018 Bossie Award as one of the best open source software projects in the data storage and analytics space.

Original Link

MySQL Workbench Tutorial: A Comprehensive Guide to the RDBMS Tool

The previous article on MySQL Tutorial mainly focused on the various commands and concepts related to SQL. In this tutorial on MySQL Workbench, you will learn the tools for MySQL to perform various operations.

The following topics will be covered along this blog:

Original Link

MySQL Tutorial: A Beginners Guide To Learn MySQL

MySQL Tutorial is the second article in this blog series. In the previous article, What is MySQL, I introduced you to all the basic terminologies that you needed to understand before you get started with this relational database. In this blog of MySQL, you will be learning all the operations and commands that you need to explore your databases.

The topics covered in this article are mainly divided into 4 categories: DDL, DML, DC, and TCL.

Original Link

Why Is Oracle and Microsoft SQL Adoption Low for Developers on AWS?

As more applications are either migrated or originally built in the cloud, the entire application stack morphs with the changes to leverage new cloud-native technologies. Typical stacks now look markedly different than those just a few years ago.

Modern apps aren’t using your father’s tools and the once-dominant market share some legacy solutions used to command are dwindling. The changes are led by an evolution in the development and deployment lifecycle, the rise of microservices and a small side project by Amazon that turned into a gargantuan business assisting cloud development.

Original Link

Finding Table Differences on Nullable Columns Using MySQL Generated Columns

Some time ago, a customer had a performance issue with an internal process. He was comparing, finding, and reporting the rows that were different between two tables. This is simple if you use a LEFT JOIN and an IS NULL comparison over the second table in the WHERE clause, but what if the column could be null? That is why he used UNION, GROUP BY and a HAVING clauses, which resulted in poor performance.

The challenge was to be able to compare each row using a LEFT JOIN over NULL values.

Original Link

How To Fix MySQL Replication After an Incompatible DDL Command

MySQL supports replicating to a slave that is one release higher. This allows us to easily upgrade our MySQL setup to a new version by promoting the slave and pointing the application to it. However, though unsupported, there are times when the MySQL version of slave deployed is one release lower. In this scenario, if your application has been performing much better on an older version of MySQL, you would like to have a convenient option to downgrade. You can simply promote the slave to get the old performance back.

The MySQL manual says that ROW based replication can be used to replicate to a lower version, provided that no DDLs replicated are incompatible with the slave. One such incompatible command is ALTER USER , which is a new feature in MySQL 5.7 and not available on 5.6. :

Original Link

Encryption of the InnoDB System Tablespace and Parallel Doublewrite Buffer

In my last post, I compared data-at-rest encryption features available for MySQL and MariaDB. As noted at the time, some of the features available for Percona Server for MySQL were in development, and the latest version (5.7.23) sees two of them released as ALPHA quality.

Encrypting the InnoDB System Tablespace

The first of the new features is InnoDB system tablespace encryption via innodb_sys_tablespace_encrypt, which would provide encryption of the following data:

Original Link

Redis + MySQL = Fast, Economic Scaling

One of the things that is so great about Redis is how well it can complement and extend other databases in your ecosystem. While replacing legacy backend databases with newer ones is often seen as expensive and risky, they don’t easily scale in the linear fashion required for users’ ‘instant experience’ expectations.

Some of the things that make it difficult to accommodate modern application needs using a traditional MySQL architecture are:

Original Link

Groovy SQL: More Groovy Goodness

Ladies and gentlemen, today, I want to share with you how, in the context of the development of my progressive web applications, the Apache Groovy language is making my life a breeze, particularly the groovy-sql module, which provides a higher-level abstraction over Java’s JDBC technology, when it is hard for me to understand why I have to use JPA and an ORM, like Hibernate, to connect to my MySQL database. Then, let us not waste any more time creating a new connection for each user, when interacting with a relational database is time-consuming, it would be a huge mistake to not use a database connection pool library, like the Apache Commons DBCP, to set up our data source.

Maven Dependencies

<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.2.0</version>
</dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.41</version>
</dependency> <dependency> <groupId>org.codehaus.groovy</groupId> <artifactId>groovy-sql</artifactId> <version>2.4.13</version>
</dependency>

Original Link

How to Optimize MySQL Queries for Speed and Performance

You can deploy fast, secure, and trusted MySQL database instances on Alibaba Cloud. Alibaba has an advanced network of cloud-based technologies and their breaking performance and flexible billing have enabled cloud without borders for its over one million paid customers.

Alibaba Cloud has continued to show enormous contribution to the open-source communities and has empowered developers worldwide. Alibaba Cloud was the winner of the prestigious 2018 MySQL Corporate Contributor Award and is also a platinum sponsor of the MariaDB foundation.

Original Link

How to Back Up MySQL Database on Alibaba Cloud ECS Running Ubuntu 16.04

MySQL is one of the most popular open source database management systems. It is widely used in websites and web applications to store data. Irrespective of its extensive use and acceptance by developers, MySQL requires backups when deployed in a production environment.

Backups are important for restoring data in case it is deleted or overwritten by mistake. When using MySQL server from Alibaba Cloud, you must consider data protection using backups and make it an essential part of your system or website even when the possibility of a database crash seems unlikely.

Original Link

This Week in Data With Colin Charles #54: Percona Server for MySQL Is Alpha

I consider this to be the biggest news for the week: Alpha Build of Percona Server for MySQL 8.0. Experiment with it in a Docker container. It is missing column compression with dictionary support, native partitioning for TokuDB and MyRocks (excited to see that this is coming!), and encryption key rotation and scrubbing. All in all, this should be a fun release to try, test, and also to file bugs for!

Database paradigms are changing, and it is interesting to see Cloudflare introducing Workers KV a key-value store, that is eventually consistent and highly distributed (at their global network of 152+ data centers). You can have up to 1 billion keys per namespace, keys up to 2kB in size, values up to 64kB, and eventual global consistency within 10 seconds. Read more about the cost and other technicals too.

Original Link

Multiple MySQL Databases With One MySQL Container

Problem Statement

I want to create 2 databases inside one MySQL container and give the user of the first database full access to the 2nd database. With the official MySQL image one can easily create a database and allow a user access to that database. However, creating a 2nd database is not easily provisioned.

Solution

Docker images work on the concept of layers. Each new command, so to speak, creates a new layer, and herein lies our solution.

Original Link

Kubernetes Networking

In the previous blog, "What Is Kubernetes," you got an understanding on Kubernetes. In this blog on Kubernetes networking, I will primarily focus on the networking concepts involved in Kubernetes.

Kubernetes is an open-source container orchestration tool that provides a portable platform for automating the deployment of containerized applications.

Original Link

Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL

Protecting the data stored in your database may have been at the top of your priorities recently, especially with the changes that were introduced earlier this year with GDPR.

There are a number of ways to protect this data, which until not so long ago would have meant either using an encrypted filesystem (e.g. LUKS), or encrypting the data before it is stored in the database (e.g. AES_ENCRYPT or other abstraction within the application). A few years ago, the options started to change, as Alexander Rubin discussed in MySQL Data at Rest Encryption, and now MariaDB®, MySQL®, and Percona Server for MySQL all support encryption at-rest. However, the options that you have — and, indeed, the variable names — vary depending upon which database version you are using.

Original Link

Provisioning SQL Databases With Initial Sets of Data Using Java and MockNeat

MockNeat is a "faker"-like Java library that is used to mock complex Java objects or to generate arbitrary data for a wide range of applications.

In this tutorial, I am going to talk about how we can programmatically provision a relational database with initial sets of data by programmatically generating and executing complex SQL Inserts statements.

Original Link

This Week in Data: The Relicensing of OSS

There has been a lot of talk around licenses in open source software, and it has hit the database world in the past weeks. Redis Labs relicensed some AGPL software to the Commons Clause (in their case, Apache + Commons Clause; so you can’t really call it Apache any longer). I’ll have more to say on this topic soon, but in the meantime you might enjoy reading Open-source licensing war: Commons Clause. This was the most balanced article I read about this move and the kerfuffle it has caused. We also saw this with Lerna (not database related), and here’s another good read: Open Source Devs Reverse Decision to Block ICE Contractors From Using Software.

Reviewing is under way for Percona Live Europe 2018 talks: the review of the tutorials is complete. We can expect to see a schedule by mid-September, so hang in there—I’ve received a lot of messages asking if talks are going to be approved or not.

Original Link

How to Use a Subquery in MySQL

How to Use Subquery in MySQL 

In this tutorial, we will show you how to use the subquery in MySQL.

What is the Subquery? A MySQL subquery is a query nested inside the other query like Select, Delete,  Update and Insert.  Suppose we have the database table as something like below:

Original Link

An Introduction to DBMS Types

This article will be of interest to those learning about databases who don’t have much prior knowledge of the different types or of the terminology involved. It provides a brief review and links to further information, which I hope is useful to anyone starting to work with databases. If you’re already a wizard with SQL or Neo4j, you won’t need to read any further!.

If you’re still with us, let’s first introduce some terminology. A database collects and organizes data, while access to that data is typically via a “database management system” (DBMS), which manages how the data is organized within the database. This article discusses some of the ways a DBMS may organize data. It reviews the difference between relational database management systems (RDBMS) and NoSQL.

Original Link

Using an ApsaraDB for RDS Client With Auto-Completion Features

MyCLI is a command line interface (CLI) for MySQL, MariaDB, and Percona with auto-completion and syntax highlighting features. This tool supports auto-completion of table names, database names, aliases, and SQL syntax. It also keeps track of the queries entered for your convenience.

You’ll need to use the pip command to install it on Linux or Unix-like system as MyCLI is written in Python. You can learn more about MyCLI here http://mycli.net/

Original Link

Python ODBC Custom API

This is a continuation of my previous article, "Database Connectivity and Transaction in Python." I strongly recommend you to read the above article first. Here, I am going to explain how to design your own generic ODBC API class in python through which you can connect to Oracle, MS SQL server, Sybase, MySql, etc. Also, I have added one more API through which you can access columns of a table with their respective column name.

Let’s start:

Original Link

MySQL Database SELECT Query Operation in Mule 4

Step1: Select Mule project from Anypoint studio and write the project name. Then click ok.

Step2: Drag and drop HTTP listener, database select and to track the logs logger component from Mule palette.

Original Link

Setting Up TDE in MySQL RDS

Transparent Data Encryption (TDE) is a technology used to encrypt databases by offering encryption at a file level. If you have critical and sensitive data, TDE can help protect the privacy of your information and prevent data breaches by enabling data-at-rest encryption in the database. TDE helps you meet various regulatory requirements including PCI DSS and HIPAA.

Source: https://www.mysql.com/products/enterprise/tde.html

Original Link

InnoDB Cluster in a Nutshell Part 3: MySQL Shell

Welcome to the third part of this series. I’m glad you’re still reading, as hopefully, this means you find this subject interesting at least. Previously we presented the first two components of MySQL InnoDB Cluster: Group Replication and MySQL Router and now, we will discuss the last component, MySQL Shell.

MySQL Shell

This is the last component in the cluster, and I love it. Oracle has created this tool to centralize cluster management, providing a friendly, command-line based user interface.

Original Link

Installing and Configuring Atlassian Confluence With MySQL in Docker Containers

Atlassian Confluence is already available as a Docker Image from the Docker Hub, but you still need to provide a database instance for a production setup. Let’s build a docker-compose file to create a container from this image together with a container running MySQL.

First, per the docs on the Docker Hub page, create an external folder /data/confluence that will get mounted as a volume by the Container.

This is my first version to get this working (keep reading for refining this to include a JDBC driver).

version: '3'
services: confluence: image: atlassian/confluence-server restart: always volumes: - /data/confluence:/var/atlassian/application-data/confluence ports: - 8090:8090 - 8091:8091 confl-mysql: build: ./mysql restart: always environment: - MYSQL_RANDOM_ROOT_PASSWORD=yes - MYSQL_DATABASE=confluence - MYSQL_USER=confluence - MYSQL_PASSWORD=your-password

After hitting your-ip:8090 for the first time, you can pick the “My own database” option:

To connect to a MySQL db, you need to drop a MySQL JDBC driver into /opt/atlassian/confluence/confluence/WEB-INF/lib so at this point we’ve got a couple of options. We could either copy the JDBC driver into the container (but since containers are ephemeral we’d lose this change if we started a new container from the image), or take a step back and rebuild the image including the driver:

The right thing to do would be to rebuild a custom image including the driver. So let’s do that.

Download the MySQL Connector driver from here.

Let’s commit it into our project and add a new Dockerfile to build a modified version of the official Confluence image, which is simply just these two lines:

FROM atlassian/confluence-server
COPY mysql-connector-java-5.1.46.jar /opt/atlassian/confluence/confluence/WEB-INF/lib

Update the docker-compose file to build this new image instead of using the provided one from Docker Hub. Replace:

image: atlassian/confluence-server

with

build: ./confl-mysql

(or your corresponding name of your custom image containing the above Dockerfile).

Now, when we startup this container and hit the app, the JDBC driver is recognized and we’re on to the next config page for our database connection params:

Entering our credentials and pressing Test, we’ve got an error about the default encoding:

To address this, the Confluence setup docs, here, describe editing the my.cnf file in MySQL, or alternatively, I could pass params. The MySQL docs have a chapter on configuring and running MySQL in Docker, and this Q&A on Stackoverflow describes passing the optional params in a command section in your docker-compose file.

My first attempt was to add this:

confl-mysql: build: ./mysql restart: always command: character-set-server=utf8 collation-server=utf8_bin

but the syntax was not quite right yet, resulting in the container startup in a restart loop, and this error appeared in the container logs:

/usr/local/bin/docker-entrypoint.sh: line 202: exec: character-set-server=utf8: not found

Reading docs for the command option, the command in the docker-compose file needs to be the command to start the app in the container as well as the optional params. So now I’m here:

confl-mysql: build: ./mysql restart: always command: [mysqld, --character-set-server=utf8 --collation-server=utf8_bin]

Now we’re getting closer. Logs from my MySQL container and how showing:

ERROR: mysqld failed while attempting to check config command was: "mysqld --character-set-server=utf8 --collation-server=utf8_bin --verbose --help" mysqld: Character set 'utf8 --collation-server=utf8_bin' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

Some Googling made me realize each of the params is command separated, so next update is:

confl-mysql: build: ./mysql restart: always command: [mysqld, --character-set-server=utf8, --collation-server=utf8_bin]

Now we’ve got both containers starting up. The list of params should be updated to add all the optional params listed in the Confluence MySQL set up docs, otherwise, you’ll get an error for each missing param. The complete list is:

command: [mysqld, --character-set-server=utf8, --collation-server=utf8_bin, --default-storage-engine=INNODB, --max_allowed_packet=256M, --innodb_log_file_size=2GB, --transaction-isolation=READ-COMMITTED, --binlog_format=row]

Now we’re in business:

Complete config, and now the containers are up!

Original Link

On MySQL and Intel Optane Performance

Recently, Dimitri published the results of measuring MySQL 8.0 on Intel Optane storage device. In this article, I wanted to look at this in more detail and explore the performance of MySQL 8, MySQL 5.7, and Percona Server for MySQL using a similar setup. The Intel Optane is a very capable device, so I was puzzled that Dimitri chose MySQL options that are either not safe or not recommended for production workloads.

Since we have an Intel Optane in our labs, I wanted to run a similar benchmark, but using settings that we would recommend our customers to use, namely:

  • use innodb_checksum
  • use innodb_doublewrite
  • use binary logs with sync_binlog=1
  • enable (by default) Performance Schema

I still used charset=latin1 (even though the default is utf8mb4 in MySQL 8), and I set a total size of InnoDB log files to 30GB (as in Dimitri’s benchmark). This setting allocates big InnoDB log files to ensure there is no pressure from adaptive flushing. Though I have concerns about how it works in MySQL 8, this is a topic for another research.

So, let’s see how MySQL 8.0 performed with these settings and compare it with MySQL 5.7 and Percona Server for MySQL 5.7.

I used an Intel Optane SSD 905P 960GB device on the server with 2 socket Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz CPUs.

To highlight the performance difference I wanted to show, I used a single case: sysbench 8 tables 50M rows each (which is about ~120GB of data) and buffer pool 32GB. I ran sysbench oltp_read_write in 128 threads.

First, let’s review the results for MySQL 8 vs MySQL 5.7

After achieving a steady state, we can see that MySQL 8 does not have ANY performance improvements over MySQL 5.7.

Let’s compare this with Percona Server for MySQL 5.7

Percona Server for MySQL 5.7 shows about 60% performance improvement over both MySQL 5.7 and MySQL 8.

How did we achieve this? All our improvements are described here.

In short:

  1. Parallel doublewrite. In both MySQL 5.7 and MySQL 8, writes are serialized by writing to doublewrite.
  2. Multi-threaded LRU flusher. We reported and proposed a solution here, however, Oracle has not incorporated the solution upstream.
  3. Single page eviction. This is another problematic area in MySQL’s flushing algorithm. The bug was reported over 2 years ago, but unfortunately, it’s still overlooked.

Summarizing performance findings:

  • For Percona Server for MySQL during this workload, I observed 1.4 GB/sec reads and 815 MB/sec writes
  • For MySQL 5.7 and MySQL 8, the numbers are 824 MB/sec reads and 530 MB/sec writes.

My opinion is that Oracle focused on addressing the wrong performance problems in MySQL 8 and did not address the real issues. In this benchmark, using real production settings, MySQL 8 does not show any significant performance benefits over MySQL 5.7 for workloads characterized by heavy IO writes.

With this, I should admit that Intel Optane is a very performant storage. By comparison, on Intel 3600 SSD under the same workload, for Percona Server I am able to achieve only 2000 tps, which is 2.5x times slower than with Intel Optane.

Drawing Some Conclusions

There are a few outcomes I can highlight:

  • Intel Optane is a very capable drive, and it is easily the fastest of those we’ve tested so far.
  • MySQL 8 is not able to utilize all the power of Intel Optane unless you use unsafe settings (which to me is the equivalent of driving 200 MPH on a highway without working brakes).
  • Oracle has focused on addressing the wrong IO bottlenecks and has overlooked the real ones.
  • To get all the benefits of Intel Optane performance, use a proper server-Percona Server for MySQL, which is able to utilize more IOPS from the device.

Original Link

MySQL-Compatible Cloud Database Cost Comparison

As a database vendor whose drop-in MySQL replacement database runs on-premise and on any cloud, we are often asked by our customers to guide them through a MySQL-compatible cloud database cost comparison.

We have discovered some hidden hard and soft costs that are important to consider. In this article, we will share some of our learnings and a MySQL-compatible cloud database cost comparison model that we hope will be helpful to those looking at using a MySQL-compatible database to run in the cloud and planning to do a MySQL-compatible cloud database cost comparison.

We will compare the costs of the MySQL-compatible offerings from the four leading cloud vendors: AWS RDS for MySQL, AWS Aurora, Cloud SQL from Google Cloud Platform, and Microsoft Azure Database for MySQL. In this MySQL-compatible cloud database cost comparison, we will look at costs for a baseline three-zone architecture and then a scaled three-zone architecture with higher throughput requirements. But first, let’s makes some assumptions about our application requirements.

MySQL-Compatible Cloud Database Cost Comparison

Application Requirements

We will define the application database requirements as follows:

  • Baseline three-zone
    • Average of 5,000 transactions a second
    • Peak of 10,000 transactions a second 2% of the time
    • A read-write mix of 90:10
    • 7500 I/Os per second
    • 1TB of storage — requiring 1.25 TB on an ongoing basis
  • Scaled three-zone
    • Average of 15,000 transactions a second
    • Peak of 30,000 transactions a second 2% of the time
    • A read-write mix of 90:10
    • 22,500 I/Os per second
    • 1TB of storage — requiring 1.25 TB on an ongoing basis

Level Field Assumptions

For the purposes of this MySQL-compatible cloud database cost comparison article, let’s make some assumptions to create a level playing field.

  • Scalability: We’ll assume that the target application has scalability requirements that exceed the capacity of a small instance and require 32 cores at a minimum and that each of these databases will scale to the level we need using the scaling method they recommend.
  • Ongoing administration costs: Every cloud database has database and administration features they tout as being valuable. For the purpose of cost comparison, let’s assume that these capabilities are equivalent between vendors and any features one lacks are compensated by its own features the others lack.
  • Backup costs: For the sake of model simplicity, we will not include backup costs since there are a variety of ways to accomplish this with any database.
  • No egress: We’ll assume that the traffic between database and application stays within cloud service, so there will be no egress charges.
  • Eastern-region pricing with annual commitment: To take advantage of the price advantages of a reserved instance, we will assume a one-year commitment and use eastern-region pricing for each.

Baseline Three-Zone Architecture

Our first comparison will be for the MySQL variants with three 32-core nodes across three zones within the same region. The three nodes serve the following functions:

  1. A read/write master
  2. A read-slave
  3. A replication for a hot backup in case of a failure in the zone with the master

MySQL-Compatible Cloud Database Cost Comparison Figure 1

The following table lists the servers and associated costs for each cloud database given the application requirements and workload defined above.

MySQL-compatible cloud database cost comparison table #1. Three zones with one instance each:

MySQL-compatible cloud database cost comparison table 1

Scaled Three-Zone Architecture

Now let’s look at what happens when you need to scale the application’s workload. For MySQL and its cloud derivatives, the required scaling approach is sharding, so we will expand the database into three shards with one shard in each of the three zones.

As you shard, you will need three master servers. Matching the requirements above for the three-node architecture, each master will have one read slave plus one backup slave.

Therefore, the total servers will be:

  • 3 read/write masters
  • 3 read slaves
  • 3 backup slaves

MySQL-compatible database cost comparison figure 2

Now we will look at the costs associated with each.

MySQL-compatible cloud database cost comparison table #2. Three zones with three instances each:

MySQL-compatible cloud database cost comparison table 2

The Hidden Costs

As you can see, when doing cost comparisons for MySQL-compatible cloud databases, costs start to add up significantly as your application gains traction and your workload and database size increase. Beyond the cost of each instance, there are additional items to consider when planning the long-term TCO for your application database.

Hidden Hard Costs

The tables above make the hidden hard-costs not so hidden anymore. They include:

  • Storage
  • I/O
  • HA: Replication instances dedicated to hot-backup and recovery, which are not contributing to handling the production workload of your application

Hidden Soft Costs

There are also some soft costs that you will need to consider when looking at your overall TCO for these cloud database. While only you know how to estimate these costs specific to your business, they are important to consider when costing out cloud database solutions.

  • Development costs: Sharding requires intelligence to be embedded in the application code to interact with the shards and handle data integrity. You will need to consider the costs of the additional development effort to modify the application so it can leverage the shards of data in your initial release — and the costs of maintaining that additional logic in all future releases.
  • Opportunity costs of delayed features: Since creating a sharded environment can take many months to a year, application features will most likely be delayed as developers modify the code to address the sharding. If the application is consumer-facing or business-critical, there is always a price to pay for delayed features, either in delayed user adoption or inefficiency for the individuals using the application.
  • Database administrator costs: Since scaling beyond one read/write master requires sharding for each of these cloud database offerings, you will need to add in the costs of employing database administrators to conduct the sharding and to maintain the complex sharding environment as the application changes going forward.
  • Cost of moving to a new cloud infrastructure provider: Since these databases have varying degrees of MySQL and ANSI-SQL compatibility, your application will need to be tailored to each database. This will restrict your options to move to different cloud vendors or databases — or cause significant application rewrites — if the need arises. In addition, most cloud vendors will charge you for moving your data from and to their platform. These need to be included in your TCO calculations.
  • Cost of downtime as a result of failure: The architectures above all continue to have a single point of failure. There is a business cost associated with applications being down due to lost customers or business processes being put on hold. Be sure to consider these when making a purchase decision.

Original Link

How to Set Up Replication Between AWS Aurora and an External MySQL Instance

Amazon RDS Aurora (MySQL) provides its own low latency replication. Nevertheless, there are cases where it can be beneficial to set up replication from Aurora to an external MySQL server, as Amazon RDS Aurora is based on MySQL and supports native MySQL replication. Here are some examples of when replicating from Amazon RDS Aurora to an external MySQL server can make good sense:

  • Replicating to another cloud or datacenter (for added redundancy)
  • Need to use an independent reporting slave
  • Need to have an additional physical backup
  • Need to use another MySQL flavor or fork
  • Need to failover to another cloud and back

In this article, I will share simple step-by-step instructions on how to do it.

Steps to Setup MySQL Replication From AWS RDS Aurora to MySQL Server

  1. Enable binary logs in the option group in Aurora (Binlog format = mixed). This will require a restart.
  2. Create a snapshot and restore it (create a new instance from a snapshot). This is only needed to make a consistent copy with mysqldump. As Aurora does not allow “super” privileges, running mysqldump --master-data is not possible. The snapshot is the only way to get a consistent backup with the specific binary log position.
    Binlog position from crash recovery is mysql-bin-changelog.000708 31278857
    
  3. Get the binary log information from the snapshot. In the console, look for the “Alarms and Recent Events” for the restored snapshot instance. We should see something like:
    # mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql
    

    Sample config:

    [mysqld]
    log-bin=log-bin
    log-slave-updates
    binlog-format=MIXED
    server-id=1000
    relay-log=relay-bin
    innodb_log_file_size=1G
    innodb_buffer_pool_size=2G
    innodb_flush_method=O_DIRECT
    innodb_flush_log_at_trx_commit=0 # as this is replication slave
    
  4. Install MySQL 5.6 (i.e. Percona Server 5.6) on a separate EC2 instance (for Aurora 5.6 — note that you should use MySQL 5.7 for Aurora 5.7). After MySQL is up and running, import the timezones:
    pt-show-grants -h myhost...amazonaws.com -u percona > grants.sql
    

    # check that grants are valid and upload to MySQL

    mysql -f < grants.sql
    

    Make a backup of all schemas except for the “mysql” system tables as Aurora using different format of those (make sure we connect to the snapshot):

    host="my-snapshot...amazonaws.com"
    mysqldump --single-transaction -h $host -u percona
    --triggers --routines
    --databases `mysql -u percona -h $host -NBe "select group_concat(schema_name separator ' ') from information_schema.schemata where schema_name not in ('mysql', 'information_schema', 'performance_schema')"` > all.sql
    
  5. From now on we will make all backups from the restored snapshot. First get all users and import those to the new instance:
    mysql -h localhost < all.sql
    
  6. Restore to the local database:
    mysql -f < grants.sql
    
  7. Restore users again (some users may fail to create where there are missing databases):
    # cd /etc/ssl
    # wget 'https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem'
    # chown mysql.mysql rds-combined-ca-bundle.pem
    
  8. Download the RDS/Aurora SSL certificate:
    # mysql -h localhost
    ...
    mysql> CHANGE MASTER TO
    MASTER_HOST='dev01-aws-1...',
    MASTER_USER='awsreplication',
    MASTER_PASSWORD='<pass>',
    MASTER_LOG_FILE = 'mysql-bin-changelog.000708',
    MASTER_LOG_POS = 31278857,
    MASTER_SSL_CA = '/etc/ssl/rds-combined-ca-bundle.pem',
    MASTER_SSL_CAPATH = '',
    MASTER_SSL_VERIFY_SERVER_CERT=1;
    mysql> start slave;
    
  9. Configure MySQL replication. Take the values for the binary log name and position from #3 above. Please note: now we connect to the actual instance, not a snapshot:
  10. Verify that the slave is working. Optionally, add the SQL_Delay option to the CHANGE MASTER TO (or anytime), and specify the slave delay in seconds.

I hope those steps will be helpful for setting up an external MySQL replica.

Original Link

Data Integrity and Performance Considerations in MySQL Semi-Synchronous Replication

MySQL semi-synchronous replication provides improved data integrity because when a commit returns successfully, it’s known that the data exists in at least two places — the master and its slave. In this article, we review some of the MySQL configurations that influence the data integrity and performance aspects of semi-synchronous replication. We’ll be using InnoDB storage engine and GTID-based replication in a 3-node replica set (master and 2 slaves), which will ensure that there is redundancy in the slaves. This means that if there are issues with one slave, we can fall back on the other.

Configurations Applicable to Both Master and Slave Nodes

These configurations guarantee high durability and consistency settings for data. That is, each committed transaction is guaranteed to be present in binary logs, and also, the logs are flushed to the disk. Hence, in the case of a power failure or operating system crash, the data consistency of MySQL is always preserved.

Configurations on the Master Node.

This option is used to configure the number of slaves that must send an acknowledgment before a semi-synchronous master can commit the transaction. In the 3-node replica set, we recommend setting this to 1 so that we always have an assurance that the data is available in at least one slave while avoiding any performance impact involved in waiting for acknowledgment from both the slaves.

This option is used to configure the amount of time that a semi-synchronous master waits for slave acknowledgment before switching back to asynchronous mode. We recommend setting this to a large number so that there is no fallback to asynchronous mode, which then defeats our data integrity objective. Since we’re operating with 2 slaves and rpl_semi_sync_master_wait_for_slave_count is set to 1, we can assume that at least one of the slaves does acknowledge within a reasonable amount of time, thereby minimizing the performance impact of this setting.

Configurations on the Slave Nodes

In the slaves, it’s always important to track two positions very accurately: the current executed position of SQL thread in relay log, and the current position of the IO thread, which indicates how far the mater binary file is read and copied to slave. The consequences of not maintaining these positions are quite obvious. If there is a slave crash and restart, SQL thread can start processing transactions from a wrong offset or the IO thread can start pulling data from a wrong position in the master binary logs. Both of these cases will lead to data corruption.

It is important to ensure crash-safety of slaves through the following configurations:

Setting relay_log_info_repository to TABLE will ensure the position of the SQL thread is updated together with each transaction commit on the slave. However, it’s difficult to maintain the exact position of IO thread and flush to the disk. This is because reading master binary log and writing to slave relay log is not based on transactions. The impact on performance is very high if IO thread position has to be updated and flushed to disk after each write to slave relay logs. A more elegant solution would be to set relay_log_recovery = ON, in which case, if there’s a MySQL restart, current relay logs will be assumed to be corrupted and will be freshly pulled from the master based on the SQL thread position.

Last but not least, it’s important to note that semi-synchronous replication ensures that the data has just “reached” one of the slaves before the master committing the transaction and does not mean that the transactions are committed on the slave. Hence, it will be good to ensure that the SQL thread works with good performance. In the ideal case, the SQL thread moves hand in hand with the IO thread so we can have the benefit of the slave not only receiving the transactions, but also committing them. It’s recommended to go with a multi-threaded slave configuration so that we can get increased slave SQL thread performance. The important configurations for multi-threaded slaves are:

The above configurations are going to promise parallelism in the slave, while at the same time, preserving the order of transactions as seen on the master.

In summary, by using the above configurations on our MySQL replica set, we’re able to maintain high data integrity along with an optimal performance.

As always, if you have any questions, feel free to leave us a comment or reach out to us. 

Original Link

What To Do When MySQL Runs Out of Memory: Troubleshooting Guide

Troubleshooting crashes is never a fun task, especially if MySQL does not report the cause of the crash. For example, when MySQL runs out of memory. Peter Zaitsev wrote a blog post in 2012: Troubleshooting MySQL Memory Usage with a lot of useful tips. With the new versions of MySQL (5.7+) and performance_schema, we have the ability to troubleshoot MySQL memory allocation much more easily.

In this article, I will show you how to use it.

First of all, there are 3 major cases when MySQL will crash due to running out of memory:

  1. MySQL tries to allocate more memory than available because we specifically told it to do so. For example: you did not set innodb_buffer_pool_size correctly. This is very easy to fix
  2. There is some other process(es) on the server that allocates RAM. It can be the application (Java, Python, PHP), web server or even the backup (i.e. mysqldump). When the source of the problem is identified, it is straightforward to fix.
  3. Memory leaks in MySQL. This is a worst-case scenario, and we need to troubleshoot.

Where to Start Troubleshooting MySQL Memory Leaks

Here is what we can start with (assuming it is a Linux server):

Part 1: Linux OS and Config Check

1. Identify the crash by checking MySQL error log and Linux log file (i.e. /var/log/messages or /var/log/syslog). You may see an entry saying that OOM Killer killed MySQL. Whenever MySQL has been killed by OOM “dmesg” also shows details about the circumstances surrounding it.

2. Check the available RAM:

  •  free -g 

  •  cat /proc/meminfo 

3. Check what applications are using RAM: “top” or “htop” (see the resident vs virtual memory)

4. Check MySQL configuration: check /etc/my.cnf or in general /etc/my* (including /etc/mysql/* and other files). MySQL may be running with the different my.cnf ( run ps ax| grep mysql )

5. Run  vmstat 5 5 to see if the system is reading/writing via virtual memory and if it is swapping

6. For non-production environments, we can use other tools (like Valgrind, gdb, etc) to examine MySQL usage

Part 2: Checks Inside MySQL

Now we can check things inside MySQL to look for potential MySQL memory leaks.

MySQL allocates memory in tons of places, especially:

  • Table cache
  • Performance_schema (run: show engine performance_schema status and look at the last line). That may be the cause for the systems with a small amount of RAM, i.e. 1G or less
  • InnoDB (run  show engine innodb status and check the buffer pool section, memory allocated for buffer_pool and related caches)
  • Temporary tables in RAM (find all in-memory tables by running: select * from information_schema.tables where engine='MEMORY')
  • Prepared statements, when it is not deallocated (check the number of prepared commands via deallocate command by running show global status like  'Com_prepare_sql';show global status like 'Com_dealloc_sql')

The good news is, starting with MySQL 5.7, we have memory allocation in performance_schema. Here is how we can use it:

  1. First, we need to enable collecting memory metrics. Run:

UPDATE setup_instruments SET ENABLED = 'YES'
WHERE NAME LIKE 'memory/%';

2. Run the report from sys schema:

select event_name, current_alloc, high_alloc
from sys.memory_global_by_current_bytes
where current_count > 0;

3. Usually, this will give you the place in code when memory is allocated. It is usually self-explanatory. In some cases, we can search for bugs or we might need to check the MySQL source code.

For example, for the bug where memory was over-allocated in triggers ( https://bugs.mysql.com/bug.php?id=86821) the select shows:

mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name | current_alloc | high_alloc |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/innodb/buf_buf_pool | 7.29 GiB | 7.29 GiB |
| memory/sql/sp_head::main_mem_root | 3.21 GiB | 3.62 GiB |
...

The largest chunk of RAM is usually the buffer pool but ~3G in stored procedures seems to be too high.

According to the MySQL source code documentation, sp_head represents one instance of a stored program, which might be of any type (stored procedure, function, trigger, event). In the above case, we have a potential memory leak.

In addition, we can get a total report for each higher level event if we want to see from the bird’s eye what is eating memory:

mysql> select substring_index( -> substring_index(event_name, '/', 2), -> '/', -> -1 -> ) as event_type, -> round(sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) as MB_CURRENTLY_USED -> from performance_schema.memory_summary_global_by_event_name -> group by event_type -> having MB_CURRENTLY_USED>0;
+--------------------+-------------------+
| event_type | MB_CURRENTLY_USED |
+--------------------+-------------------+
| innodb | 0.61 |
| memory | 0.21 |
| performance_schema | 106.26 |
| sql | 0.79 |
+--------------------+-------------------+
4 rows in set (0.00 sec)

I hope these simple steps can help troubleshoot MySQL crashes due to running out of memory.

Original Link

You Should Always Use a Custom DB Parameter Group When Creating an RDS Instance — Here’s How

Before spinning up a new Amazon RDS instance, it’s critical to first make sure you have a new custom DB parameter group ready to use with it. If you don’t, it might become necessary to perform an RDS instance restart to replace the default DB parameter group, even though the database parameter you’d like to change is modifiable and dynamic.

For AWS RDS instances, you manage your database engine configuration through the use of parameters in a DB parameter group. DB parameter groups act as a container for engine configuration values that are applied to one or more DB instances.

A default DB parameter group is created if you make a database instance without specifying a custom DB parameter group. This default group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance.

Now, let’s say you need to modify the AUTOCOMMIT DB parameter of your MySQL 5.6 RDS instance. Because it’s modifiable and dynamic, it seems like you can just go ahead and change it, and it’ll just take effect, right?

Image title

Not so fast. You cannot just modify the parameter settings of a default DB parameter group — you must create your own DB parameter group to change parameter settings from their default value.

For example, this is what happens if you go to the default MySQL 5.6 parameter group and click the “Edit Parameters” button.

Image title

Image title

Image title

Image title

Image title

With the intention of replacing the default DB parameter group, you proceed to modify the RDS instance and select to apply the changes immediately.

Image title

However, this doesn’t mean you’re done. Your RDS instance will now wait for the next reboot before making the DB parameter group replacement effective, even though you just told it to “apply immediately.” The parameter group will show “applying” and then “pending-reboot,” which will clear after you bounce the instance.

Image title

The superior method here is to create a new DB Parameter Group before creating the RDS instance in anticipation of your future needs. Even though you didn’t plan on changing any of the DB parameters at the time, preparing a custom DB parameter group for any new RDS instance provides the flexibility to rapidly realize necessary modifications down the road.

Image title

And there you have it!

Ron Eickler is Manager, Cloud Engineering at Stratalux, a consulting and managed services provider for Amazon Web Services (AWS).

Original Link

Chunk Change: InnoDB Buffer Pool Resizing

Since MySQL 5.7.5, we have been able to resize dynamically the InnoDB Buffer Pool. This new feature also introduced a new variable — innodb_buffer_pool_chunk_size — which defines the chunk size by which the buffer pool is enlarged or reduced. This variable is not dynamic, and if it is incorrectly configured, it could lead to undesired situations.

Let’s see first how innodb_buffer_pool_size, innodb_buffer_pool_instances, and innodb_buffer_pool_chunk_size interact:

The buffer pool can hold several instances, and each instance is divided into chunks. There is some information that we need to take into account: the number of instances can go from 1 to 64, and the total amount of chunks should not exceed 1000.

So, for a server with 3GB RAM, a buffer pool of 2GB with 8 instances, and chunks at default value (128MB), we are going to get 2 chunks per instance:

This means that there will be 16 chunks.

I’m not going to explain the benefits of having multiple instances, I will focus on resizing operations. Why would you want to resize the buffer pool? Well, there are several reasons, such as:

  • On a virtual server, you can add more memory dynamically.
  • For a physical server, you might want to reduce database memory usage to make way for other processes.
  • On systems where the database size is smaller than available RAM.
  • If you expect a huge growth and want to increase the buffer pool on demand.

Reducing the Buffer Pool

Let’s start reducing the buffer pool:

| innodb_buffer_pool_size | 2147483648 |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_chunk_size | 134217728 |
mysql> set global innodb_buffer_pool_size=1073741824;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.00 sec)

If we try to decrease it to 1.5GB, the buffer pool will not change and a warning will be shown:

mysql> set global innodb_buffer_pool_size=1610612736;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Cannot resize buffer pool to lesser than chunk size of 134217728 bytes. |
+---------+------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.01 sec)

Increasing the Buffer Pool

When we try to increase the value from 1GB to 1.5GB, the buffer pool is resized, but the requested innodb_buffer_pool_size is considered to be incorrect and is truncated:

And the final size is 2GB. Yes! You intended to set the value to 1.5GB, and you succeeded in setting it to 2GB. Even if you set 1 byte higher, like setting: 1073741825, you will end up with a buffer pool of 2GB.

Interesting Scenarios

Increasing Size in the Config File

Let’s suppose one day you get up willing to change or tune some variables in your server, and you decide that as you have free memory, you will increase the buffer pool. In this example, we are going to use a server with 2GB of buffer pool size, which will be increased to 2.5GB

So, we set in the configuration file:

innodb_buffer_pool_size = 2684354560

But then, after restart, we found:

mysql> show global variables like 'innodb_buffer_pool_%size' ;
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size | 4294967296 |
+-------------------------------+------------+
2 rows in set (0.00 sec)

And the error log says:

2018-05-02T21:52:43.568054Z 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 16, chunk size = 128M

So, after we have set innodb_buffer_pool_size in the config file to 2.5GB, the database gives us a 4GB buffer pool, because of the number of instances and the chunk size. What the message doesn’t tell us is the number of chunks, and this would be useful to understand why such a huge difference.

Let’s take a look at how that’s calculated.

Increasing Instances and Chunk Size

Changing the number of instances or the chunk size will require a restart and will take into consideration the buffer pool size as an upper limit to set the chunk size. For instance, with this configuration:

innodb_buffer_pool_size = 2147483648
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 134217728

We get this chunk size:

mysql> show global variables like 'innodb_buffer_pool_%size' ;
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| innodb_buffer_pool_chunk_size | 67108864 |
| innodb_buffer_pool_size | 2147483648 |
+-------------------------------+------------+
2 rows in set (0.00 sec)

However, we need to understand how this is really working. To get the innodb_buffer_pool_chunk_size it will make this calculation: innodb_buffer_pool_size / innodb_buffer_pool_instances with the result rounded to a multiple of 1MB.

In our example, the calculation will be 2147483648 / 32 = 67108864, which 67108864%1048576=0, no rounding needed. The number of chunks will be one chunk per instance.

When does it consider that it needs to use more chunks per instance? When the difference between the required size and the innodb_buffer_pool_size configured in the file is greater or equal to 1MB.

That is why, for instance, if you try to set the innodb_buffer_pool_size equal to 1GB + 1MB – 1B, you will get 1GB of buffer pool:

innodb_buffer_pool_size = 1074790399
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 67141632
2018-05-07T09:26:43.328313Z 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 16, chunk size = 64M

However, if you set the innodb_buffer_pool_size equals to 1GB + 1MB, you will get 2GB of buffer pool:

innodb_buffer_pool_size = 1074790400
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 67141632
2018-05-07T09:25:48.204032Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 16, chunk size = 64M

This is because it considers that two chunks will fit. We can say that this is how the InnoDB Buffer pool size is calculated:

determine_best_chunk_size{ if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size then innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / innodb_buffer_pool_instances) fi
}
determine_amount_of_chunks{ innodb_buffer_amount_chunks_per_instance = roundDown(innodb_buffer_pool_size / innodb_buffer_pool_instances / innodb_buffer_pool_chunk_size) if innodb_buffer_amount_chunks_per_instance * innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size - innodb_buffer_pool_size > 1024*1024 then innodb_buffer_amount_chunks_per_instance++ fi
}
determine_best_chunk_size
determine_amount_of_chunks
innodb_buffer_pool_size = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * innodb_buffer_amount_chunks_per_instance

What Is the Best Setting?

In order to analyze the best setting, you will need to know that there is an upper limit of 1000 chunks. In our example with 16 instances, we can have no more than 62 chunks per instance.

Another thing to consider is what each chunk represents in percentage terms. Continuing with the example, each chunk per instance represents 1.61%, which means that we can increase or decrease the complete buffer pool size in multiples of this percentage.

From a management point of view, I think that you might want to consider at least a range of 2% to 5% to increase or decrease the buffer. I performed some tests to see the impact of having small chunks and I found no issues but this is something that needs to be thoroughly tested.

Original Link

Lucidchart’s Database Migration to Amazon Aurora

Lucidchart was built on AWS from day one, so I was very excited for the opportunity to go to Seattle and have Lucidchart featured on the AWS video series, “This is My Architecture.” I talked about our database migration to Amazon Aurora—you can watch the interview below, and for those interested in the details of the process, continue reading.

When a customer signs up for Lucidchart, they expect to be able to create new documents and open existing ones. Seems simple enough. Because of this core requirement, we had a requirement from the very beginning to have a very low latency, write-optimized database cluster. We were using MySQL on EC2 and EBS as the underlying data store.

Another big requirement for us was being able to have very little downtime when switching availability zones so that if we ever needed to do database maintenance or something similar, our users weren’t impacted. What we ended up with was a master-master MySQL implementation—and that presented some challenges.

Challenges with the Current State

The challenges that we ran into were the same challenges that you’re going to run into any time you have a complex database implementation. In addition to our master-master implementation that supports our applications, Lucidchart and Lucidpress, we also had to support the rest of our internal business tools. For example, our business analytics and business intelligence tools are very read-heavy, but our application is designed to be write-heavy, because as users make changes to diagrams, we need to persist those as quickly as possible.

To support both the application (write-heavy) and internal tools (read-heavy), we ended up with a master/master SQL cluster and multiple slaves pointing at the masters. This setup introduced a lot of complexity in maintaining the health of the cluster. At peak, we had 15 clusters and around 50 database instances.

We also ran into another issue regarding backups—for legal and contractual purposes, we are required to keep backups for an extended period of time. Every time we would initiate a snapshot on the underlying EBS volumes, there would be a major performance hit, because, in addition to the running application, there was also the snapshot load.

Identifying a Better Solution

As a SaaS company, we spend a lot of time and effort making an awesome product. Any time that we spend building out infrastructure, especially if somebody else has built that infrastructure or has the capability of building it for us, is opportunity lost for my team.

On average, maintaining and optimizing our database clusters took 25% of a senior developer’s time. That time was spent on resolving data collisions, balancing and optimizing read/write capacity, EBS and EC2 optimization, and disaster recovery/business continuity verification. We knew that we wanted a managed database service, but none of the services that we looked into met all of our needs.

We had been excited about Aurora for quite a while. They were touting some pretty outstanding claims, like up to 5x performance increase and zero downtime patches. It seemed like from the time that we started watching it, every couple months, they were checking more and more boxes of requirements that we had.

It wasn’t until they said they could do encryption at rest out of the box in Aurora, but also encryption cross region out of the box—meaning we could have a primary cluster in one region with a backup cluster in a second region with real-time replication and encryption—that Aurora really became a possibility for us.

Migrating to Aurora

Disaster recovery

Aurora is a MySQL compatible cluster, which made our transition much easier. We realized that Aurora’s outstanding claims turned out to be true, including the fact that every Aurora instance has at least six disks spread across three availability zones.

When a database write operation occurs, the master node in the Aurora cluster requires that at least three of the disks persist the transaction or it is not counted as a success and is rolled back. Even if an availability zone is having an issue, or in some cases, two availability zones, we do not have to worry about data loss or corruption. It also means that we are able to perform database maintenance, such as security patches, version upgrades, etc., without the risk of impacting replication or our users.

When automated backups are enabled on an Aurora cluster, it allows you to perform a “point in time” restore. The automated backup retention period is configurable from one to 35 days. When performing a point in time restore, you can select a specific time anytime within the configurable window with second-level granularity.

automated backup

Aurora also supports manual snapshots, which can be kept for as long as you would like. These snapshots can also be copied to different regions for disaster recovery purposes. We use a lambda function to take the snapshot and also to purge snapshots as they age out of our retention periods.

Encryption

Encryption is an important part of our infrastructure. We have always maintained encryption at rest ourselves using LUKS. Aurora’s ability to use disk-level encryption out of the box simplified our configuration significantly. Aurora allows you to encrypt your cluster, and it’s as simple as clicking the checkbox “encryption” while creating the cluster. You will be prompted to select the encryption key, which is stored in Amazon Key Management Service (KMS).

Cost Savings

Moving to Aurora ended up saving us quite a bit of money in hosting and data costs.

cost savings

The diagram above shows what one of our MySQL clusters looked like. We used the master instances for all of our services. The snapshot slaves were only used for manual queries as well as snapshotting a specific master so that we could restore without rebuilding the entire cluster.

The first cost savings came from decreasing our instance count from five instances to three instances per cluster. Aurora instances are slightly more expensive than regular EC2 instances of the same size/type, but we more than made up the difference by being able to drop two of the SQL instances from the cluster. We removed one of the masters and one of the slaves.

cost savings 2

The second cost savings came from the persistent storage. Aurora only charges for what you use, and the disks grow dynamically. With our MySQL implementation, each instance had to have more than enough disk space to handle growth. We were able to expand EBS volumes, but it was not something that we enjoyed managing. We would usually increase our EBS volumes in 500GB steps. Every time we did that it would increase the EBS usage by at least 2.5TB per cluster. Aurora only charges for data one time for a given cluster even though there are at least six copies of the data. This decreased our storage cost by almost 5x.

The last (and greatest) cost savings came from the way snapshots work. We would take at least two snapshots per cluster. We also managed disk-level encryption ourselves via LUKS. As our users made changes to their documents, we would update rows in our database. Even though there would be very little actual data change in a customer’s dataset, the encrypted bits would look completely different on disk. When we would take a snapshot, even though snapshots are incremental, we would end up having very large snapshots due to the frequent updates. Aurora is very smart about what and how it backs up data. Our snapshot sizes dropped almost 10x.

Without calculating the engineering and opportunity costs, our overall database costs decreased roughly 25% after switching to Aurora.

Standing up Our First Instance

As soon as Aurora came out and said that they supported real-time cross-region replication with both regions being encrypted, we decided that it was time to give it a real trial and make sure that it would actually meet our needs. In the end, we decided to move to Aurora.

We stood up our first instance as a standalone. We just exported the data from our database and then imported it into the Aurora cluster. First, we noticed that the import was about three times faster than what we could do on our own cluster, even with all of the tuning and optimizations we had put in. That validated the first claim that Aurora can be up to five times faster than MySQL.

Once we imported that data, we tested the multi-availability-zone capabilities of Aurora. We went into Amazon, right-clicked on our instance, and spun up a second instance in another availability zone. This process usually took about 24 hours, because this was one of our larger database instances, and in Aurora, it only took two hours to create that slave in the second availability zone.

Increased Performance

Performance is a big benefit that we’ve found after moving to Aurora. Our writes are roughly the same speed, but the read capabilities that Aurora provides gave us far better performance. First off, Aurora is very optimized for parallel queries. Our SQL instances were only as optimized as the number of instances that we could stand up. But due to the underlying disk architecture of Aurora, you get better throughput for the queries that are issued against the Aurora clusters.

Second, when you stand up an Aurora cluster, it gives you two different endpoints that you can connect to. There’s a read/write endpoint, but there’s also a read-only endpoint.

increased performance

The diagram above shows two different workload types. The one on the left represents workloads that need to have read-write access, the one on the right represents workloads that only need read access. To send read-write traffic to the master node, you use the read-write endpoint. The workloads that need read-only access can use the read-only endpoint. Many of our services will actually use both endpoints at the same time. Our services will use the read-write endpoint if they need write access or guaranteed up-to-date data, but they will prefer the read-only endpoint if they only need to perform reads.

Increased Stability

We calculate database uptime based off a cluster’s write availability. Several factors can impact this metric, such as manually putting the cluster in read-only mode, data inconsistencies, performance issues, or outright failures. Before we migrated to Aurora, the cluster with the worst write availability had an average of 99.95% in 2016. We started using Aurora for all of our database clusters at the beginning of 2017. Our worst cluster in 2017 had a write availability of 99.999%.

Conclusion

We are very happy with our transition to Aurora for two main reasons.

The first is that Aurora is MySQL compatible and supports being both a master and a slave. This functionality enabled us to move to Aurora with zero downtime. All that we had to do was create the Aurora cluster and make it a slave to our existing MySQL cluster. After replication had caught up, we changed the DNS record to point our application at Aurora instead of our MySQL clusters. We did have to do this for each of our services and MySQL clusters, but the process was very simple.

The second main benefit was that Aurora simplified the process of keeping data in multiple regions. For disaster recovery and business continuity purposes, we have to keep data in multiple regions, not only in different availability zones. Before we moved to Aurora, we would have to copy snapshots periodically between our primary region and our secondary region, so our snapshots were always out of date by some period of time.

With Aurora, we went to the master cluster, right-clicked, and selected “Create a replica in a different region.” That not only copied the data, but it also created a database cluster in that region that is completely up to date, in real time, only delaying by a few milliseconds.

Over the years, I have been involved in many vendor evaluations, and often the claims that the vendor makes start to fall over when you get into the details of the offering. You should always do your homework before switching to a new solution, but for us, Aurora surpassed all of our expectations. Our main takeaway during this process is that Aurora delivers.

Original Link

How Monyog Helps Profile Slow Queries in MariaDB

MariaDB came into being the day that Oracle announced the purchase of Sun in 2010. In order to keep it free under the GNU GPL, Michael Widenius forked MySQL and took several MySQL developers with him in the process. Since then, MariaDB has been a drop-in replacement for MySQL, albeit with more features and better performance.

In the Improve MariaDB Performance using Query Profiling blog, we learned some useful techniques for tracking and analyzing slow queries using a couple of MariaDB server’s built-in tools: the Slow Query Log and the Performance Schema.

The Slow Query Log records queries that are deemed to be slow and potentially problematic, that is, queries that take longer than the long_query_time global system variable value to run.

The Performance Schema is a storage engine that contains a database called performance_schema, which in turn consists of a number of tables. It may be utilized to view raw data in the summary views as well as review performance over time.

Both of the above tools come with their own pros and cons. For example, the slow query log is easy to work with and may be viewed with any text editor. The Performance Schema tables may be queried with regular SQL statements for a wide range of performance information. At the same time, both tools tend to produce a wealth of data that can be a burden to wade through.

That’s where a professional monitoring tool adds tremendous value.

More than a real-time monitoring tool, Monyog features RDS OS and file-based log monitoring, including General Query, Slow Query, and Error logs in a single view. It also lets you view RDS OS metrics like CPU Utilization, RAM usage, etc. using the CloudWatch API.

Configuring the Slow Query Log

In MariaDB, as in MySQL, the Slow Query Log is disabled by default. It must be enabled by setting the slow_query_log global system variable to 1. There are a few other system variables for:

  1. Setting the time in seconds/microseconds that define a slow query.
  2. Writing to a file or table.
  3. Providing the name of the slow query log file.
  4. Logging queries that don’t use indexes.

In Monyog, you can configure all of these settings via the ADVANCED tab of the Server Settings dialog. It is accessible by clicking:

  1. The Servers icon (#1 in the image below).
  2. The ellipsis on the server summary box (#2 in the image below).
  3. The Edit Server item from the popup (not pictured).
  4. The ADVANCED header (underlined in blue in the image below).
  5. The MySQL Query Log item (highlighted in blue in the image below).

The ADVANCED tab of the MySQL Query Log item contains settings for General Query, Slow Query, and Error Logs.

The Server Settings dialog also allows us to apply the Slow Query Log settings to the current server or to all servers with tags the same as the current server.

Clicking the SAVE button closes the dialog and persists the Slow Log settings.

Dashboard Metrics

The Dashboard displays a set of charts so that DBAs can easily understand the complete security, availability, and performance picture of all their MySQL servers in one place. Monyog ships with a default dashboard called “Performance metrics,” but DBAs can create their own set of charts for database and OS specific metrics for one or more servers. These include query performance metrics such as Queries Executed, Statements, and Query Cache Efficiency.

All charts and graphs displayed on the Dashboard can be exported in PDF/JPG/PNG formats. To export a chart, click the download icon and select your preferred file format from the drop-down context menu.

Viewing MySQL Logs Details

The Monyog Monitors page displays a detailed display of server parameters and metrics. Clicking the MySQL Logs item under the MONITOR GROUP header brings up details about General Query, Slow Query (highlighted with a red box in the image below), and Error logs for monitored servers.

Slow Query information includes:

  • Slow log — Enabled? (Yes/no)
  • Minimum execution time for a query to be considered slow, in seconds
  • Number of slow queries
  • Log queries not using indexes? (Yes/no)

Trend Values Graph

A graph or chart is a visual information graphic representation of tabular numeric data. Graphs are often used to make it easier to understand large quantities of data and the relationship between different parts of the data. Graphs can usually be read more quickly than the raw data that they come from.

One type of chart is called a trend chart or run chart. It’s utilized to show trends in data over time. Due to data fluctuations, single point measurements can be misleading. Displaying data over time increases understanding of the real performance, particularly with regard to an established target or goal.

Clicking on a Trend value graph icon in the No. of slow queries row will display a graph, depicting query performance over time.

The following is an example of a trend chart for the Master server:

The SERVERS legend lists all of the servers from the SQL Logs screen. Each is assigned its own color for easy identification in the graph. Servers whose trend values do not appear in the graph are “greyed out.” Clicking a server toggles its inclusion in the graph, thus saving having to return to the SQL Logs screen to select or deselect it. For example, the above graph was produced by clicking the Trend value graph icon in the master column of the monitor’s table. Hence, the other three servers are greyed out in the legend. Clicking any of these servers will add it to the graph, while clicking the Master server will remove it from the graph.

Hovering the mouse over the graph line will display the details for that point on the graph:

Clicking anywhere outside of the graph dialog closes the dialog.

All charts and graphs displayed by the Monitors can be exported in CSV format. To export a chart, select the option from the drop-down context menu.

Displaying Trend Values for a Specific Timeframe

The trend graphs explored above presents the current trend data. In Monyog Professional, Enterprise, and Ultimate editions, you can also select a specific time-frame for which to include in the graph by choosing the History item from the TIMEFRAME dropdown.

This will display an additional drop-down for selecting the timeframe range. It contains a number of intervals such as “Today,” “Yesterday,” and “Last 2 Days” as well as start and end fields to set a custom range. Clicking on either custom range fields presents a calendar widget for choosing an exact date and time.


Now, clicking on the “Trend Graph” icon in the number of slow queries row displays the trends graph.


Along with the graph, the Historical Trend Graph also shows the monitor values for each server in tabular form underneath the graph. You can enable the option Show Only Changed Values to restrict values to those before and after changes.

Displaying Delta Results

The third type of Time Frame, Delta, displays results based on data for the period between the last data collection and the collection before that. This setting can help give you a better idea of the current situation and how much it differs from the “average” or “normal” situation.

Query Analyzer

In the “Query Analyzer” tab, select which of the MySQL servers you want and the type of log (including the Slow Query log) you want to analyze. Next, click the Analyze button to begin the analysis.

After a few seconds, an analysis result like the following will appear:

The Query Analyzer screen is divided into 2 parts: the top half of the screen contains the Top Queries based on Total Time, while the bottom half shows all of the queries using results paging.

Top Queries Based on Total Time

This section of the screen displays the top queries, sorted so that the slowest query appears at the top. It includes:

  • The query statement
  • COUNT: how many times the statement appears in the log.
  • TOTAL TIME: How much time the queries took to execute, in hh:mm:ss:ms format.
  • AVERAGE LATENCY: The average query execution time, in hh:mm:ss:ms format.
  • USER@HOST: The user and host that executed the query.

Each statement is presented as a bar chart at the very top of the query data whereby each query is represented using a unique color. Each query’s Total Execution Time appears from left-to-right so that the slowest would be displayed at the far left. The bar chart helps to quickly assess how slow each of the slowest queries compares to the slowest. In the image above, we can see that the slowest query was several magnitudes slower than all of the other slow queries times combined!

Clicking on a row brings up the Query Details. This includes additional information such as the query’s first and last seen date & times, its Max Time, Rows Sent, and Rows Examined:

This is also true of the Queries section.

Queries

The Queries section provides a more complete list of analyzed queries. In addition to having the ability to navigate through all of the queries via paging, it also features:

  • Filtering: Queries can be filtered to narrow down the list to those that you are interested in. The four filtering options are:
    • Containing
    • Not containing
    • Matching regex
    • Not matching regex

Here is a filter that restricts results to statements that contain the regex “sakila*”:

  • Sorting: Rows can be sorted by any column by clicking the header. An arrow shows sorting order, i.e. ascending, descending.

  • Managing Columns: Individual columns may be added and removed from the query list via the Manage Columns dialog:

It’s accessible by clicking the Show/Hide Columns icon next to the Results Navigation controls:

Export as CSV

To the immediate left of the Show/Hide Columns icon, the Export as CSV icon saves the query data to a Comma Separated Values (.csv) file.

CSV files may be read by applications like MS Excel.

Changing the Field Delimiter

The option to define the field delimiter is provided because some localized Windows programs that use the comma (,) as a decimal sign will require a semicolon (;) as a field separator. This includes Microsoft Office programs like Excel and Access. On Linux, the situation is less uniform, but some localized applications such as OpenOffice Calc (spreadsheet app) requires a semicolon (;) as the field separator.

Users can change the CSV export settings by using General > CSV EXPORT from the Settings screen.

Filter Settings

The Query Analyzer offers a few options specific to the Slow Query Log. These are accessible by clicking the Settings icon (highlighted in red below).

Options include:

  • Filter Users/Filter Hosts: A list of users/hosts to include or exclude from the analysis. Both of these options accept the asterisk “*” wildcard character.
  • Include Users executing the queries with Hostnames: If this option is selected, it will display both the “user” and “host” of that particular query, and it will group the query analyzer table based on “user@host” and “query”.
  • Read All: Selecting the Read All option causes the Query Analyzer to consider the whole file for analyzing. It won’t consider any particular timeframe but displays all queries within the specified KB, MB, or bytes size/chunk as set in the Reading limit from file option. Otherwise, it reads the last specified chunk in KB, MB, or bytes set in the Reading limit from file option in the log file. Note that it is the “smallest” of those two settings that will have effect for the analysis.
  • Reading limit from file: Specifies the number of KB, MB, or bytes size/chunk to read from the log file according to the Read All setting.

Conclusion

Both of the Slow Query Log and Performance Schema come with their own pros and cons. Where the slow query log is easy to work with and may be viewed with any text editor, the Performance Schema tables may be queried with regular SQL statements for a wide range of performance information. At the same time, both tools tend to produce a wealth of data that can be a burden to wade through.

That’s where a professional monitoring tool like Monyog can add tremendous value. Specifically:

  • The Monyog Monitors page displays a detailed display of server parameters and metrics for General Query, Slow Query, and Error logs for monitored servers. It provides Trend charts that show trends in data over time. The data timeframe may be current, historical, or a delta.
  • The Query Analyzer screen contains the Top Queries based on Total Time as well as a list of all queries using results paging.

Query profiling is a useful technique for analyzing the overall performance of a database. Employing Monyog to monitor the MariaDB Slow Query Log and the Performance Schema is one of the most efficient ways to do that.

Original Link

A First Look at dbForge Studio for MySQL

dbForge Studio for MySQL is a powerful integrated development environment (IDE) for MySQL from Devart, an industry leader known for its database development tools. In this article, we will discuss some of its features database developers, analysts, DBAs, or architects may find useful.

Disclaimer

This is not a product promotion article. The author is not affiliated with Devart or any other company associated with Devart.

General Features

Access to a Wide Range of MySQL Flavors

dbForge Studio for MySQL is compatible with a wide range of MySQL flavors, storage engines, and connection protocols. Besides the open-source MySQL database engine, it can connect to MariaDB, Amazon Aurora for MySQL, Google Cloud MySQL, and Percona Server to name a few. Other exotic distributions include Oracle MySQL cloud, Alibaba cloud, and Galera cluster. We were able to seamlessly connect it to an Amazon RDS MariaDB instance. When we tried to do the same from MySQL Workbench. it showed us the following message:

MySQL Workbench Warning

MySQL Workbench did connect to the MariaDB instance after the warning, but we could not see the system databases from its navigation pane.

Look and Feel

The user interface of dbForge Studio has a modern, intuitive look and feel. Tabbed panes, non-cluttered toolbars, and context-specific menus make navigation through the tool fairly simple.

dbForge Studio for MySQL UI

Those familiar working with Visual Studio will feel right at home with its default “skin.” There are other skins to change the UI theme:

dbForge Studio for MySQL &quot;skins&quot;

Command Line Automation

One really good feature of dbForge is that most actions on the UI can be exported to an operating system command. There is a button labelled “Save Command Line…” with most dialog boxes. This allows the action of the dialog box to be exported as an operating system command. The options chosen in the dialog box become parameters for the command. This can help users automate regular database tasks from their desktop:

Command Line Execution File Settings

For the Database Developer

Code Snippets

A good IDE should help developers save time and automate tasks as much as possible. When it comes to developer productivity, dbForge for MySQL offers some of the industry standard features like code completion, syntax checking, code formatting or code snippets. Here are some examples of code completion and code snippets:

Code Auto Complete

Creating Code Snippets

Using Code Snippets

Object Dependencies

Objects like tables or views can be checked for their relationships to other objects in the database. This can be done by choosing the “Depends On” or “Used By” folders from the object tree. The dependencies are shown in recursive manner. This can be really handy when troubleshooting or debugging code:

Checking Object Dependencies in dbForge Studio for MySQL

The CRUD Generator

Another good feature of this tool is the CRUD generator. Right clicking on a table and selecting CRUD from the popup menu will create a template for four stored procedures. Each procedure will be for a basic CRUD operation (SELECT, INSERT, UPDATE, DELETE):

The CRUD Generator

Here is a portion of a sample script:

DROP PROCEDURE IF EXISTS usp_dept_emp_Insert;
DELIMITER $$
CREATE PROCEDURE usp_dept_emp_Insert (IN p_emp_no INT(11), IN p_dept_no CHAR(4), IN p_from_date DATE, IN p_to_date DATE)
BEGIN START TRANSACTION; INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date) VALUES (p_emp_no, p_dept_no, p_from_date, p_to_date); /* -- Begin Return row code block SELECT emp_no, dept_no, from_date, to_date FROM dept_emp WHERE emp_no = p_emp_no AND dept_no = p_dept_no AND from_date = p_from_date AND to_date = p_to_date; -- End Return row code block */ COMMIT;
END$$
DELIMITER ;

The Schema Comparison Tool

Most database client tools would offer schema comparison and synchronization feature. dbForge is no exception. The intuitive user interface makes searching and reconciling schema differences very easy:

The Schema Comparison Tool

The Debugger Tool

Finally, the debugger will be another great feature for developers:

Debug Wizard

The debugger wizard requires the dbForge debug engine to be deployed in the MySQL server and creates a database called cr_debug. This database has all the procedures, functions, and tables required for user code debugging. Deploying the debug engine requires the user to have process admin rights, and we found this feature does not work with MySQL in Amazon RDS, because RDS does not allow access to the backend server.

For systems that allow deploying the debug engine, MySQL developers can run their stored procedures with “Compile for Debugging” option. This inserts custom debug code to the procedure, calling routines from the cr_debug database. This added code allows developers to perform step by step debugging on the code instead of using custom debug messages. To keep things simple, the debug code is not displayed when a procedure or function is loaded in the dbForge editor.

Once the code is ready, developers can easily remove debug information with a few mouse clicks.

For the Data Analyst

The Data Comparison Tool

Like the schema comparison tool, dbForge for MySQL has a data comparison tool that should be useful for data analysts and developers. It has an intuitive interface for comparing and reconciling data between two tables:

The Data Comparison Tool

Data Import and Export

dbForge Studio for MySQL can connect to ten different types of data sources for importing and exporting data. Notable types are Google Sheets, XML, or even ODBC connections. We were able to copy an Excel sheet in no time. Then, we tried with a JSON document — again, that was a breeze.

Data Import Wizard

Compared to these types, the Table Data Import wizard in MySQL Workbench can import CSV or JSON only.

Master-Detail Browser

The Master-detail browser is a great tool for viewing data relationship. Analysts can use this tool to quickly check different categories of master data and their child records:

Master-Detail Browser

Pivot Table

The Pivot Table feature can be used for data aggregation, grouping, sorting and filtering. For example, a source table may look like this (we are using the Sakila database as a sample):

Source Table View

With a few mouse clicks, the pivoting feature allows us to break down or roll up the rental income figure:

Pivot Table in dbForge Studio for MySQL

Reporting

Not too many enterprise class query tools have a built-in reporting facility. dbForge Studio for MySQL comes with a nifty report designer. Users can create reports either by choosing one or more tables or using their own custom queries. Once the wizard finishes, the report opens in a WYSIWYG editor for further customization. Once ready, it can be saved in Data Report (.rdb) format:

A Simple Report Created in dbForge Studio for MySQL

For the Database Administrator

Database administrators would find most tools they use for day-to-day management of MySQL databases are similar between dbForge and MySQL Workbench. This includes:

  • User management (“Security Manager” in dbForge, “Users and Privileges” in MySQL Workbench)
  • Table Maintenance (Analyze, Optimize, Check, CHECKSUM, Repair)
  • Current connections to the instance
  • System and Status variables

Similarly, backing up a database is as simple as right-clicking on it and choosing “Backup and Restore > Backup Database…” from the popup menu. dbForge creates a SQL dump file for the database. Restoring a database simple as well.

We could not find the server log file viewer in dbForge Studio for MySQL, although it’s readily available in MySQL Workbench. With Amazon RDS MySQL, the log files can’t be accessed from either of these client tools.

Copy Database

Copying databases from one instance to another is an intuitive and simple process with dbForge Studio. All the user needs to do is choose the source and the destination instances, select the databases to copy in the source plus any extra options if necessary, and then click on the little green arrow:

Copy Databases Tool

Copy Databases Settings

What’s more, databases can be copied between different flavors of MySQL: we were able to successfully copy a MySQL database to a MariaDB instance.

MySQL Workbench also offers Schema Transfer Wizard for copying databases, but the Wizard wasn’t able to show our MariaDB instance as a connection.

MySQL Workbench Schema Transfer Wizard

dbForge Studio for MySQL allows copying databases within the same instance (the new database name has a suffix of “_copy”). This is not possible with MySQL Workbench.

Query Profiler

Where dbForge really shines for the DBA is the query profiler. Using the query profiler, a DBA can capture different session statistics for a slow running query such as execution time, query plan, status variables etc. Behind the scenes, dbForge uses MySQL native commands like EXPLAIN and SHOW PROFILE to gather session data and presents it in an easy-to-understand format in the GUI. Looking at these metrics can help identify potential candidates for query tuning. Once tuning is done and the query is run again, the query profiler will again save the sessions statistics. Comparing the two different session profiles can help the DBA check the effectiveness of the tuning. What’s more, there is no reason to manually copy and save query texts between different runs. Selecting a profile session and clicking on the “SQL Query” button will automatically show the query executed for that session in the editor. This is possible because the query profiler saves the query text with session statistics.

Query Profiler in dbForge Studio for MySQL

For the Data Architect

Database Diagrams

Reverse engineering an existing database’s structure is often part of a data architect’s job and dbForge Studio for MySQL makes this process simple. Tables from the database tree can be dragged and dropped into a Database Diagram and it will automatically create a nice ER diagram, as shown below:

Database Diagram created by Reverse Engineering a Database

Database Documenter

Most high-end database tools offer some type of reverse engineering capability, but dbForge goes one step further by enabling the user to create database documentation. A full-blown professional looking system architecture document can be created with only a few clicks of a mouse. The documentation will describe tables and views, indexes, column data types, constraints and dependencies along with the SQL scripts to create the objects.

Database Documenter Options

The documentation can be created in HTML, PDF, or Markdown format:

Database Documentation Output File Types

Data Generator

Finally, the feature that database architects and developers would love is the Data Generator tool. Database design often requires meaningful dummy data for quick proof-of-concepts, load testing, or customer demonstrations. dbForge offers an out-of-box solution for this. Using the intuitive data generator wizard, it is possible to populate an empty schema of a MySQL database in no time:

Data Generator General Properties

The generator keeps foreign key relationships in place during data load, although foreign keys and triggers can be disabled if needed:

Data Generator Options for Table Constraints

Also, only a subset of tables can be populated if necessary:

Choosing Tables to Populate in Data Generator

The tool can create a data generator script and load it into the SQL editor, save it as a file, or run it directly against the database:

Data Generator Output Options

Conclusion

dbForge Studio for MySQL comes in four different editions: Enterprise, Professional, Standard, and Express. The Express edition is free with the next tier (Standard edition) retailing at $149. The Professional edition is priced at $299 and the Enterprise edition at $399. There is a volume discount available for 10 or more licenses.

dbForge also offers subscriptions for customers wishing to upgrade their product to newer versions. The subscription is available for one, two, or three years. Licensing prices come down with longer subscriptions. Also, a one-year subscription is included with new licenses.

Being a free tool, MySQL Workbench may seem an attractive alternative to stay with. In our opinion, the wide number of features available in dbForge editions make their prices seem fair. Also, the major differences between Professional and Enterprise edition are copy database, data generator, and database documenter.

The Express edition (free) or the 30-day free trial of any edition can be a good choice for anyone to get a feel for the product.

One thing to keep in mind is dbForge for MySQL is a Windows-only tool. This can be a major factor for shops where MacBooks are a favorite.

Overall, we would say it’s a good product, in fact, a very good product — one that deserves at least a serious test drive from the community.

Original Link

Creating an Ajax MySQL DataGrid with 7 Lines of Code

We’ve all been there, our boss asks us to create something that allows for viewing and editing some MySQL datatable from some web app. This problem is so common, it’s even has its own acronym; CRUD! If you’re anything like me, you try to code as little as possible. Not necessarily because you’re lazy, but because you know that the more code you create, the more potential bugs you’ll end up with. So the first question you should ask yourselves is, “How few lines of code is it possible to solve this problem with?” Well, let’s first define the problem.

First, you’ll need to know which database and table you should wrap. Then, you’ll need to know which columns you should display. In addition, you’ll need to be able to securely and dynamically create your SQL in such a way that it supports pagination, filtering, and sorting — IF your manager tells you he wants these features in the future. Hence, arguably, the smallest possible amount of code you should be able to get away with, is the following.

micro.widgets.mysql.datagrid database:camphora table:sales columns product amount seller

The above code declares the database, it declares the table, and it declares the columns you want to display. No need to go all “fancy” here. The above should be enough, right?

Of course, you’ll need to make sure your code renders responsively, such that your users can use it from their phones, tablets, computers, and “whatever”. Okay, so we need a CSS grid wireframe to put our datagrid inside of. Well, that problem should be easily solvable with the following code, right?

/* * Includes our CSS grid wireframe. */
micro.css.include /* * Creates our entire page, including our MySQL datagrid. */
create-widget class:container widgets div class:row widgets div class:col widgets /* * Our actual datagrid. */ micro.widgets.mysql.datagrid database:camphora table:sales columns product amount seller

17 lines of code, plus a couple of comments, and you’ve got a nicely laid out page with a Bootstrap’ish CSS grid container/row/column layout, displaying an Ajax datagrid that fetches items from your MySQL database. Seriously, the problem we started out with shouldn’t really need more than the above code, right…?

Well, actually, the above is the solution to our problem. If you’ve never seen Hyperlambda before, I bet you thought the above code was “pseudo code,” right? Let me surprise you a little bit. The above is a working implementation of the problem we started out with, written in “Hyperlambda.” If you paste the above code into “Hypereval” from Phosphorus Five, and you save it as a “page,”  The above code will actually create a page for you with its own unique URL. Below is a screenshot of how creating such a “page” will look like.

Image title

When you have saved your page, you can click the “play” button to preview it, at which point a popup window will display something like the following in your browser. Notice how our solution already has its own friendly URL, matching the name of our “page.”

Image title

A perfectly working, Ajax DataGrid, wrapping your MySQL database — assuming you actually have a database named “camphora” and a table named “sales.” If you don’t, you can exchange the database, table, and columns to whatever database and table you happen to have in your MySQL database.

WUT…?

Most problems you encounter during your day at the office are in fact so generic in nature that they don’t need “coding” for you to solve them. In fact, you can easily get away with “declaring” the solutions to most of your problems. Several years ago, I realised that universal fact, and I set out to create Hyperlambda, which is kind of like a “markup language for web apps,” similar in syntax to YAML. Hyperlambda just so happens to allow you to “declare” your web apps without anything resembling what you would refer to as “code” to function. If you look at the above “code” example for instance, it kind of resembles JSON more than it resembles C# or PHP, right?

Now, if you want to add pagination, filtering, or sorting to your datagrid, this is also very easy. In fact, let’s add sorting to it just for fun.

/* * Includes our CSS. */
micro.css.include /* * Creates our page with a container/row/column CSS grid layout. */
create-widget class:container widgets div class:row widgets div class:col widgets /* * Our actual datagrid. */ micro.widgets.mysql.datagrid:your-datagrid database:camphora table:sales columns product .header widgets button innerValue:Product onclick /* * Re-databinding our datagrid, sorting by "product". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:product amount .header widgets button innerValue:Amount onclick /* * Re-databinding our datagrid, sorting by "amount". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:amount seller .header widgets button innerValue:Seller onclick /* * Re-databinding our datagrid, sorting by "seller". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:seller

If you remove the comments and spacing above, we’re now up to 38 lines of code, AND we have created an Ajax DataGrid, wrapping a MySQL database, allowing the user to sort. At this point, you’re probably suspicious, and apart from the “WUT?” parts, you’re probably asking yourself what the price for this is. However, before we take a look at that, let’s look at our result so far.

Image title

Clicking the buttons in your solution will now sort your result and re-databind your datagrid. However, what is “the cost” for this? Normally such RAD tools as you’re using above consumes megabytes of bandwidth, right? Well, believe it or not, the above web app will never download more than 5.7Kb of JavaScript to your client. Its total bandwidth consumption is 82Kb, including your CSS files, buttons, and the whole she bang. And it renders perfectly on your phone, tablet, or “whatever.” When you click a button, it consumes an additional 1.6Kb of bandwidth. That is 1.6Kb of bandwidth consumption to sort a datagrid. Most other RAD datagrids out there will easily consume megabytes of initial bandwidth and easily 10x as much bandwidth for each “databind” operation.

Since the datagrid never downloads more items to the client than it selects during a databind invocation, this implies that you can have a datatable with millions of records without ever needing to spend more than some ~100Kb of initial bandwidth because the whole magic is all happening on the server.

So, How Flexible Is This?

Well, if you look at the above code, you will see the following:

amount .header widgets button innerValue:Amount onclick /* * Re-databinding our datagrid, sorting by "amount". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:amount

If you take a look at line 4 above, it’s declaring a “button widget.” This implies that it will create a simple “button” HTML element for you, automatically creating an “onclick” event handler for it, which automatically will create the necessary Ajax request for you, going towards your server, and evaluating your Hyperlambda for you as the button is clicked, and if you look at the source for your web app, you will see that it renders a plain old HTML table for you. Hence, if you know HTML and CSS from before, there shouldn’t be anything in the above code’s result that’s not well known to you from before. In fact, it’ll even nicely format your HTML. Not too bad for 38 lines of code and a handful of comments. Of course, comparing our 38 lines of actual code above with “whatever tutorial” you find out there on the internet, trying to accomplish something similar, with “whatever framework”, should really be all the arguments you need. And our initial datagrid started out with 7 lines of code! And it consumes 5.7Kb of JavaScript! Please allow these numbers to sink in …

If you want to play around with the above code, you can download Phosphorus Five here and use “Hypereval” to play with the code. In later articles, I might dive deeper into this and implement pagination and filtering if there is interest in it.

Original Link

RDS Vs. MySQL on EC2 [Comic]

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:

aws ,rds ,mysql ,ec2 ,database administration ,relational database management system ,relational sql database ,cloud cost ,choices

Original Link

3 Day Coding Challenge: Creating MySQL Admin. for ASP.NET

I have just finished my “3 days coding challenge” and released a new version of Phosphorus Five, containing a stable, extremely secure, and unbelievably lightweight MySQL admin module that allows you to do most of the important tasks you’d normally need MySQL Workbench or PHPMyAdmin to perform. It features the following:

  • Automatic Syntax Highlighting of your SQL.

  • AutoCompletion of your SQL (click Ctrl+Space/Cmd+Space).

  • Paging with a “never-ending scrolling” experience.

  • Saving SQL snippets, and loading them, to allow you to build a “library” of snippets.

  • Automatically creates pie charts and column charts if your dataset supports it.

  • Rock solid security!

  • Plugin support, +++.

You can see a 3-minute long video of how the system works here.

In addition to the above features, the system is rock solid secure. This was my primary concern, in fact, since my current Linux box gets thousands of HTTP requests every day, from malicious IPs, attempting to break into PHPMyAdmin, which I don’t even have on my box for the record. In fact, you can’t even see or read its connection string unless you’re logged in as root since this is stored in the web.config file, which has “extra protection” in the system. Its usernames and passwords are stored in a file that has added protection and they aren’t even available for a root account since the passwords are stored as server-side salted and hashed strings. So, even if a malicious hacker should get a hold of your password file — which should be impossible — he still can’t figure out your passwords.

Multiple users is a given. The system also comes with an installation script, which allows you to install it on a Linux/Apache server in 5 minutes, further tightening down the box’ security, closing all ports excepts 80, 443 and 22 (SSH) — creating automatic redirect rules from port 80 to port 443, to automatically redirect to encrypted access, if the user is trying to access it insecurely. This script also installs an SSL key pair, and auto-renews it every 90 days. Etc, etc, etc, etc. There are no guarantees when it comes to security. However, this is as close as you come. At least assuming you trust Mono, Linux, MySQL, and Apache.

It doesn’t have all the features that PHPMyAdmin or MySQL Workbench have — obviously — since I’ve only spent three days implementing it, but already I believe it should be a valuable add-on in any sys-admin’s/db-admin’s toolbox. Two things I’ll probably end up implementing in the near future, which I miss myself, is creating and restoring backups, and “in-place editing” on its datagrid. Below are two screenshots of the system. First its “dashboard.”

Image title

Then, how it looks if you choose to display your result set as a pie chart, which is automatically done by simply clicking a button, if your result has less than 40 records and two columns, the values in your second column can somehow be converted into a number of some sort.

Image title

The system itself comes with 7 skins out of the box, in addition to 57 different code themes, allowing you to easily change its colors if you’re not happy with my default choice. However, to change its “theme,” you’ll need to do this from Hyper IDE. I still haven’t implemented a “settings” button to modify its theme from the system itself.

The system is called (for obvious reasons) — Hyper SQL, and you can download it here.

Original Link

Create Inline CRUD Using jQuery and AJAX

These are the four actions that make up the significant part of the actions of a PHP project. By the time developers get to the mid-level, they have actually created dozens of CRUD grids. In many cases, CRUD operations are an important part of CMS, inventory, and accounts management systems.

The idea behind the CRUD operations is to empower the users so that they could use the app to the maximum. All the information generated or modified through CRUD operations is stored in a database (generally MySQL).

To help budding developers, I will demonstrate a simple inline CRUD app using jQuery and AJAX.

Prerequisites

For the purpose of this tutorial, I assume that you have a PHP application installed on a web server. My setup is:

  • PHP7
  • JQuery
  • MYSQL

Create the Database and Tables

The step is the creation of the database and the relevant tables.

Since I am on Cloudways PHP Hosting, I already have a database created for my project. If this is not the case with you, use phpMyAdmin to create the database.

Next, I will create a table named posts_db through the following query:

CREATE TABLE IF NOT EXISTS `posts` ( `id` int(8) NOT NULL, `post_title` varchar(255) NOT NULL, `description` text NOT NULL, `post_at` date DEFAULT NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 

Setup Database Connection

To create the database connection, create a file named dbcontroller.php. Paste the following code into it:

class DBController { private $conn = ""; private $host = "localhost"; private $user = "root"; private $password = ""; private $database = "blog_samples"; function __construct() { $conn = $this->connectDB(); if(!empty($conn)) { $this->conn = $conn; } } function connectDB() { $conn = mysqli_connect($this->host,$this->user,$this->password,$this->database); return $conn; } }

Insert, Update, and Delete Functions

In the DBController class, there is a connectDB function. This is the perfect place for adding the functions for insert, update and delete functions. Here is the code for these functions:

function runSelectQuery($query) { $result = mysqli_query($this->conn,$query); while($row=mysqli_fetch_assoc($result)) { $resultset[] = $row; } if(!empty($resultset)) return $resultset; } function executeInsert($query) { $result = mysqli_query($this->conn,$query); $insert_id = mysqli_insert_id($this->conn); return $insert_id; } function executeUpdate($query) { $result = mysqli_query($this->conn,$query); return $result; } function executeQuery($sql) { $result = mysqli_query($this->conn,$sql); return $result; } function numRows($query) { $result = mysqli_query($this->conn,$query); $rowcount = mysqli_num_rows($result); return $rowcount; } 

Add Information to the Database

To add data to the database, create a file named add.php and add the following code to it:

<?php require_once("dbcontroller.php"); $db_handle = new DBController(); if(!empty($_POST["title"])) { $title = mysql_real_escape_string(strip_tags($_POST["title"])); $description = mysql_real_escape_string(strip_tags($_POST["description"])); $sql = "INSERT INTO posts (post_title,description) VALUES ('" . $title . "','" . $description . "')"; $faq_id = $db_handle->executeInsert($sql); if(!empty($faq_id)) { $sql = "SELECT * from posts WHERE id = '$faq_id' "; $posts = $db_handle->runSelectQuery($sql); } ?> <tr class="table-row" id="table-row-<?php echo $posts[0]["id"]; ?>"> <td contenteditable="true" onBlur="saveToDatabase(this,'post_title','<?php echo $posts[0]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[0]["post_title"]; ?></td> <td contenteditable="true" onBlur="saveToDatabase(this,'description','<?php echo $posts[0]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[0]["description"]; ?></td> <td><a class="ajax-action-links" onclick="deleteRecord(<?php echo $posts[0]["id"]; ?>);">Delete</a></td> </tr> <?php } ?>

Edit the Data in the Database

To edit information in the database, create a file named edit.php and add the following code to it:

<?php require_once("dbcontroller.php"); $db_handle = new DBController(); $sql = "UPDATE posts set " . $_POST["column"] . " = '".$_POST["editval"]."' WHERE id=".$_POST["id"]; $result = $db_handle->executeUpdate($sql); ?>

Delete the Data in the Database

In order to delete information from the database, create a file named delete.php and add the following code to it:

<?php require_once("dbcontroller.php"); $db_handle = new DBController(); if(!empty($_POST['id'])) { $id = $_POST['id']; $sql = "DELETE FROM posts WHERE id = '$id' "; $db_handle->executeQuery($sql);}?>

Bringing It All Together

It is time to create the form that will act as the view (front-end) of the app. Create the index.php and add the following code to it:

<?php require_once("dbcontroller.php"); $db_handle = new DBController(); $sql = "SELECT * from posts"; $posts = $db_handle->runSelectQuery($sql); ?> <table class="tbl-qa"> <thead> <tr> <th class="table-header">Title</th> <th class="table-header">Description</th> <th class="table-header">Actions</th> </tr> </thead> <tbody id="table-body"> <?php if(!empty($posts)) { foreach($posts as $k=>$v) { ?> <tr class="table-row" id="table-row-<?php echo $posts[$k]["id"]; ?>"> <td contenteditable="true" onBlur="saveToDatabase(this,'post_title','<?php echo $posts[$k]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[$k]["post_title"]; ?></td> <td contenteditable="true" onBlur="saveToDatabase(this,'description','<?php echo $posts[$k]["id"]; ?>')" onClick="editRow(this);"><?php echo $posts[$k]["description"]; ?></td> <td><a class="ajax-action-links" onclick="deleteRecord(<?php echo $posts[$k]["id"]; ?>);">Delete</a></td> </tr> <?php } } ?> </tbody> </table>

AJAX Inline Add Functionality

I will now create the Save and Cancel inline functionality through the native append and remove jQuery functions. The following code will use these functions, save the information in the database and download the loader.gif:

<script> function createNew() { $("#add-more").hide(); var data = '<tr class="table-row" id="new_row_ajax">' + '<td contenteditable="true" id="txt_title" onBlur="addToHiddenField(this,\'title\')" onClick="editRow(this);"></td>' + '<td contenteditable="true" id="txt_description" onBlur="addToHiddenField(this,\'description\')" onClick="editRow(this);"></td>' + '<td><input type="hidden" id="title" /><input type="hidden" id="description" /><span id="confirmAdd"><a onClick="addToDatabase()" class="ajax-action-links">Save</a> / <a onclick="cancelAdd();" class="ajax-action-links">Cancel</a></span></td>' + '</tr>'; $("#table-body").append(data); } function cancelAdd() { $("#add-more").show(); $("#new_row_ajax").remove(); } function addToDatabase() { var title = $("#title").val(); var description = $("#description").val(); $("#confirmAdd").html('<img src="loaderIcon.gif" />'); $.ajax({ url: "add.php", type: "POST", data:'title='+title+'&description='+description, success: function(data){ $("#new_row_ajax").remove(); $("#add-more").show(); $("#table-body").append(data); } }); } function addToHiddenField(addColumn,hiddenField) { var columnValue = $(addColumn).text(); $("#"+hiddenField).val(columnValue); } </script> 

AJAX Inline Edit & Delete Functionalities

On the blur event of the editable column, create the Edit that removes the row from first the UI and then the database. Here is the code:

<script> function saveToDatabase(editableObj,column,id) { $(editableObj).css("background","#FFF url(loaderIcon.gif) no-repeat right"); $.ajax({ url: "edit.php", type: "POST", data:'column='+column+'&editval='+$(editableObj).text()+'&id='+id, success: function(data){ $(editableObj).css("background","#FDFDFD"); } }); } function deleteRecord(id) { if(confirm("Are you sure you want to delete this row?")) { $.ajax({ url: "delete.php", type: "POST", data:'id='+id, success: function(data){ $("#table-row-"+id).remove(); } }); } } </script>

Conclusion

CRUD operations form the backbone of any client-facing app. In many cases, these operations could be easily implemented through inline jQuery and AJAX. Let me know in the comments if you have any issues in implementing the idea.

Original Link

Mssql-cli Command-Line Query Tool

A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product.

Microsoft announced mssql-cli, a SQL Server user-friendly, command line interactive tool hosted by the dbcli-org community on GitHub. It’s an interactive, cross-platform command line query tool. The public preview release of mssql-cli is available for testing. Mssql-cli is based on Python and the command-line interface projects such as pgcli and mycli. Microsoft released this tool under the OSF (Open Source Foundation) BSD 3 license. We can find its source code on GitHub. Now, the tool and the code are available for public preview. The tool is officially supported on Windows, Linux, and MacOS, and is compatible with Python versions 2.7, 3.4, and higher.

Mssql-cli improves the interactive CLI experience for T-SQL and includes support for SQL Server, MySQL, and PostgreSQL. The SQL Tools Service is the micro-service that forms the backend and is based on .NET SDK Framework.

Mssql-cli is a little ahead of sqlcmd in terms of functionality and enhancements. Some of these features are introduced below:

Features

  1. T-SQL IntelliSense
    • This provides an array of keyword references that can be easily accessible during the development process.
    • The parameter or metadata population is contextual.
    • It also provides a quick reference.
  2. Syntax highlighting
    • This feature helps in identifying the troubleshooting of typo or syntax errors.
    • It helps to complete keywords and snippets automatically, thus, improving efficiency.
    • Autosuggestion and auto-completion of tasks where needed are available.
  3. Query history
    • It provides a way to store the history.
    • This tool is no different than many other editors-use the up/down arrow keys to select the desired SQL statement.
    • The advantage of this tool is to automatically suggest commands from the history.
  4. Configuration
    • A configuration file is used to configure the initial settings and parameters.
    • By default, the configuration file is stored to the following path:
      • Important file location paths on Linux
        • Configuration file – ~/.config/mssqlcli/config
        • Log file ~/.config/mssqlcli/config/mssqlcli.log
        • History file – ~/.config/mssqlcli/config/history
      • Important file location paths on Windows
        • Configuration file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\
        • Log file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\mssqlcli.log
        • History file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\history\
  5. Multi-line queries

Linebreaking within a query is very similar to what we have in SQL. To provide another example, in many languages, even shell languages, we can break up a single long command or a chain of commands into multiple lines. In PowerShell, we break the line with a backtick (`). In BAT/CMD, we use ^ to indicate that we have not yet done to issue commands to the shell.

Mssql-cli works in two very popular modes right out of the box: VI and EMACS. Those who are familiar with UNIX would instantly recognize these tools-text editors, to be precise. Most VI (and VIM-or VI Improved) users talk about “thinking in VI(M)”, which essentially means that the editor is so friendly and minimal that typing on it is almost the second nature to most VI users. If you’re one of them, you should feel right at home using this tool in the VI mode.

If you’re one of the EMACS users, you can enable or even customize this mode on mssql-cli. EMACS has a library of the LISP code that enhances EMACS and extends its capabilities.

Further reading on mssql-cli.

Happy Learning!

Original Link

How Binary Logs Affect MySQL 8.0 Performance

As part of my benchmarks of binary logs, I’ve decided to check how the recently released MySQL 8.0 performance is affected in similar scenarios, especially as binary logs are enabled by default. It is also interesting to check how MySQL 8.0 performs against the claimed performance improvements in redo logs subsystem.

I will use a similar setup as in my last article with MySQL 8.0 using the utf8mb4 charset.

I have a few words about MySQL 8.0 tuning. Dimitri recommends in his blog posts to use innodb_undo_log_truncate=off and innodb_doublewrite=0. However, in my opinion, using these settings is the same as participating in a car race without working brakes: you will drive very fast, but it will end badly. So, contrary to Dimitri’s recommendations, I used innodb_undo_log_truncate=on and innodb_doublewrite=1.

Servers Comparison

For the first run, let’s check the results without binary logs vs. with binary logs enabled, but with sync_binlog=1 for Percona Server for MySQL 5.7 vs. MySQL 8.0.

Image title

In tabular form:

Image title

Binary Log Effect

In tabular form:

Image title

Conclusions

It seems that binary logs have quite an effect MySQL 8.0, and we see up to a 30% performance penalty as opposed to the 13% for Percona Server for MySQL 5.7.

In general, for in-memory workloads, Percona Server for MySQL 5.7 outperforms MySQL 8.0 by 10-20% with binary logs enabled, and 4-9% without binary logs enabled.

For io-bound workloads (buffer pool size <= 30GB), the performance numbers for Percona Server for MySQL and MySQL are practically identical.

Hardware Spec

Supermicro server:

  • Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
  • 2 sockets / 28 cores / 56 threads
  • Memory: 256GB of RAM
  • Storage: SAMSUNG SM863 1.9TB Enterprise SSD
  • Filesystem: ext4/xfs
  • Percona-Server-5.7.21-20
  • OS: Ubuntu 16.04.4, kernel 4.13.0-36-generic

Extra Raw Results, Scripts, and Config

My goal is to provide fully repeatable benchmarks. I have shared all scripts and settings I used in this GitHub repo.

Original Link

MySQL 8.0 GA: Quality or Not?

What does Anton Ego — a fictional restaurant critic from the Pixar movie Ratatouille — have to do with MySQL 8.0 GA?

When it comes to being a software critic, a lot.

In many ways, the work of a software critic is easy. We risk very little and thrive on negative criticism, which is fun to read and write.

But what about those who give their many hours of code development and those who have tested such code before release? How about the many people behind the scenes who brought together packaging, documentation, multiple hours of design, marketing, online resources, and more?

And all of that, I might add, is open-source! Free for the world to take, copy, adapt, and even incorporate in full or in part into their own open development!

It is in exactly that area that the team at MySQL shines once again: they have from their humble beginnings build up a colossally powerful database software that handles much of the world’s data — and fast.

Used in every area of life — aerospace, defense, education, finances, government, healthcare, pharma, manufacturing, media, retail, telecoms, hospitality, and finally, the web — it truly is a community effort.

My little contribution to this effort is first and foremost to say: Well done! Well done for such an all-in-all huge endeavor. When I tested MySQL 8.0, I experienced something new: an extraordinarily clean bug report screen when I unleashed our bug hunting rats… ahem… I mean tools. This was somewhat unexpected. Usually, new releases are a fun playground even for seasoned QA engineers who look for the latest toy to break.

I have a suspicion that the team at Oracle either uses newly improved bug-finding tools or perhaps they included some of our methods and tools in their setup. In either case, it is, was, and will be welcome.

When the unexpected occurs, a fight-or-flight syndrome happens. I tend to be a fighter, so I upped the battle and managed to find about 30 bugs, with 21 bugs logged already. Quite a few of them are Sig 11’s in release builds. Signal 11 exceptions are unexpected crashes, and release builds are the exact same build you would download at dev.mysql.com.

The debug build also had a number of issues, but less than expected, leading me to the conclusions drawn above. Since Oracle engineers marked many of the issues logged as security bugs, I didn’t list them here. I’ll give Oracle some time to fix them, but I might add them later.

In summary, my personal recommendation is this: Unless you are a funky new web company thriving on the latest technology, give Oracle the opportunity to make a few small point bug fix releases before adopting MySQL 8.0 GA. After that, providing upgrade prerequisites are matched, and that your software application is compatible, go for it and upgrade.

Before that, this is a great time to start checking out the latest and greatest that MySQL 8.0 GA has to offer!

All in all, I like what I saw, and I expect MySQL 8.0 GA to have a bright future.

Signed, a seasoned software critic.

Original Link

Binlog and Replication Improvements in Percona Server for MySQL

Due to continuous development and improvement, Percona Server for MySQL incorporates a number of improvements related to binary log handling and replication. This results in replication specifics, distinguishing it from MySQL Server.

Temporary Tables and Mixed Logging Format

Summary of the Fix

As soon as some statement involving temporary tables was met when using a mixed binlog format, MySQL switched to row-based logging for all statements until the end of the session (or until all temporary tables used in the session were dropped). This is inconvenient when you have long-lasting connections, including replication-related ones. Percona Server for MySQL fixes the situation by switching between statement-based and row-based logging when necessary.

Details

The new mixed binary logging format, supported by Percona Server for MySQL, means that the server runs in statement-based logging by default but switches to row-based logging when replication would be unpredictable, e.g. in the case of a nondeterministic SQL statement that could cause data divergence if reproduced on a slave server. The switch is done when matching any condition from a long list, and one of these conditions is the use of temporary tables.

Temporary tables are never logged using row-based format, but any statement that touches a temporary table is logged in row mode. This way, we intercept all the side effects that temporary tables can produce on non-temporary ones.

There is no need to use the row logging format for any other statements, solely because of the temp table presence. However, MySQL undertook such an excessive precaution: once some statement with a temporary table had appeared and the row-based logging was used, MySQL was logging unconditionally put all subsequent statements in row format.

Percona Server for MySQL has implemented more accurate behavior. Instead of switching to row-based logging until the last temporary table is closed, the usual rules of row vs. statement format apply, and we don’t consider the presence of currently opened temporary tables. This change was introduced with the fix of bug #151 (upstream #72475).

Temporary Table Drops and Binlogging on GTID-Enabled Server

Summary of the Fix

MySQL logs DROP statements for all temporary tables regardless of the logging mode under which these tables were created. This produces binlog writes and errand GTIDs on slaves with row and mixed logging. Percona Server for MySQL fixes this by tracking the binlog format at temporary table create time and uses it to decide whether a DROP should be logged or not.

Details

Even with read_only mode enabled, the server permits some operations, including ones with temporary tables. With the previous fix, temporary table operations are not binlogged in row or mixed mode. But MySQL server doesn’t track what the logging mode was when a temporary table was created, and therefore unconditionally logs DROP statements for all temporary tables. These DROP statements receive IF EXISTS addition, which is intended to make them harmless.

Percona Server for MySQL has fixed this with the bug fixes #964, upstream #83003, and upstream #85258. Moreover, with all the binlogging fixes discussed so far, nothing involving temporary tables is logged to the binary log in row or mixed format. There is no need to consider CREATE/DROP TEMPORARY TABLE unsafe for use in stored functions, triggers, and multi-statement transactions in row/mixed format. Therefore, we introduced an additional fix to mark the creation and drop of temporary tables as unsafe inside transactions in statement-based replication only (the fixed bug is #1816, while the correspondent upstream one is #89467 and it is still open).

Safety of Statements With a LIMIT Clause

Summary of the Fix

MySQL Server considers all UPDATE/DELETE/INSERT ... SELECT statements with the LIMIT clause unsafe, no matter if they are really producing non-deterministic results or not. Percona Server for MySQL is more accurate because it acknowledges such instructions as safe when they include ORDER BY PK or WHERE condition.

Details

MySQL Server treats UPDATE/DELETE/INSERT ... SELECT statements with the LIMIT clause as unsafe, considering that they produce an unpredictable number of rows. But some such statements can still produce an absolutely predictable result. One such deterministic case takes place when a statement with the LIMIT clause has an ORDER BY PK or WHERE condition.

The patch, making updates and deletes with a limit to be supposed as safe if they have an ORDER BY pk_column clause, was initially provided on the upstream bug report and incorporated later into Percona Server for MySQL with additional improvements. Bug fixed #44 (upstream #42415).

Performance Improvements

There are also two modifications in Percona Server related to multi-source replication that improve performance on slaves.

The first improvement is about relay log position, which was always updated in multi-source replications setups regardless of whether the committed transaction has already been executed. Percona Server omits relay log position updates for the already logged GTIDs.

These unconditional relay log position updates caused additional fsync operations in the case of relay-log-info-repository=TABLE. With the higher number of channels transmitting such duplicate (already executed) transactions, the situation became proportionally worse. The problem was solved in Percona Server 5.7.18-14. Bug fixed #1786 (upstream #85141).

The second improvement decreases the load on slave nodes configured to update the master status and connection information only on log file rotation. MySQL additionally updated this information in the case of multi-source replication when a slave had to skip the already executed GTID event. This behavior was the cause of substantially higher write loads on slaves and lower replication throughput.

The configuration with master_info_repository=TABLE and sync_master_info=0 makes the slave update the master status and connection information in this table on log file rotation and not after each sync_master_info event, but it didn’t work on multi-source replication setups. Heartbeats sent to the slave to skip GTID events that it had already executed previously were evaluated as relay log rotation events and reacted with mysql.slave_master_info table sync. This inaccuracy could produce a huge (up to five times on some setups) increase in write load on the slave before this problem was fixed in Percona Server for MySQL 5.7.20-19. Bug fixed #1812 (upstream #85158).

Current Status of Fixes

The three issues related to temporary tables that were fixed in Percona Server 5.5 and contributed upstream, and the final fixes of the bugs (#72475, #83003, and #85258) have landed into MySQL Server 8.0.4.

Original Link