Thursday, January 28, 2010

[Level X] Customize your own storage engine.

refer to the following website:









http://forge.mysql.com/wiki/MySQL_Internals_Custom_Engine

Wish this helps.

regards,
Stanley Huang

[Books] Recommanded books for MySQL.

Two recommanded books for MySQL.
The detail, please refer to the website of Amazon

High Performance MySQL:
http://www.amazon.com/gp/product/0596101716?ie=UTF8&tag=perinc-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0596101716

MySQL Developer's Library:
http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/ref=sr_1_1?ie=UTF8&s=books&qid=1268323842&sr=8-1



Wish this helps.

regards,
Stanley Huang

Tuesday, January 26, 2010

[Level 2] Spider Storage Engine for MySQL

The new storage engine "Spider" does work for its strong scalability to access other storage engine of MySQL, to idea to the most considerations are below;
1:Scalability
2:Faster Access
3:Data Synchronizations
4:Reduce The Cost

More information please refer to the following links:
http://spiderformysql.com
https://launchpad.net/spiderformysql

Wish this helps.

regards,
Stanley Huang

[Level 2] How to get the difference between two tables.

Two table have the same data in echo column, how to get the difference between two tables.
You can use out join and constraint the condition.

Example:
mysql> create table table1 (id int, name varchar(32));
mysql> create table table2 (id int, name varchar(32));
mysql> insert into table1 values(1,'Stanley'),(2,'John'),(4,'Mary');
mysql> insert into table2 values(1,'Stanley'),(3,'Joseph'),(5,'Christy');

case 1: find the data in table1 but not in table2;
mysql> select * from table1 left join table2 on table1.id=table2.id where table2.id is null;

case 2: find the data in table2 but not in table1;
mysql> select * from table2 left join table1 on table2.id=table1.id where table1.id is null;

Wish this helps.

regards,
Stanley Huang

[Level 2] Convert character set in MySQL

How to convert character set from Big5 to UTF-8 in MySQL.

After MySQL 4.1, you can use alter command to convert character.

alter all column in one command:
mysql> alter table table_name convert to character set 'utf8';

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0



mysql>



alter each column in one command:
mysql> alter table orb modify a varchar(32) character set 'utf8';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0



mysql>



Wish this helps.

regards,
Stanley Huang

[Web Links] MySQL University

There is a good website for your to learning MySQL.

http://forge.mysql.com/wiki/MySQL_University

Wish this helps.

regards,
Stanley Huang

[Books] SQL Performance Tuning

A good book for study SQL performance tuning.



The introduction in Amazon:
http://www.amazon.com/SQL-Performance-Tuning-Peter-Gulutzan/dp/0201791692/ref=sr_1_1?ie=UTF8&qid=1264478735&sr=8-1

Wish this helps.

regards,
Stanley Huang

[Level 3] Enhance to save ip address in MySQL

What data type will you define the column for ip values.
Usually I got the answer is char(15).
But should we really have to set char(15)?
Any way to reduce the data size for saving ip data?
Yes, you can use "Int Unsigned" data type to save it!
Int??? Yes just Int!
At the same time you have to use another two stored routine for it, called
"inet_aton()" and "inet_ntoa()".
inet_aton() is the routine to convert the ip address from domain to integer.
inet_ntoa() is the routine to convert the integer to ip address domain value.

my sample as the following:

mysql> create table ip_table (ipaddr int unsigned); Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into ip_table values (inet_aton('127.0.0.1')), (inet_aton('255.255.255.0'));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select inet_ntoa(ipaddr) from ip_table;
+-------------------+
| inet_ntoa(ipaddr) |
+-------------------+
| 127.0.0.1         |
| 255.255.255.0     |
+-------------------+
2 rows in set (0.00 sec)
 
mysql>

Wish this helps.

regards,
Stanley Huang

[Level 2] MySQL Table lock status

Sometimes when database has performance issue.
You may check if the root cause is session waiting to long to execute.
You can use the command "show global status like 'Table_locks%" to get the table lock status.

mysql> show global status like 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 18    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.06 sec)

mysql>

There are two variables called "Table_locks_immediate" and "Table_locks_waited",
if the percentage of "immediate" is low and "waited" is high,
that shows you that the root cause "might be" the table lock.

Wish this helps.

regards,
Stanley Huang

Monday, January 25, 2010

[Info] FrederatedX Storage Engine

FederatedX storage engine is base on Federate Engine and enhance with following features:
* Multiple connections
* Transactions
* Pushdown conditions - LIMIT, non-indexed columns
* Support of other connection protocols other than libmysql

And it's just in development
More information, please reference to

