MemSQL 入門體驗小記

1 看到MemSQL  的介紹後，花了幾分鐘瀏覽網站，閱讀getting started並下載安裝啟動，在此小記。

2 MemSQL      http://memsql.com/

其宣傳Slogan 是 THE WORLD'S FASTEST DATABASE.YOUR DATA. ACCELERATED.

我們看到最多的中文介紹是  “由前Facebook工程師創辦的MemSQL，號稱世界上最快的Distributed Relational Database Service，相容MySQL但快30倍，能實現每秒150萬次事務。原理是僅用記憶體並將SQL先行編譯為C++。”

3  如何開始？

http://developers.memsql.com/docs/1b/guides/starting.html

- 點擊右上方的下載連結  http://www.memsql.com/#download  ，它會要求你提供email等資訊，提交後返回一個license key和下載連結，我們下載developer版的即可。

- 安裝

$ tar -xzf memsqlbin_amd64.tar.gz

$ cd memsqlbin

- 啟動

$ ./check_system

$ ./memsqld

註：

- check_system會告訴系統是否滿足需求，比如核心版本，OS版本，64位 等等

- 啟動的時候，要加 -u root，否則會報fatal錯誤；如果你已經有mysql在運行，那麼可能需要更改預設啟動連接埠3306為其他連接埠，比如 3307

./memsqld -u root --port 3307- 如果你的RAM沒有8G，啟動的時候會有警告，但可以忽略。

- 測試

$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "

註： 這裡-h不要使用localhost，用 127.0.0.1

進入命令列後就是標準的mysql用戶端了。

memsql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| memsql            |

+--------------------+

2 rows in set (0.00 sec) 

建庫： 

memsql> create database chris001;

Query OK, 0 rows affected (0.08 sec)

 

memsql> show databases;         

+--------------------+

| Database          |

+--------------------+

| information_schema |

| memsql            |

| chris001          |

+--------------------+

3 rows in set (0.00 sec) 

建表： 

memsql> use chris001;

Database changed

memsql>

memsql> create table tab001 (id int primary key, name varchar(100), memo varchar(200),num int);

 

Query OK, 0 rows affected (6.71 sec)

 

memsql>

memsql>

memsql> show tables;

+--------------------+

| Tables_in_chris001 |

+--------------------+

| tab001            |

+--------------------+

1 row in set (0.00 sec)

 

memsql> desc tab001;

+-------+--------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id    | int(11)      | NO  | PRI | NULL    |      |

| name  | varchar(100) | YES  |    | NULL    |      |

| memo  | varchar(200) | YES  |    | NULL    |      |

| num  | int(11)      | YES  |    | NULL    |      |

+-------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec) 

插入資料後查詢： 

memsql> insert into tab001 values (1,'chris','xxx',100);

Query OK, 1 row affected (1.82 sec)

 

memsql>

memsql>

memsql> select * from tab001;

+----+-------+------+------+

| id | name  | memo | num  |

+----+-------+------+------+

|  1 | chris | xxx  |  100 |

+----+-------+------+------+

1 row in set (0.71 sec)

 

memsql> select * from tab001;

+----+-------+------+------+

| id | name  | memo | num  |

+----+-------+------+------+

|  1 | chris | xxx  |  100 |

+----+-------+------+------+

1 row in set (0.00 sec)

 

memsql> select * from tab001;

+----+-------+------+------+

| id | name  | memo | num  |

+----+-------+------+------+

|  1 | chris | xxx  |  100 |

+----+-------+------+------+

1 row in set (0.00 sec)

 

memsql> select * from tab001;

+----+-------+------+------+

| id | name  | memo | num  |

+----+-------+------+------+

|  1 | chris | xxx  |  100 |

+----+-------+------+------+

1 row in set (0.00 sec)

 

memsql> select count(0) from tab001;

+----------+

| count(0) |

+----------+

|        1 |

+----------+

1 row in set (0.63 sec)

 

memsql>

memsql> select count(0) from tab001;

+----------+

| count(0) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

 

memsql> select count(0) from tab001;

+----------+

| count(0) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec) 

查詢的時候，服務端會打出日誌： 

136674795 2012-06-19 10:02:31 INFO: Table chris001.tab001 compiled in 6709 miliseconds (3185 miliseconds for header)

136678436 2012-06-19 10:02:31 INFO: Table chris001.tab001 row size 80 bytes (base overhead 32 bytes, column id 4 bytes, column name 12 bytes, column memo 12 bytes, column num 8 bytes)

186966810 2012-06-19 10:03:21 INFO: Query chris001.'insert into tab001 values (?,?,?,?)' compiled in 1825 miliseconds

194366714 2012-06-19 10:03:29 INFO: Query chris001.'select * from tab001' compiled in 712 miliseconds

283342115 2012-06-19 10:04:58 INFO: Query chris001.'select count(0) from tab001' compiled in 632 miliseconds 

從以上測試我們看到，兩次查詢的首次和之後時間對比： 

select * from tab001;                (0.71 sec)      (0.00 sec) 

select count(0) from tab001;    (0.63 sec)        (0.00 sec) 

從日誌可以知道，首次查詢的耗時基本全都花在編譯上了  。 

- 關閉 

$ killall memsqld 

4 總結   

引用 MemSQL 網站的介紹 

MemSQL 寫道
MemSQL places data into memory and translates SQL into C++ for the utmost optimization in query execution. This enables MemSQL to write and read data at incredible speeds, and by offering a relational interface, you can unify the data you’d normally store in a short-lived medium—cache or key-value store—and place it directly into a database along with your existing data.
 

附： 軟硬體要求

支援的部署環境，必須 64位

Distribution Minimum Version Command

Amazon AMI	2012.03	sudo yum install gcc-c++
CentOS	6.0	sudo yum install gcc-c++
Debian	6.0	sudo apt-get install g++
Fedora	15	sudo yum install gcc-c++
OpenSUSE	11.3	sudo zypper install gcc-c++
Red Hat	6.1	sudo yum install gcc-c++
Ubuntu	10.04	sudo apt-get install g++

硬體： 

Intel Core i3 or better processor
At least 8GB RAM
註：測試時RAM沒有8G會有警告，不影響使用。

MemSQL 的詳細介紹：請點這裡
MemSQL 的：請點這裡