October 27, 2014 By Severalnines

The term data warehousing often brings to mind things like large complex projects, big businesses, proprietary hardware and expensive software licenses. With Hadoop came open source data analysis software that ran on commodity hardware, this helped address at least some of the cost aspects. We had previously blogged about MongoDB and MySQL to Hadoop. But setting up and maintaining a Hadoop infrastructure might still be out of reach for small businesses or small projects with limited budgets. Well, perhaps then you might want to have a look at Redshift.

…

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 –  …

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.
…

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 …

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 …

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 …

MySQL sys version 1.2.0 has just been released.

All views now work with MySQL 5.7.5, and the ONLY_FULL_GROUP_BY changes.

There is also a new script available (generate_sql_file.sh) that will allow RDS users to easily generate a single SQL file, using a specified user, that can be loaded in to an RDS instance. See the README for details on how to use that.

Here’s a full summary of the other changes:

Backwards Incompatible Changes

• The host_summary_by_stages and user_summary_by_stages wait_sum and wait_avg columns were renamed to total_latency and avg_latency respectively, for consistency.
…