https://launchpad.net/federatedx

Wish this helps.

regards,
Stanley Huang

Thursday, January 7, 2010

[Level 2] Distribution Constructor

What is distribution constructor? It is the tool for user to customize OpenSolaris image.
How to create a customize OpenSolaris image, the step as the following:
1. modify the manifest file.
# vi /usr/share/distro_const/slim_cd/slime_cd_x86.xml

2. display distribution list.
# distro_const build -l /usr/share/distro_const/slim_cd/slime_cd_x86.xml
Step           Resumable Description
-------------- --------- -------------
im-pop                   Populate the image with packages
im-mod                   Image area modifications
slim-im-mod              Slim CD Image area Modifications
ba-init                  Boot archive initialization
slim-ba-config           Slim CD boot archive configuration
ba-config                Boot archive configuration
ba-arch                  Boot archive archiving (64-bit)
ba-arch-32               Boot archive archiving (32-bit)
slim-post-mod            Slim CD post boot archive image area modification
grub-setup               Grub menu setup
post-mod                 Post boot archive image area modification
gen-slim-cont            Generate Slim CD image content list
iso                      ISO image creation
usb                      USB image creation

3. There are several ways to build the image.
  3-1. build all image.
  # distro_const build /usr/share/distro_const/slim_cd/slime_cd_x86.xml
  3-2. build image and stop the process before the point.
  # distro_const build -p im-mod /usr/share/distro_const/slim_cd/slime_cd_x86.xml
  3-3. resume building process from the step
  # distro_const build -r im-mod /usr/share/distro_const/slim_cd/slime_cd_x86.xml


Wish this helps.

regards,
Stanley Huang

[Level 3] Create Belenix on USB stick

It's very easy to install Belenix on a USB stick.
Open a terminal on Belenix, and then execute the command 'usbdump'.
At this time, the script will part the USB then install grub on it.

The following script is that modified from usbdump.

1. uncompress_microroot.sh
uncompress the mircroroot archieve and mount it on/tmp/microroot.

2. compress_microroot.sh
compress the /tmp/mircroroot and archieve it on USB stick.

3. init_belenix.sh
Initialize Belenix, it will execute the script uncompress_microroot.sh and compress_microroot.sh
Because Belenix cannot regonize my Ethernet card, so I add the setting on file [archive dir]/etc/driver_alias and [archive dir]/boot/solaris/devicedb/master.

PS. My shellwill create backup flag file for DR.



##---------------------------------------------{compress_microroot.sh}

#!/usr/bin/bash -x
sNow=`date "+%Y%m%d%H%M%S"`
sTmpDir=/tmp
sTmpMicroRoot=$sTmpDir/microroot
sTmpRootFile=$sTmpDir/x86.microroot
sUSBDir=/.cdrom
sUSBRootFile=$sUSBDir/boot/x86.microroot
if [ ! -d $sTmpMicroRoot ]
then
echo "cannot find microroot directory ($sTmpMicroRoot) !"
echo "exit program!"
exit
fi
umount -f $sTmpMicroRoot
lofiadm -d $sTmpRootFile
rmdir $sTmpMicroRoot
cd $sTmpDir
gzip $sTmpRootFile
mv $sUSBRootFile $sUSBRootFile.$sNow
cp $sTmpRootFile.gz $sUSBRootFile
cd $sUSBDir/boot
mv -f $sTmpRootFile.gz $sTmpRootFile.$sNow
echo "Please change directory to `pwd` ..."



##-------------------------------------------{uncompress_microroot.sh }
#!/usr/bin/bash -x
#sNow=`date "+%Y%m%d%H%M%S"`
sTmpDir=/tmp
sTmpMicroRoot=$sTmpDir/microroot
sTmpRootFile=$sTmpDir/x86.microroot
sUSBDir=/.cdrom
sUSBRootFile=$sUSBDir/boot/x86.microroot
if [ ! -f $sUSBRootFile ]
then
echo "cannot find microroot ($sUSBRootFile) !"
echo "exit program!"
exit
fi
#cp $sUSBRootFile $sUSBRootFile.$sNow
cp $sUSBRootFile $sTmpRootFile.gz
cd $sTmpDir
gunzip $sTmpRootFile.gz
mkdir -p $sTmpMicroRoot
mount -F ufs -o nologging `lofiadm -a $sTmpRootFile` $sTmpMicroRoot
cd $sTmpMicroRoot
echo "Please change directory to `pwd` ..."


