<<
>>

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

张映 发表于 2021-03-03

分类目录： tidb

标签：, ,

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

1，检查tiflash是不是开启

查看复制打印?
  1. $ tiup ctl pd -u http://10.0.10.17:2379 -i  //-i交互模式  
  2. Found ctl newer version:  
  3.   
  4.     The latest version:         v5.0.0-rc  
  5.     Local installed version:    v4.0.9  
  6.     Update current component:   tiup update ctl  
  7.     Update all components:      tiup update --all  
  8.   
  9. Starting component `ctl`: /home/tidb/.tiup/components/ctl/v4.0.9/ctl pd -u http://10.0.10.17:2379 -i  
  10. » config show replication  
  11. {  
  12.   "max-replicas": 3,  
  13.   "location-labels": "",  
  14.   "strictly-match-label": "false",  
  15.   "enable-placement-rules": "true"   //如果为ture就开启了  
  16. }  
  17.   
  18. » config placement-rules enable     //开启  
  19. » config placement-rules disable    //关闭  
  20.   
  21. » config show all //能看到集群中很多配置，但是新手不建议调整  

2，创建测试表

查看复制打印?
  1. mysql> CREATE TABLE `user` (  
  2.     ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',  
  3.     ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT 'sex',  
  4.     ->   `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,  
  5.     ->   PRIMARY KEY (`id`)  
  6.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='user';  
  7. Query OK, 0 rows affected (0.19 sec)  
  8.   
  9. mysql> insert into user(sex,name)values(1,'tank');  
  10. Query OK, 1 row affected (0.04 sec)  
  11.   
  12. mysql> desc select * from user;  
  13. +------------------------+----------+-----------+---------------+------------------------------------------------+  
  14. | id                     | estRows  | task      | access object | operator info                                  |  
  15. +------------------------+----------+-----------+---------------+------------------------------------------------+  
  16. | TableReader_5          | 10000.00 | root      |               | data:TableRangeScan_4                          |  
  17. | └─TableRangeScan_4     | 10000.00 | cop[tikv] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |  
  18. +------------------------+----------+-----------+---------------+------------------------------------------------+  
  19. 2 rows in set (0.00 sec)  

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

3，创建tiflash副本

  1. mysql> ALTER TABLE user SET TIFLASH REPLICA 2;  
  2. Query OK, 0 rows affected (0.29 sec)  
  3.   
  4. mysql> desc select * from user;  
  5. +------------------------+---------+-----------+---------------+------------------------------------------------+  
  6. | id                     | estRows | task      | access object | operator info                                  |  
  7. +------------------------+---------+-----------+---------------+------------------------------------------------+  
  8. | TableReader_5          | 1.00    | root      |               | data:TableRangeScan_4                          |  
  9. | └─TableRangeScan_4     | 1.00    | cop[tikv] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |  
  10. +------------------------+---------+-----------+---------------+------------------------------------------------+  
  11. 2 rows in set (0.00 sec)  

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

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

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

查看复制打印?
  1. mysql> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'user';  
  2. +--------------+------------+----------+---------------+-----------------+-----------+----------+  
  3. | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |  
  4. +--------------+------------+----------+---------------+-----------------+-----------+----------+  
  5. | test         | user       |     9062 |             2 |                 |         1 |        1 |  
  6. +--------------+------------+----------+---------------+-----------------+-----------+----------+  
  7. 1 row in set (0.00 sec)  
  8.   
  9. mysql> SELECT * FROM information_schema.tiflash_replica;  
  10. +--------------+------------+----------+---------------+-----------------+-----------+----------+  
  11. | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |  
  12. +--------------+------------+----------+---------------+-----------------+-----------+----------+  
  13. | test         | user       |     9062 |             2 |                 |         1 |        1 |  
  14. +--------------+------------+----------+---------------+-----------------+-----------+----------+  
  15. 1 row in set (0.00 sec)  

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

查看复制打印?
  1. mysql> select @@tidb_isolation_read_engines;   //读的引擎有3种  
  2. +-------------------------------+  
  3. | @@tidb_isolation_read_engines |  
  4. +-------------------------------+  
  5. | tikv, tiflash, tidb           |  
  6. +-------------------------------+  
  7. 1 row in set (0.00 sec)  
  8.   
  9. mysql> desc select /*+ read_from_storage(tikv[user] )*/ * from user;   //强制使用tikv  
  10. +------------------------+---------+-----------+---------------+------------------------------------------------+  
  11. | id                     | estRows | task      | access object | operator info                                  |  
  12. +------------------------+---------+-----------+---------------+------------------------------------------------+  
  13. | TableReader_5          | 1.00    | root      |               | data:TableRangeScan_4                          |  
  14. | └─TableRangeScan_4     | 1.00    | cop[tikv] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |  
  15. +------------------------+---------+-----------+---------------+------------------------------------------------+  
  16. 2 rows in set (0.00 sec)  
  17.   
  18. mysql> desc select /*+ read_from_storage(tiflash[user] )*/ * from user;  //强制使用tiflash  
  19. +------------------------+---------+--------------+---------------+------------------------------------------------+  
  20. | id                     | estRows | task         | access object | operator info                                  |  
  21. +------------------------+---------+--------------+---------------+------------------------------------------------+  
  22. | TableReader_5          | 1.00    | root         |               | data:TableRangeScan_4                          |  
  23. | └─TableRangeScan_4     | 1.00    | cop[tiflash] | table:user    | range:[0,+inf], keep order:false, stats:pseudo |  
  24. +------------------------+---------+--------------+---------------+------------------------------------------------+  
  25. 2 rows in set (0.00 sec)  

5，正式数据测试速度

查看复制打印?
  1. mysql> desc select count(1) from video_report;  
  2. +----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+  
  3. | id                         | estRows    | task      | access object                                                     | operator info                     |  
  4. +----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+  
  5. | StreamAgg_24               | 1.00       | root      |                                                                   | funcs:count(Column#88)->Column#77 |  
  6. | └─IndexReader_25           | 1.00       | root      |                                                                   | index:StreamAgg_8                 |  
  7. |   └─StreamAgg_8            | 1.00       | cop[tikv] |                                                                   | funcs:count(1)->Column#88         |  
  8. |     └─IndexFullScan_23     | 8123356.00 | cop[tikv] | table:video_report, index:video_report_signature_index(signature) | keep order:false                  |  
  9. +----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+  
  10. 4 rows in set (0.00 sec)  
  11.   
  12. mysql> select count(1) from video_report;  
  13. +----------+  
  14. | count(1) |  
  15. +----------+  
  16. |  8218538 |  
  17. +----------+  
  18. 1 row in set (0.61 sec)  
  19.   
  20. mysql> ALTER TABLE video_report SET TIFLASH REPLICA 2;  
  21. Query OK, 0 rows affected (0.25 sec)  
  22.   
  23. mysql> SELECT * FROM information_schema.tiflash_replica;  
  24. +--------------+--------------+----------+---------------+-----------------+-----------+----------+  
  25. | TABLE_SCHEMA | TABLE_NAME   | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |  
  26. +--------------+--------------+----------+---------------+-----------------+-----------+----------+  
  27. | result_ziwei | video_report |     9030 |             2 |                 |         0 |     0.05 |  
  28. | test         | user         |     9062 |             2 |                 |         1 |        1 |  
  29. +--------------+--------------+----------+---------------+-----------------+-----------+----------+  
  30. 2 rows in set (0.00 sec)  
  31.   
  32. mysql> select count(1) from video_report;  
  33. +----------+  
  34. | count(1) |  
  35. +----------+  
  36. |  8218538 |  
  37. +----------+  
  38. 1 row in set (0.05 sec)  

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

6，删除tiflash副本

  1. mysql> ALTER TABLE user SET TIFLASH REPLICA 0;  
  2. Query OK, 0 rows affected (0.18 sec)  
  3.   
  4. mysql> SELECT * FROM information_schema.tiflash_replica;  
  5. +--------------+--------------+----------+---------------+-----------------+-----------+----------+  
  6. | TABLE_SCHEMA | TABLE_NAME   | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |  
  7. +--------------+--------------+----------+---------------+-----------------+-----------+----------+  
  8. | result_ziwei | video_report |     9030 |             2 |                 |         1 |        1 |  
  9. +--------------+--------------+----------+---------------+-----------------+-----------+----------+  
  10. 1 row in set (0.00 sec)  

 



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

相关文章