Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 11 to 20 of 33349 10 Older Entries
Data Warehouse in the Cloud - How to Upload MySQL data into Amazon Redshift for reporting and analytics
+0 Vote Up -0Vote Down
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

  [Read more...]
A transaction duration tracking audit plugin for MariaDB and MySQL
+2 Vote Up -0Vote Down
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.


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:


  [Read more...]
Refactoring replication topologies with Pseudo GTID: a visual tour
+0 Vote Up -0Vote Down

Orchestrator 1.2.1-beta supports Pseudo GTID (read announcement): a means to refactor the replication topology and connect slaves even without direct relationship; even across failed servers. This post illustrates two such scenarios and shows the visual way of mathcing/re-synching slaves.

Of course, orchestrator is not just a GUI tool; anything done with drag-and-drop is also done via web API (in fact, the drag-and-drop invoke the web API) as well as via command line. I'm mentioning this as this is the grounds for failover automation planned for the future.

Scenario 1: the master unexpectedly dies


  [Read more...]
Orchestrator 1.2.1 BETA: Pseudo GTID support, reconnect slaves even after master failure
+0 Vote Up -0Vote Down

orchestrator 1.2.1 BETA is released. This version supports Pseudo GTID, and provides one with powerful refactoring of one's replication topologies, even across failed instances.

Depicted: moving a slave up the topology even though its local master is inaccessible

Enabling Pseudo-GTID

You will need to:

  • Inject a periodic
  •   [Read more...]
    MariaDB 10.1.1: engine_condition_pushdown flag deprecated
    +0 Vote Up -0Vote Down

    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

      [Read more...]
    When your query is blocked, but there is no blocking query - Part 3
    +0 Vote Up -0Vote Down
    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

      [Read more...]
    MariaDB 10.1.1: system variables and their metadata
    +0 Vote Up -0Vote Down

    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

      [Read more...]
    MariaDB 10.1.1 Overview and Highlights
    +0 Vote Up -0Vote Down

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

    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
      [Read more...]
    When your query is blocked, but there is no blocking query - Part 2
    +0 Vote Up -0Vote Down
    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

      [Read more...]
    When your query is blocked, but there is no blocking query
    +1 Vote Up -0Vote Down
    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.

    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

      [Read more...]
    10 Newer Entries Showing entries 11 to 20 of 33349 10 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.