Facebook employees have three presentations at the MySQL Conference next week. The database performance team presents High Concurrency MySQL on Tuesday at 10:50am. The database operations team presents Database Operations at Scale on Wednesday at 2pm. The database engineering team presents High-throughput MySQL on Thursday at 10:50am

While it appears that the performance team swiped the title for their talk from the engineering team, the talks are not the same. All talks will have many stories and details.

I spent some time this weekend fixing up the Gearman MySQL UDFs (user defined functions) and fixed a few bugs in the Gearman Server. You can find links to the new releases on the Gearman website. The UDFs now use Monty Taylor’s pandora-build autoconf files instead of the old fragile autoconf setup that relied on pkgconfig.

If you are attending the MySQL Conference & Expo next week and want to learn more about Gearman, be sure to check out one of the three sessions Giuseppe Maxia and I are giving:

• Getting started with Gearman for MySQL
• …

MySQL has inet_aton and inet_ntoa functions for converting IPv4 addresses to an unsigned 32 bit integer value, and back into dotted decimal format. Since IPv6 is becoming more popular, I decided to create a couple functions for handling IPv6 addresses as well. In this case, we want to convert the friendly ::0:53D6:0001 style addresses to a 16 byte binary value that is easier to store. But we also want to extract them in a human readable format as well. To do that we now have inet_aton6() and inet_ntoa6().

Examples:

mysql> SELECT inet_aton6('0:0:0:0:0:0:af00:b001');
+------------------------------------------+
|      inet_aton6('0:0:0:0:0:0:af00:b001') |
+------------------------------------------+
| 000000000000000000000000AF00B001         |
+------------------------------------------+

mysql> SELECT …

I have been annoyed by the fact that I couldn’t easily print file count for all of the folders in certain directory.  Most of the time I just want to see what space each folder is using (du -hs *) but there are times when I need to know how many files are in each folder (checking cache folder, session folders etc).   So I whipped together a command line which does just that for me:

for i in `find -maxdepth 1 -type d`; do  echo -n $i " ";find $i|wc -l; done

I am sure there are many different ways to show file count for each folder in a directory and I am curious to see what people do so please do post comments with what you do.

Above command is pretty simple and can be expanded to do whatever you need.  For example, you can throw it into a bash script and be able to pass parameters.  For example:  count_files /home/  In this case your command line would …

In this post I’m going to describe how to write an authentication plugin for Drizzle. The plugin I’ll be demonstrating is a simple file-based plugin that takes a file containing a list of ‘username:password’ entries (one per line like a .htpasswd file for Apache). The first step is to setup a proper build environment and create a branch, see the Drizzle wiki page to get going. From here I’ll assume you have Drizzle checked out from bzr and are able to compile it.

Setup a development branch and plugin directory

Change to your shared-repository directory for Drizzle and run (assuming you branched ‘lp:drizzle’ to ‘drizzle’):

shell$ bzr branch drizzle auth-file
Branched 1432 revision(s).
shell$ cd auth-file

Next, we’ll want to create the plugin directory and create …

As you all are aware you can create a job is MS SQL using the SQL server Job agent and run the jobs in at any interval. Unlike MS SQL, MySQL does not have a Job agent to schedule the jobs and hence you need to create a CRON job in Linux or the MySQL 5.1.12 and above has been introduced with EVENT. You can write a event to run a SP at an interval.

We will see how to write a CRON job to run a Stored Procedure at an Interval. Follow the steps below

At the Prompt> CRON –e

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /root/Call_SP.sh 2>&1 >> /root/call_sp.log

Here I’m specifying an interval of 5 mins, hence the cron job will run the sp every 5 mins

Now create a shell script to call the sp from a database.

At the Prompt> vi Call_SP.sh

mysql -h ‘IPADDRESS’ -u root –p’password’ mydatabase -e "call …

I was telling someone how we roll changes to production at dealnews and they seemed really amazed by it. I have never really thought it was that impressive. It just made sense. It has kind of happened organically here over the years. Anyhow, I thought I would share.

Version Control

So, to start with, everything is in SVN. PHP code, Apache configs, DNS and even the scripts we use to deploy code. That is huge. We even have a misc directory in SVN where we put any useful scripts we use on our laptops for managing our code base. Everyone can share that way. Everyone can see what changed when. We can roll things back, branch if we need to, etc. I don't know how anyone lives with out. We did way back when. It was bad. People were stepping on each other. It was a mess. We quickly decided it did not work.

For our PHP code, we have trunk and a production branch. There are also a couple of …

If you are attending this year’s O’Reilly MySQL Conference & Expo, it is with great pleasure that I invite you to attend my presentation featuring Securich, the MySQL user administration and security plugin. It is a user friendly tool which will help make user management and general database security much more versatile and fun than ever before.

The session will be held at 10:50am on Wednesday 14th April 2010 at BallRoom A.

http://en.oreilly.com/mysql2010/public/schedule/detail/13351

So come on over and `grant all on *.*` no more :)

Hasta luego mez amis
Haben eine sichere fahrt

We noticed some performance issues on one of our Netapp Filers and during my investigation I noticed a few things that happen under the covers and I wanted to share them with you.

Netapp SnapMirror is an asynchronous method of mirroring files from one location to another. At my company we use it to replicate property images from one datacentre to another. I've always wondered what happens to the replica as SnapMirror is a block copy though obviously the destination may be different therefore it needs to do something 'smart' to correct the differences between the two filers.

The 'smart'ness is what is causing performance problems - so lets go through them.

Here is some of the output of a 'statit' on the SnapMirror replica. The filer is idle - its not doing any traffic and its not snapmirroring.

nas02*> statit -e

Hostname: nas02 ID: xxxx Memory: 16376 MB
NetApp …

MySQL database dumps can be automated via a CRON job, without providing a password, to achieve the same follow these steps

Edit you my.cnf and make the following entries

[mysqldump]

User=root

Password=secret

Save and exit the my.cnf file

Now write the following script to the CRON job

#! /bin/sh

mysqldump –u root –all-databases –routines

>/var/lib/mysqldump/mysql.dump

Automating the database dumps without using the password will make the maintenance easier.

-Death

…