Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 33 Next 3 Older Entries

Displaying posts with tag: transactions (reset)

Introducing TokuMX Transactions for MongoDB Applications
+1 Vote Up -1Vote Down

Since our initial release last summer, TokuMX has supported fully ACID and MVCC multi-statement transactions. I’d like to take this post to explain exactly what we’ve done and what features are now available to the user.

But before beginning, an important note: we have implemented this for non-sharded clusters only. We do not support distributed transactions across different shards.

At a high level, what have we done?

We have taken MongoDB’s basic transactional behavior, and extended it. MongoDB is transactional with respect to one, and only one, document. MongoDB guarantees single document atomicity. Journaling provides durability

  [Read more...]
How InnoDB works with transactions and auto recovery
+0 Vote Up -0Vote Down
How InnoDB work with transactions: When any transaction will be completed with COMMIT,  InnoDB will write those changes in InnoDB Buffer Pool. After that InnoDB will run some background operations like checkpoint.  Checkpoint is the most important operation which will Continue reading →   [Read more...]
MySQL needs single master to check data integrity
+0 Vote Up -0Vote Down

Read the original article at MySQL needs single master to check data integrity

MySQL slaves can drift out of sync. Many of our clients are surprised to find some data differences in their replication topology, once we do some checking and sniffing around. Such checks require a single reliable or authoritative master to compare against. Click through to the end for multi-master solutions that work with MySQL. Reason [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:
  • MySQL requires an authoritative master to build slaves
  •   [Read more...]
    Implications of Metadata Locking Changes in MySQL 5.5
    +1 Vote Up -0Vote Down

    While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

    The post Implications of Metadata Locking Changes in MySQL 5.5 appeared first on ovais.tariq.

    Easily testing MySQL 5.6 GTID in a sandbox
    +6 Vote Up -0Vote Down

    MySQL 5.6 seems to be ready for GA. I have no inside information about it, but from some clues collected in various places I feel that the release should not be far away. Thus, it's time for some serious testing, and for that purpose I have worked at updating MySQL Sandbox with some urgent features.

    I have just released MySQL Sandbox 3.0.28, with more support for MySQL 5.6. Notably in this release, there is suppression of MySQL 5.6 annoying verbosity, additional suppression of more annoying warnings ( actually a bug) when using empty passwords

      [Read more...]
    XA Transactions between TokuDB and InnoDB
    +2 Vote Up -0Vote Down
    The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

    XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

    Internal XA Transactions are used to keep the binary log and InnoDB in sync.

    Demo 1: XA Transaction on 1 node:
    mysql55-tokudb6> XA START 'demo01';
    Query OK, 0 rows affected (0.00 sec)

    mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
    Query OK, 1 row affected (0.01 sec)

    mysql55-tokudb6> SELECT * FROM xatest;
    +----+--------+
    | id | name |
    +----+--------+
    | 3 | demo01 |
    +----+--------+
    1 row in set (0.00 sec)




















      [Read more...]
    Consistent transactions between storage engines
    +1 Vote Up -0Vote Down

    You may not realize it, but in MariaDB 5.2 and earlier and in MySQL up to version 5.5, START TRANSACTION WITH CONSISTENT SNAPSHOT does not give any guarantees of consistency between different storage engines.

    For example, suppose you have two transactions which run in parallel:

    Transaction T1:

    BEGIN;
        SET @t = NOW();
        UPDATE xtradb_table SET a= @t WHERE id = 5;
        UPDATE pbxt_table SET b= @t WHERE id = 5;
        COMMIT;
    

    Transaction T2:

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        START TRANSACTION WITH CONSISTENT SNAPSHOT;
        SELECT t1.a, t2.b
          FROM xtradb_table t1 INNER JOIN pbxt_table t2 ON t1.id=t2.id
        WHERE t1.id = 5;
    

    In the above case, it is possible, even with a "consistent" snapshot, to see the changes in a transaction only in InnoDB/XtraDB tables, and not in

      [Read more...]
    Better scaling of read-only workloads
    Employee +3 Vote Up -0Vote Down

    The problem and its cause

    There have been several complaints over the years about InnoDB’s inability to scale beyond 256 connections. One of the main issues behind this scalability bottleneck was the read view creation that is required for MVCC (Multi Version Concurrency Control) to work. When the user starts a transaction this is what InnoDB does under the hood:

    • Create or reuse a transaction instance – usually it is reused, the transactions are reused from a pool (trx_sys_t::mysql_trx_list).
    • Initialize the transaction start time and assign a rollback segment
    • Append the transaction to an active  transaction list ordered on trx_t::id in descending order

    The append to  the trx_sys_t::trx_list and corresponding remove during commit is covered by trx_sys_t::mutex. After the transaction is


      [Read more...]
    SQL Locking and Transactions – OSDC 2011 video
    +0 Vote Up -0Vote Down
    This recent session at OSDC 2011 Canberra is based on part of an Open Query training day, and (due to time constraints) without much of the usual interactivity, exercises and further MySQL specific detail. People liked it anyway, which is nice! The info as presented is not MySQL specific, it provides general insight in how databases implement concurrency and what trade-offs they make. See http://2011.osdc.com.au/SQLL for the talk abstract.
    Shinguz: MySQL Query Cache does not work with Complex Queries in Transactions
    +1 Vote Up -0Vote Down

    We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
    When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
    This was verified on a testing system which had the Query Cache enabled by accident.

    But we all thought that the Query Cache would make sense in this situation so we investigated a bit more.

    They have a Java application where they do pretty complex queries (10 to 30-way Joins) and they Connect with Connector/J to the database. We tried it out in the application on a dedicated system and verified that the Query Cache was not serving our queries but the query did a full dive to the



      [Read more...]
    A rollback query on innodb does not reset the auto_increment counter?!?!?
    +0 Vote Up -0Vote Down
    So, this is a small blog that I felt necessity to do cause I am seeing many tweets on twitter (obviously) talking about that “a rollback query on innodb does not reset the auto_increment counter”. I quickly went test it and really didn’t understand what happened with this user environment, due to it I decided [...]
    Understanding InnoDB transaction isolation levels
    +0 Vote Up -0Vote Down
    Isolation is an important part of ACID properties that guarantee that transactions are processed in a reliable manner. But there are four different levels of isolation available and you have to understand each one of them to be able to select the correct one for your needs. This post intends on explaining the four levels together with their effects on locking and performance.
    On Hot Backups and Restore using XtraBackup
    +3 Vote Up -0Vote Down
    Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.
    No implicit commit (on the road to transactional DDL)
    +2 Vote Up -0Vote Down

    A long time ago, in a time that can only serve to make some feel old and others older, MySQL didn’t support transactions. Each statement was executed as it went, there was no ROLLBACK (or COMMIT or crash recovery etc). Then there were transactions. Other RDBMSs implement auto_commit functionality, but for MySQL users, we think of it as the magic compatibility mode that (mostly) makes applications written for MyISAM magically work on InnoDB (okay, and making “you should use transactions” a really easy consulting gig :)

    I’m currently working on finishing up a patch that removes the implicit COMMIT from DDL operations in Drizzle. Instead, you get an error message saying that Transactional DDL is not currently supported. I see a future where we have one of two situations (possibly depending on

      [Read more...]
    Tuning InnoDB Configuration
    +2 Vote Up -0Vote Down
    I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn't intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.
    Dispelling some unintentional MySQL FUD
    +13 Vote Up -0Vote Down
    There are three types of FUD: the first and more genuine is (#1) the intentional spreading of falsehood, mostly to gain some marketing advantage over a competing product. While I despise this practice, I understand it.
    Then there is (#2) FUD spread by ignorance, when the originators are so blindly enraged by their hatred for a product that they don't care about getting the facts straight.
    And finally, there is a third kind, not less dangerous, which is (#3) the spreading of FUD with good intentions, when the authors believe that they have the facts straight and they want to help.I have recently come across two examples of

      [Read more...]
    Why do I recommend switching over from MyISAM to Innodb!
    +1 Vote Up -0Vote Down
    Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.
    MySQL Idiosyncrasies That Bite
    +2 Vote Up -0Vote Down

    The following are my slides that I presented at ODTUG Kaleidoscope 2010. This presentation talks about the MySQL defaults including a non-transactional state, silent data truncations, date management, and transaction isolation options. These are all critical for data integrity and consistency. I also cover in-depth topics including SQL_MODE, character sets and collations.

    MySQL Idiosyncrasies That Bite View more presentations from Ronald Bradford.
    Benchmarking MySQL ACID performance with SysBench
    +0 Vote Up -2Vote Down

    A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”

    Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):

    • sysbench –test=oltp
      [Read more...]
    When commit appears to fail
    +0 Vote Up -1Vote Down
    So you're using explicit transactions. Everything appears to work (every query gives the expected result) until you get to COMMIT.

    Then you get an exception thrown from COMMIT. What happened?

    Usually this would be because the server has been shut down, or you've lost the connection.

    The problem is, that you can't assume that the commit failed, but you also can't assume it succeeded.

    A robust application must make NO ASSUMPTION about whether a failed commit did, indeed, commit the transaction or not. It can safely assume that either all or none of it was committed, but can't easily tell which.

    So the only way to really know is to have your application somehow remember that the transaction MIGHT have failed, and check later.

    Possible solutions:
    • Ignore it and deal with any inconsistencies manually, or













      [Read more...]
    Introducing the InnoDB Blog
    +0 Vote Up -0Vote Down

    Well, here we are … the first post to the InnoDB blog.   Now there is a blog dedicated solely to InnoDB products and technology. The Innobase team will be posting here regularly on all manner of topics regarding the InnoDB storage engine.  We plan to provide timely updates and important technical information about InnoDB-related products including the built-in InnoDB distributed by MySQL, the InnoDB Plugin and InnoDB Hot Backup.  We invite you to visit regularly and post your comments.

    We’ve borrowed the name “Transactions on” from the computer-science journal Transactions on Database Systems, published by the ACM society for computing professionals.  Like that journal, this blog will cover a wide range of database topics, specifically as they relate to InnoDB.

    Users of InnoDB know a transaction is an atomic all-or-nothing set of

      [Read more...]
    Refactoring MySQL Applications: Part 1
    +0 Vote Up -0Vote Down

    First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It’s been a bunch of busy weeks, but I’m fully expecting to remedy that in this year.

    Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.

    So, you are a DBA or a developer, and you’re faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.

    Let’s start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let’s take the refactoring way, focusing on SQL

      [Read more...]
    Great things afoot in the MySQL community
    +0 Vote Up -0Vote Down

    tl;dr: The MySQL community rocks. Percona, XtraDB, Drizzle, SSD storage, InnoDB IO scalability challenges.

    For anyone who lives and dies by MySQL and InnoDB, things are finally starting to heat up and get interesting. I’ve been banging the “MySQL/InnoDB scales poorly” drums for years now, and despite having paid Enterprise licenses, I haven’t been able to get anywhere. I was pretty excited when Sun

      [Read more...]
    Using BASE instead of ACID for scalability
    +0 Vote Up -0Vote Down

    My editor Andy Oram recently sent me an ACM article on BASE, a technique for improving scalability by being willing to give up some other properties of traditional transactional systems.

    It’s a really good read. In many ways it is the same religion everyone who’s successfully scaled a system Really Really Big has advocated. But this is different: it’s a very clear article, with a great writing style that really cuts out the fat and teaches the principles without being specific to any environment or sounding egotistical.

    He mentions a lot of current thinking in the field, including the CAP principle, which Robert Hodges of Continuent first turned me onto a

      [Read more...]
    Statement-based replication is disabled for Falcon
    Employee +0 Vote Up -0Vote Down
    Contrary to what I said earlier, Falcon has decided to deliberately disable statement-based replication using the same capabilities mechanism that InnoDB uses.

    The reason is that isolation between concurrent transactions cannot be guaranteed, meaning that two concurrent transactions are not guaranteed to be serializable (the result of a concurrent transaction that has committed can "leak" into an ongoing transaction). Since they are not serializable, it means they cannot be written to the binary log in an order that produce the same result on the slave as on the master.

    However, when using row-based replication they are serializable, because whatever values are written to the tables are also written to the binary log, so if data "leaks" into an ongoing

      [Read more...]
    Checking transactions in MySQL
    +0 Vote Up -0Vote Down
    I'd been doing some stress testing of my mysql application today, and I was hitting some weird cases. Several transactions were deadlocking - this was expected - but the number of records that got inserted into my table was more than the number that I expected after subtracting errors.

    My test was fairly simple:
  • Fork 15 processes
  • Insert and update 100 records in each process, running each INSERT/UPDATE pair inside one transaction
  • ROLLBACK on error
  • Either the INSERT or the UPDATE was expected to fail due to deadlock, and the whole transaction should have rolled back leaving no record in the table.

    Before I go on, I should mention that I was using InnoDB, which does support transactions.

    What I expected was that the total number of records in the table + the total number of INSERT/UPDATE aborts due to deadlock should be equal





      [Read more...]
    Fasten your seatbelts ...
    +0 Vote Up -0Vote Down
    ... or how to safely run data manipulation statements in your database.Reading posts on Devshed's forums I sometime notice people doing maintenance work on their data without any safety net apart from occasional ages old backups ;-).Anyway I think there's no need for a restore if you just issued the wrong update query, I mean, transactions are here for this, it's just a matter of educating people
    Making PBXT Fully Durable
    +0 Vote Up -0Vote Down
    Until now PBXT has been ACId (with a lower-case d). This is soon to change as I have had some weeks to work on a fully durable version of the transactional engine (http://www.primebase.com/xt).

    My first concern in making PBXT fully durable was to what extent I would have to abandon the original "write-once" design. While there are a number of ways to implement durability, the only method used by databases (as far as I know) is the write-ahead log.

    The obvious advantage of this method is that all changes can be flushed at once. However, this requires that all data be written twice: once to the log and after that, to the database itself.

    My solution to this problem is a compromise, but I think it is a good one. In a nutshell: short records are written twice, and long records are written once. When it comes to





      [Read more...]
    MySQL Toolkit version 989 released
    +0 Vote Up -0Vote Down

    MySQL Parallel Dump can now dump a single table simultaneously into many files of a user-specifed size. This not only helps speed dumps, but it paves the way for much more efficient parallel restores. Read on for the details.

    Version 1.5.2 of the innotop MySQL monitor released
    +0 Vote Up -0Vote Down

    This release is part of the unstable 1.5 branch. Its features will ultimately go into the stable 1.6 branch. You can download it from the innotop-devel package.

    The major change is I've ripped out the W (Lock Waits) mode and enabled innotop to discover not only what a transaction is waiting for, but what it holds too. The new mode that replaces W is L (Locks). My last article goes into more detail on this.

    Showing entries 1 to 30 of 33 Next 3 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.