If you were ever wondering what other people check out on this site, here are the most popular articles by pageviews for the last 12 months. Seems most people like the LVM snapshots article, articles about running multiple MySQL instances and the various benchmark articles. mysql backups using lvm snapshots oracle 11g on ec2 using silent install mysql multi master master replication

I'm pleased to announce the release of Spider storage engine version 0.17.(RC)
Development status is RC from this version.
http://spiderformysql.com/

The main changes in this version are following.
- Add table parameter "use_table_charset".
- Add server parameter "spider_use_table_charset" and "spider_local_lock_table".
  "use_table_charset" and "spider_use_table_charset" are used for choosing the communication charset (UTF8 or table charset) at connections from Spider to remote server. Basically, you don't need changing this option but you can change this option for performance improvement if the SQL has very longer table and column name string than column data string.
  "spider_local_lock_table" is used for suppressing sending "lock table" statement to remote server for locking table at local server only. This option …

Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t.

When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this:

SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772);

The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

This resulted in an outage and revenue loss to the company.

Daniel and I (mostly Daniel) continue to improve mk-upgrade to make it easy and inexpensive to find these kinds of scenarios before they bite you. Don’t get caught with your pants down — next time you make …

Inspired by NILFS: A File System to Make SSDs Scream and some customers asked if they should try NILFS on their SSD disks I decided to run quick tests to see how it performs.

Installation on our Ubuntu 8.10 with SSD disk (Intel X25-E, 32GB) was pretty plain and I got partition with NILFS without problem. After that I run script for sysbench fileio:

PLAIN TEXT CODE:

1. for size in 256M 16G; do
2.    for mode in seqwr seqrd rndrd rndwr rndrw; do
3.       ./sysbench --test=fileio --file-num=1 --file-total-size=$size prepare
4.       for threads in 1 4 8; do
5.          echo PARAMS $size $mode $threads> sysbench-size-$size-mode-$mode-threads-$threads
…

Like many other languages, PL/SQL has its own "dot notation". If we assume that most people can intuit or easily look up things like the syntax for '''IF/THEN/ELSIF''', that means that first-timer users might quickly run into dots and want to understand their significance.The authoritative docs on the dots is in the Oracle Database 11g PL/SQL Language Reference, in particular Appendix B, How PL/

I have been meaning to, for months, get working on the FederatedX storage engine. There are a lot of great features it has -- such as better support for transactions as well as some architectural changes, thanks to the work of Antony Curtis. It now is designed so that the connection is abstracted into a federated_io_ class. This allows you to be able to subclass different connection schemes/drivers. For instance, I have the code for Federated ODBC that I need to take the code from and port to a federated_io_odbc class.

FederatedX has been pulled into an older MariaDB repository (thanks Antony!) that I need to merge with the latest, as well as figure out how to get the test suite to load the plugable storage engine. I've tried to add 'INSTALL PLUGIN federated SONAME 'ha_federatedx.so'' to the test, but the server expects ha_federated.so to be in a particular file location. That needs to be solved. I looked at PBXT's test suite but …

So during preparation of XtraDB template for EC2 I wanted to understand what IO characteristics we can expect from EBS volume ( I am speaking about single volume, not RAID as in my previous post). Yasufumi did some benchmarks and pointed me on interesting behavior, there seems several level of caching on EBS volume.

Let me show you. I did sysbench random read IO benchmark on files with size from 256M to 5GB with step 256M. And, as Morgan pointed me, I previously made first write, to avoid first-write penalty:

dd if=/dev/zero of=/dev/sdk bs=1M

for reference script is:

PLAIN TEXT CODE:

1. #!/bin/sh
2. set -u
3. set -x
…

Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t. When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this: SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772); The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

I put together the following diagram to explain what the origin of the current MySQL forks and deltas looks like:

But in the distributed revision control world we live in, it's never really that simple. Here are some other notes:

• XtraDB is an InnoDB fork and "mostly" only a storage engine. It has a future in MariaDB and Drizzle, but may not make it into MySQL (about to be owned by Oracle, who also owns InnoDB).
• OurDelta collects more patches than just the Percona patches, and enables the PBXT storage engine. There's also a repository of OurDelta for 5.1 - binaries just aren't out yet.
• Like XtraDB, PBXT has a future in Drizzle, MariaDB and *maybe* also MySQL (Oracle may be more friendly towards PBXT than XtraDB, but Oracle is not known to be friendly so who knows!). I left PBXT absent from the diagram for not having much of a lineage with previous MySQL releases (flames welcome if you disagree).
…

Welcome to the 157th edition of Log Buffer, the weekly, cross-platform review of database blogs.

SQL Server

We start with Michelle Ufford, the SQL Fool, who gives us the poor (wo)man’s graph, a fast and ingenious way to create handsome text-based graphs.

What is the importance of running regular consistency checks? Paul S. Randal returns with some survey results and analysis. He writes, “The results are actually surprising – I didn’t expect so many people to be running consistency …