Showing entries 1 to 10 of 18
8 Older Entries »
Displaying posts with tag: acid (reset)
MySQL Master Replication Crash Safety Part #2: lagging slaves

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

On the consequences of sync_binlog != 1 (part #1)

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

Q: Does MySQL support ACID? A: Yes

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]
How to structure and design a relational database to support you data storage needs?

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 (ACID rules):

1. Normalization
2. Reliability
3. Consistency
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.

Steps:

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 …

[Read more]
Slides from PLMCE 2014 breakout session

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 more]
MySQL needs single master to check data integrity

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:

  1. MySQL requires an authoritative master to build slaves
[Read more]
On Eventual Consistency– Interview with Monty Widenius.

“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 [...]

Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels

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]
Understanding InnoDB transaction isolation levels

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.

Tuning InnoDB Configuration

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.

Showing entries 1 to 10 of 18
8 Older Entries »