The history tells that a single RDBMS node cannot handle tons of traffics on web system which come from all over the world, no matter how the database is tuned. MySQL has implemented a master/slave style replication built-in for long time, and it has enabled web applications to handle traffics using a scale-out strategy. Having many slaves has been suitable for web sites where most of traffics are reads. Thus, MySQL's master/slave replication has been used on many web sites, and is being used still.

However, when a site grow large, amount of traffic may exceed the replication's capacity. In such a case, people may use memcached. It's an in-memory, very fast and well-known KVS, key value store, and its read throughput is far better than MySQL. It's been used as a cache for web applications to store 'hot' data with MySQL as a back-end storage, as it can reduce read requests to MySQL dramatically.

While 1:N replication …

It seems that the MySQL Conference selection committee didn’t seem to like the talk (and tutorial) I submitted about integrating MySQL Replication with the Pacemaker cluster stack, enabling full MySQL scale-out in an environment you previously knew only for its synchronous-replication High Availability features. But — fear not, my fellow HA geeks! — we are instead making the talk available for free, over the web, one week ahead of the conference.

In this 45-minute presentation, we will give an overview about

• the Pacemaker cluster stack and the Linux-HA project it evolved out of;
• the current state of MySQL integration in Pacemaker, and of course
• leveraging Pacemaker’s built-in clone infrastructure for MySQL Replication.

It’s pretty cool stuff that the Linux-HA …

To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge. How it worksThe script works by querying the information_schema.PARTITIONS system view to …

One of the exciting things[1] about working on a storage engine in Drizzle is that you get to manage your own metadata. When the database engine you’re writing the storage engine interface for has a pretty complete data dictionary (e.g. Embedded InnoDB) you could just directly use it. At some point I plan to do this for the embedded_innodb engine for Drizzle so that you could just point Drizzle at an existing Embedded InnoDB database and run SQL queries on it.

The Drizzle table message does have some things in it that aren’t in the InnoDB data dictionary though (e.g. table and column comments). We want to preserve these (and also things like there may be several data types in Drizzle that map to the same data type in InnoDB). Since the Embedded InnoDB API allows us to do things within the DDL transaction (such …

Using the configuration described elsewhere I ran a modified version of sysbench to compare PBXT, InnoDB and MyISAM using two update intensive workloads on a CPU-bound server. The workloads are simple but reproduce a bottleneck that is intermittent on some of our MySQL servers. Each workload is one statement run by many concurrent connections. In both cases the statements increment a column in one very popular row. One test uses an update statement like UPDATE sbtest set c=c+1 where id=1. The other test uses an insert statement like INSERT INTO sbtest values(1,0,'0','ayyyyyyyyy') ON DUPLICATE KEY UPDATE c = c + 1 and always executes the ON DUPLICATE KEY UPDATE clause.

Note that this is a very narrow benchmark (nothing but …

Last week a customer called me and reported that MySQL was 30 times slower than MS SQL server. Oooops. That's a lot. No way to argue or throw smoke bombs. 30 times slower! It was a standard installation of MySQL (typical install option) on plain Windows 7 and the same for MS SQL Server 2008. The test run was a batch of 30.000 INSERT commands in an SQL script. Runtime was 1 minute on MSSQL and 30 minutes on MySQL. Some tests later we found out that it was only bad on InnoDB. MyISAM was as fast as MSSQL. (I didn't care which one was a bit faster. I didn't care as long as InnoDB was 30 times slower) Finally we nailed the problem down to one parameter in MySQL: innodb_flush_log_at_trx_commit Each INSERT statement is a single transaction (autocommit mode). MySQL is configured very faithfully and ensures that each …

You have to love all the debating going on over NOSQL -vs- SQL don’t you? With my UC session on choosing the right data storage tools ( does this sound better then SQL-vs-NoSQL?) I have been trying to stay current with the mood of the community so i can make my talk more relevant. Today I was catching up on reading a few blogs posts and I thought I would pass along these two: Pro SQL and Pro NoSQL … these represent the two very different views on this subject. (Note I think there are misleading facts and figures in these that should be flushed out more, but they are a good sample of what I am talking about). …

This post covers the basic information of files that MySQL Server uses / creates for various tasks. Update: Various files in MySQL 8.0 Refer following updated article for latest information…

The post MySQL related file types and basic information first appeared on Change Is Inevitable.

I am giving a talk in a couple of weeks at the 2010 MySQL User Conference that will touch on use cases for NOSQL tools -vs- More relational tools, the talk is entitled “Choosing the Right Tools for the Job, SQL or NOSQL”. While this talk is NOT supposed to be a deep dive into the good, bad, and ugly of these solutions, rather a way to discuss potential use cases for various solutions and where they may make a lot of sense, being me I still felt a need to at least do some minor benchmarking of these solutions. The series of posts I wrote last year over on mysqlperformanceblog.com comparing Tokyo Tyrant to both MySQL and Memcached was fairly popular. In fact the initial set of benchmark scripts I used for that series actually has been put to good use since then testing out …

I have to admit I'm getting old, and I am now scaringly close to being a sad old git, and not only that, I'm a database guy also, and I have worked with SQL based relational databases for more than 20 years now.

So considering my age and heritage, I really should just dispose the NoSQL movement as something for those young kids with earrings and a baseball cap (and to add insult to injury, the cap is worn backwards) and that any serious database dude like myself, with my loads of experience (like the invaluable experience of having run Oracle on a Wang word-processor. Real valuable stuff I tell you). But no, you will not hear me do that. But also, you will not hear me say that NoSQL key-value stores will replace all SQL databases within 5 years (If I worked for an analyst and was paid dearly to say things like that, I might have, though. Any takers?).

My take is actually quite simple. The strength of the relational …