Posts

Showing posts from January, 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.

Image
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

[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

Image
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) |

[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

[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

[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 set

[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 s

[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, 2500

[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

[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

[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=.     a