mariadb json字段查询 mariadb columnstore

转载

架构思维大师 2024-05-08 14:26:01

文章标签 mariadb json字段查询 2d SQL perl 文章分类 计算机视觉人工智能

ariolumnStore

简介

Part1:写在最前

MariaDB ColumnStore is the future of data warehousing. ColumnStore allows us to store more data and analyze it faster. Everyday, Pinger’s mobile applications process millions of text messages and phone calls. We also process more than 1.5 billion rows of logs per day. Analytic scalability and performance is critical to our business. MariaDB’s ColumnStore manages massive amounts of data and will scale with Pinger as we grow.

----from mariadb.com

Part2:大牛如何说

MariaDB ColumnStore是在MariaDB 10.1基础上移植了InfiniDB4.6.2构建的大规模并行，高性能，压缩，分布式开源列式存储引擎，类似收费产品Infobright。它设计用于大数据离线分析，用来抗衡Hadoop。你可以使用标准SQL语句进行查询，支持目前流行的sqlyog/navicat客户端工具连接，对业务方使用没有任何的不便，并且你不需要创建任何索引，不需要修改业务方的复杂SQL（自身就支持复杂的关联查询、聚合、存储过程和用户定义的函数），你唯一要做的就是把数据导入到ColumnStore里，就没你事了。这对一家没有Hadoop工程师的公司来说，MariaDB ColumnStore会是一个更好的替代产品。

-----from 贺春旸

Part3:环境简介

192.168.1.248 HE1 um1

192.168.1.249 HE2 um2

192.168.1.250 HE3 pm1

192.168.1.251 HE4 pm2

Column Store架构概述

Part1:写在最前

MariaDB ColumnStore是一种专为分布式大规模并行处理（MPP）设计的列式存储引擎。它由三个组件组成，协同工作。

在官方给出的架构图中，我们可以看到分为三个组件构成：UM、PM、数据存储层。

用户模块（UM）：

用户模块管理和控制终端用户查询的操作，它维护每个查询的状态，向一个或多个性能模块发出请求以代为执行SQL查询工作，最后，用户模块汇集来自各个参与的性能模块的所有查询结果，以形成返回给用户的完整的查询结果集。

性能模块（PM）：

性能模块负责存储，检索和管理数据，处理对查询操作的块请求，并将其传递回用户模块以完成查询请求。性能模块将获取的数据缓存在其内存中计算。MPP是通过允许用户配置尽可能多的性能模块，以实现更高的处理能力。

存储：

MariaDB ColumnStore对于存储系统极为灵活。当在内部运行时，它可以使用本地存储或共享存储（例如SAN）来存储数据。在Amazon EC2环境中，它可以使用临时或弹性块存储（EBS）卷。当无共享部署需要数据冗余时，它被构建为与GlusterFS和Apache Hadoop分布式文件系统（HDFS）集成。

一句话总结：用户模块（UM）将客户端发出的SQL请求进行分配，分配到后端性能模块（PM），PM进行数据查询分析，将处理的结果返回给UM，UM再把PM分析的结果进行聚合，最后返回给客户端最终的查询结果。

Column Store性能对比

Percona专业MySQL服务提供商性能测试InfiniDB比其他OLAP优势明显。

Column Store安装

Part1:打通互信

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh-keygen</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh-copy-id '-p 22 root@192.168.1.248'</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh-copy-id '-p 22 root@192.168.1.249'</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh-copy-id '-p 22 root@192.168.1.250'</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh-copy-id '-p 22 root@192.168.1.251'</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh HE1</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh HE2</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh HE3</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># ssh HE4</span>
<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># cat /etc/hosts</span>
192.168.1.248   HE1
192.168.1.249   HE2
192.168.1.250   HE3
192.168.1.251   HE4</code></span></span>

在HE1,HE2,HE3,HE4每台机器上执行上述命令，打通ssh互信。

Part2:关闭防火墙

[root@HE1 ~]# /etc/init.d/iptables status

iptables: Firewall is not running.

[root@HE1 ~]# chkconfig iptables off

[root@HE1 ~]# chkconfig --list | grep iptables

iptables 0:off1:off2:off3:off4:off5:off6:off

