SM SKP ADS-1
Planet MySQL - http://www.planetmysql.org/
Prepared Statement Samples
One of our most useful features is Top Queries, which allows users to examine various metrics for families of similar queries. Once a query family is selected, we can drill down into its query samples. Individual samples are shown on a scatterplot. Selecting individual samples will display its captured SQL, latency, errors, and more.
We collect query samples by decoding the MySQL protocol over TCP and extracting the query text. Not only that, we also capture errors, warnings, and more by inspecting traffic over the wire.
Until now, query samples excluded prepared statements. This is because prepared statements are not sent over the wire as strings of SQL text. Statement preparation and execution is quite different from regular query execution, but in the end we generate a good approximation of what the statement SQL would look like and display it on the samples page.
Regenerating the statement text happens in three steps. First, we grab the statement text from a COM_STMT_PREPARE command sent from a client to the MySQL server. The server then sends the client a COM_STMT_PREPARE response with a statement ID. When a statement is to be executed, the client sends a COM_STMT_EXECUTE command to the server with the statement ID and its associated parameters. The parameters sent by the client are sent in the binary protocol format. When our agent sees a COM_STMT_EXECUTE, it decodes the parameters and does a string replace in the original statement text to approximate the SQL query text.
Regenerating SQL from prepared statement executions is not perfect, but should be very good. We understand the argument types, escape strings and text when necessary, and represent parameters in hex when we have to. You'll notice that the metric parameter is a quoted string, not a literal number. That's because we actually send that parameter in ASCII format, as text. The protocol doesn't lie!
Of course, we're showing a sample of SQL that never executed as displayed, which is kind of disingenuous, isn't it? But if you think about it for a moment, this is exactly what you get from sources like the slow query log, which also shows SQL that's reconstructed from the prepared statement with parameters substituted in. The main difference is that the slow query log doesn't tell you it's showing you SQL that never actually executed, whereas here the Action column is execute which tells you it's a prepared statement, not a simple query.
Astute readers will have noticed the EXPLAIN tab to the right of the SQL sample. That's an upcoming feature. Stay tuned for more on that!
If you are interested in seeing how this can benefit your company, please click below for a demo or here for a free trial.
Putting MySQL Fabric to Use: July 30 webinar
Martin and I have recently been blogging together about MySQL Fabric (in case you’ve missed this, you can find the first post of the series here), and on July 30th, we’re going to be presenting a webinar on this topic titled “Putting MySQL Fabric to Use.”The focus of the webinar is to help you get started quickly on this technology, so we’ll include very few slides (mostly just a diagram or two) and then jump straight into shared screen mode, with lots of live console and source code examples.In order to make the best use of time, we won’t show you how to install and configure MySQL Fabric. However, we can point you to a few resources to help you get ready and even follow our examples as we go:The official manual is an obvious starting pointOur second post in the series includes configuration instructionsThis git repo contains the test environment we’ll use to run our demos. Specifically, we’ll use the sharding branch, so if you intend to follow our examples as we go, we recommend checking that one out.If you’re interested, you can register for this webinar here, and if there’s something specific you’d like to see (we had a request for PHP examples in the comments to our last post) feel free to post that as a comment. We can’t promise we’ll be able to cover all requests during the webinar, but we’ll incorporate examples to the repo as time allows.Hope to see you then!The post Putting MySQL Fabric to Use: July 30 webinar appeared first on MySQL Performance Blog.
New webinar-on-demand: Geographically distributed multi-master MySQL clusters
Global data access can greatly expand the reach of your business. Continuent Tungsten multi-site multi-master (MSMM) solutions enable applications to accept write traffic in multiple locations across on-premises and cloud providers. This includes the following important real-world use cases:
Improve performance for globally distributed users registering hardware devices by permitting updates
Docker: Containers for the Masses -- Getting terms straight
I was recently discussing container technologies with my team and was trying to explain the various container-related projects including LXC, libcontainer, Docker, Kubernetes, CoreOS, and how all these fit together.
I agreed that a blog post would be a good way to further clarify some terms. I wanted to continue my more in-depth example-laden blog posts, particularly about Ansible and Docker as well as the Ansible dynamic inventory plugin for Docker, though wanted to get my thoughts out before I forget (I have young children)!
This blog post is the latest in the series:
Ansible and Docker
Building Docker Images with Ansible
Building Docker Images with Ansible (yet another way)
Anisible Docker Facts module
The first term is one that anyone reading this article already knows, but I find it useful to revisit it when discussing containers. I myself still fall back into thinking about virtual machine concepts when discussing containers which is a normal way to grok a new concept, though containers really are different and using accurate terminology is ultimately a requirement to understanding something correctly.
A virtual machine, or for the sake of discussion, a VM, is emulation software that provides the ability to run programs as if you are running them on real hardware, constrained to that machine, that environment.
This usually means you install an OS on a virtual machine and on top of that OS, programs of your choice to make your VM complete. You can start and stop a VM, create an image based off of the VM at a given state and launch those images to have more copies of a given machine and environment.
There are various types of implementations that provide virtual machines including: KVM, VMware, Virtualbox, Windows Virtual PC, and QEMU.
In terms of containers, VMs can be incredibly useful for setting up something like Docker -- giving one the ability to run numerous VMs with containers running on each VM. VMs are also useful for composing blog posts and providing a test environment for Jekyll!
A container is an operating system-level virtualization method that provides an self-contained execution environment, that look and feel like virtual machines, but run within the OS and us the OS itself to provide this functionality. Containers don't require installing and operating system. When you run a container, you run whatever program that you want to run in the container without the overhead having to run an entire operating system. The processes run in a container are only visible inside the container and isolated from the host OS and other containers and don't require any emulating to run.
Container functionality is made possible by Linux kernel features such a cgroups, namespaces, apparmor, networking interfaces, and firewalling rules - the idea being, use the host OS to create and provide an environment where disk, CPU, memory, and networking work as if on a host of its own, just as you have with a VM.
There are various mechanisms that make containers possible: chroot, Docker, LXC, OpenVZ, Parallels, Solaris Containers, FreeBSD Jail. This blog post hones in on Docker and similar Linux containers.
Until recently, LXC was the default execution environment for Docker. LXC stands for LinuX Containers. LXC combines cgroups, Linux Kernel namespaces, apparmor profiles, seccomp policies, and chroots to provide containers-- or what they refer to as "Chroot on steroids".
LXC, written in C, provides a library, liblxc, language bindings, a set of tools for runnign containers.
libcontainer, written in the Go language, is the default execution environment for Docker. libcontainer provides essentially the same thing that LXC provides but in Go with no external dependencies, as well as a goal to be underlying container technology agnostic.
CoreOS is a stripped-down Linux OS based off of [Chrome OS][chromeos] that uses Docker containers to run multiple isolated Linux systems for resource partitioning.
CoreOS provides etcd, a key/value store written in Go to provide both distributed configuration information as well as service discovery for the cluster, implemented using what CoreOS also provides called Fleet, a cluster management daemon used to control systemd on each node.
This is certainly a topic this blog will revisit!
Kubernetes another topic the author has devled into and will have a separate post on in the future, is a project by Google (Google Cloud Platform guys). It is an Open Source container cluster management project.
Google uses container technology thoughout Google to both scale out and provide security for a number of applications such as search and Gmail where they run up to 2 billion containers a week — 3300/sec! GCE -- Google Cloud Environment-- runs VMs inside of containers for resource isolation between VMs and non-VM workloads.
Google doesn't currently use Docker internally yet has written kernel features that make containerization possible cgroups (control groups) which are used to limit, account, and isolate resource usage (CPU, memory, disk I/O) of process groups. There is also the work Google did with LMCTFY (Let Me Contain That For You), which is the open-source version of Google's container stack (https://github.com/google/lmctfy) which the functionality is being moved into libcontainer, the current default Go-based container driver for Docker.
Kubernetes is written in Go and the idea is to build on top of Docker, which Google sees as a technology that will be a standard for containerization (hence the aforementioned work they are doing). It is a scheduler for containers organized into what it refers to as "pods" as well as providing communication for containers.
The primary purpose of pods is support of co-located and co-managed helper programs such as content management systems, logging, log management and backup, proxies, bridges, adaptors, etc.
Also, like CoreOS does, Kubernetes makes use of etcd which provides the persistent state of the master.
There is much more information about Kubernetes, and as already has been mentioned, there will be a future blog post specifically covering Kubernetes.
How MariaDB makes Stored Procedures usable
I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.
First, SELECT is not the only SQL statement which returns a resultset. Other examples are DELETE RETURNING, CALL, SHOW, EXPLAIN and administrative commands like ANALYZE TABLE or CHECK TABLE. But these commands cannot be used in place of SELECT in the following contexts:
Subqueries, derived tables, JOINs, UNIONs
CREATE TABLE ... SELECT
INSERT/REPLACE INTO ... SELECT
CREATE VIEW ... AS SELECT
DECLARE CURSOR ... SELECT
SET @my_var := SELECT
This means that there is no way to read the output of these statements within a stored procedure or trigger.
Also, some statements cannot be executed in a stored procedures and/or in prepared statements. An example is CHECK TABLES: even if you don’t care about the resultset (which would be a serious limitation), you cannot execute it in a stored procedure or in a prepared statements.
MariaDB and MySQL support the DO statement, which is identical to SELECT except that it doesn’t return any result. However, stored procedures could return one or more resultsets too, and you cannot invoke them with DO. This means that in some contexts you may have to deal to annoying resultsets, or perhaps you cannot call those procedures (within triggers or functions).
I faced all these problems while developing STK/Unit and in other projects.
But MariaDB lets us workaround these limitations – and the funny thing is that probably its developers are not aware about this! Here is an example:
CREATE OR REPLACE TABLE show_master_status
ENGINE = CONNECT
TABLE_TYPE = MYSQL
SRCDEF = 'SHOW MASTER STATUS'
CONNECTION = '_'
CREATE PROCEDURE test()
DECLARE eof BOOL DEFAULT FALSE;
DECLARE v_file, v_position TEXT;
DECLARE cur CURSOR FOR
SELECT `File`, `Position` FROM show_master_status;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET eof := TRUE;
FETCH cur INTO v_file, v_position;
IF eof IS TRUE THEN
SELECT CONCAT('I''ll do something with ', v_file, v_position);
MariaDB [_]> CALL test();
| CONCAT('I''ll do something with ', v_file, v_position) |
| I'll do something with binlog.00000242965 |
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
As you can see, with this trick I was able to use a cursor with SHOW MASTER STATUS.
How does the trick work? If you know CONNECT, probably you already guessed. CONNECT is a storage engine which allows the users to read data from several kind of data sources as if they were regular SQL tables. Supported data sources include several file formats, remote DBMS’s and more. In this case I defined a table which connects to a MariaDB/MySQL server (TABLE_TYPE=MYSQL). The server is… localhost. Since I specified a SRCDEF table option, the data source is not a table in the server, it is the resultset of the specified query (SHOW MASTER STATUS). This means that, when the table is queried, the CONNECT engine opens a connection to the local server, it executes SHOW MASTER STATUS, and it returns the results of that statement. But since this work is done by the CONNECT engine, from the MariaDB point of view we are just querying a table. So, we can use that query in all contexts where SELECT works.
Also note that we don’t have to specify the table structure. CONNECT supports table discovery, which means that it automatically knows which columns and types are needed.
Confused? I hope not. Look again at the example – everything will be clear.
Of course, having to CREATE a TABLE each time is not very comfortable, even with table discovery. So I wrote a stored procedure which does the boring work for us:
CREATE PROCEDURE `_`.`materialize_sql`(IN p_sql TEXT)
MODIFIES SQL DATA
SET @v_materialized_sql := CONCAT_WS('',
'CREATE OR REPLACE TABLE `_`.`materialized_results`'
, ' ENGINE = CONNECT'
, ' TABLE_TYPE = MYSQL'
, ' SRCDEF = ''', REPLACE(p_sql, '''', ''''''), ''''
, ' CONNECTION = ''_'''
PREPARE stmt_materialized_sql FROM @v_materialized_sql;
DEALLOCATE PREPARE stmt_materialized_sql;
SET @v_materialized_sql := NULL;
SELECT * FROM _.materialized_results;
Occasionally, this doesn’t work for me, and fails with a weird error about non-existing columns. I think that a bug affects this technique, but I wasn’t able to isolate it still. When I will find a stable way to reproduce the bug, I will report it. If you are able to isolate the bug before I do, please report it. But in the meanwhile, this technique seems to me usable in most cases. Test it with real queries, before using those queries in production. If it doesn’t work in your specific case, manually create the table. You can do it in a stored procedure, with an hardcoded query – not a prepared statement.
There is also a limitation you must be aware of: CONNECT doesn’t support BLOB and TEXT types. If you need a query which returns a BLOB or TEXT, you can probably convert it to VARCHAR, but this probably means that you can’t use table discovery. This limitation affects mainly ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE and REPAIR TABLE. All these statements returns the same columns, so I wrote a stored procedure and a table which handle them:
-- contains resultsets for OPTIMIZE, ANALYZE, CHECK, REPAIR
CREATE OR REPLACE TABLE `_`.`administrative_sql`
`Table` VARCHAR(64) NOT NULL
, `Op` VARCHAR(255) NOT NULL
, `Msg_type` VARCHAR(255) NOT NULL
, `Msg_text` VARCHAR(255) NOT NULL
ENGINE = CONNECT
TABLE_TYPE = MYSQL
SRCDEF = 'CHECK TABLE t'
CONNECTION = '_'
DROP PROCEDURE IF EXISTS `_`.`administrative_sql`;
CREATE PROCEDURE `_`.`administrative_sql`(IN p_sql TEXT)
MODIFIES SQL DATA
SET @v_administrative_sql := CONCAT_WS('', 'ALTER TABLE _.administrative_sql SRCDEF = \'', p_sql, '\'');
PREPARE stmt_administrative_sql FROM @v_administrative_sql;
DEALLOCATE PREPARE stmt_administrative_sql;
SET @v_administrative_sql := NULL;
SELECT * FROM _.administrative_sql;
MariaDB [_]> CALL administrative_sql('ANALYZE TABLE t');
| Table | Op | Msg_type | Msg_text |
| _.t | analyze | status | Engine-independent statistics collected |
| _.t | analyze | status | OK |
2 rows in set (0.22 sec)
Query OK, 0 rows affected (0.22 sec)