{"action":"created","issue":{"url":"https://api.github.com/repos/tempesta-tech/mariadb/issues/314","repository_url":"https://api.github.com/repos/tempesta-tech/mariadb","labels_url":"https://api.github.com/repos/tempesta-tech/mariadb/issues/314/labels{/name}","comments_url":"https://api.github.com/repos/tempesta-tech/mariadb/issues/314/comments","events_url":"https://api.github.com/repos/tempesta-tech/mariadb/issues/314/events","html_url":"https://github.com/tempesta-tech/mariadb/issues/314","id":272545178,"node_id":"MDU6SXNzdWUyNzI1NDUxNzg=","number":314,"title":"MDEV-16226 TRX_ID-based versioned tables performance improvement","user":{"login":"midenok","id":3307037,"node_id":"MDQ6VXNlcjMzMDcwMzc=","avatar_url":"https://avatars2.githubusercontent.com/u/3307037?v=4","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false},"labels":[{"id":446169241,"node_id":"MDU6TGFiZWw0NDYxNjkyNDE=","url":"https://api.github.com/repos/tempesta-tech/mariadb/labels/info","name":"info","color":"000000","default":false},{"id":388954542,"node_id":"MDU6TGFiZWwzODg5NTQ1NDI=","url":"https://api.github.com/repos/tempesta-tech/mariadb/labels/performance","name":"performance","color":"ffaad0","default":false},{"id":442030504,"node_id":"MDU6TGFiZWw0NDIwMzA1MDQ=","url":"https://api.github.com/repos/tempesta-tech/mariadb/labels/task","name":"task","color":"0e8a16","default":false}],"state":"open","locked":false,"assignee":{"login":"midenok","id":3307037,"node_id":"MDQ6VXNlcjMzMDcwMzc=","avatar_url":"https://avatars2.githubusercontent.com/u/3307037?v=4","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false},"assignees":[{"login":"midenok","id":3307037,"node_id":"MDQ6VXNlcjMzMDcwMzc=","avatar_url":"https://avatars2.githubusercontent.com/u/3307037?v=4","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/tempesta-tech/mariadb/milestones/17","html_url":"https://github.com/tempesta-tech/mariadb/milestone/17","labels_url":"https://api.github.com/repos/tempesta-tech/mariadb/milestones/17/labels","id":2919746,"node_id":"MDk6TWlsZXN0b25lMjkxOTc0Ng==","number":17,"title":"1.1: First priority tasks after merge","description":"","creator":{"login":"midenok","id":3307037,"node_id":"MDQ6VXNlcjMzMDcwMzc=","avatar_url":"https://avatars2.githubusercontent.com/u/3307037?v=4","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false},"open_issues":8,"closed_issues":39,"state":"open","created_at":"2017-11-17T13:43:00Z","updated_at":"2018-06-01T08:58:04Z","due_on":null,"closed_at":null},"comments":41,"created_at":"2017-11-09T12:21:41Z","updated_at":"2018-06-01T19:59:32Z","closed_at":null,"author_association":"NONE","body":"### Objectives\r\n1. Make index used (see comments in code).\r\n\r\n2. Also as [commented](https://github.com/tempesta-tech/mariadb/pull/313#pullrequestreview-75387477) on `TR_table::query()`:\r\n> Allocations on every examined row. This memory won't be freed until the query end, right? So such query on a big table will eat too much memory. I don't think this code scales well. We need to create READ_RECORD and surrounding stuff once per query and reuse. Let it be future work. For now I suggest to add a comment or open and issue on that topic.\r\n\r\n### Reproduce\r\n```sql\r\ncreate or replace table i1 (\r\n  x int,\r\n  row_start bigint(20) unsigned as row start invisible,\r\n  row_end bigint(20) unsigned as row end invisible,\r\n  period for system_time (row_start, row_end))\r\nwith system versioning\r\nengine innodb;\r\n\r\ninsert into i1 values (1);\r\nset @t1= now(6);\r\ndelete from i1;\r\ninsert into i1 values (2);\r\n\r\nselect *, row_start, row_end from i1 for system_time as of timestamp @t1;\r\n```\r\n### Expected\r\n1 row with x == 1;\r\n\r\n### Variations (implementation approach)\r\n1. Utilize `rr_quick()` in `make_select()` ([see below](https://github.com/tempesta-tech/mariadb/issues/314#issuecomment-347277304));\r\n2. Access TRT via fully-processed subquery ([see below](https://github.com/tempesta-tech/mariadb/issues/314#issuecomment-378863625)).\r\n\r\n**Approach 1.** shows [x2 performance boost](https://github.com/tempesta-tech/mariadb/issues/314#issuecomment-378245194), but absolute value is anyway too low, so it was chosen to use **Approach 2.**\r\n\r\n### Scripts\r\n* [t2.sql](https://gist.github.com/midenok/22f260f7ba209741b5f35462d098f17b)\r\n* [perf.sql](https://gist.github.com/midenok/021e2c6d05a08aaf8c46b69139e4e992)\r\n\r\n### Approach 2. TODO\r\n- [x] Generate TRT subquery name;\r\n- [x] TRT subquery must be added for each timestamp specifier;\r\n- [x] `__trt_subquery` derived table must be excluded from wildcard expansion;\r\n- [ ] Remove TR_table::query() code\r\n- [ ] TRT subqueries must be removed if they are not used (table is not trx_id-based) or not added at all.\r\n- [ ] Forced versioning crash test.\r\n- [ ] Test versioning suite with `--repeat=N`.\r\n\r\n\r\n### Description\r\nCurrent performance of `SELECT` by timestamp from TRX_ID-based tables is very low due to linear scan of TRANSACTION_REGISTRY table when doing timestamp -> TRX_ID translation. Utilize join optimizer for querying TRANSACTION_REGISTRY:\r\n\r\n1. For each timestamped `Vers_history_point` selector forge `TRANSACTION_REGISTRY` subquery and add it to `SELECT`.\r\n\r\n#### Example\r\nQuery\r\n```sql\r\nselect *, row_start, row_end from i1 for system_time as of timestamp @t1;\r\n```\r\nis transformed into\r\n```sql\r\nselect i1.x as x,\r\n       i1.row_start as row_start,\r\n       i1.row_end as row_end\r\nfrom i1\r\nfor SYSTEM_TIME all\r\njoin (\r\n   select transaction_id\r\n   from mysql.transaction_registry\r\n   where commit_timestamp <= @t1\r\n   order by commit_timestamp desc\r\n   limit 1\r\n) __trt_0\r\nwhere trt_trx_sees(i1.row_end, __trt_0.transaction_id)\r\n  and trt_trx_sees_eq(__trt_0.transaction_id, i1.row_start)\r\n```\r\n\r\n2. subquery must be excluded from wildcard expansion, i.e.\r\n```sql\r\nselect transaction_id from i1 for system_time as of timestamp @t1;\r\n```\r\nmust be resolved to `i1.transaction_id`."},"comment":{"url":"https://api.github.com/repos/tempesta-tech/mariadb/issues/comments/393993264","html_url":"https://github.com/tempesta-tech/mariadb/issues/314#issuecomment-393993264","issue_url":"https://api.github.com/repos/tempesta-tech/mariadb/issues/314","id":393993264,"node_id":"MDEyOklzc3VlQ29tbWVudDM5Mzk5MzI2NA==","user":{"login":"midenok","id":3307037,"node_id":"MDQ6VXNlcjMzMDcwMzc=","avatar_url":"https://avatars2.githubusercontent.com/u/3307037?v=4","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false},"created_at":"2018-06-01T19:59:32Z","updated_at":"2018-06-01T19:59:32Z","author_association":"NONE","body":"### Info: `join_list` increased\r\n```c++\r\n#0  base_list::push_front_impl (this=0x7fff800054e8, node=0x7fff8007af60) at /home/midenok/src/mariadb/trunk/src/sql/sql_list.h:201\r\n#1  0x00000000006c3dc1 in base_list::push_front (this=0x7fff800054e8, info=0x7fff8007a908, mem_root=0x7fff80006278) at /home/midenok/src/mariadb/trunk/src/sql/sql_list.h:208\r\n#2  0x00000000007cb220 in List<TABLE_LIST>::push_front (this=0x7fff800054e8, a=0x7fff8007a908, mem_root=0x7fff80006278) at /home/midenok/src/mariadb/trunk/src/sql/sql_list.h:496\r\n#3  0x00000000007c6722 in st_select_lex::add_joined_table (this=0x7fff80005348, table=0x7fff8007a908) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8503\r\n#4  0x0000000000a4b8c0 in MYSQLparse (thd=0x7fff80000d50) at /home/midenok/src/mariadb/trunk/src/sql/sql_yacc.yy:11801\r\n#5  0x00000000007c588a in parse_sql (thd=0x7fff80000d50, parser_state=0x7fffe4f02650, creation_ctx=0x0, do_pfs_digest=true) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:10030\r\n#6  0x00000000007b08e0 in mysql_parse (thd=0x7fff80000d50, rawbuf=0x7fff8007a718 \"select * from t1\", length=16, parser_state=0x7fffe4f02650, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7972\r\n```"}}
{"actor":{"display_login":"midenok"}}