Part3:关闭文件系统访问时间和修改磁盘调度策略

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># cat /etc/fstab </span>

<span style="color:slategray">#</span>
<span style="color:slategray"># /etc/fstab</span>
<span style="color:slategray"># Created by anaconda on Sat Mar  5 09:35:40 2016</span>
<span style="color:slategray">#</span>
<span style="color:slategray"># Accessible filesystems, by reference, are maintained under '/dev/disk'</span>
<span style="color:slategray"># See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info</span>
<span style="color:slategray">#</span>
UUID<span style="color:#9a6e3a">=</span>397d50ba-22b0-4d50-9e29-89e3b92d2d07 /                       ext4    defaults,noatime,barrier<span style="color:#9a6e3a">=</span>0        1 1


<span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># echo "deadline" > /sys/block/sda/queue/scheduler</span></code></span></span>

Part4:关闭numa

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@HE1 ~<span style="color:#999999">]</span><span style="color:slategray"># cat /etc/grub.conf</span>
<span style="color:slategray"># grub.conf generated by anaconda</span>
<span style="color:slategray">#</span>
<span style="color:slategray"># Note that you do not have to rerun grub after making changes to this file</span>
<span style="color:slategray"># NOTICE:  You have a /boot partition.  This means that</span>
<span style="color:slategray">#          all kernel and initrd paths are relative to /boot/, eg.</span>
<span style="color:slategray">#          root (hd0,0)</span>
<span style="color:slategray">#          kernel /vmlinuz-version ro root=/dev/sda2</span>
<span style="color:slategray">#          initrd /initrd-[generic-]version.img</span>
<span style="color:slategray">#boot=/dev/sda</span>
default<span style="color:#9a6e3a">=</span>1
timeout<span style="color:#9a6e3a">=</span>5
splashimage<span style="color:#9a6e3a">=</span><span style="color:#999999">(</span>hd0,0<span style="color:#999999">)</span>/grub/splash.xpm.gz
hiddenmenu
title CentOS <span style="color:#999999">(</span>2.6.32-573.18.1.el6.x86_64.debug<span style="color:#999999">)</span>
root <span style="color:#999999">(</span>hd0,0<span style="color:#999999">)</span>
kernel /vmlinuz-2.6.32-573.18.1.el6.x86_64.debug ro root<span style="color:#9a6e3a">=</span>UUID<span style="color:#9a6e3a">=</span>397d50ba-22b0-4d50-9e29-89e3b92d2d07 rd_NO_LUKS rd_NO_LVM LANG<span style="color:#9a6e3a">=</span>en_US.UTF-8 rd_NO_MD SYSFONT<span style="color:#9a6e3a">=</span>latarcyrheb-sun16 crashkernel<span style="color:#9a6e3a">=</span>auto  KEYBOARDTYPE<span style="color:#9a6e3a">=</span>pc KEYTABLE<span style="color:#9a6e3a">=</span>us rd_NO_DM rhgb quiet numa<span style="color:#9a6e3a">=</span>off
initrd /initramfs-2.6.32-573.18.1.el6.x86_64.debug.img
title CentOS <span style="color:#999999">(</span>2.6.32-431.el6.x86_64<span style="color:#999999">)</span>
root <span style="color:#999999">(</span>hd0,0<span style="color:#999999">)</span>
kernel /vmlinuz-2.6.32-431.el6.x86_64 ro root<span style="color:#9a6e3a">=</span>UUID<span style="color:#9a6e3a">=</span>397d50ba-22b0-4d50-9e29-89e3b92d2d07 rd_NO_LUKS rd_NO_LVM LANG<span style="color:#9a6e3a">=</span>en_US.UTF-8 rd_NO_MD SYSFONT<span style="color:#9a6e3a">=</span>latarcyrheb-sun16 crashkernel<span style="color:#9a6e3a">=</span>auto  KEYBOARDTYPE<span style="color:#9a6e3a">=</span>pc KEYTABLE<span style="color:#9a6e3a">=</span>us rd_NO_DM rhgb quiet numa<span style="color:#9a6e3a">=</span>off
initrd /initramfs-2.6.32-431.el6.x86_64.img</code></span></span>

Part5:安装jemalloc内存管理

