Typical MySQL environment involves one Master receiving writes and multiple slaves to scale the reads. The “slave” term has been used in MySQL because the Slave servers have to perform every task in copying from the Master binlog, then updating their relay logs and finally committing to the Slave databases. The Master plays no role in replication here other than storing the replication events in the binlog.
With this kind of Master- Slave set up, there are several limitations-
-       Slave lag -       Stale or old data -       Data loss -       Manual failover which is error-prone and time consuming
In SchoonerSQL, there is no concept of “Slaves” inside synchronous cluster. We refer to it as "Read Masters" because of our synchronous approach and different replication architecture. It is …

Everyone who started using MySQL before 5.5 started off with MyIsam. It was the default storage engine and you had to go out of your way to use anything else. It was a good looking database, rugged, simple and fast in many respects. But crash recovery was not a MyIsam forte. Neither was locking or transactions. Speed was pretty good on reads. But many database snobs pointed to the short comings of MyIsam and claimed it was proof that MySQL was a ‘toy database’.

But InnoDB arrived with transactions, row level locking, and better crash recovery than MyIsam. Many detractors of MySQL now started talking positively about it. In some cases the performance for some bench marks was behind MyIsam. Notice the use of ‘was‘. Now the InnoDB and server teams report to the same management and are going through all the code carefully to achieve better performance. Now the REDO log in its own table space, buffer pools can be …

In my last post, we talked about the read/write tradeoff of indexing data structures, and some ways that people augment B-trees in order to get better write performance. We also talked about the significant drawbacks of each method, and I promised to show some more fundamental approaches.

We had two “workload-based” techniques: inserting in sequential order, and using fewer indexes, and two “data structure-based” techniques: a write buffer, and OLAP. Remember, the most common thing people do when faced with an insertion bottleneck is to use fewer indexes, and this kills query performance. So keep in mind that all our work on write-optimization is really work for read-optimization, in that write-optimized indexes are cheap enough that you can keep all the ones you need to get good read performance.

…

For those with an interest in MySQL, this week at Oracle OpenWorld has gotten off to a great start.  Demonstrating how Oracle drives MySQL innovation Tomas' "State of the Dolphin" keynote on Monday gave a great overview of the new MySQL products that have recently been delivered:

• MySQL Installer for Windows
• New MySQL Security and Scalability features 
• New MySQL High Availability features 
• New early access features in the MySQL 5.6.3 Development Milestone Release ("DMR") 
• New early access features in the …

For those with an interest in MySQL, this week at Oracle OpenWorld has gotten off to a great start.  Demonstrating how Oracle drives MySQL innovation Tomas' "State of the Dolphin" keynote on Monday gave a great overview of the new MySQL products that have recently been delivered:

• MySQL Installer for Windows
• New MySQL Security and Scalability features 
• New MySQL High Availability features 
• New early access features in the MySQL 5.6.3 Development Milestone Release ("DMR") 
• New early access features in the …

Note: this article was originally published on http://blogs.innodb.com on Sept 28, 2011 by Calvin Sun.

Sunny and I will be presenting at the Oracle OpenWorld next week:

• Introduction to InnoDB, MySQL’s Default Storage Engine,  10/04/11 Tuesday 01:15 PM,   Marriott Marquis – Golden Gate C3,     Calvin Sun
• InnoDB Performance Tuning,  10/04/11 Tuesday  03:30 PM,   Marriott Marquis – Golden Gate C2,   Sunny Bains

The first session is for beginners, who are new to InnoDB and MySQL. The second session will cover many new performance features in MySQL 5.5 and 5.6, and share some tuning tips to maximize MySQL performance.

What to learn more about MySQL? There will be something for …

MySQL isn’t too concerned about table handler memory usage – it will allocate row size buffer thrice per each table invocation. There’s a few year old bug discussing UNION memory usage – for each mention in an union one can allocate nearly 200k of unaccounted memory – so a megabyte sized query can consume 7GB of RAM already.

Partitioning though adds even more pain here – it will allocate those three buffers per each partition, so opening a table with 1000 partitions looks like this on memory profile:

Click to enlarge, and you will see 191MB sent to execute a simple single-row fetching query from a table (I filed a bug on this).

There’re multiple real …

Some indexing structures are write optimized in that they are better than B-trees at ingesting data. Other indexing structures are read optimized in that they are better than B-trees at query time. Even within B-trees, there is a tradeoff between write performance and read performance. For example, non-clustering B-trees (such as MyISAM) are typically faster at indexing than clustering B-trees (such as InnoDB), but are then slower at queries.

This post is the first of two about how to understand write optimization, what it means for overall performance, and what the difference is between different write-optimized indexing schemes. We’ll be talking about how to deal with workloads that don’t fit in memory—in particular, if we had our data in B-trees, only the internal nodes (perhaps not even all of them) would fit in memory.

As I’ve already said, there is a tradeoff between write and read …

I’m creating a library of benchmarks and test suites that will run as part of a Continuous Integration (CI) process here at Tokutek. My goal is to regularly measure several aspects of our storage engine over time: performance, correctness, memory/CPU/disk utilization, etc. I’ll also be running tests against InnoDB and other databases for comparative analysis. I plan on posting a series of blog entries as my CI framework evolves, for now I have the results of my first benchmark.

Compression is an always-on feature of TokuDB. There are no server/session variables to enable compression or change the compression level (one goal of TokuDB is to have as few tuning parameters as possible). My compression benchmark uses iiBench to measure the insert performance and compression achieved by …

We’re pleased to let you know that the 2nd episode of our “Meet The MySQL Experts” podcast series, where Oracle engineers share their expertise, is now available.

Wei-Chen Chiu interviews InnoDB team member Inaam Rana who comes back on the new InnoDB features and improvements delivered in MySQL 5.5 and in the first development milestone release of MySQL 5.6. Inaam also talks to us about the InnoDB features available in labs.mysql.com.

Enjoy!