##--------------------------------------------------{init_belenix.sh }
#!/usr/bin/bash -x
#sNow=`date "+%Y%m%d%H%M%S"`
sTmpDir=/tmp
sTmpMicroRoot=$sTmpDir/microroot
sTmpRootFile=$sTmpDir/x86.microroot
sUSBDir=/.cdrom
sUSBRootFile=$sUSBDir/boot/x86.microroot
sUSBShell=$sUSBDir/shell
sBenelixVersion=$sUSBDir/BELENIX_VERSION
#sShellDir=`pwd`/`dirname $0`
#sBaseDir=$sShellDir/..
ls $sUSBDir
if [ ! -d $sUSBDir ]
then
#echo "$sUSBDir doesnot exist! exit program..."
#exit
mkdir $sUSBDir
fi
if [ `ls $sUSBDir | wc -l` -eq 0 ]
then
#echo "$sUSBDir is empty! exit program..."
#exit
mount -F lofs $sUSBDir $sUSBDir
elif [ ! -f $sBenelixVersion ]
then
echo "The folder ($sUSBDir) is not the home directory of belenix!"
echo "exit program"
exit
fi
#exit
if [ "$1" == "-f" ] # force running belenix initialization
then
echo "Re-initialize belenix..."
[ -f $sUSBRootFile.orig ] && cp $sUSBRootFile.orig $sUSBRootFile
[ -f $sUSBShell/.init_down ] && rm $sUSBShell/.init_down
elif [ -f $sUSBShell/.init_down ] # check init status
then
echo "init belenix done!"
echo "exit program..."
exit # prevent run the log file...
else
echo "init belenix..."
fi
if [ -f $sUSBRootFile ] && [ ! -f $sUSBRootFile.orig ]
then
cp $sUSBRootFile $sUSBRootFile.orig
fi
################################### in microroot
#s. uncompress_microroot.sh
$sUSBShell/uncompress_microroot.sh
#1. $sTmpMicroRoot/etc/driver_aliases
# add: 'iprb "pci8086,103e"'
echo 'iprb "pci8086,103e"' >> $sTmpMicroRoot/etc/driver_aliases
#2. $sTmpMicroRoot/boot/solaris/devicedb/master
# add: 'pci8086,103e pci8086,103e net pci iprb.bef "Intel PRO/100 VE Ethernet"
'
echo 'pci8086,103e pci8086,103e net pci iprb.bef "Intel PRO/100 VE Ethernet"
' >> $sTmpMicroRoot/boot/solaris/devicedb/master
#3. $sTmpMicroRoot/etc/skel/.bashrc
# add: export PATH=$sUSBShell:$PATH
# export PS1='\u@\h:\w# '
echo "export PATH=$sUSBShell:\$PATH" >> $sTmpMicroRoot/etc/skel/.bashrc
echo "export PS1='\u@\h:\w# '" >> $sTmpMicroRoot/etc/skel/.bashrc
echo "xset -b" >> $sTmpMicroRoot/etc/skel/.bashrc
#4. $sTmpMicroRoot/etc/shadow
# $ passwd root
# copy password to $sTmpMicroRoot/etc/shadow
passwd root
sNewPwd=`grep "^root:" /etc/shadow | cut -d: -f2`
perl -pi -e "s/tj5ElkRBX7LF2/$sNewPwd/" $sTmpMicroRoot/etc/shadow
#5. $sTmpMicroRoot/etc/xdg/xfce4/xinitrc
# add: xset -b
if [ `grep -c "^xset -b$" $sTmpMicroRoot/etc/xdg/xfce4/xinitrc` -eq 0 ]
then
echo "xset -b" >> $sTmpMicroRoot/etc/xdg/xfce4/xinitrc
fi
#e. compress_microroot.sh
# $sUSBShell/compress_microroot.sh
################################### in /.cdrom/belenix.zlib
################################### in /.cdrom/belenix.1.zlib
################################### create flag file
echo "create flag file ($sUSBShell/.init_down)..."
echo "This file is for identify the initialization of belenix, do not remove it.
.." > $sUSBShell/.init_down









Wish this helps.

regards,
Stanley Huang

Wednesday, January 6, 2010

[Level 2] How to create ram disk?

If you want to create a RAM disk, you can use the command ramdiskadm.
The synopsis as the following:
     /usr/sbin/ramdiskadm -a name size [g | m | k | b]
     /usr/sbin/ramdiskadm -d name
     /usr/sbin/ramdiskadm

The sample as the following:
# ramdiskadm -a disk_a 128m
/dev/ramdisk/disk_a
# ls -l /dev/ramdisk/
total 1
lrwxrwxrwx   1 root root  40 2010-01-06 20:42 disk_a -> ../../devices/pseudo/ramdisk@1024:disk_a

