This is Part #2 of the MySQL Master Replication Crash Safety series. In the previous post, we explored the consequence of reducing durability on masters with slaves using legacy file+position replication. The consequences are data inconsistencies with a clear warning sign: the slaves stop replicating and report an error. In this post, we extend our understanding of the impact of running a
8 Older Entries »
A well-known performance booster in MySQL is to set sync_binlog to 0. However, this configuration alone comes with serious consequences on consistency and on durability (the C and D of ACID); I explore those in this series. In this post, I give some background on the sync_binlog parameter and I explain part of the problem with setting it to 0 (or to a value different from 1). The other
I was recently asked this question by an experienced academic at the NY Oracle Users Group event I presented at.
Does MySQL support ACID? (ACID is a set of properties essential for a relational database to perform transactions, i.e. a discrete unit of work.)
Yes, MySQL fully supports ACID, that is Atomicity, Consistency, Isolation and Duration. (*)
This is contrary to the first Google response found searching this question which for reference states “The standard table handler for MySQL is not ACID compliant because it doesn’t support consistency, isolation, or durability”.
The question is however not a simple Yes/No because it depends on timing …[Read more]
Well, every now and then, when we began to start a new project or
app, which has some data storage requirement, we have a deep
intriguing thought as to how best represent the data structure so
as to support a variety of needs including but not limited to
4. And many others
Below, I provide a set of steps which you can follow to arrive at a data model that correctly suites your requirements.
1. Identify the project or app requirements / specifications and business rules which tell you what your app will be able to do when it is ready.
2. From these business rules, identify possible objects for each business rule and mark them in a paper using rectangular sections like authors, posts etc.
3. Once you have recognized the …
As many of you already know, PLMCE is an annual MySQL
community conference and Expo organized by Percona in the month of April
(usually). It is a great conference, not only to meet new and eminent people in
MySQL and related database fields, but also to attend interesting talks, and
also to give some.
This year I spoke about synchronous replication at a higher
level. The talk was
titled “ACIDic Clusters: Review of current relational databases with synchronous replication”. Having previously given talks with boring titles (but interesting content), this time I decided to go with an interesting title, and it seemed to fit well with topic being discussed.
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:[Read more]
“For analytical things, eventual consistency is ok (as long as you can know after you have run them if they were consistent or not). For real world involving money or resources it’s not necessarily the case.” — Michael “Monty” Widenius. In a recent interview, I asked Justin Sheehy, Chief Technology Officer at Basho Technologies, maker [...]
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 size 96696, 218786 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 localhost root …[Read more]
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.
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.
8 Older Entries »