{"action":"created","issue":{"url":"https://api.github.com/repos/facebook/mysql-5.6/issues/117","labels_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/117/labels{/name}","comments_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/117/comments","events_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/117/events","html_url":"https://github.com/facebook/mysql-5.6/issues/117","id":120826523,"number":117,"title":"MyRocks does merge sort with small data sets","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":[{"url":"https://api.github.com/repos/facebook/mysql-5.6/labels/myrocks","name":"myrocks","color":"fef2c0"}],"state":"open","locked":false,"assignee":{"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},"milestone":null,"comments":2,"created_at":"2015-12-07T17:29:14Z","updated_at":"2015-12-07T18:03:35Z","closed_at":null,"body":"How to repeat:\r\n```\r\nDROP DATABASE IF EXISTS test29;\r\nCREATE DATABASE test29;\r\nuse test29;\r\n-- reload https://gist.github.com/yoshinorim/31fc436eaf2cdef855ad\r\nshow global status like '%sort%';\r\nSELECT * FROM test29.ti_nk ORDER BY a,b,c;\r\nshow global status like '%sort%';\r\n```\r\n\r\nWhen repeating the above multiple times, sometimes Sort_merge_passes status variable increases by 1. Merge sort should not be chosen, since data size is much smaller than sort_buffer_size.\r\n\r\nI think there are two issues causing this behavior.\r\n1. MyRocks hasn't supported estimate_rows_upper_bound() yet\r\n2. MyRocks table statistics is not always accurate (reporting smaller number of rows) if table is small, because it doesn't always include data in memstore\r\n\r\nRelevant code:\r\n```\r\nsql/filesort.cc\r\n  // If number of rows is not known, use as much of sort buffer as possible.\r\n  num_rows= table->file->estimate_rows_upper_bound();\r\n.....\r\n      ha_rows keys= memory_available / (param.rec_length + sizeof(char*));\r\n      param.max_keys_per_buffer= (uint) min(num_rows, keys);\r\n      table_sort.alloc_sort_buffer(param.max_keys_per_buffer, param.rec_length);\r\n```\r\n\r\nSome storage engines, like Federated/CSV, support estimate_rows_upper_bound() and just returning max bigint unsigned (HA_POS_ERROR). I verified adding the same function in MyRocks solved this problem."},"comment":{"url":"https://api.github.com/repos/facebook/mysql-5.6/issues/comments/162609571","html_url":"https://github.com/facebook/mysql-5.6/issues/117#issuecomment-162609571","issue_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/117","id":162609571,"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},"created_at":"2015-12-07T18:03:35Z","updated_at":"2015-12-07T18:03:35Z","body":"https://reviews.facebook.net/D51639"}}