API tools faq
paste
Guest User

Untitled

a guest
Jul 20th, 2018
130
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.48 KB | None | 0 0
raw download clone embed print report
  1. mysql> select @@version,@@version_comment;
  2. +-----------+--------------------------------------------------------+
  3. | @@version | @@version_comment |
  4. +-----------+--------------------------------------------------------+
  5. | 5.7.22-22 | Percona Server (GPL), Release '22', Revision 'f62d93c' |
  6. +-----------+--------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show create table tok1\G
  10. *************************** 1. row ***************************
  11. Table: tok1
  12. Create Table: CREATE TABLE `tok1` (
  13. `id` int(11) NOT NULL AUTO_INCREMENT,
  14. `a` varchar(50) DEFAULT NULL,
  15. `d` datetime DEFAULT NULL,
  16. PRIMARY KEY (`id`),
  17. KEY `a` (`a`),
  18. KEY `d` (`d`)
  19. ) ENGINE=TokuDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
  20. 1 row in set (0.00 sec)
  21.  
  22.  
  23. mysql> show variables like 'tokudb_%analyze%';
  24. +--------------------------------+-------------------------+
  25. | Variable_name | Value |
  26. +--------------------------------+-------------------------+
  27. | tokudb_analyze_delete_fraction | 1.000000 |
  28. | tokudb_analyze_in_background | ON |
  29. | tokudb_analyze_mode | TOKUDB_ANALYZE_STANDARD |
  30. | tokudb_analyze_throttle | 0 |
  31. | tokudb_analyze_time | 5 |
  32. | tokudb_auto_analyze | 30 |
  33. +--------------------------------+-------------------------+
  34. 6 rows in set (0.00 sec)
  35.  
  36.  
  37. # ./mysql_random_data_loader -h 10.129.7.190 -u root test tok1 10
  38. INFO[2018-07-20T08:22:55Z] Starting
  39. 0s [====================================================================] 100%
  40. INFO[2018-07-20T08:22:56Z] 10 rows inserted
  41.  
  42.  
  43. mysql> show indexes from tok1;
  44. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  45. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  46. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  47. | tok1 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
  48. | tok1 | 1 | a | 1 | a | A | 10 | NULL | NULL | YES | BTREE | | |
  49. | tok1 | 1 | d | 1 | d | A | 10 | NULL | NULL | YES | BTREE | | |
  50. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  51. 3 rows in set (0.00 sec)
  52.  
  53. # ./mysql_random_data_loader -h 10.129.7.190 -u root test tok1 100
  54. INFO[2018-07-20T08:24:46Z] Starting
  55. 0s [====================================================================] 100%
  56. INFO[2018-07-20T08:24:46Z] 100 rows inserted
  57.  
  58. mysql> show indexes from tok1;
  59. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  60. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  61. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  62. | tok1 | 0 | PRIMARY | 1 | id | A | 110 | NULL | NULL | | BTREE | | |
  63. | tok1 | 1 | a | 1 | a | A | 110 | NULL | NULL | YES | BTREE | | |
  64. | tok1 | 1 | d | 1 | d | A | 110 | NULL | NULL | YES | BTREE | | |
  65. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  66. 3 rows in set (0.00 sec)
  67.  
  68. mysql> EXPLAIN select * from tok1 where id>100;
  69. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  70. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  71. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  72. | 1 | SIMPLE | tok1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
  73. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  74. 1 row in set, 1 warning (0.00 sec)
  75.  
  76. mysql> set global tokudb_auto_analyze=0;
  77. Query OK, 0 rows affected (0.00 sec)
  78.  
  79. # ./mysql_random_data_loader -h 10.129.7.190 -u root test tok1 100
  80. INFO[2018-07-20T08:27:50Z] Starting
  81. 0s [====================================================================] 100%
  82. INFO[2018-07-20T08:27:50Z] 100 rows inserted
  83.  
  84. mysql> show indexes from tok1;
  85. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  86. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  87. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  88. | tok1 | 0 | PRIMARY | 1 | id | A | 210 | NULL | NULL | | BTREE | | |
  89. | tok1 | 1 | a | 1 | a | A | 210 | NULL | NULL | YES | BTREE | | |
  90. | tok1 | 1 | d | 1 | d | A | 210 | NULL | NULL | YES | BTREE | | |
  91. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  92. 3 rows in set (0.00 sec)
  93.  
  94. mysql> show table status like 'tok1'\G
  95. *************************** 1. row ***************************
  96. Name: tok1
  97. Engine: TokuDB
  98. Version: 10
  99. Row_format: tokudb_zlib
  100. Rows: 210
  101. Avg_row_length: 49
  102. Data_length: 10439
  103. Max_data_length: 9223372036854775807
  104. Index_length: 11909
  105. Data_free: 75746
  106. Auto_increment: 211
  107. Create_time: 2018-07-20 08:07:34
  108. Update_time: 2018-07-20 08:24:50
  109. Check_time: NULL
  110. Collation: latin1_swedish_ci
  111. Checksum: NULL
  112. Create_options:
  113. Comment:
  114. 1 row in set (0.00 sec)
  115.  
  116. mysql> EXPLAIN select * from tok1 where id>100;
  117. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  118. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  119. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  120. | 1 | SIMPLE | tok1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 110 | 100.00 | Using where |
  121. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  122. 1 row in set, 1 warning (0.00 sec)
  123.  
  124. -- restarted with options in my.cnf:
  125. tokudb_auto_analyze = 0
  126. tokudb_analyze_in_background = 0
  127.  
  128. mysql> show variables like 'tokudb_%analyze%';
  129. +--------------------------------+-------------------------+
  130. | Variable_name | Value |
  131. +--------------------------------+-------------------------+
  132. | tokudb_analyze_delete_fraction | 1.000000 |
  133. | tokudb_analyze_in_background | OFF |
  134. | tokudb_analyze_mode | TOKUDB_ANALYZE_STANDARD |
  135. | tokudb_analyze_throttle | 0 |
  136. | tokudb_analyze_time | 5 |
  137. | tokudb_auto_analyze | 0 |
  138. +--------------------------------+-------------------------+
  139. 6 rows in set (0.00 sec)
  140.  
  141. -- every next insertion by 100 rows updates cardinality and rows count in table status exactly.
  142.  
  143. mysql> select count(*) from tok1;
  144. +----------+
  145. | count(*) |
  146. +----------+
  147. | 710 |
  148. +----------+
  149. 1 row in set (0.00 sec)
  150.  
  151. mysql> show indexes from tok1;
  152. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  153. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  154. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  155. | tok1 | 0 | PRIMARY | 1 | id | A | 710 | NULL | NULL | | BTREE | | |
  156. | tok1 | 1 | a | 1 | a | A | 710 | NULL | NULL | YES | BTREE | | |
  157. | tok1 | 1 | d | 1 | d | A | 710 | NULL | NULL | YES | BTREE | | |
  158. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  159. 3 rows in set (0.00 sec)
  160.  
  161. -- even one row insert updates statistics:
  162.  
  163. mysql> show indexes from tok1;
  164. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  165. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  166. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  167. | tok1 | 0 | PRIMARY | 1 | id | A | 711 | NULL | NULL | | BTREE | | |
  168. | tok1 | 1 | a | 1 | a | A | 711 | NULL | NULL | YES | BTREE | | |
  169. | tok1 | 1 | d | 1 | d | A | 711 | NULL | NULL | YES | BTREE | | |
  170. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  171. 3 rows in set (0.00 sec)
  172.  
  173. mysql> EXPLAIN select * from tok1 where id>100;
  174. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  175. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  176. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  177. | 1 | SIMPLE | tok1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 611 | 100.00 | Using where |
  178. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  179. 1 row in set, 1 warning (0.00 sec)
  180.  
  181. -- changed global to tokudb_analyze_mode='TOKUDB_ANALYZE_CANCEL'
  182.  
  183. mysql> show variables like 'tokudb_%analyze%';
  184. +--------------------------------+-----------------------+
  185. | Variable_name | Value |
  186. +--------------------------------+-----------------------+
  187. | tokudb_analyze_delete_fraction | 1.000000 |
  188. | tokudb_analyze_in_background | OFF |
  189. | tokudb_analyze_mode | TOKUDB_ANALYZE_CANCEL |
  190. | tokudb_analyze_throttle | 0 |
  191. | tokudb_analyze_time | 5 |
  192. | tokudb_auto_analyze | 0 |
  193. +--------------------------------+-----------------------+
  194. 6 rows in set (0.00 sec)
  195.  
  196. -- add 100 more rows:
  197.  
  198. mysql> select count(*) from tok1;
  199. +----------+
  200. | count(*) |
  201. +----------+
  202. | 811 |
  203. +----------+
  204. 1 row in set (0.00 sec)
  205.  
  206. mysql> show indexes from tok1;
  207. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  208. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  209. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  210. | tok1 | 0 | PRIMARY | 1 | id | A | 811 | NULL | NULL | | BTREE | | |
  211. | tok1 | 1 | a | 1 | a | A | 811 | NULL | NULL | YES | BTREE | | |
  212. | tok1 | 1 | d | 1 | d | A | 811 | NULL | NULL | YES | BTREE | | |
  213. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  214. 3 rows in set (0.00 sec)
  215.  
  216. mysql> EXPLAIN select * from tok1 where id>100;
  217. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  218. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  219. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  220. | 1 | SIMPLE | tok1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 711 | 100.00 | Using where |
  221. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  222. 1 row in set, 1 warning (0.00 sec)
  223.  
  224. mysql> delete from tok1 where id>400;
  225. Query OK, 411 rows affected (0.03 sec)
  226.  
  227.  
  228. mysql> EXPLAIN select * from tok1 where id>100;
  229. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  230. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  231. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  232. | 1 | SIMPLE | tok1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 400 | 100.00 | Using where |
  233. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  234. 1 row in set, 1 warning (0.00 sec)
  235.  
  236.  
  237. mysql> select * from INFORMATION_SCHEMA.TOKUDB_BACKGROUND_JOB_STATUS;
  238. Empty set (0.00 sec)
  239.  
  240.  
  241. -- Cardinality for this TokuDB table is 100% accurate at this point, where for similar InnoDB table is not:
  242.  
  243. mysql> select count(*) from inno1;
  244. +----------+
  245. | count(*) |
  246. +----------+
  247. | 800 |
  248. +----------+
  249. 1 row in set (0.01 sec)
  250.  
  251. mysql> show indexes from inno1;
  252. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  253. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  254. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  255. | inno1 | 0 | PRIMARY | 1 | id | A | 559 | NULL | NULL | | BTREE | | |
  256. | inno1 | 1 | a | 1 | a | A | 559 | NULL | NULL | YES | BTREE | | |
  257. | inno1 | 1 | d | 1 | d | A | 559 | NULL | NULL | YES | BTREE | | |
  258. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  259. 3 rows in set (0.00 sec)
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!