MonetDB usage

MonetDB的安装参考 : 

http://blog.163.com/digoal@126/blog/static/16387704020147138518796/

MonetDB的性能让我眼前一亮, 对比PostgreSQL, 某些查询远超PG.

下面看看MonetDB简单的使用.

首先是使用monetdbd初始化一个dbfarm. 生成配置文件.merovingian_properties

[root@176 ~]# monetdbd create /data01/mdb2
[root@176 ~]# ll -la /data01/mdb2
.merovingian_properties

现在目录中只有一个配置文件, 在启动monetdbd前, 需要设置一下监听, 因为本地如果启用了多个monetdbd的话, 端口会冲突的.

首先获得当前创建的dbfarm的配置文件内容, 注意配置文件只能通过monetdbd来set.

# cat .merovingian_properties
# DO NOT EDIT THIS FILE - use monetdb(1) and monetdbd(1) to set properties
# This file is used by monetdbd

control=false

获得配置文件 : 

[root@176 ~]# monetdbd get all /data01/mdb2
   property            value
hostname         176.sky-mobi.com
dbfarm           /data01/mdb2
status           no monetdbd is serving this dbfarm
mserver          unknown (monetdbd not running)
logfile          /data01/mdb2/merovingian.log
pidfile          /data01/mdb2/merovingian.pid
sockdir          /tmp
port             50000
exittimeout      60
forward          proxy
discovery        true
discoveryttl     600
control          no
passphrase       <unknown>
mapisock         /tmp/.s.monetdb.50000
controlsock      /tmp/.s.merovingian.50000

设置属性, 一般设置监听端口即可, unix socket视情况修改. 

[root@176 ~]# monetdbd set port=50001 /data01/mdb2

如果要通过monetdb远程控制它的话, 还需要设置passphrase和control, 注意测试一下passphrase, 好像超过4个字母会有问题

[root@176 ~]# monetdbd set passphrase=1Digo /data01/mdb2
[root@176 mdb2]# monetdbd set control=yes /data01/mdb2
[root@176 mdb2]# monetdbd get all /data01/mdb2
   property            value
hostname         176.sky-mobi.com
dbfarm           /data01/mdb2
status           monetdbd[23923] 1.7 (Jan2014-SP3) is serving this dbfarm
mserver          /opt/monetdb11.17.21/bin/mserver5
logfile          /data01/mdb2/merovingian.log
pidfile          /data01/mdb2/merovingian.pid
sockdir          /tmp
port             50001
exittimeout      60
forward          proxy
discovery        yes
discoveryttl     600
control          yes
passphrase       {SHA512}d9343d4c860655ac2e815666f08d50750c58c974c05debe205aa243c0654706fa60f858fbcbd239f878777ee25f6558cd9e2af917e73ae00760a3aa522c52725
mapisock         /tmp/.s.monetdb.50001
controlsock      /tmp/.s.merovingian.50001

启动monetdbd

[root@176 ~]# monetdbd start /data01/mdb2

现在可以看到监听了, 目录下多了日志和PID文件.

[root@176 ~]# netstat -anp|grep 50001
tcp        0      0 0.0.0.0:50001               0.0.0.0:*                   LISTEN      23923/monetdbd
udp        0      0 0.0.0.0:50001               0.0.0.0:*                               23923/monetdbd
unix  2      [ ACC ]     STREAM     LISTENING     1036931 23923/monetdbd      /tmp/.s.monetdb.50001
unix  2      [ ACC ]     STREAM     LISTENING     1036935 23923/monetdbd      /tmp/.s.merovingian.50001
[root@176 ~]# ll /data01/mdb2/
total 2
-rw------- 1 root root 666 Aug 13 17:13 merovingian.log
-rw-r--r-- 1 root root   6 Aug 13 17:13 merovingian.pid
[root@176 ~]# ps -efw|grep monet
root     24361     1  0 17:30 ?        00:00:00 monetdbd start /data01/mdb2
root     24436 24361  6 17:34 ?        00:00:46 /opt/monetdb11.17.21/bin/mserver5 --dbpath=/data01/mdb2/test --set merovingian_uri mapi:monetdb://176.sky-mobi.com:50001/test --set mapi_open false --set mapi_port 0 --set mapi_usock /data01/mdb2/test/.mapi.sock --set monet_vault_key /data01/mdb2/test/.vaultkey --set gdk_nr_threads 8 --set max_clients 64 --set sql_optimizer default_pipe --set monet_daemon yes

接下来要使用monetdb连接这个monetdbd来创建数据库.

测试passphrase是否正常.

[root@176 mdb2]# monetdb -h 127.0.0.1 -p 50001 -P 1Digo discover
discover: =OK

