A big shoutout to the openSUSE team: openSUSE 11.3 has been released! And with it comes a choice for the database users out there – you can now choose between MySQL or MariaDB.

I wrote in February 2010 that MariaDB was in the unstable repository for openSUSE, and was informed by Michal that it would be included a little while ago. The version that is shipping is …

I recently did a quick analysis of the distribution of writes to InnoDB's log files. On a high-traffic commodity MySQL server running Percona XtraDB for a gaming workload (mostly inserts to the "moves" table), I used strace to gather statistics about how the log file writes are distributed in terms of write size. InnoDB writes to the log files in multiples of 512 bytes. Mark Callaghan explained this and some of its performance implications here. How many big writes does InnoDB do, and how many small writes?

First, I found out the file descriptor numbers of the log files:

# lsof -p $(pidof mysqld) | grep ib_log
mysqld  29772 mysql    8uW  REG                8,2   268435456   7143989 /var/lib/mysql/ib_logfile0
mysqld  29772 mysql    9uW  REG                8,2   268435456   7143993 /var/lib/mysql/ib_logfile1

The …

A short post marks Pythian’s 195th edition of Log Buffer, a blog of blogs encapsulating what’s going on in the world of database administration.

Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note on why you think that others will want to read it too.

Now on to Log Buffer #195. Alex Gorbachev starts us off with his suggested readings and funnily enough, …

SugarCRM. Funding for EnterpriseDB and Morphlabs. Even more core. And more

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

Bittersweet
# OStatic asked whether SugarCRM has violated open source principles.

# Larry Augustin clarified SugarCRM’s approach to open source and openness.

# Savio Rodrigues advised anyone considering SugarCRM not to get hung-up on source code availability.

Funding round
# EnterpriseDB has reportedly raised $7.5m of a …

As many probably already know, PBXT is the first MySQL Storage Engine to use a log-based architecture. Log-based means that data that would normally first be written to the transaction log, and then to the database tables, is just written to the log, and the log becomes part of the database.

This result is that data is only written once, and is always written sequentially. The advantage when writing is obvious, but there is a down side (as always). The data is written to the disk in write order, which is seldom the order in which the data is retrieved. So this results in a lot of random reads to the disk when accessing the data later.

Placing the data logs on a Solid State Drive would solve this problem, because SSDs have no seek time. But the problem with this solution is that SSDs are still way to expense to base all your storage needs on such hardware.

The solution: an SSD-based 2nd Level Cache.
…

We usually do our English-language Linux Cluster Stack training sessions in the U.S., with training in Europe being primarily held in German. So if you live in Europe, speak English, and want to hone your high availability skills on your own continent, learning from the top notch experts, here is your rare chance:

November 9-12, were are doing our DRBD Total training in one of Europe’s finest cities, Berlin. Berlin is easily reached by air or rail, and our training location is directly next to historical Allied Checkpoint Charlie on Friedrichstraße. Training is exclusively in English, …

Later today I head off to PDX to attend OSCon and the Community Leadership Summit. Will you be there? If so, and you’re a GNOMEist, Ubuntu user or especially if you’re involved with MySQL or MariaDB, find me!

If you’re at OSCon and have any interest in databases, be sure to attend the MariaDB Bof. Yes, yes … we’ll have black vodka.

I hate losing useful SQL queries, so I tend to save them. Ever so often, I stumble across a file of queries from some work I was doing months ago. The following two queries are just such a case. I was analyzing some call data for a call center that uses Asterisk. I set up a temporary database for this, so I can't even run them again. Still, it's nice to save them away for a rainy day.

Call Center Call Volume by Day of Week and Hour

SET @tz = 'EST5EDT';
select 
  DAYNAME(CONVERT_TZ(start, 'GMT', @tz)) AS "Weekday",
  HOUR(CONVERT_TZ(start, 'GMT', @tz)) AS "Hour",
  COUNT(id), 
  SUM(duration), 
  SUM(billsec), 
  AVG(duration), AVG(billsec) 
  FROM cdr 
    WHERE dcontext IN ('Queue1', 'Queue2', 'Queue3', 'Queue4')
      AND duration > 60
    GROUP BY Weekday, Hour
    ORDER BY WEEKDAY(CONVERT_TZ(start, 'GMT', @tz)), Hour 
;

I was trying to figure out …

Background Knowledge

The Search Plugin for TaskFreak! created by DaDaemon and xdu v0.0.1 (March 26, 2007) was designed to create a simple, quick search capability of the tasks title and description. As well it only searched through he current task view (tasks visible at the time) and tasks that are not completed. For some this was not what was desired and would rather have the Search Plugin search through all tasks weather completed or not and as well search through the comments of tasks along with the title and description. I’ll show you how this is done using Searcher, bchristie and davidlmansfield instructions posted on the TaskFreak! Forums.

Solution – Add the Ability to Search All Tasks

Edit the “index.php” located in …

What I'm up to lately (giving love to some projects):

* Fixing bugs in DBD::mysql, just released 4.015, 4.016, and next 4.017. I had a patch sent yesterday from a user/developer that I want to get out there
* Memcached::libmemcached - 0.4201 version - now using latest libmemcached 0.42. This is the only Perl client that supports binary protocol!

patg@patg-desktop:~/code_dev/perl-libmemcached$ PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/12-set-get-binary.t
t/12-set-get-binary....ok                                                   
All tests successful.
Files=1, …