[root@HE1 ~]# yum install jemalloc-*

[root@HE1 ~]# reboot

在HE1,HE2,HE3,HE4每台机器上执行上述命令

Part6:安装boost软件包

[root@HE1 ~]# yum -y install boost*

[root@HE1 ~]# yum -y groupinstall "Development Tools"

[root@HE1 ~]# yum -y install cmake

[root@HE1 ~]# tar xvf boost_1_55_0.tar.gz

[root@HE1 ~]# cd boost_1_55_0

[root@HE1 boost_1_55_0]# ./bootstrap.sh --with-libraries=atomic,date_time,exception,filesystem,iostreams,locale,program_options,regex,signals,system,test,thread,timer,log --prefix=/usr

[root@HE1 boost_1_55_0]# ./b2 install

Part6:安装perl依赖包

[root@HE1 ~]# yum -y install expect perl perl-DBI openssl zlib perl-DBD-MySQL

Part7:安装Mariadb ColumStore

[root@HE1 ~]# tar xvf mariadb-columnstore-1.0.6-1-centos6.x86_64.bin.tar.gz -C /usr/local

Part8:配置Mariadb ColumStore

[root@HE1 ~]# /usr/local/mariadb/columnstore/bin/postConfigure

至此，MariaDB ColumnStore安装并配置完成

Column Store单点故障测试

Part1:关闭HE2

当前HE2为primary um

<span style="color:#333333"><span style="color:black"><code class="language-bash">mcsadmin<span style="color:#9a6e3a">></span> getSystemStatus
getsystemstatus   Mon Dec 26 15:56:47 2016
System columnstore-1
System and Module statuses
Component     Status                       Last Status Change
------------  --------------------------   ------------------------
System        BUSY_INIT                    Mon Dec 26 15:56:38 2016
Module um1    AUTO_DISABLED/DEGRADED       Mon Dec 26 15:56:40 2016
Module um2    ACTIVE                       Mon Dec 26 15:54:21 2016
Module pm1    ACTIVE                       Mon Dec 26 02:03:27 2016
Module pm2    ACTIVE                       Mon Dec 26 02:03:41 2016
Active Parent OAM Performance Module is <span style="color:#669900">'pm1'</span>
Primary Front-End MariaDB Columnstore Module is <span style="color:#669900">'um2'</span>
MariaDB Columnstore Replication Feature is enabled
<span style="color:#999999">[</span>root@HE2 ~<span style="color:#999999">]</span><span style="color:slategray"># reboot</span></code></span></span>

在当前primary um重启后，可以看到primary um已经自动切换至um1

<span style="color:#333333"><span style="color:black"><code class="language-bash">mcsadmin<span style="color:#9a6e3a">></span> getSystemStatus
getsystemstatus   Mon Dec 26 15:58:19 2016
System columnstore-1
System and Module statuses
Component     Status                       Last Status Change
------------  --------------------------   ------------------------
System        BUSY_INIT                    Mon Dec 26 15:58:10 2016
Module um1    ACTIVE                       Mon Dec 26 15:57:17 2016
Module um2    AUTO_DISABLED/DEGRADED       Mon Dec 26 15:58:11 2016
Module pm1    ACTIVE                       Mon Dec 26 02:03:27 2016
Module pm2    ACTIVE                       Mon Dec 26 02:03:41 2016
Active Parent OAM Performance Module is <span style="color:#669900">'pm1'</span>
Primary Front-End MariaDB Columnstore Module is <span style="color:#669900">'um1'</span>
MariaDB Columnstore Replication Feature is enabled</code></span></span>

Part2:检查状态