# ls -al /dev/rramdisk/
total 1
lrwxrwxrwx   1 root root  44 2010-01-06 20:42 disk_a -> ../../devices/pseudo/ramdisk@1024:disk_a,raw
# newfs /dev/rramdisk/disk_a
newfs: construct a new file system /dev/rramdisk/disk_a: (y/n)? y
/dev/rramdisk/disk_a:    262036 sectors in 436 cylinders of 1 tracks, 601 sectors
    127.9MB in 28 cyl groups (16 c/g, 4.70MB/g, 2240 i/g)
super-block backups (for fsck -F ufs -o b=#) at:
 32, 9648, 19264, 28880, 38496, 48112, 57728, 67344, 76960, 86576,
 173120, 182736, 192352, 201968, 211584, 221200, 230816, 240432, 250048, 259664

# mkdir -p /mnt/ramdisk

# mount /dev/ramdisk/disk_a /mnt/ramdisk
#

Now, you can put the file in directory /mnt/ramdisk, the file will exists in RAM.
If you want to destroy it, the sample as following:
# umount /mnt/ramdisk
# ramdisk -d /dev/ramdisk/disk_a
#

Wish this helps.

regards,
Stanley Huang

[Level 2] How to create a FAT32 file system on OpenSolaris

Today, my colleague ask me how to create FAT32 on OpenSolaris~
Actually, you can use the command "mkfs" to create it.

# mkfs -F pcfs -o FAT=32 /dev/rdsk/c1t0d0p0:1

ref: man page, mkfs_pcfs(1M)

Wish this helps.

regards,
Stanley Huang

Tuesday, January 5, 2010

[Level 1] Back to the last position in Vim

Everytime when we left Vim, and we have to use the arrow down from the first line,
is that possible to back to the last position when we re-open it?
You can add the configuration below in your .vimrc!

au BufReadPost * if line("'\"") > 0|if line("'\"") <= line("$")|exe("norm '\"")|else|exe "norm $"|endif|endif

Wish this helps.

regards,
Stanley Huang

Saturday, January 2, 2010

[Level 3] Customize menu script

My college asked me to write a customize menu script with several functions.
The features are:
1. can insert, update, delete menu items.
2. have timeout limitration.
3. setting no. of columns.
4. setting column width.
So, after I finished it, I also share it to you.

#!/usr/bin/bash
showUsage() {
  cat << EOF
Usage:
  $0
     [-e] (expert mode)
     [-h] (help menu)
     [-t] timeout_sec (default timeout = $nTimeout sec)
     [-c] no. of columns (default no. of column = $nColumn)
     [-w] column wide (default column wide = $nWide)
Ex.
  $0
  $0 -e
  $0 -h
  $0 -t 10
  $0 -c 3
  $0 -w 10
EOF
}

pak2c() {
  read -n 1 -p "Press any key to continue..." pak2c
}

buildMenu() {
  clear
  declare -i nColumn=$1
  declare -i nWide=$2
  declare -i nCount=0
  declare -i nMod=0
  declare    sCount=""

  while read fItem item x
  do
    nCount=$nCount+1 && sCount=$nCount && [ $isExpert -eq 0 ] && sCount=.
    aMenu[$nCount]=$item
    fDisable=`echo $fItem | sed -e 's/[^*]//g'`
    printf "%${#nTotalCount}s) %${nWide}s%1s" $sCount $item $fDisable
  
    nMod=$nCount%$nColumn
    if [ $nMod -eq 0 ]
    then
      printf "\n";
    else
      printf " ";
    fi
  done << EOF
`grep "$sFilter" $0 ## | sort -k 2 ## disable display with sorting`
EOF
  [ $nMod -ne 0 ] &&  printf "\n"

  [ "$isExpert" -eq 0 ] && \
print "---------------------------------------" && \
printf "%${#nTotalCount}s) %${nWide}s\n" "a" "add" && \
printf "%${#nTotalCount}s) %${nWide}s\n" "r" "remove" && \
printf "%${#nTotalCount}s) %${nWide}s\n" "e" "enable" && \
printf "%${#nTotalCount}s) %${nWide}s%s\n" "d" "disable" ", marked *" && \
printf "%${#nTotalCount}s) %${nWide}s\n" "s" "sorting data" && \
printf "%${#nTotalCount}s) %${nWide}s\n" "q" "quit" && \
print "---------------------------------------"
}

############################################# main
clear
set -o noglob
declare -i nTimeout=10
declare -i isExpert=1
declare -a aMenu
declare    sFilter=""
declare -i nTotalCount=0
declare -i nColumn=1
declare -i nWide=10

while getopts ":ehc:t:w:" opt
do
  case $opt in
  e)
    isExpert=0
    nTimeout=65535
    nColumn=3
    ;;
  h)
    showUsage
    exit 0
    ;;
  c)
    nColumn=$OPTARG
    ;;
  t)
    nTimeout=$OPTARG
    ;;
  w)
    nWide=$OPTARG
    ;;
  :)
    echo "The option ($opt) without parameters, exit program..."
    showUsage
    exit 1
    ;;
  \?)
    echo "Not such option($OPTARG), exit program..."
    showUsage
    exit 1
    ;;
  esac
