{"action":"created","issue":{"url":"https://api.github.com/repos/facebook/mysql-5.6/issues/102","labels_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/102/labels{/name}","comments_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/102/comments","events_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/102/events","html_url":"https://github.com/facebook/mysql-5.6/issues/102","id":116105516,"number":102,"title":"MyRocks index statistics can be wrong after sequential insert","user":{"login":"spetrunia","id":2734550,"avatar_url":"https://avatars.githubusercontent.com/u/2734550?v=3","gravatar_id":"","url":"https://api.github.com/users/spetrunia","html_url":"https://github.com/spetrunia","followers_url":"https://api.github.com/users/spetrunia/followers","following_url":"https://api.github.com/users/spetrunia/following{/other_user}","gists_url":"https://api.github.com/users/spetrunia/gists{/gist_id}","starred_url":"https://api.github.com/users/spetrunia/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/spetrunia/subscriptions","organizations_url":"https://api.github.com/users/spetrunia/orgs","repos_url":"https://api.github.com/users/spetrunia/repos","events_url":"https://api.github.com/users/spetrunia/events{/privacy}","received_events_url":"https://api.github.com/users/spetrunia/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-11-10T13:25:42Z","updated_at":"2015-11-10T16:10:22Z","closed_at":"2015-11-10T16:10:22Z","body":"Filing this separately from issue #100, because this example doesn't delete any key-value pairs:\r\n\r\n```\r\ncreate table ten(a int primary key);\r\ninsert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);\r\n\r\ncreate table one_k(a int primary key);\r\ninsert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;\r\n\r\ncreate table t1 (\r\n  pk int primary key, \r\n  a int,\r\n  b int,\r\n  c int,\r\n  col1 varchar(64), \r\n  col2 varchar(64),\r\n  key(a)\r\n) engine=rocksdb;\r\nset @a=-1;\r\n```\r\n\r\nThen\r\n```\r\ninsert into t1 \r\nselect @a:=@a+1, @a, @a, 12345, repeat('cd', 32), repeat('de',32)\r\nfrom\r\n one_k A, one_k B where B.a <100;\r\n```\r\nRun the above 9-10 times. Then run OPTIMIZE TABLE:\r\n\r\n```\r\noptimize table t1;\r\n+---------+----------+----------+----------+\r\n| Table   | Op       | Msg_type | Msg_text |\r\n+---------+----------+----------+----------+\r\n| test.t1 | optimize | status   | OK       |\r\n+---------+----------+----------+----------+\r\n1 row in set (40.89 sec)\r\n```\r\n\r\nAs a result, all files are on L3:\r\n\r\n```\r\n| CF_COMPACTION | default    | \r\n** Compaction Stats [default] **\r\nLevel    Files   Size(MB) Score Read(GB)  Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) Comp(cnt) Avg(sec) Stall(cnt)  KeyIn KeyDrop\r\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n  L0      0/0       0.00   0.0      0.0     0.0      0.0       0.1      0.1       0.0   0.0      0.0     27.2         6         9    0.615          0       0      0\r\n  L1      0/0       0.00   0.0      0.2     0.1      0.0       0.2      0.1       0.0   1.1     10.9     10.9        15         3    5.062          0   1801K      0\r\n  L2      0/0       0.00   0.0      0.1     0.1      0.0       0.1      0.1       0.1   1.1      9.7      9.7        10        17    0.595          0   1311K      0\r\n  L3     75/0     150.47   0.2      0.3     0.1      0.2       0.3      0.1       0.0   2.2     10.0     10.0        28       131    0.213          0   3528K      0\r\n Sum     75/0     150.47   0.0      0.5     0.4      0.2       0.7      0.5       0.1   4.6      9.2     11.8        59       160    0.367          0   6641K      0\r\n Int      0/0       0.00   0.0      0.3     0.2      0.2       0.3      0.2       0.0  20.9      9.2      9.6        36       146    0.249          0   4711K      0\r\nFlush(GB): cumulative 0.147, interval 0.016\r\nStalls(count): 0 level0_slowdown, 0 level0_slowdown_with_compaction, 0 level0_numfiles, 0 level0_numfiles_with_compaction, 0 pending_compaction_bytes, 0 memtable_compaction, 0 leveln_slowdown_soft, 0 leveln_slowdown_hard\r\n```\r\n\r\nBoth PK and 'a' columns have many distinct values:\r\n\r\n```\r\nmysql> select count(distinct a) from t1; \r\n| count(distinct a) |\r\n+-------------------+\r\n|            900000 |\r\n+-------------------+\r\n1 row in set (3.51 sec)\r\n\r\nmysql> select count(distinct pk) from t1;\r\n+--------------------+\r\n| count(distinct pk) |\r\n+--------------------+\r\n|             900000 |\r\n+--------------------+\r\n1 row in set (3.49 sec)\r\n```\r\n\r\nBut statistics shows that there are only 1K and 500, respectively:\r\n\r\n```\r\nmysql> show keys in t1;\r\n+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |\r\n+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\r\n| t1    |          0 | PRIMARY  |            1 | pk          | A         |        1000 |     NULL | NULL   |      | BTREE      |         |               |\r\n| t1    |          1 | a        |            1 | a           | A         |         500 |     NULL | NULL   | YES  | BTREE      |         |               |\r\n+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\r\n2 rows in set (0.00 sec)\r\n```\r\n\r\nSatistics about total #rows is wrong also:\r\n\r\n```\r\nmysql> explain select * from t1;\r\n+----+-------------+-------+------+---------------+------+---------+------+------+-------+\r\n| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |\r\n+----+-------------+-------+------+---------------+------+---------+------+------+-------+\r\n|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 | NULL  |\r\n+----+-------------+-------+------+---------------+------+---------+------+------+-------+\r\n1 row in set (0.00 sec)\r\n```\r\n\r\nServer restart doesn't change the situation.\r\n\r\nI am not sure why this happens.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/facebook/mysql-5.6/issues/comments/155467444","html_url":"https://github.com/facebook/mysql-5.6/issues/102#issuecomment-155467444","issue_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/102","id":155467444,"user":{"login":"spetrunia","id":2734550,"avatar_url":"https://avatars.githubusercontent.com/u/2734550?v=3","gravatar_id":"","url":"https://api.github.com/users/spetrunia","html_url":"https://github.com/spetrunia","followers_url":"https://api.github.com/users/spetrunia/followers","following_url":"https://api.github.com/users/spetrunia/following{/other_user}","gists_url":"https://api.github.com/users/spetrunia/gists{/gist_id}","starred_url":"https://api.github.com/users/spetrunia/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/spetrunia/subscriptions","organizations_url":"https://api.github.com/users/spetrunia/orgs","repos_url":"https://api.github.com/users/spetrunia/repos","events_url":"https://api.github.com/users/spetrunia/events{/privacy}","received_events_url":"https://api.github.com/users/spetrunia/received_events","type":"User","site_admin":false},"created_at":"2015-11-10T16:10:22Z","updated_at":"2015-11-10T16:10:22Z","body":"Ah, was fooled by @@rocksdb_seconds_between_stat_recomputes being too big."}}