If you’ve wondered whether a MySQL-based column database can hold its own against one of the big, leading row-based databases in a benchmark with terabytes of data and serious queries, well, now you at least have some information to consider. We recently commissioned one of the top data warehouse gurus out there – Bert Scalzo – to test InfiniDB against a leading row-based database to see how well we’d do. Bert’s an expert not only in data warehousing but also in Read More...

Finally settled for a wiki for sar-sql using Ubuntu's own wiki. Right now it only has my regular installation procedure. I will enhance and keep adding items as my time allows. I hope it will help to shape the future of the script.

Enjoy it with responsibility.

PS: I use the Kubuntu format because it is my desktop of choice.

MySQL Certification had a very busy 2009 with a great deal of change. Not all of the changes happened gracefully and now I am correcting problems with the fulfillment system. Regular readers will remember that MySQL Certification was integrated into the systems used by Sun Learning in mid 2009. Where things got shaky was in the processing of DBA 5.1 certificates.

Previously Ricky Ho and I oversaw data entry, database updates, and fulfillment. The deeply missed Ricky did the heavy lifting in the printing and mailing of certificates. The test data from the multiple choice exams was flowing properly into the new (for MySQL) system but not for the hands-on DBA 5.1 exam. I have been going through the certification records and manually entering the data for the 5.1 DBA certificate holders. It will take a week or so …

It may sound like a dramatic number, and that’s because it is. One thing that is enjoyable about working on beta applications is finding new solutions and better methods to improve the user experience. The original method for displaying the recent addition of overview analytics data in the beta version of Kontrollbase was to run [...]

A few years back I considered adding a feature to MySQL that would only execute queries with known fingerprints. Queries that cause MySQL to consistently crash are remarkably rare. I encounter one every other year. I prefer to avoid them. SQL statements can disrupt database quality of service without crashing the server. There are a few things that you can do to avoid these problems:

• Run the SQL on a test server before running it production
• Read the explanation of explain in MySQL documentation
• Run explain on the SQL. Alas, explain is not supported for INSERT/UPDATE/DELETE. You can convert the INSERT/UPDATE/DELETE statement to a query and …

Assuming this table: create table t1(i int, j int, primary key (i,j)), which form of predicates should you use?

• WHERE (i,j) in ((1,1), (3000,3000))
• WHERE (i=1 and j=1) or (i=3000 and j=3000)

Have you ever used the wrong form by mistake? The following tests were done with 5.0.44, 5.0.84 and 5.1.38. Oracle does the right thing for either form. Maybe the lazyweb can tell me whether Postgres does the right thing for both.

First create test data:

echo "drop table t1;"
echo "create table t1 (i int, j int, primary key (i,j)) engine=innodb;"

for i in $( seq 1 10000 ); do
  echo "insert into t1 values ($i, $i);"
done

This uses a full index scan and is probably slower than you expect. Subscribe to bug 31188 if you want that to change.

mysql> explain select count(*) from t1 where …

The O’Reilly MySQL Conference & Expo 2010 Call for Participation ends in just under 3 weeks. I am on the conference committee, and thus get to see and review all the conference proposals.

This blog post will briefly explain the how each part of the proposal is used, then have a list of what not to do in your conference proposal, and end with a checklist of questions to go over your proposal before submitting. Click here if you want to skip to the checklist.

The proposal has several parts.
Title: This is the title of your presentation. This shows up on the schedules …

Earlier Peter at Percona posted an interesting article about MySQL upgrades. As a database consultant, it’s not uncommon for me to have enterprise-level customers that still run MySQL 4.0 or 4.1  - for a number of reasons that I will enumerate later, this kind of migration can be tricky. I  just finished such a major upgrade so I will share my opinion on the subject.

The Methodology

If you are in a replicated environment you may want to migrate one of your slaves first as a prototype. Dumping and reloading is the recommended way because of many storage file format changes between 4.0 and 5.0 versions. For heavy databases I usually choose the parallel dump approach to save some precious time, either maatkit’s mk-parallel-dump or domas mituzas’ mydumper tools will do the trick.

Note that binary …

By default InnoDB uses REPEATABLE READ as its isolation level. So this is the isolation level used with innoDB by almost all MySQL users.

The default isolation level of Oracle is READ COMMITTED. READ COMMITTED is the mode widely used by Oracle users. This mode incurs less penalty on the server scalability by allowing to [...]

Following my recent blogg on the performance of MariaDB and PBXT on Windows showed that these new kids still has some work to do. Another question I was thinking testing was if the overhead of Transactions really matters, they do have a performance advantage also (like: You don't have to persist until the transaction is done), and so does row-level locking. And what about Linux? We could see that MariaDB on Windows was less than optimal, maybe this was a Windows problem. And finally, the Windows box I was testing on was a real old clunky box.

Hardware setup
So now, here are the results on a Linux box with a 4 Core AMD CPU and 4Gb of memory. Still not an exceptional box, but a decent one.

The first test on Linux
Again, all engines have the default settings, no tuning has been done at …