Today I was doing some tests with XA transactions in MySQL 5.6. The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column: The good news is that 5.7 has transaction information in performance_schema: mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode -> from performance_schema.events_transactions_current; +-----------------+-----------------+--------+--------+----------+-------------+ | trx_id | isolation_level | state | xid | xa_state | access_mode | +-----------------+-----------------+--------+--------+----------+-------------+ | NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE | | 421476507015704 | REPEATABLE READ | ACTIVE | NULL | NULL | READ WRITE | | NULL | REPEATABLE READ | ACTIVE | foo-1 | ACTIVE | READ WRITE | | NULL | REPEATABLE READ | ACTIVE | NULL | NULL | READ ONLY | | NULL | ...

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. The first session: mysql [test] > create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql [test] > insert...

With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database. Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too small to hold the complete dataset or very small temporary tables. In MySQL 5.6 there is a new variable introduced to se...

I'm using a very small MariaDB instance as a datastore for my YouLess energy monitor , my own mail server (postfix, roundcube). It's a virtual machine from a commercial VPS provider. All data fits in memory and the overhead of running with performance_schema on is not an issue. While I was reading a blog post about performance_schema by Mark Leith I wanted to see what P_S could tell me about my own server. This is the output from the first query: mysql> select * from file_summary_by_event_name order by count_read desc,count_write desc limit 10; +--------------------------------------+------------+-------------+--------------------------+---------------------------+ | EVENT_NAME | COUNT_READ | COUNT_WRITE | SUM_NUMBER_OF_BYTES_READ | SUM_NUMBER_OF_BYTES_WRITE | +--------------------------------------+------------+-------------+--------------------------+---------------------------+ | wait/io/file/sql/FRM | 25387 | ...