MemSQL Aggregation commands

October 20, 2014 · by dbversity · in MemSQL

some useful Aggregator commands.
 
At Master Aggregator :-
---------------------
 
 
Please find the each command description at http://developers.memsql.com/docs/latest/ref/index.html#select
 
 
mysql> select database();
+------------+
| database() |
+------------+
| ycsb |
+------------+
1 row in set (0.00 sec)
 
 
mysql> show tables;
+-----------------+
| Tables_in_ycsb |
+-----------------+
| airline_on_time |
| usertable |
+-----------------+
2 rows in set (0.00 sec)
 
 
mysql> select * from usertable limit 1\G
*************************** 1. row ***************************
YCSB_KEY: user100040185076507112
 FIELD1: 4*3.61)"/11#?(1;?=603,#/7&0 >-40#:>.%.8!$%&4//"&&?24-'5''7'5&#17$"5,+91;)-&4$?3!(=)+"% 62<6"" 0/+ 9?
 FIELD2: >=?-%073'#=+%*"2-<;3-0),9>18,85>%/+" +;<=:/?$+85,">*"- 3640;"%:58-'?<"<)<496"-((.=#*/"? 29-79"!>(04
 FIELD3: #>52-*9-;,*->-74?'2+*%#&-;273./'+43(>8.++->#7)4468:>'4%&7.>#7;3$!'7+(,111: <0140#+&%0,?96862559 "2:?
 FIELD4: 6+: 0-:!:03:! $<* ->/+?+04("&1:().4+%7')./37#$?#71&.6%'+2"*(.&',$"&)'+6<=4 &$<")' :297/*6#,1 !2!#/%2
 FIELD5: =.0&%% #*1(!"&-%/7;$::;*>+7,5>0".4<1928:8+;#10?/%03':&<#,##765$>4?)!4.&#+"3=4(/"+424*&5;!!=;<8<70=;8
 FIELD6: *#.)-.7&-/:;6?=29>/)>&? 4-%<70*6#".) >6?/263-'7%,6&&#6.&93,4$6!4=51'"$/99#394.4/4=8+('.>.:*634.;5$:-
 FIELD7: 3,4+.2%$$26)3&;4188(/+='///0+&!(4/3.>$?#')%$.+?!:00(88$+?2*-,.70/10!%"&;"$,?"3+%;>)67#*0 +4667&.?5!/
 FIELD8: -/;8=76425!1,.79!8)1! 6--1<5&).=-3::9-(:+:*$2?/'<5 <#;),9/3>$?6$6:7&3 ;:#8-(!#+:,,)<>><)8,<$$,;9;,->
 FIELD9: =+1;7603#"$/8$''+;2#/4=4*=1>%=)>&+"(588>09%5'%6?=%:01/$&;#:7!#=:>%7 +8;0;>?/.+0'19/$!635/ #;*$=36.%)
FIELD10: "<4:.;?=,+5;+14:4($2;4 5$''$'(,4!--58?8!)/8)/"(&-+'5,"?656?/7.&-#) +6+:2&!7(6,0'.-%,% (.'2*%.% 9=*37
1 row in set (0.01 sec)
 
 
mysql>
mysql>
mysql> select count(1) from usertable;
+----------+
| count(1) |
+----------+
| 1000000 |
+----------+
1 row in set (6.11 sec)
 
 
mysql>
mysql> show leaves;
+-----------------+------+--------------------+-----------------+-----------+--------+--------------------+---------------------------+
| Host | Port | Availability_Group | Pair_Host | Pair_Port | State | Opened_Connections | Average_Roundtrip_Latency |
+-----------------+------+--------------------+-----------------+-----------+--------+--------------------+---------------------------+
| host-name-vm04 | 3306 | 1 | host-name-vm03 | 3306 | online | 187 | 0.786 |
| host-name-vm05 | 3306 | 1 | host-name-vm02 | 3308 | online | 68 | 0.575 |
| host-name-vm02 | 3308 | 2 | host-name-vm05 | 3306 | online | 81 | 0.637 |
| host-name-vm03 | 3306 | 2 | host-name-vm04 | 3306 | online | 167 | 0.840 |
+-----------------+------+--------------------+-----------------+-----------+--------+--------------------+---------------------------+
4 rows in set (0.00 sec)
 
 
mysql>
mysql> SHOW PLANCACHE;
+----------+-----------------------------------------------------+--------+----------+-----------+----------+---------------+---------------+--------------+-------------+--------------+-----------------+
| Database | QueryText | PlanId | Commits | Rollbacks | RowCount | ExecutionTime | LogBufferTime | LogFlushTime | RowLockTime | StreamedRows | LeafNetworkTime |
+----------+-----------------------------------------------------+--------+----------+-----------+----------+---------------+---------------+--------------+-------------+--------------+-----------------+
| SYSADM | select * from PS_PRL_DRLBCK_RPT | 23 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 0 | 0 |
| ycsb | INSERT INTO usertable VALUES(?,?,?,?,?,?,?,?,?,?,?) | 20 | 999985 | 0 | 999977 | 1108379 | 0 | 0 | 0 | 999983 | 1067319 |
| ycsb | SELECT * FROM usertable WHERE YCSB_KEY = ^ | 22 | 16969301 | 0 | 16969425 | 15275079 | NULL | NULL | NULL | 16969689 | 15057286 |
| ycsb | UPDATE usertable SET FIELD1=^ WHERE YCSB_KEY = ^ | 21 | 5498514 | 0 | 5498561 | 5426893 | 0 | 0 | 0 | 0 | 5346931 |
+----------+-----------------------------------------------------+--------+----------+-----------+----------+---------------+---------------+--------------+-------------+--------------+-----------------+
4 rows in set (0.00 sec)
 
 
 
 
mysql> show partitions;
+---------+-----------------+------+--------+--------+
| Ordinal | Host | Port | Role | Locked |
+---------+-----------------+------+--------+--------+
| 0 | host-name-vm03 | 3306 | Slave | 0 |
| 0 | host-name-vm04 | 3306 | Master | 0 |
| 1 | host-name-vm02 | 3308 | Master | 0 |
| 1 | host-name-vm05 | 3306 | Slave | 0 |
| 2 | host-name-vm04 | 3306 | Master | 0 |
| 2 | host-name-vm03 | 3306 | Slave | 0 |
| 3 | host-name-vm04 | 3306 | Master | 0 |
| 3 | host-name-vm03 | 3306 | Slave | 0 |
| 4 | host-name-vm03 | 3306 | Slave | 0 |
| 4 | host-name-vm04 | 3306 | Master | 0 |
| 5 | host-name-vm02 | 3308 | Master | 0 |
| 5 | host-name-vm05 | 3306 | Slave | 0 |
| 6 | host-name-vm04 | 3306 | Master | 0 |
| 6 | host-name-vm03 | 3306 | Slave | 0 |
| 7 | host-name-vm04 | 3306 | Master | 0 |
| 7 | host-name-vm03 | 3306 | Slave | 0 |
| 8 | host-name-vm03 | 3306 | Slave | 0 |
| 8 | host-name-vm04 | 3306 | Master | 0 |
| 9 | host-name-vm02 | 3308 | Master | 0 |
| 9 | host-name-vm05 | 3306 | Slave | 0 |
| 10 | host-name-vm03 | 3306 | Master | 0 |
+---------+-----------------+------+--------+--------+
 
 
 
 
mysql> SHOW REBALANCE STATUS ON ycsb;
+-------------------+---------+-----------------+-------------+-------+---------+--------------+
| Action | Ordinal | Target_Host | Target_Port | Phase | Status | Running_Time |
+-------------------+---------+-----------------+-------------+-------+---------+--------------+
| ATTACH PARTITION | 1 | host-name-vm02 | 3308 | 1 | success | 2340 |
| ATTACH PARTITION | 13 | host-name-vm02 | 3308 | 1 | success | 2403 |
| ATTACH PARTITION | 17 | host-name-vm02 | 3308 | 1 | success | 2403 |
| ATTACH PARTITION | 21 | host-name-vm02 | 3308 | 1 | success | 2403 |
| ATTACH PARTITION | 25 | host-name-vm02 | 3308 | 1 | success | 2403 |
| ATTACH PARTITION | 29 | host-name-vm02 | 3308 | 1 | success | 2403 |
| ATTACH PARTITION | 5 | host-name-vm02 | 3308 | 1 | success | 2403 |
| ATTACH PARTITION | 9 | host-name-vm02 | 3308 | 1 | success | 2403 |
| PROMOTE PARTITION | 1 | host-name-vm02 | 3308 | 8 | success | 6382 |
| PROMOTE PARTITION | 5 | host-name-vm02 | 3308 | 8 | success | 6382 |
| PROMOTE PARTITION | 9 | host-name-vm02 | 3308 | 8 | success | 6382 |
| PROMOTE PARTITION | 13 | host-name-vm02 | 3308 | 8 | success | 6382 |
| COPY PARTITION | 1 | host-name-vm05 | 3306 | 9 | success | 10221 |
| COPY PARTITION | 5 | host-name-vm05 | 3306 | 9 | success | 10221 |
| COPY PARTITION | 9 | host-name-vm05 | 3306 | 9 | success | 11122 |
| COPY PARTITION | 13 | host-name-vm05 | 3306 | 9 | success | 11122 |
+-------------------+---------+-----------------+-------------+-------+---------+--------------+
16 rows in set (0.00 sec)
 
 
mysql>
 
 
mysql> SHOW AGGREGATORS;
+-----------+------+--------+--------------------+---------------------------+-------------------+
| Host | Port | State | Opened_Connections | Average_Roundtrip_Latency | Master_Aggregator |
+-----------+------+--------+--------------------+---------------------------+-------------------+
| 127.0.0.1 | 3306 | online | 0 | NULL | 1 |
+-----------+------+--------+--------------------+---------------------------+-------------------+
1 row in set (0.00 sec)
 
 
 
 
 
 
mysql> select * from information_schema.processlist where info is not null order by time desc;
+-----+-------------+--------------------------------------+--------------------+---------+--------+-----------+----------------------------------------------------------------------------------------+---------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | PLAN_ID |
+-----+-------------+--------------------------------------+--------------------+---------+--------+-----------+----------------------------------------------------------------------------------------+---------+
| 34 | distributed | 1host-name-VM03.nam.nsroot.net:46299 | ycsb | Query | 114641 | NULL | MEMSQL REPLICATION START | NULL |
| 33 | distributed | 1host-name-VM03.nam.nsroot.net:46298 | inq | Query | 114641 | NULL | MEMSQL REPLICATION START | NULL |
| 32 | distributed | 1host-name-VM03.nam.nsroot.net:46297 | SYSADM | Query | 114641 | NULL | MEMSQL REPLICATION START | NULL |
| 35 | distributed | 1host-name-VM04.nam.nsroot.net:51051 | SYSADM | Query | 114588 | NULL | MEMSQL REPLICATION START | NULL |
| 37 | distributed | 1host-name-VM04.nam.nsroot.net:51053 | ycsb | Query | 114587 | NULL | MEMSQL REPLICATION START | NULL |
| 36 | distributed | 1host-name-VM04.nam.nsroot.net:51052 | inq | Query | 114587 | NULL | MEMSQL REPLICATION START | NULL |
| 38 | distributed | 1host-name-VM05.nam.nsroot.net:42229 | SYSADM | Query | 114528 | NULL | MEMSQL REPLICATION START | NULL |
| 40 | distributed | 1host-name-VM05.nam.nsroot.net:42231 | ycsb | Query | 114527 | NULL | MEMSQL REPLICATION START | NULL |
| 39 | distributed | 1host-name-VM05.nam.nsroot.net:42230 | inq | Query | 114527 | NULL | MEMSQL REPLICATION START | NULL |
| 43 | distributed | 1host-name-VM02.nam.nsroot.net:57181 | ycsb | Query | 114099 | NULL | MEMSQL REPLICATION START | NULL |
| 42 | distributed | 1host-name-VM02.nam.nsroot.net:57180 | inq | Query | 114099 | NULL | MEMSQL REPLICATION START | NULL |
| 41 | distributed | 1host-name-VM02.nam.nsroot.net:57179 | SYSADM | Query | 114099 | NULL | MEMSQL REPLICATION START | NULL |
| 782 | root | localhost:41969 | information_schema | Query | 2048 | executing | select * from information_schema.processlist where info is not null order by time desc | NULL |
| 792 | root | 1host-name-VM06.nam.nsroot.net:35489 | ycsb | Query | 1639 | NULL | SELECT * FROM usertable WHERE YCSB_KEY = ^ | 22 |
| 787 | root | 1host-name-VM06.nam.nsroot.net:35484 | ycsb | Query | 1639 | NULL | SELECT * FROM usertable WHERE YCSB_KEY = ^ | 22 |
| 786 | root | 1host-name-VM06.nam.nsroot.net:35483 | ycsb | Query | 1639 | NULL | SELECT * FROM usertable WHERE YCSB_KEY = ^ | 22 |
+-----+-------------+--------------------------------------+--------------------+---------+--------+-----------+----------------------------------------------------------------------------------------+---------+
16 rows in set (0.00 sec)
 
 
mysql> SHOW REPLICATION STATUS;
+--------+----------+-----------+----------------+--------------------------+-----------+-----------------+------------+----------------+---------------+--------------+
| Role | Database | MasterURI | MasterPosition | SlaveURI | Connected | NetworkPosition | SlaveState | ReplayPosition | MasterCommits | SlaveCommits |
+--------+----------+-----------+----------------+--------------------------+-----------+-----------------+------------+----------------+---------------+--------------+
| master | SYSADM | NULL | 0:168854 | xx.xx.xxx.xxx:3306/SYSADM | yes | NULL | NULL | NULL | 234 | NULL |
| master | SYSADM | NULL | 0:168854 | xx.xx.xxx.142:3308/SYSADM | yes | NULL | NULL | NULL | 234 | NULL |
| master | SYSADM | NULL | 0:168854 | xx.xx.xxx.143:3306/SYSADM | yes | NULL | NULL | NULL | 234 | NULL |
| master | SYSADM | NULL | 0:168854 | xx.xx.xxx.144:3306/SYSADM | yes | NULL | NULL | NULL | 234 | NULL |
| master | inq | NULL | 0:8169 | xx.xx.xxx.xxx:3306/inq | yes | NULL | NULL | NULL | 298 | NULL |
| master | inq | NULL | 0:8169 | xx.xx.xxx.142:3308/inq | yes | NULL | NULL | NULL | 298 | NULL |
| master | inq | NULL | 0:8169 | xx.xx.xxx.143:3306/inq | yes | NULL | NULL | NULL | 298 | NULL |
| master | inq | NULL | 0:8169 | xx.xx.xxx.144:3306/inq | yes | NULL | NULL | NULL | 298 | NULL |
| master | ycsb | NULL | 0:377786 | xx.xx.xxx.xxx:3306/ycsb | yes | NULL | NULL | NULL | 244 | NULL |
| master | ycsb | NULL | 0:377786 | xx.xx.xxx.142:3308/ycsb | yes | NULL | NULL | NULL | 244 | NULL |
| master | ycsb | NULL | 0:377786 | xx.xx.xxx.143:3306/ycsb | yes | NULL | NULL | NULL | 244 | NULL |
| master | ycsb | NULL | 0:377786 | xx.xx.xxx.144:3306/ycsb | yes | NULL | NULL | NULL | 244 | NULL |
+--------+----------+-----------+----------------+--------------------------+-----------+-----------------+------------+----------------+---------------+--------------+
12 rows in set (24.00 sec)

Leave a Reply

← MongoDB ConfigDB differ issue – Recovery procedure

Node requirement for MemSQL commands →