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 17

Displaying posts with tag: locking (reset)

InnoDB scalability issues due to tables without primary keys
+0 Vote Up -0Vote Down

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight:

Scalability issues due to tables without primary keys

This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various places. I will only mention a few of them:

  • During operations such as opening and closing table handles, or
  • When accessing I_S tables, or
  • During undo of a freshly inserted row, or
  • During other data dictionary modification operations such as CREATE TABLE, or
  • Within the “Persistent Stats” subsystem,
  [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.
    Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
    +6 Vote Up -0Vote Down

    As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels.  There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.

    Extra locking (not gap locking)
    It is important to remember that InnoDB actually locks index entries, not rows. During the execution of a statement InnoDB must lock every entry in the index that it traverses to find the rows it is modifying. It must do this to prevent deadlocks and maintain the isolation level.

    If you run an UPDATE that is not well indexed you will lock many rows:

    update employees set store_id = 0 where store_id = 1;
    ---TRANSACTION 1EAB04, ACTIVE 7 sec
    633 lock struct(s), heap


      [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.
    InnoDB locking makes me sad
    +5 Vote Up -0Vote Down

    Vadim and others have pointed at the index->lock problems before, but I think they didn’t good job enough at pointing out how bad it can get (the actual problematic was hidden somewhere as some odd edge case). What ‘index lock’ means is generally the fact that InnoDB has table-level locking which will kill performance on big tables miserably.

    InnoDB is a huge pie of layers, that have various locking behaviors, and are layered on top of each other, and are structured nicely as subdirectories in your innodb_plugin directory. Low level storage interfaces are done via os/ routines, then on top of that there’s some file space manager, fsp/, which allocates space for btr/ to live in, where individual page/ entities live, with multiple row/ pieces.

      [Read more...]
    A few notes on locking in MySQL
    +0 Vote Up -0Vote Down
    This is another article in a series of articles titled "A few notes ..." in which I will be posting some important information about locking concepts, different types of locks and what locks table engines support. Just like the previous article, the purpose of this article is to highlight important aspects that you should have in the back of your mind when developing applications.
    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.
    The Casual MySQL DBA – Operational Basics
    +2 Vote Up -0Vote Down

    So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

  • Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
  • Can you connect locally via cli?
  • What’s in the MySQL error log?
  • What are current MySQL threads doing? Locked? long running? how many? idle sources?
  • Can you connect remotely via cli?
  • Verify free diskspace?
  • Verify system physical resources?
  • If this is a slave, is MySQL replication running? Is it up to date?
  • What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
  • What is the current InnoDB state and load? (based on if your using InnoDB)
  • After you do this manually more then

      [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.
    Common MySQL Scalability Mistakes
    +3 Vote Up -3Vote Down

    This week I was one of the presenters at the first Surge Scalability Conference in Baltimore. An event that focused not just on one technology but on what essential tools, technologies and practices system architects need to know about for successfully scaling web applications.

    While MySQL is an important product in many environments, it is only one component for a successful technology stack and for many organizations is one of several products that manage your data.

    My presentation was on the common MySQL scalability mistakes and how to avoid them. This is a problem/solution approach and is a companion talk with

      [Read more...]
    Understanding Innodb Transaction Isolation
    +6 Vote Up -0Vote Down

    The MySQL Innodb storage engine provides ACID compliance, the ‘I’ being isolation. There are four states defined in MySQL with the tx_isolation system variable, READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ and SERIALIZABLE.

    Generally MySQL installations do not modify the default value of tx_isolation = REPEATABLE-READ, however I have seen with a number of clients the default value has been changed to READ-COMMITTED. When I attempt to identify the reason why, I have always received the same reason. Oracle uses a default transaction isolation of READ-COMMITTED. See

      [Read more...]
    Understanding InnoDB MVCC
    +0 Vote Up -0Vote Down

    Multi versioning concurrency control (MVCC) is a database design theory that enables relational databases to support concurrency, or more simply multiple user access to common data in your database.

    In MySQL (http://www.mysql.com) the InnoDB storage engine provides MVCC, row-level locking, full ACID compliance as well as other features.

    In my understanding of database theory, access to modify independent sections of unique data (e.g. UPDATE) under MVCC should fully support concurrency. I have however experienced a level of exclusive locking under Innodb.

    I wanted to clearly document this situation so I could then seek the advice of the guru’s in InnoDB Internals such as Mark Callaghan, Percona and the Innodb development team for example.

      [Read more...]
    InnoDB Conference Presentations Now Online
    Employee +2 Vote Up -0Vote Down

    Well, it took us a little while (we’ve been busy !), but we’ve now posted our presentations on InnoDB from the MySQL Conference and Expo 2009. You can download these presentations by Heikki Tuuri, Ken Jacobs and Calvin Sun from the InnoDB website, as follows:

      [Read more...]
    MySQL University: Low-Level Locking in mysqld and InnoDB
    Employee +0 Vote Up -0Vote Down

    Happy New Year!

    MySQL University sessions are starting again after the winter break. This Thursday, we're beginning with Tim Cook's presentation on low-level locking in mysqld and InnoDB – the good, the bad, and the ugly. Tim works in the Performance and Applications Engineering department at Sun Microsystems. Expect to get some deep insights into the inner workings of the MySQL Server and InnoDB.

    Tim's based in California, so note that his session will start rather late for Europeans.

    For MySQL University sessions, point your browser

      [Read more...]
    MySQL Locks (and a bit of the Query Cache)
    Employee +0 Vote Up -0Vote Down

    MySQL uses locks for concurrency control. Whenever a client/thread acquires a lock, it will have exclusive access to that table or row (depending on the granularity of the lock). Other clients however, will be prevented from writing and possibly reading to/from the locked resource. The two main existing locks are:

    READ LOCK – A read lock will allow the other clients to read from the locked resource but not write to it.
    WRITE LOCK – a write lock will prevent the other clients from reading or writing to the locked resource.

    Also, different storage engines have different lock granularity. For example, MyISAM will lock the whole table while InnoDB will only lock the rows it needs.

    Let’s try this out using a MyISAM table. We open two concurrent sessions and in the first lock the City table from the


      [Read more...]
    How to notify event listeners in MySQL
    +0 Vote Up -0Vote Down

    A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL's GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.

    This post was prompted by a message to the MySQL general emailing list some time ago, but I'm finally getting around to actually testing the theoretical solution I mentioned then. I can never just think my way through anything that involves locking and waiting... I have to test it.

    Showing entries 1 to 17

    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.