在原primary um (HE2)中进入数据库查看，现在已经是um1的从库

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@HE2 ~<span style="color:#999999">]</span><span style="color:slategray"># mcsmysql </span>
Welcome to the MariaDB monitor.  Commands end with <span style="color:#999999">;</span> or \g.
Your MariaDB connection <span style="color:#dd4a68">id</span> is 10
Server version: 10.1.19-MariaDB Columnstore 1.0.6-1
Copyright <span style="color:#999999">(</span>c<span style="color:#999999">)</span> 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type <span style="color:#669900">'help;'</span> or <span style="color:#669900">'\h'</span> <span style="color:#0077aa">for</span> help. Type <span style="color:#669900">'\c'</span> to <span style="color:#dd4a68">clear</span> the current input statement.
MariaDB <span style="color:#999999">[</span><span style="color:#999999">(</span>none<span style="color:#999999">)</span><span style="color:#999999">]</span><span style="color:#9a6e3a">></span> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting <span style="color:#0077aa">for</span> master to send event
                  Master_Host: 192.168.1.248
                  Master_User: idbrep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 1879
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 537
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1879
              Relay_Log_Space: 829
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span><span style="color:#999999">(</span>none<span style="color:#999999">)</span><span style="color:#999999">]</span><span style="color:#9a6e3a">></span></code></span></span>

Column Store测试

Part1:主键和索引

