The latest from the MySQL community

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 …

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 …

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT":

SQL> SHOW PROCESSLIST;

27      user    host:33214      foodmart        Query   14440   sleeping        XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x

SQL> SHOW ENGINE INNODB STATUS;

TRANSACTIONS
============
---TRANSACTION 2DE71D, ACTIVE 14459 sec
9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115
MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart …