UPDATE: Here’s the recording, enjoy!

Hello everybody!

#DBHangOps coming at you this week, Wednesday 1/30/12 at 12:00pm PST. Be sure to check out this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

This week’s topics were:
* bug fixes in recent versions
* Plugins
** authorization plugins (e.g. LDAP)
** audit plugin API — http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html
*** state transitions of records in MySQL with a plugin?
* InnoDB Status variables from twitter — https://github.com/twitter/mysql/wiki/InnoDB-Status-Variables
* Table alters/schema changes
** Twitter patch for non-blocking alter table (throws a different error than “lock_wait_timeout”) — …

This article was initially planned to follow the previous posts about RW (Read+Write) workloads on MySQL & Linux (part#1, part#2)... But, when I've started to run real MySQL/InnoDB IO-bound tests on the Fusion-io card, I've quickly realized that I'm hitting so hot internal contentions within InnoDB code, that I'm simply unable to use storage capacity on its full power.. So, I've moved to another server (having SSD only), and then continued with it (as to explain RW limits it'll be pretty enough). Also, curiously, on this server XFS performance on writes is way better than EXT4 (while on the the previous server XFS got some unexpected problems, seems to be known bugs)..

So far, for the …

yum

It’s often pain to guess package names when you need to install stuff on, lets say, CentOS. So there is a list, although maybe not full, of what I needed to get another VM build and run MariaDB server and to execute at least some tests on it (all done via yum install):

cmake
gcc
ncurses-devel
bison
g++
gcc-c++
aclocal
automake
libtool
perl-DBD-MySQL
gdb
libaio-devel
openssl-devel

Same in one line, for lazy me:
sudo yum install cmake gcc ncurses-devel bison g++ gcc-c++ perl-DBD-MySQL gdb libaio-devel openssl-devel

To install bzr (if it’s not in the official repo):

su -c ‘rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-2.noarch.rpm’
(check the architecture)

and then can use yum install
bzr

Another story (taken …

well, I incremented max_allowed_packet from 16M to 512M
anyway, I got the same error on the next clean import.
so decided to find a workaround.
so, how to get the data separated from the ddl statements:

# get the tables names into the insert statement, its better to have that in file for future usage
sed '/^INSERT INTO/!d;s/ VALUES.*$//' jiradb.20130118.sql | sort | uniq > tablas.como.nombres.txt

then how to get the data separated:

root@jiragg:[Fri Jan 18 15:26:33]:[/usr/local/BACKUP]$ cat make.inserts.sh
#!/bin/sh
# trim function thank to http://stackoverflow.com/questions/369758/how-to-trim-whitespace-from-bash-variable
# and http://codesnippets.joyent.com/posts/show/1816
trim() {
    local var=$1
    var="${var#"${var%%[![:space:]]*}"}"   # remove leading whitespace characters
    var="${var%"${var##*[![:space:]]}"}"   # remove trailing whitespace characters
    echo -n "$var"
}

while read tabname
do …

We assume the new disk is /dev/sdb1 formatted as ext3
and it will be mounted as /data

# 0. make sure there is no mysqlm mysql data directory :
yum remove mysql mysql-server -y 
test -d /data/mysql/ && rm -rf /data/mysql/
test -d /var/lib/mysql/ && rm -rf /var/lib/mysql/

# 1. install Mysql
yum install mysql mysql-server -y

# 2. check the mysql  status
service mysqld status

# 3. start the mysqld if not started
service mysqld start

# 4. check the mysql status again
service mysqld status

# 5. stop mysqld in case its started, and check thre is n mysql process:
service mysqld stop
ps axu | grep mysql

# 6. make sure the /data partition is added to the /etc/fstab. If not add it:
test  `cat  /etc/fstab | grep /data | wc -l ` -eq 0  && echo "/dev/sdb1 /data ext3  defaults 1 1" >>  /etc/fstab

# 7. make sure the /data partition is mounted, 
test  `cat /proc/mounts  | grep /data | grep -v grep | wc -l` -eq 0 && mount /data

# 8. disable selinx …

Update: Recording!

Finally, the end of year madness is over and we’re in a fresh new year. That must mean it’s time to get back into #DBHangops! Tune in on Wednesday, 1/16/13 at 5:00pm PST to get in on the fun and share your experiences.

Check back on this blog post tomorrow or check this twitter search to grab the link to join the Google Hangout.

Topics for this weeks hangout include:
* triggers: maintenance, problems, etc.
* Oracle MySQL utilities (similar to Percona toolkit)
** Slideshow of MySQL utilities
* Database caching and cache expiration
* InnoDB log file size
** …

Just a quick note to say that the site has been updated to a new theme which is based on the super awesome Twitter Bootstrap UI framework. To make life easier, since this site is also using WordPress at the core, I’ve made use of the WordPress Bootstrap plugin which allows for very simple integration. However, that wasn’t enough because the Bootstrap plugin comes with rather basic and boring generic styles; so I added the plugin for Google Font support and then modified the CSS accordingly.

You will also notice that the site is undergoing some reorganization of categories and content tags. This should help clean up search results as well as general information sorting. I’ve removed the sidebar widget for category listings in favor …

In my ongoing efforts to migrate my fun side projects and coding experiments from SVN to Git I’ve come across some of my favorite Python based apps – which are all available in their respective repos on BitBucket, as follows:

IRC Bot Commander

• What it does: it’s an IRC bot that takes commands and does your bidding on whichever remote server the bot is installed on.
• How it does it: the bot runs on whatever server you install it on, then it connects to the IRC server and channel you configured it to connect to and it waits for you to give it commands, then it execs the commands and returns the output to your IRC chat window.

MacroBase – MySQL Analytics

…

Unfortunately jQuery doesn’t come with default form validation to check for ip-addresses or subnet masking. So without a long winded explanation here’s the code. Just include this as a separate JS file like the rest of your page’s JS.

// 'ipv4': IPv4 Address Validator
$.validator.addMethod('ipv4', function(value) {
    var ipv4 = /^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$/;    
    return value.match(ipv4);
}, 'Invalid IPv4 address');

// 'netmask': IPv4 Netmask Validator
$.validator.addMethod('netmask', function(value) {
    var mask = /^[1-2]{1}[2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-9]{1,3}$/;    
    return value.match(mask);
}, 'Invalid IPv4 netmask');

You can use it like this.

$("#myform_here").validate({
    rules:{
        ipaddress:{
            required:true,
            ipv4:true
        },
        netmask:{ …

As a follow up to the previous post about logstash, here are a couple of related init scripts for anyone implementing the OpenSource Log Analytics setup that is explained over at divisionbyzero. These have been tested on CentOS 6.3 and are based on generic RC functions from Redhat so they will work with Redhat, CentOS, Fedora, Scientific Linux, etc.

• Init script for ElasticSearch: located in the repo by clicking here – servicewrapper required
• Init script for Kibana: …