<span style="color:#333333"><span style="color:black"><code class="language-bash">MariaDB <span style="color:#999999">[</span>helei<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> create table helei_innodb<span style="color:#999999">(</span>
    -<span style="color:#9a6e3a">></span> <span style="color:#dd4a68">id</span> int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned NOT NULL AUTO_INCREMENT,
    -<span style="color:#9a6e3a">></span> c1 int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> NOT NULL DEFAULT <span style="color:#669900">'0'</span>,
    -<span style="color:#9a6e3a">></span> c2 int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned DEFAULT NULL,
    -<span style="color:#9a6e3a">></span> c5 int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned NOT NULL DEFAULT <span style="color:#669900">'0'</span>,
    -<span style="color:#9a6e3a">></span> c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -<span style="color:#9a6e3a">></span> c4 varchar<span style="color:#999999">(</span>200<span style="color:#999999">)</span> NOT NULL DEFAULT <span style="color:#669900">''</span>,
    -<span style="color:#9a6e3a">></span> PRIMARY KEY<span style="color:#999999">(</span>id<span style="color:#999999">)</span>,
    -<span style="color:#9a6e3a">></span> KEY idx_c1<span style="color:#999999">(</span>c1<span style="color:#999999">)</span>,
    -<span style="color:#9a6e3a">></span> KEY idx_c2<span style="color:#999999">(</span>c2<span style="color:#999999">)</span>
    -<span style="color:#9a6e3a">></span> <span style="color:#999999">)</span>ENGINE<span style="color:#9a6e3a">=</span>InnoDB <span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.03 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>helei<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> create table helei_cs<span style="color:#999999">(</span>
    -<span style="color:#9a6e3a">></span> <span style="color:#dd4a68">id</span> int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned NOT NULL AUTO_INCREMENT,
    -<span style="color:#9a6e3a">></span> c1 int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> NOT NULL DEFAULT <span style="color:#669900">'0'</span>,
    -<span style="color:#9a6e3a">></span> c2 int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned DEFAULT NULL,
    -<span style="color:#9a6e3a">></span> c5 int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned NOT NULL DEFAULT <span style="color:#669900">'0'</span>,
    -<span style="color:#9a6e3a">></span> c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -<span style="color:#9a6e3a">></span> c4 varchar<span style="color:#999999">(</span>200<span style="color:#999999">)</span> NOT NULL DEFAULT <span style="color:#669900">''</span>,
    -<span style="color:#9a6e3a">></span> PRIMARY KEY<span style="color:#999999">(</span>id<span style="color:#999999">)</span>,
    -<span style="color:#9a6e3a">></span> KEY idx_c1<span style="color:#999999">(</span>c1<span style="color:#999999">)</span>,
    -<span style="color:#9a6e3a">></span> KEY idx_c2<span style="color:#999999">(</span>c2<span style="color:#999999">)</span>
    -<span style="color:#9a6e3a">></span> <span style="color:#999999">)</span>ENGINE<span style="color:#9a6e3a">=</span>Columnstore<span style="color:#999999">;</span>
ERROR 1069 <span style="color:#999999">(</span>42000<span style="color:#999999">)</span>: Too many keys specified<span style="color:#999999">;</span> max 0 keys allowed</code></span></span>

这里可以看出，columnstore存储引擎不支持主键也不需要索引

<span style="color:#333333"><span style="color:black"><code class="language-bash">MariaDB <span style="color:#999999">[</span>helei<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> create table helei_cs<span style="color:#999999">(</span>
    -<span style="color:#9a6e3a">></span> <span style="color:#dd4a68">id</span> int<span style="color:#999999">(</span>10<span style="color:#999999">)</span> unsigned NOT NULL ,
    -<span style="color:#9a6e3a">></span> c1 varchar<span style="color:#999999">(</span>200<span style="color:#999999">)</span> NOT NULL DEFAULT <span style="color:#669900">''</span>
    -<span style="color:#9a6e3a">></span> <span style="color:#999999">)</span>ENGINE<span style="color:#9a6e3a">=</span>Columnstore<span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.34 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>helei<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> insert into helei_cs values<span style="color:#999999">(</span>1,<span style="color:#669900">'1'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
Query OK, 1 row affected <span style="color:#999999">(</span>0.60 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>helei<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> insert into helei_cs values<span style="color:#999999">(</span>2,<span style="color:#669900">'2'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
Query OK, 1 row affected <span style="color:#999999">(</span>0.08 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>helei<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> insert into helei_cs values<span style="color:#999999">(</span>3,<span style="color:#669900">'3'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
Query OK, 1 row affected <span style="color:#999999">(</span>0.17 sec<span style="color:#999999">)</span></code></span></span>

这里可以看出columnstore的插入速度较慢

Warning:警告

columnstore不支持主键、索引、timestamp、collate用法、char\varchar类型的sum/average用法。

Part2:效率测试

1G内存虚拟机MariaDB ColumnStore 2.82s，线上生产库8G的innodb_buffer_pool_size该条慢查询耗时17.894s。

BUG发现

如果pm2的机器挂掉了，按照原本的想法，应该pm1可以继续工作,但无法进行查询，会报错：

ERROR 1815 (HY000): Internal error: st: 10000 TupleBPS::sendPrimitiveMessages() caught an exception: IDB-2034: At least one DBRoot required for that query is offline.

这应该是一个BUG，因为pm是负责拉取数据到内存中进行计算的，数据本文中的实验应该都存放在um机器下。我们来查一下现在表中的数据：

<span style="color:#333333"><span style="color:black"><code class="language-bash">MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> show tables<span style="color:#999999">;</span>
+--------------------+
<span style="color:#9a6e3a">|</span> Tables_in_erp_test <span style="color:#9a6e3a">|</span>
+--------------------+
<span style="color:#9a6e3a">|</span> erp_bjlikp         <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> erp_bjlips         <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> erp_likp           <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> erp_lips           <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> erp_mara           <span style="color:#9a6e3a">|</span>
+--------------------+
5 rows <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_bjlikp<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>        0 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>1.15 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_bjlips<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>        0 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>1.15 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_lips<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>        0 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>1.14 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_mara<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>        0 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>1.15 sec<span style="color:#999999">)</span></code></span></span>

会发现所有的表行数都变为0了

<span style="color:#333333"><span style="color:black"><code class="language-bash">ProcessMonitor      pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
ProcessManager      pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
DBRMControllerNode  pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
ServerMonitor       pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
DBRMWorkerNode      pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
DecomSvr            pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
PrimProc            pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016
WriteEngineServer   pm2       AUTO_OFFLINE      Tue Dec 27 22:23:50 2016</code></span></span>

<span style="color:#333333"><span style="color:black"><code class="language-bash">MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_lips<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>  3147299 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.37 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_mara<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>     4361 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.08 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_bjlips<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>  2762244 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.13 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_bjlikp<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>    19032 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.09 sec<span style="color:#999999">)</span>
MariaDB <span style="color:#999999">[</span>erp_test<span style="color:#999999">]</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> from erp_likp<span style="color:#999999">;</span>
+----------+
<span style="color:#9a6e3a">|</span> count<span style="color:#999999">(</span>*<span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
+----------+
<span style="color:#9a6e3a">|</span>   169002 <span style="color:#9a6e3a">|</span>
+----------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.08 sec<span style="color:#999999">)</span></code></span></span>

pm2机器启动后，发现又恢复正常

这应该是软件的一个bug，应该会在1.0.7GA版本修复。

感谢贺春旸老师指点，本人在测试环境中得以验证该BUG

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。