done

if [ $isExpert -eq 0 ]
then
  sFilter="^#[@*] "
else
  sFilter="^#[@] "
fi
nTotalCount=`grep -c "$sFilter" $0`

while true
do
  buildMenu $nColumn $nWide
  declare -i nChoice
  declare    sChoice
  declare -i fRead=0

  while true
  do
    echo -n "Please enter your choice ($nTimeout secs to timeout): "
    read -t $nTimeout sChoice || fRead=$?
    [ $fRead -eq 142 ] && break
    nChoice=$sChoice

    [ -z "$sChoice" ] && buildMenu $nColumn $nWide && continue
    [ $isExpert -eq 1 ] && ( [ $nChoice -le 0 ] || [ $nChoice -gt $nTotalCount ] ) && continue
    [ $isExpert -eq 0 ] && ( [ $sChoice == "0" ] || [ $nChoice -ne 0 ] ) && continue
    break
  done

  if [ $fRead -eq 142 ]
  then
    echo "Time out..."
    break
  else
    #echo "sChoice:$sChoice/$nTotalCount:"
    case "$sChoice" in
    a)
      while true
      do
        read -p "Please enter the item you want to add: " sItem
        #echo "sFilter=$sFilter"
        [ `grep -c "$sFilter[ ]*$sItem$" $0` -gt 0 ] && echo "Item ($sItem) exist!!" && continue
        [ ! -z $sItem ] && break
      done
      echo "#@ $sItem" >> $0
      echo "Add item ($sItem) done..."
      ;;
    r)
      while true
      do
        read -p "Please enter the item you want to remove: " sItem
        [ ! -z $sItem ] && break
      done
      perl -pi -e "s/^#[@*] $sItem\n$//" $0
      echo "remove item ($sItem) done..."
      ;;
    e)
      while true
      do
        read -p "Please enter the item you want to enable: " sItem
        [ ! -z $sItem ] && break
      done
      perl -pi -e "s/^#\* $sItem$/#@ $sItem/" $0
      echo "enable item ($sItem) done..."
      ;;
    d)
      while true
      do
        read -p "Please enter the item you want to disable: " sItem
        [ ! -z $sItem ] && break
      done
      perl -pi -e "s/^#@ $sItem$/#* $sItem/" $0
      echo "disable item ($sItem) done..."
      ;;
    s)
      declare -i nEOS=`grep -n '#### \*\*\* Data \*\*\*$' $0 | cut -d: -f1`
      declare -i nBOD=$nEOS+1
      head -$nEOS $0 > /tmp/$$.main
      tail +$nBOD $0 | sed -e "s/^#[@*][ ]*$//" | sed -e "/^$/d" | sort > /tmp/$$.data
      cat /tmp/$$.main /tmp/$$.data > $0
      cp $0 $0.bak
      rm /tmp/$$.main /tmp/$$.data
      echo "Sorting data done, please restart your program..."
      exit 0
      ;;
    q)
      exit 0
      ;;
    "")
      ;;
    *)
      if [ $isExpert -eq 1 ]
      then
        #####################################################################
        #
        # Modify Here.....
        #
        #####################################################################
        case ${aMenu[$nChoice]} in
        apple)
          echo "apple is red!"
          ;;
        banana)
          echo "banana is yellow!"
          ;;
        esac
      
        echo "You press $nChoice, $nChoice is ${aMenu[$nChoice]}."
      else
        continue
      fi
      ;;
    esac
  fi
  pak2c
done

echo "End of process..."

####
#### Menu item:
####   Use '#@ ', '#* ' at the begin of the line for setting menu item
####   #@ enable items
####   #* disable items
#### Ex.
####   #@ item
#### PS.
####   Do not modify the data below, use option "-e" (export mode) to modify.
####
#### *** Data ***
#@ apple
#@ banana
#@ cake
#* drink
#@ egg
#@ fruit
#@ grape
#@ ham
#* ice
#* juice
#@ lemon


Wish this helps.

regards,

Stanley Huang