hello MemSQL 入门安装示例

      一，介绍

                              MemSQL号称世界上最快的分布式关系型数据库，兼容mysql但快30倍，能实现每秒150万次事务。原理是仅用内存并将SQL预编译为C++。

     二，部署

                           官网下载地址：http://www.memsql.com/download/

- 安装
$ tar -xzf memsqlbin_amd64.tar.gz
$ cd memsqlbin

- 启动
$ ./check_system
./memsqld -u root --port 3307

- 关闭
$ killall memsqld

部署要求：

MemSQL 只能运行在 64 位的 Linux 系统，在运行之前必须按照 g++，下面是各种 Linux 发行版上安装 g++ 的方法：

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++

硬件

MemSQL 推荐的最低硬件配置：

• Intel Core i3 or better processor
• At least 8GB RAM

MemSQL 也可以在低于 8G 内存的机器上运行，但不推荐。MemSQL 的存储能力受限于系统内存，内存有多大存储就有多大。MemSQL 专门为 SSE 4.2 指令集进行优化，但也可以运行在老的处理器上

      三，简单实用

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

 

注： 这里-h不要使用localhost，用 127.0.0.1


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 test_001 (id int primary key, name varchar(100), addr varchar(200),num int);

 

Query OK, 0 rows affected (6.71 sec)

 

memsql>

memsql>

memsql> show tables;

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

| Tables_in_chris001 |

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

| test_001             |

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

1 row in set (0.00 sec)

 

memsql> desc test_001;

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

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

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

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

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

| addr  | varchar(200) | YES  |     | NULL    |       |

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

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

4 rows in set (0.00 sec)

 

 

 

 

插入数据后查询：

 

 

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

Query OK, 1 row affected (1.82 sec)

 

memsql>

memsql>

memsql> select * from test_001;

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

| id | name  | addr | num  |

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

|  1 | chris | xxx  |  100 |

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

1 row in set (0.71 sec)

 

memsql> select * from test_001;

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

| id | name  | addr | num  |

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

|  1 | chris | xxx  |  100 |

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

1 row in set (0.00 sec)

 

memsql> select * from test_001;

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

| id | name  | addr | num  |

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

|  1 | chris | xxx  |  100 |

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

1 row in set (0.00 sec)

 

memsql> select * from test_001;

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

| id | name  | addr | num  |

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

|  1 | chris | xxx  |  100 |

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

1 row in set (0.00 sec)

 

memsql> select count(0) from test_001;

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

| count(0) |

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

|        1 |

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

1 row in set (0.63 sec)

 

memsql>

memsql> select count(0) from test_001;

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

| count(0) |

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

|        1 |

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

1 row in set (0.00 sec)

 

memsql> select count(0) from test_001;

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

| count(0) |

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

|        1 |

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

1 row in set (0.00 sec)

 

 

 

查询的时候，服务端会打出日志：

 

 

136674795 2014-08-15 14:02:31 INFO: Table chris001.test_001 compiled in 6709 miliseconds (3185 miliseconds for header)

136678436 2014-08-15 14:02:31 INFO: Table chris001.test_001 row size 80 bytes (base overhead 32 bytes, column id 4 bytes, column name 12 bytes, column addr 12 bytes, column num 8 bytes)

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

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

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

 

 

从以上测试我们看到，两次查询的首次和之后时间对比：

 

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

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

 

从日志可以知道，首次查询的耗时基本全都花在编译上了  。 

四,应用场景

              MemSQL的插入性能非常高，所以它们的目标客户群是那些存在频繁交易的系统用.mysql迁移memsql，参考http://www.linuxeden.com/html/database/20120619/126001.html

参考:http://nodex.iteye.com/blog/1563763