创建1个数据库, 数据库创建的时候处于维护状态, 也就是LOCK状态, 必须release后才可以读写.

[root@176 mdb2]# monetdb -p 50001 create test
created database in maintenance mode: test
[root@176 mdb2]# monetdb -p 50001 release test
taken database out of maintenance mode: test

获得当前集群的配置信息,例如允许的线程数, 客户端连接数等. 

[root@176 mdb2]# monetdb -p 50001 get all
     name          prop     source           value
test             name      -        test
test             type      default  database
test             shared    default  yes
test             nthreads  default  8
test             optpipe   default  default_pipe
test             readonly  default  no
test             nclients  default  64

查看monetdbd状态

[root@176 mdb2]# monetdb -p 50001 status -l
test:
  connection uri: mapi:monetdb://176.sky-mobi.com:50001/test
  database name: test
  state: crashed
  locked: no
  scenarios: (none)
  start count: 0
  stop count: 0
  crash count: 0
  average uptime: 0s
  maximum uptime: 0s
  minimum uptime: 0s
  last start with crash: (unknown)
  last start: 1970-01-01 08:00:00
  last stop: (unknown)
  average of crashes in the last start attempt: 0
  average of crashes in the last 10 start attempts: 0.00
  average of crashes in the last 30 start attempts: 0.00

[root@176 mdb2]# monetdb -p 50001 status -c
database test, crashed on 1970-01-01 07:59:59
  crash average: 0.00 0.00 0.00 (over 1, 15, 30 starts) in total 0 crashes
  uptime stats (min/avg/max): 0s/0s/0s over 0 runs

使用mclient连接到刚才创建的数据库, 现在需要用到默认的用户密码monetdb.

mclient的参数详见man mclient, 例如编码, 语言, 时区等.

[root@176 mdb2]# mclient -p 50001 test
user(root):monetdb
password: 输入monetdb
Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://176.sky-mobi.com:50001/test'
Type \q to quit, \? for a list of available commands
auto commit mode: on

查看帮助

sql>\?
\?      - show this message
\<file  - read input from file
\>file  - save response in file, or stdout if no file is given
\|cmd   - pipe result to process, or stop when no command is given
\h      - show the readline history
\D table- dumps the table, or the complete database if none given.
\d[Stvsfn]+ [obj] - list database objects, or describe if obj given
\A      - enable auto commit
\a      - disable auto commit
\e      - echo the query in sql formatting mode
\f      - format using a built-in renderer {csv,tab,raw,sql,xml}
\w#     - set maximal page width (-1=unlimited, 0=terminal width, >0=limit to num)
\r#     - set maximum rows per page (-1=raw)
\L file - save client/server interaction
\X      - trace mclient code
\q      - terminate session

查看系统表

sql>\dSt
SYSTEM TABLE  sys._columns
SYSTEM TABLE  sys._tables
SYSTEM TABLE  sys.args
SYSTEM TABLE  sys.auths
SYSTEM TABLE  sys.connections
SYSTEM TABLE  sys.db_user_info
SYSTEM TABLE  sys.dependencies
SYSTEM TABLE  sys.functions
SYSTEM TABLE  sys.idxs
SYSTEM TABLE  sys.keys
SYSTEM TABLE  sys.objects
SYSTEM TABLE  sys.privileges
SYSTEM TABLE  sys.schemas
SYSTEM TABLE  sys.sequences
SYSTEM TABLE  sys.statistics
SYSTEM TABLE  sys.storagemodelinput
SYSTEM TABLE  sys.systemfunctions
SYSTEM TABLE  sys.triggers
SYSTEM TABLE  sys.types
SYSTEM TABLE  sys.user_role

创建表, 插入测试数据

sql>create table a(id int auto_increment primary key, info varchar(32));
operation successful (16.583ms)
sql>insert into a (info) values ('test');
1 affected rows, last generated key: 1 (7.604ms)
sql>insert into a (info) select info from a;

多次执行, 再创建一个b表.

测试count和关联. 速度非常快.

sql>select count(*) from a;
+----------+
| L1       |
+==========+
| 33554432 |
+----------+
1 tuple (1.446ms)
sql>select count(*) from b;
+----------+
| L1       |
+==========+
| 33554432 |
+----------+
1 tuple (1.938ms)
sql>select count(*) from b,a where a.id=b.id;
+----------+
| L1       |
+==========+
| 33554432 |
+----------+
1 tuple (18.6s)

[参考]

1. https://www.monetdb.org/Documentation

2. https://www.monetdb.org/Documentation/Extensions

3. man monetdbd mclient monetdb