[MemSQL]: Cluster set-up
Step 1: Install the below required packages on all the servers which you want to make MemSQL Cluster.
Required Packages for MemSQL Installation :-
——————————————
[root@hostname rpms]# ls -lhtr
total 118M
-rw-r–r– 1 mongod games 2.8M May 14 06:43 binutils-2.20.51.0.2-5.36.el6.x86_64.rpm
-rw-r–r– 1 mongod games 94K May 14 06:43 cloog-ppl-0.15.7-1.2.el6.x86_64.rpm
-rw-r–r– 1 mongod games 76K May 14 06:43 crypto-utils-2.4.1-24.2.el6.x86_64.rpm
-rw-r–r– 1 mongod games 3.8M May 14 06:43 cpp-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 11M May 14 06:43 gcc-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 4.8M May 14 06:43 gcc-c++-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 15M May 14 06:43 glibc-common-2.12-1.107.el6_4.4.x86_64.rpm
-rw-r–r– 1 root root 3.8M May 14 06:43 glibc-2.12-1.107.el6.x86_64.rpm
-rw-r–r– 1 mongod games 3.8M May 14 06:43 glibc-2.12-1.107.el6_4.4.x86_64.rpm
-rw-r–r– 1 mongod games 975K May 14 06:43 glibc-devel-2.12-1.107.el6_4.4.x86_64.rpm
-rw-r–r– 1 mongod games 605K May 14 06:43 glibc-headers-2.12-1.107.el6_4.4.x86_64.rpm
-rw-r–r– 1 root root 2.4M May 14 06:43 kernel-headers-2.6.32-358.el6.x86_64.rpm
-rw-r–r– 1 mongod games 2.4M May 14 06:43 kernel-headers-2.6.32-358.18.1.el6.x86_64.rpm
-rw-r–r– 1 mongod games 101K May 14 06:43 libgcc-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 118K May 14 06:43 libgomp-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 293K May 14 06:43 libstdc++-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 389K May 14 06:43 make-3.81-20.el6.x86_64.rpm
-rw-r–r– 1 mongod games 1.6M May 14 06:43 libstdc++-devel-4.4.7-3.el6.x86_64.rpm
-rw-r–r– 1 mongod games 157K May 14 06:43 mpfr-2.4.1-6.el6.x86_64.rpm
-rw-r–r– 1 mongod games 885K May 14 06:43 mysql-5.1.66-2.el6_3.x86_64.rpm
-rw-r–r– 1 mongod games 1.4M May 14 06:43 openssl-1.0.0-27.el6.x86_64.rpm
-rw-r–r– 1 mongod games 1.3M May 14 06:43 ppl-0.10.2-11.el6.x86_64.rpm
-rw-r–r– 1 root root 17M May 14 06:44 MySQL-client-5.5.21-1.linux2.6.x86_64.rpm
-rw-r–r– 1 root root 45M May 14 06:44 memsql-2.6.x86_64.rpm
[root@hostname rpms]#
If you’ve different way of packaging, we got to use –force& –nodeps options to install these packages.
[root@hostname rpms]# rpm -ivh –force –nodeps *.rpm
warning: binutils-2.20.51.0.2-5.36.el6.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Preparing… ########################################### [100%]
1:libgcc ########################################### [ 4%]
2:glibc-common ########################################### [ 8%]
3:glibc ########################################### [ 13%]
4:libstdc++ ########################################### [ 17%]
5:mpfr ########################################### [ 21%]
6:cpp ########################################### [ 25%]
7:libstdc++-devel ########################################### [ 29%]
8:ppl ########################################### [ 33%]
9:cloog-ppl ########################################### [ 38%]
10:binutils ########################################### [ 42%]
11:libgomp ########################################### [ 46%]
12:openssl ########################################### [ 50%]
13:kernel-headers ########################################### [ 54%]
14:glibc-headers ########################################### [ 58%]
15:glibc-devel ########################################### [ 63%]
16:gcc ########################################### [ 67%]
17:gcc-c++ ########################################### [ 71%]
18:memsql ########################################### [ 75%]
19:mysql ########################################### [ 79%]
20:crypto-utils ########################################### [ 83%]
21:glibc ########################################### [ 88%]
22:make ########################################### [ 92%]
23:MySQL-client ########################################### [ 96%]
24:kernel-headers ########################################### [100%]
[root@hostname rpms]#
You’ll see MemSQL Data Directory as below after the installation :-
—————————————————————–
[root@hostname rpms]# cd /var/lib/memsql/
[root@hostname memsql]#
[root@hostname memsql]# ls -lhtr
total 7.2M
-rwxr-xr-x 1 memsql memsql 11K Apr 14 16:39 check_system
-rw-r–r– 1 memsql memsql 4.0K Apr 14 16:39 README
-rw-r–r– 1 memsql memsql 1.1K Apr 14 16:39 memsql.cnf
-rw-r–r– 1 memsql memsql 7.1K Apr 14 16:39 LICENSE
-rwxr-xr-x 1 memsql memsql 7.1M May 11 16:08 memsqld
drwxr-xr-x 3 memsql memsql 4.0K May 14 06:45 objdir
drwxr-xr-x 7 memsql memsql 4.0K May 14 06:45 lib
drwxr-xr-x 3 memsql memsql 4.0K May 14 06:45 utils
drwxr-xr-x 3 memsql memsql 4.0K May 14 06:45 share
drwxr-xr-x 2 memsql memsql 4.0K May 14 06:45 tracelogs
drwxr-xr-x 3 memsql memsql 4.0K May 14 06:45 plancache
-rw-rw—- 1 memsql memsql 6 May 14 06:45 memsql.pid
drwxr-xr-x 4 memsql memsql 4.0K May 14 06:45 data
[root@hostname memsql]#
To do memSQL check system to check whether any package installation errors:-
————————————————————————–
[root@hostname memsql]# ./check_system
Warning: The number of allowed open file descriptors should be at least 64000. This OS is currently configured with the limit set to 1024. Please visit http://developers.memsql.com/docs/2.6/admin/admin_concepts.html#configuring-the-linux-file-descriptor-limit
./check_system: line 313: 16220 Illegal instruction $SCRIPT_DIR/lib/check_crc32 > /dev/null 2> /dev/null
Warning: SSE4.2 is not supported. Resorting to software CRC32C. MemSQL recovery and log writing performance will be negatively impacted.
[root@hostname memsql]#
Step 2: Stop the service & add the require parameter in the memsql.cnf file.
Stop the service & add maximum_memory=88064 in the memsql.cnf :-
—————————————————————–
[root@hostname memsql]# service memsql stop
Stopping memsql: [ OK ]
Waiting up to 60 seconds for a clean exit: [ OK ]
[root@hostname memsql]#
To make the Cluster : –
——————-
Step 3: Configure the Master & Chile Aggregator config files as below (Changes are highlighed) & start memSQL
[root@mst-agg-host-name hpadmin]# cat /var/lib/memsql/memsql.cnf
[server]
; —————————–
; MemSQL configuration options:
; —————————–
; Possible values: on, off
; If durability is off, no data is saved to disk, but database
; and table definitions are persisted.
durability = on
; The transaction buffer is a per-database, in-memory buffer of the
; transaction log. By default, it is asynchronously flushed to disk.
; You can set it to 0 for synchronous durability (transactions are
; flushed on commit). A positive value sets the size of the buffer.
transaction-buffer = 64m
; snapshot-trigger-size indicates the size of the transaction
; log at which MemSQL starts a snapshot and truncates the log
snapshot-trigger-size = 256m
; ———————————
; MySQL-style configuration options
; ———————————
basedir = .
tmpdir = .
lc-messages-dir = ./share
socket = memsql.sock
port = 3306
lock_wait_timeout = 60
bind-address = 0.0.0.0
max-connections = 8192
maximum_memory=88064
master-aggregator
[root@mst-agg-host-name hpadmin]#
[root@chld-agg-host-name memsql]# cat /var/lib/memsql/memsql.cnf
[server]
; —————————–
; MemSQL configuration options:
; —————————–
; Possible values: on, off
; If durability is off, no data is saved to disk, but database
; and table definitions are persisted.
durability = on
; The transaction buffer is a per-database, in-memory buffer of the
; transaction log. By default, it is asynchronously flushed to disk.
; You can set it to 0 for synchronous durability (transactions are
; flushed on commit). A positive value sets the size of the buffer.
transaction-buffer = 64m
; snapshot-trigger-size indicates the size of the transaction
; log at which MemSQL starts a snapshot and truncates the log
snapshot-trigger-size = 256m
; ———————————
; MySQL-style configuration options
; ———————————
basedir = .
tmpdir = .
lc-messages-dir = ./share
socket = memsql.sock
port = 3306
lock_wait_timeout = 60
bind-address = 0.0.0.0
max-connections = 8192
core-file
maximum_memory = 88064
master-aggregator=mst-agg-host-name
[root@chld-agg-host-name memsql]#
Step 4: Finally connect memSQL and add the leaves as below.
[root@mst-agg-host-name hpadmin]# mysql -h 127.0.0.1 -u root –prompt=”master-agg-memsql>”
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3007
Server version: 5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
master-agg-memsql>
master-agg-memsql>
To add/remove the LEAF(s)
master-agg-memsql>
master-agg-memsql>ADD LEAF root@’leaf-host-name’:3306;
Query OK, 1 row affected, 1 warning (0.53 sec)
master-agg-memsql>
master-agg-memsql>REMOVE LEAF ‘leaf-host-name’:3306;
Query OK, 1 row affected (0.47 sec)
master-agg-memsql>
master-agg-memsql>
To check the exisitng aggregators
master-agg-memsql>SHOW AGGREGATORS;
+————-+——+——–+——————–+—————————+——————-+
| Host | Port | State | Opened_Connections | Average_Roundtrip_Latency | Master_Aggregator |
+————-+——+——–+——————–+—————————+——————-+
| 127.0.0.1 | 3306 | online | 0 | NULL | 1 |
| xx.xx.87.37 | 3306 | online | 1 | 0.134 | 0 |
+————-+——+——–+——————–+—————————+——————-+
2 rows in set (0.00 sec)
Replication status
master-agg-memsql>
master-agg-memsql>
master-agg-memsql>SHOW REPLICATION STATUS;
+——–+———-+———–+—————-+—————————+———–+—————–+————+—————-+—————+————–+
| Role | Database | MasterURI | MasterPosition | SlaveURI | Connected | NetworkPosition | SlaveState | ReplayPosition | MasterCommits | SlaveCommits |
+——–+———-+———–+—————-+—————————+———–+—————–+————+—————-+—————+————–+
| master | sharding | NULL | 0:289 | xx.xx.87.37:3306/sharding | yes | NULL | NULL | NULL | 5 | NULL |
+——–+———-+———–+—————-+—————————+———–+—————–+————+—————-+—————+————–+
1 row in set (2.00 sec)
Currently running queries/processes
master-agg-memsql> select * from information_schema.processlist where info is not null order by time desc;
+——+————-+————————————+———-+———+——+———–+—————————————————————————————-+———+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | PLAN_ID |
+——+————-+————————————+———-+———+——+———–+—————————————————————————————-+———+
| 236 | distributed | CHLD-AGG-HOST.nam.nsroot.net:56989 | sharding | Query | 2982 | NULL | MEMSQL REPLICATION START | NULL |
| 3007 | root | localhost:37228 | NULL | Query | 213 | executing | select * from information_schema.processlist where info is not null order by time desc | NULL |
+——+————-+————————————+———-+———+——+———–+—————————————————————————————-+———+
2 rows in set (0.01 sec)
[…] Step 1: Install the below required packages on all the servers which you want to make MemSQL Cluster. Required Packages for memSQL Installation :- —————————————— [root@hostname rpms]# ls -lhtr total 118M -rw-r–r– 1 mongod games 2.8M May 14 06:43 binutils-2.20.51.0.2-5.36.el6.x86_64.rpm… […]