Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 17 7 Older Entries

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.

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

    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.
    Showing entries 1 to 10 of 17 7 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.