{"action":"opened","issue":{"url":"https://api.github.com/repos/MariaDB/webscalesql-5.6/issues/32","labels_url":"https://api.github.com/repos/MariaDB/webscalesql-5.6/issues/32/labels{/name}","comments_url":"https://api.github.com/repos/MariaDB/webscalesql-5.6/issues/32/comments","events_url":"https://api.github.com/repos/MariaDB/webscalesql-5.6/issues/32/events","html_url":"https://github.com/MariaDB/webscalesql-5.6/issues/32","id":53550122,"number":32,"title":"Bug: Doubling space by primary key","user":{"login":"yoshinorim","id":253614,"avatar_url":"https://avatars.githubusercontent.com/u/253614?v=3","gravatar_id":"","url":"https://api.github.com/users/yoshinorim","html_url":"https://github.com/yoshinorim","followers_url":"https://api.github.com/users/yoshinorim/followers","following_url":"https://api.github.com/users/yoshinorim/following{/other_user}","gists_url":"https://api.github.com/users/yoshinorim/gists{/gist_id}","starred_url":"https://api.github.com/users/yoshinorim/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yoshinorim/subscriptions","organizations_url":"https://api.github.com/users/yoshinorim/orgs","repos_url":"https://api.github.com/users/yoshinorim/repos","events_url":"https://api.github.com/users/yoshinorim/events{/privacy}","received_events_url":"https://api.github.com/users/yoshinorim/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"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},"milestone":null,"comments":0,"created_at":"2015-01-06T19:05:12Z","updated_at":"2015-01-06T19:05:12Z","closed_at":null,"body":"I just noticed primary keys are stored twice in RocksDB SE – as both key and value, even though\r\nthey are not needed to be stored as value.\r\n\r\nHow to repeat:\r\n\r\n# 10 column primary key\r\ncreate table r1 (\r\nid1 bigint not null,\r\nid2 bigint not null,\r\nid3 bigint not null,\r\nid4 bigint not null,\r\nid5 bigint not null,\r\nid6 bigint not null,\r\nid7 bigint not null,\r\nid8 bigint not null,\r\nid9 bigint not null,\r\nid10 bigint not null,\r\nid11 bigint not null,\r\nid12 bigint not null,\r\nprimary key (id1, id2, id3, id4, id5, id6, id7, id8, id9, id10)\r\n) engine=rocksdb charset=latin1 collate=latin1_bin;\r\n\r\n# 1 column primary key\r\ncreate table r2 (\r\nid1 bigint not null,\r\nid2 bigint not null,\r\nid3 bigint not null,\r\nid4 bigint not null,\r\nid5 bigint not null,\r\nid6 bigint not null,\r\nid7 bigint not null,\r\nid8 bigint not null,\r\nid9 bigint not null,\r\nid10 bigint not null,\r\nid11 bigint not null,\r\nid12 bigint not null,\r\nprimary key (id1)\r\n) engine=rocksdb charset=latin1 collate=latin1_bin;\r\n\r\nInserting 2 million rows on these tables. Values sequentially increasing from 1 to 2 million (all columns have same values per row) -- can be generated like this.\r\nfor(my $i=1; $i<= 2000000; $i++) {\r\n  for(my $j=0; $j < 12; $j++) {\r\n    if($j < 11) {\r\n      print \"$i,\";\r\n    }else {\r\n      print \"$i\\n\";\r\n    }\r\n  }\r\n}\r\n\r\nthen make compaction happen.\r\n\r\nSize in KB:\r\nid1-id10 pk:\r\n353764\r\n\r\nid1 pk:\r\n212984\r\n\r\ninnodb\r\nid1-id10 pk:\r\n264241\r\n\r\nid1 pk:\r\n260046\r\n\r\nSo basically long primary keys increase on-disk space.\r\n\r\nI also checked via gdb (setting breakpoint at rocksdb::WriteBatch::Put()) and confirmed.\r\ninsert into r1 values (1,1,1,1,1,1,1,1,1,1,1,1);\r\n=>\r\n(gdb) p key.size_\r\n$1 = 84\r\n(gdb) p value.size_\r\n$2 = 96\r\n\r\ninsert into r2 values (1,1,1,1,1,1,1,1,1,1,1,1);\r\n=>\r\n(gdb) p key.size_\r\n$3 = 12\r\n(gdb) p value.size_\r\n$4 = 96\r\n\r\nI expected value.size_ were 12 and 84 respectively. Since both key fields and value fields are stored, duplicate fields can be removed from value fields."}}