Mon, 2014-10-27 10:21hartmut

This plugin logs long running transactions to the server error log when transaction took more than a configurable number of seconds.

https://github.com/hholzgra/transaction_time_audit

Motivation

A customer was looking for a solution to track down long running transactions. After discussing some alternatives we decided that an Audit Plugin was the best approach towards this.

An Audit Plugin has the advantage that it:

• Is executed within the mysqld server. Its access to server internals via the official API is limited, but it turned out that the existing API provided all the building blocks we needed for a basic implementation:

   

  • the full stream of executed queries is visible
  • current transaction ID is visible …

Let me start with a little story. You sit in your house near the fireplace in the living room and need a book from the library… Eh, no, sorry, wrong century. You’re building a robotic arm that will open your beer or brew your coffee or supply you with whatever other drinks of your choice… while you’ll be building the next robotic arm. So, you — soldering iron in one hand and Arduino in another — ask your little brother to bring a box with specific resistors (that you unexpectedly run out of) from the cellar. The problem — your brother is small and cannot tell a resistor from a respirator. You explain that it’s small thing with two wires sticking out of it. And he starts going back and forth brining you boxes after boxes of different small things with two wires.

This is approximately where we were in MySQL when NDB Cluster was just added. The use wants to find a row, say WHERE number_of_wires=2 AND size='small' AND …

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says:
"The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another …

OpenSQLCamp Boston has only been over for a week, but I already have about 2/3 of the videos uploaded to YouTube.  I have updated the schedule page with all the videos and slides I knew about.  I welcome comments with more information (e.g. links to slides, or tag or description suggestions for the YouTube videos).

Here’s the list of videos and slides so far (also linked at http://opensqlcamp.org/Events/Boston2010/Schedule):

Adventures in Alternative Energy “Data Monitoring” with MySQL — architecture and design case study – Matt Yonkovit, Percona – video

Cassandra and Lucene – Jake Luciani, Riptano –  …

I don’t think it’ll surprise anybody if I say that MariaDB or MySQL server knows a lot more about server system variables, then just their values. Indeed, every variable can be session or global only, read-only or writable, it has an associated help text (that is printed on mysqld --help --verbose), certain variables only accept values from a given set of strings (this set of allowed values is also printed in mysqld --help --verbose since MariaDB 10.1.0), numeric variables have lower and upper range boundaries of valid values (that are never printed anywhere), and so on. I always thought it’s kind of a waste that there is no way to query this information. That could’ve been very convenient, in particular for various GUI clients — they could show the help in tooltips, validate values and so on.

But recently we’ve got our users asking for it — precisely, for system variable metadata, whether a variable …

MariaDB 10.1.1 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.1/

This is the second alpha release of MariaDB 10.1, so there are a lot of new changes and functionalities added, and many, many bugs fixed (I counted 637). Since it’s alpha, I’ll only cover the major changes and additions, as there are a lot of great new features, and omit covering any of the bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

• InnoDB: You can now use OPTIMIZE TABLE to defragment InnoDB tablespaces (merged the Facebook/Kakao defragmentation patch). (Good blog post …

In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema.

But what are the possible solutions?

The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution.

The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution.

The …

When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state.

Example:

mysql [information_schema] > select trx_id,trx_state 

-> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974   | LOCK WAIT |
| 7962   | RUNNING   |
+--------+-----------+
2 rows in set (0.00 sec)

Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful.

Let's try to recreate the situation and see exactly what happened. I'll use two sessions for the transactions and a third to monitor the server.
…

Introduction

When you e.g. delete rows, these rows are just marked as deleted not really physically deleted from indexes and free space introduced is not returned to operating system for later reuse. Purge thread will physically delete index keys and rows, but still free space introduced is not returned to operating system and this operation can lead holes on page. If you have variable length rows, this could lead to situation where this free space can’t be used for new rows (if these rows are larger than old ones). User may use OPTIMIZE TABLE or ALTER TABLE <table> ENGINE=InnoDB to reconstruct the table.

Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared …

Fri, 2014-10-24 09:21maxmether

This is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog.

Dynamic Columns within WHERE

I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause:

SELECT id, name AS 'White Items'
FROM items
WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white';

+----+----------------------+
| id | White Items          |
+----+----------------------+
|  3 | Samsung Galaxy …