数据量比大的情况下tiflash比tikv快很多

张映 发表于 2021-03-03

分类目录: tidb

标签:, ,

tiflash列式存储,tikv是行式存储,存储上就占有优势。tidb中创建表,默认都是tikv的。

1,检查tiflash是不是开启

$ tiup ctl pd -u http://10.0.10.17:2379 -i  //-i交互模式
Found ctl newer version:

    The latest version:         v5.0.0-rc
    Local installed version:    v4.0.9
    Update current component:   tiup update ctl
    Update all components:      tiup update --all

Starting component `ctl`: /home/tidb/.tiup/components/ctl/v4.0.9/ctl pd -u http://10.0.10.17:2379 -i
» config show replication
{
  "max-replicas": 3,
  "location-labels": "",
  "strictly-match-label": "false",
  "enable-placement-rules": "true"   //如果为ture就开启了
}

» config placement-rules enable     //开启
» config placement-rules disable    //关闭

» config show all //能看到集群中很多配置,但是新手不建议调整

2,创建测试表

mysql> CREATE TABLE `user` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT 'sex',
    ->   `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='user';
Query OK, 0 rows affected (0.19 sec)

mysql> insert into user(sex,name)values(1,'tank');
Query OK, 1 row affected (0.04 sec)

mysql> desc select * from user;
+------------------------+----------+-----------+---------------+------------------------------------------------+
| id                     | estRows  | task      | access object | operator info                                  |
+------------------------+----------+-----------+---------------+------------------------------------------------+
| TableReader_5          | 10000.00 | root      |               | data:TableRangeScan_4                          |
| └─TableRangeScan_4     | 10000.00 | cop[tikv] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |
+------------------------+----------+-----------+---------------+------------------------------------------------+
2 rows in set (0.00 sec)

cop[tikv],走的存储引擎是tikv

3,创建tiflash副本

mysql> ALTER TABLE user SET TIFLASH REPLICA 2;
Query OK, 0 rows affected (0.29 sec)

mysql> desc select * from user;
+------------------------+---------+-----------+---------------+------------------------------------------------+
| id                     | estRows | task      | access object | operator info                                  |
+------------------------+---------+-----------+---------------+------------------------------------------------+
| TableReader_5          | 1.00    | root      |               | data:TableRangeScan_4                          |
| └─TableRangeScan_4     | 1.00    | cop[tikv] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------+------------------------------------------------+
2 rows in set (0.00 sec)

创建tiflash副本后,还是走的tikv。看看官方说明,对于创建了 TiFlash 副本的表,TiDB 优化器会自动根据代价估算选择是否使用 TiFlash 副本。具体有没有选择 TiFlash 副本,可以通过 desc 或 explain analyze 语句查看。

也就是说用不用tiflash,tidb自己选择

4,查看tiflash副本是不是创建成功

mysql> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'user';
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test         | user       |     9062 |             2 |                 |         1 |        1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.tiflash_replica;
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test         | user       |     9062 |             2 |                 |         1 |        1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)

AVAILABLE表示可用,PROGRESS表示生成副本的进度

mysql> select @@tidb_isolation_read_engines;   //读的引擎有3种
+-------------------------------+
| @@tidb_isolation_read_engines |
+-------------------------------+
| tikv, tiflash, tidb           |
+-------------------------------+
1 row in set (0.00 sec)

mysql> desc select /*+ read_from_storage(tikv[user] )*/ * from user;   //强制使用tikv
+------------------------+---------+-----------+---------------+------------------------------------------------+
| id                     | estRows | task      | access object | operator info                                  |
+------------------------+---------+-----------+---------------+------------------------------------------------+
| TableReader_5          | 1.00    | root      |               | data:TableRangeScan_4                          |
| └─TableRangeScan_4     | 1.00    | cop[tikv] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------+------------------------------------------------+
2 rows in set (0.00 sec)

mysql> desc select /*+ read_from_storage(tiflash[user] )*/ * from user;  //强制使用tiflash
+------------------------+---------+--------------+---------------+------------------------------------------------+
| id                     | estRows | task         | access object | operator info                                  |
+------------------------+---------+--------------+---------------+------------------------------------------------+
| TableReader_5          | 1.00    | root         |               | data:TableRangeScan_4                          |
| └─TableRangeScan_4     | 1.00    | cop[tiflash] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |
+------------------------+---------+--------------+---------------+------------------------------------------------+
2 rows in set (0.00 sec)

5,正式数据测试速度

mysql> desc select count(1) from video_report;
+----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+
| id                         | estRows    | task      | access object                                                     | operator info                     |
+----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+
| StreamAgg_24               | 1.00       | root      |                                                                   | funcs:count(Column#88)->Column#77 |
| └─IndexReader_25           | 1.00       | root      |                                                                   | index:StreamAgg_8                 |
|   └─StreamAgg_8            | 1.00       | cop[tikv] |                                                                   | funcs:count(1)->Column#88         |
|     └─IndexFullScan_23     | 8123356.00 | cop[tikv] | table:video_report, index:video_report_signature_index(signature) | keep order:false                  |
+----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+
4 rows in set (0.00 sec)

mysql> select count(1) from video_report;
+----------+
| count(1) |
+----------+
|  8218538 |
+----------+
1 row in set (0.61 sec)

mysql> ALTER TABLE video_report SET TIFLASH REPLICA 2;
Query OK, 0 rows affected (0.25 sec)

mysql> SELECT * FROM information_schema.tiflash_replica;
+--------------+--------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME   | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+--------------+----------+---------------+-----------------+-----------+----------+
| result_ziwei | video_report |     9030 |             2 |                 |         0 |     0.05 |
| test         | user         |     9062 |             2 |                 |         1 |        1 |
+--------------+--------------+----------+---------------+-----------------+-----------+----------+
2 rows in set (0.00 sec)

mysql> select count(1) from video_report;
+----------+
| count(1) |
+----------+
|  8218538 |
+----------+
1 row in set (0.05 sec)

要等一会,等到AVAILABLE变成1,PROGRESS变成1才能测试,测试的结果800多万,tiflash比tikv要快10倍左右。

6,删除tiflash副本

mysql> ALTER TABLE user SET TIFLASH REPLICA 0;
Query OK, 0 rows affected (0.18 sec)

mysql> SELECT * FROM information_schema.tiflash_replica;
+--------------+--------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME   | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+--------------+----------+---------------+-----------------+-----------+----------+
| result_ziwei | video_report |     9030 |             2 |                 |         1 |        1 |
+--------------+--------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)

 



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/tidb/2508.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   2X2=?