MySQL logfiles analysis

September 4th, 2007

Looking for a way to check your global MySQL performance, to identify missing indexes and other possible optimization points?

Today’s featured link should solve all your issues: mysqlsla.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]

The MS SQL Server transaction log

August 7th, 2007

The transaction log grows, grows, grows and quite often fills up.

I know two ways of shrinking it:

  • With backup:

backup log netmeter(db_name) to disk=’specify the filename’

  • Without backup:

backup log netmeter with truncate_only

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]

WORM - aka how to write things in stone

August 1st, 2007

WORM stands for Write Once, Read Many - it’s usually used for archival storage systems, and it’s actually sometimes a legal requirement (typically in SOX).

At least three solutions can be commonly found on the market:

  • Centera, by EMC, is a WORM solution featuring a retention policy
  • Network Appliance (NetApp) has a software solution which runs on their systems called SnapLock
  • HP sells WORM Tape Drives

But there is no Open Source filesystem doing this yet AFAIK…

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]

Flip a coin

July 9th, 2007

AKA the binary decision make process here

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]

SQL Rosetta Stone

June 29th, 2007

aka how to translate commands between MySQL and PostgreSQL

Meta information

MySQL PostgreSQL
show databases; \dn
show tables; \dt
show columns from t1; \d t1
show indexes fom t1; \di

Time information

MySQL PostgreSQL
UNIX_TIMESTAMP() SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
FROM_UNIXTIME() select 1063147331.843::int4::abstime;
NOW() NOW()

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]

Fixing a broken replication in MySQL

November 10th, 2006

The easy way…

Log on to the slave and do a “load data from master” :

host:/home/mac# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 131 to server version: 4.0.24_Debian-10sarge2-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> load data from master;
ERROR 1189: Net error reading from master

This means that there is most likely a corruption in your logfiles…

What to do ?

* On the direct parent :

mysqldump -a --add-drop-table -e -x -p --master-data
-u root -p -B DB_to_Replicate > /tmp/master.sql

* Copy the file to the slave
* On the slave :

mysql> stop slave;
mysql> . /tmp/master.sql
mysql> start slave;
mysql> show slave status;

Wait a bit and check that the replication catches on…

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]

Creating a minimal Linux with Busybox

October 21st, 2006

I’m working from a regular Debian Sarge, but the following operations should work with pretty much any distribution.

In order to create a bootable Linux system you need two things :

  1. A kernel to boot on
  2. A minimal filesystem (aka a root disk)

We’re going to create those in a few steps :

Compiling the kernel

We’ll start by building a very simple, vanillia kernel :

wget http://kernel.org/pub/linux/kernel/v2.6/linux-2.6.18.1.tar.bz2
tar xjf linux-2.6.18.1.tar.bz2
cd linux-2.6.18.1
make menuconfig

at this point, just exit and save the default configuration. Start the compilation (this can take a while) :

make

Preparing the root disk :

Then we have to prepare the virtual disk, mount it and prepare its skeleton structure. 10 MB will be way enough.

dd if=/dev/zero of=/tmp/fsfile bs=1k count=10000
/sbin/mkfs.ext2 -F /tmp/fsfile
mkdir /tmp/fsfile.mount
sudo mount -o loop -t ext2 /tmp/fsfile /tmp/fsfile.mount
cd /tmp/fsfile.mount
mkdir proc bin sbin tmp mnt etc etc/init.d etc/mtab usr usr/bin devcd dev
cd dev
sudo mknod console c 5 1
sudo mknod tty1 c 4 1
sudo mknod tty2 c 4 2
sudo mknod tty3 c 4 3
sudo mknod tty4 c 4 4
sudo mknod tty5 c 4 5
cd ..
chmod 1777 tmp/

Preparing busybox :

wget http://www.busybox.net/downloads/busybox-1.2.1.tar.gz
tar xzvf busybox-1.2.1.tar.gz
cd busybox-1.2.1
make

You need to configure 3 specific items there :

  1. Busybox Settings / General Configuration / “Support –install” to YES
  2. Busybox Settings / Build Options / “Build BusyBox as a static binary” to YES
  3. Init Utilities / init to YES
  4. Shells / ash to YES

Save your configuration and redo :

make

Installing busybox :

cp busybox /tmp/fsfile.mount/bin/
cd /tmp/fsfile.mount
sudo mount -t proc proc proc
sudo chroot . /bin/busybox –install -s
sudo umount proc
cd ..
sudo umount /tmp/fsfile.mount

Testing with qemu :

Go back to the directory linux-2.6.18.1 where we built the kernel

qemu -hda /tmp/fsfile -kernel arch/i386/boot/bzImage -append “root=/dev/hda”

Conclusion :

Obviously with so few efforts a few things are missing :

  • The kernel is a stand-alone file, it’s not integrated in the ext2 image
  • The kernel is rather heavy-weight
  • Not many commands are installed (/sbin/poweroff is not really all that interesting alone :P )

That will be the topic of next posts.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google]