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 …[Read more]
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 …[Read more]
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 …
[Read more]
Taxonomy upgrade extras: myenv operation MySQL Operations multi instance consolidation Backup catalog
FromDual has the pleasure to announce the release of the new version 1.1.2 of its popular MySQL, Galera, MariaDB and Percona Server multi-instance environment MyEnv.
You can download MyEnv from …
[Read more]Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.
Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:
Q: Does Solr automatically maintain its index against MySQL? Do you have to hit the Solr server with a specific query to keep the index ‘warm’?
There are several strategies for updating a Solr …
[Read more]As a part of improving the error handling, in MySQL 5.7.5 we have re-implemented STRICT sql mode.
STRICT mode in MySQL affects the errors that arise from invalid, missing, or out of range values in DML statements such as INSERT, UPDATE, and DELETE. The new implementation aims to make the behavior of STRICT mode more consistent, yet maintain backward compatibility as much as possible.
In MySQL 5.7.5, we have made three general improvements to STRICT mode behavior:
1. STRICT mode got simpler
It was observed that having a large number of sql modes dependent on STRICT mode creates confusion among users. Specifically, we are talking about NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes. You can se further details on the above sql modes here. These modes only …
[Read more]With a last minute change to the host_summary_by_stages view for the 1.2.0 release of the sys schema, I managed to break the views without noticing it.
So, in the shortest release cycle for the sys schema ever (less than 24 hours), 1.3.0 has now been released.
This comes with a bonus though, there was also a new view that was contributed by Jesper Wisborg Krogh waiting to be merged as well, so I’ve added that to the 1.3.0 version (hence the 1.3.0 instead of 1.2.1).
This is the innodb_lock_waits view, which shows all sessions that are waiting for a lock within InnoDB, as well as the details of who is blocking them, here’s an example output:
mysql> SELECT * FROM innodb_lock_waits\G *************************** 1. row *************************** waiting_trx_id: 805505 …[Read more]