MonetDB usage
湛光明
MonetDB的安装参考 :
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个字母会有问题
启动monetdbd
现在可以看到监听了, 目录下多了日志和PID文件.
接下来要使用monetdb连接这个monetdbd来创建数据库.
[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是否正常.
创建1个数据库, 数据库创建的时候处于维护状态, 也就是LOCK状态, 必须release后才可以读写.
获得当前集群的配置信息,例如允许的线程数, 客户端连接数等.
查看monetdbd状态
使用mclient连接到刚才创建的数据库, 现在需要用到默认的用户密码monetdb.
[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)
[参考]
3. man monetdbd mclient monetdb
类似资料：