In this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement.

The SQL statement “create table <table_name> as select …” is used to create a normal or temporary table and materialize the result of the select. Some applications use this construct to create a copy of the table. This is one statement that will do all the work, so you do not need to create a table structure or use another statement to copy the structure.

At the same time there are a number of problems with this statement:

1. You don’t create indexes for the new table
2. You are mixing transactional and non-transactional statements in one transaction. As with any DDL, it will commit current and unfinished transactions
3. CREATE TABLE … SELECT …

Introducing HTTP server in ProxySQL 1.4.4

ProxySQL exports a lot of metrics, all visible in the stats schema and queryable using any client that uses the MySQL protocol.
This allows to ingest metrics into external monitoring tools, like Percona PMM, grafana, Severalnines ClusterControl, graphite, datadog, and others already existing or being under development.

Although we believe these monitoring tools are the preferred and right way to monitor ProxySQL, we often faced the issue that ProxySQL isn’t monitored while still being evaluated. This makes very difficult the troubleshooting of performance and behavior in such scenarios. In fact, it is common that users report issues, but without metrics it is often very difficult to distinguish between a real performance bug, or a misconfiguration, or even issues completely unrelated to ProxySQL.
For this reason, ProxySQL version 1.4.4 introduces …

MariaDB ColumnStore Distributed User Defined Aggregate Functions david.hall@mar… Wed, 01/10/2018 - 11:31

MariaDB ColumnStore 1.1 introduces the Distributed User Defined Aggregate Functions (UDAF) C++ API. MariaDB Server has supported UDAF (a C API) for a while, but now we have extended it to the ColumnStore Engine. This new feature allows anyone to create aggregate functions of arbitrary complexity for distributed execution in the ColumnStore Engine. These functions can also be used as Analytic (Window) functions just like any built in aggregate. You should have a working understanding of C++ to use this API.

The UDAF API supports an arbitrary number of parameters to be defined for your Aggregate function. Version 1.1 of ColumnStore supports one parameter and will be enhanced to support any number of parameters in the next version.

For …

This tutorial shows how to install ISPConfig 3.1 on a CentOS 7.4 (64Bit) server. ISPConfig 3 is a web hosting control panel that allows you to configure the following services through a web browser: Apache web server, Postfix mail server, MySQL, BIND nameserver, PureFTPd, SpamAssassin, ClamAV, Mailman, and many more.

Monitoring MySQL backups is a vital part of any reliable backup solution. The Recovery Time Objective and Recovery Point Objective are the most common disaster recovery metrics. The TwinDB Backup Tool along with Datadog allows monitoring both of them. Recovery Point Objective Basically, the Recovery Point Objective aka RPO means how much data you can […]

The post Monitoring MySQL backups with Datadog and TwinDB Backup Tool appeared first on TwinDB.

In this post, we’ll look at updating and deleting rows with ClickHouse. It’s the first of two parts.

ClickHouse is fast – blazing fast! It’s quite easy to pick up, and with ProxySQL integrating with existing applications already using MySQL, it’s way less complex than using other analytics options. However, ClickHouse does not support UPDATE/DELETE (yet). That entry barrier can easily dissuade potential users despite the good things I mentioned.

If there is a will, there is a way! We have so far taken advantage of the new feature that supports more granular partitioning strategy (by week, by day or something else). With more granular partitions, and knowing what rows have changed, we can drop partitions with modified/deleted data and re-import them from MySQL. It is worth noting that this feature is …

Join Percona’s, CEO and Co-Founder, Peter Zaitsev as he presents Why We’re Excited About MySQL 8.0 on Wednesday, January 10, 2018, at 11:00 am PST / 2:00 pm EST (UTC-8).

Experience: Basic

Tags: Developer, DBAs, Operations, Executive, MySQL

There are many great new features in MySQL 8.0, but how exactly can they help your applications? This session takes a practical look at MySQL 8.0 features and improvements. It looks at the bugs, issues and limitations of previous MySQL versions and how MySQL 8.0 addresses them. It will also cover what you can do with MySQL 8.0 that you couldn’t before.

…

MariaDB MaxScale 2.1 and SSL Certificates Wagner Bianchi Tue, 01/09/2018 - 13:44

MariaDB MaxScale has becoming increasingly popular as a database proxy, adopted by users that would like to take advantage of a good strategy for scaling out databases and data infrastructure. As with any database environment, it is important to make the environment safe and to adopt the top industry best practices. Most MariaDB MaxScale users have or will have MaxScale handling traffic to database instances/backends in a wan, where servers can be added to MariaDB’s intelligent database proxy and based on the configurations, traffic is routed to those servers. In some cases, man-in-the-middle and other attack strategies are used to intercept information while data is being replicated and, while connections are routed to the backend databases.

This blog post will explore the setup of an environment using self-signed OpenSSL …

Join Laurent Blume, Unix Systems Engineer & PCI Specialist and Vinay Joosery, CEO at Severalnines, as they discuss all there is to know about how to achieve PCI compliance for MySQL & MariaDB with ClusterControl in this new webinar on January 30th.

The Payment Card Industry Data Security Standard (PCI-DSS) is a set of technical and operational requirements defined by the PCI Security Standards Council (PCI SSC) to protect cardholder data. These standards apply to all entities that store, process or transmit cardholder data – with requirements for software developers and manufacturers of applications and devices used in those transactions.

Download whitepaper

 PCI Compliance for MySQL …

In my previous post, I write that Write Set is not only in MySQL 8.0 but also in MySQL 5.7 though a little hidden.  In this post, I describe Write Set in 5.7 and this will bring us in the inner-working of Group Replication.  I am also using this opportunity to explain and show why members of a group can replicate faster than a standard slave.  We will also see the impacts, on Group Replication,