As you may know, Kristian Nielsen made a fix for the Group Commit Problem which we many times wrote about. The fix came into MariaDB 5.3 and Mark Callaghan tested it recently . We ported this patch to Percona Server (it is not in the main branch yet), and here are the results of my testing of the new Group Commit in Percona Server 5.1.

As background information, the problem appears when you have strict durability and recover-ability requirements, that is innodb_flush_log_at_trx_commit=1, sync_binlog=1 and you do not have storage that provides fast syncs (i.e. you do not have a battery-backed cache on your RAID card). This scenario may also appear when being on battery and your RAID card dies, automatically switching from write-back to write-through cache mode.

We …

Innovation Nation & GOSCON 2011
GOSCON returns to Washington D.C. on August 23, 2011
The list of speakers is available here: http://goscon.org/speakers.
I will be at the conference this year in support of MySQL and I am eagerly awaiting to meet everyone. So please stop by and say hello ! 

Probably a more rarely used dialog for most users: Import > "Insert files into BLOB/TEXT fields". However, very useful in a few cases when you have file contents in database table, and you need to import files from your harddisk. Now even more powerful as it supports distinction between binary and text files and shows all columns in a better overview:

I spent my day doing updates to the automysqlbackup script.  Here is some of what I’ve added over the last year.

The bug number fixes are from SourceForge.  https://sourceforge.net/tracker/?atid=628964&group_id=101066&func=browse

# 2.5.5 MTG – (2011-07-21)
#    – Bug – Typo Ureadable Unreadable config file line 424 – ID: 3316825
#    – Bug – Change “#!/bin/bash” to “#!/usr/bin/env bash” – ID: 3292873
#    – Bug – problem with excludes – ID: 3169562
#    – Bug – Total disk space on symbolic links – ID: 3064547
#    – Added DEBUG option to only print the commands that will be executed.
#    – Bug – WHICH command didn’t work if there …

MySQL is a powerful open-source database.  With more and more database driven applications, people have been pushing MySQL to its limits.  Here are 101 tips for tuning and optimizing your MySQL install.  Some tips are specific to the environment they are installed on, but the concepts are universal.   I have divided them up into several categories to help you with getting the most out of MySQL:

MySQL Server Hardware and OS Tuning:

1. Have enough physical memory to load your entire InnoDB file into memory – InnoDB is much faster when the file can be accessed in memory rather than from disk.
2. Avoid Swap at all costs …

I’ve used the OPTIMIZE command for a long time, in particular for disk space problems.

But I recently had a problem with this command and an auto-incremented table, you can reproduce this problem like that :

Create a table with an auto-incremented column :

mysql> use test
Database changed
mysql> create table test_optimize (id int(8) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (1.09 sec)

mysql> insert into test_optimize values (1),(2),(3),(4);
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show table status like ‘test_optimize’\G
*************************** 1. row ***************************
…

For MyISAM one of the most important variables is the Key Buffer.  The Key Buffer is sometimes called the Key Cache. It's used as a buffer for the indices of MyISAM tables. There is some overhead in the buffer depending on the configured key block size.

The official way to calculate the key buffer usage as documented in the MySQL Reference manual:

1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

 This will return the factor, so you have to multiply it with 100 to get the percentage. The Key_blocks_unused is used instead of the more obvious Key_blocks_used. This is due to the fact that Key_blocks_used is the maximum number of key blocks ever used. It will not return to 0 after a FLUSH TABLES.

This calculation does not take the overhead in account. The key buffer efficiency can be calculated if the key buffer is empty or (has been) completely full.

If …

As the song goes, "it's been a while".

I wanted to leave a note here for anyone that hasn't read it on other more active blogs but might stumble upon this in a freak search engine accident. There's a couple of notable dates for the UK MySQL scene over the next few months.

Firstly the `MySQL London Meetup` is hosting on the 18th of July 2011 and they have some MySQL rockstars talking and socialising. I'm looking forward to hearing both Zaitzev's Scaling MySQL talk and Andrew Hutchings' (@linuxjedi) words on Drizzle. There's loads of other good material too, so check out the event page.

Later in the year Peter returns to London for Percona Live in October. This is a one day intensive summit packed to the brim with expert talks, advice and all the tricks you can …

I’ve used the OPTIMIZE command for a long time, in particular for disk space problems.

But I recently had a problem with this command and an auto-incremented table, you can reproduce this problem like that :

Create a table with an auto-incremented column :

mysql> use test
Database changed
mysql> create table test_optimize (id int(8) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (1.09 sec)

mysql> insert into test_optimize values (1),(2),(3),(4);
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show table status like ‘test_optimize’\G
*************************** 1. row ***************************
Name: …

Cassandra Internals by Gary Dusbabek of Rackspace Questions? What's the best way to access data if you're running a program in the same JVM as Cassandra? -- will talk about it during StorageProxy section of the talk Performance characteristics of using MMAP vs. not using it? - won't cover it. When does repair happen?  will talk about it during repair part of the talk How do Snitch and replication strategy work together? -- will discuss though there is no slide on it. Ring services - services that go throughout the ring.  These are in a class called StorageService. Storage services - things that happen locally.  In a class called StorageProxy. The cassandra executable in /bin executes cassandra.in.sh, which does: - sets $CLASSPATH - looks for the .jar files - sets $CASSANDRA_CONF (mandatory, where yaml file lives)

- sets $CASSANDRA_HOME (not mandatory) then it looks for another file [didn't get what …