oqgraph-dev team mailing list archive

Thread
Date
Lets get this moving again - recap, and what to do about integer latches

To: oqgraph-dev@xxxxxxxxxxxxxxxxxxx
From: Andrew McDonnell <bugs@xxxxxxxxxxxxxxxxxxx>
Date: Sun, 28 Apr 2013 19:24:15 +0930
Reply-to: bugs@xxxxxxxxxxxxxxxxxxx
User-agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.6esrpre) Gecko/20120810 Icedove/10.0.6
Hiya

sorry its been a while

Recap:
- I have a branch up on LP with my changes to support the string-based latch
- it also interprets numeric values passed to the latch to support backwards compatibility
- however if the numeric latch is specified as a number instead of a string (e.g. select ... where latch=3   vs.  select ... where latch='3' ) it fails

In recent conversation with Arjen:
>> Although there was some discussion around what to do
>> about legacy instances because of the problem with numeric autocast?
> 
> Is it that the server doesn't use the correct (indexed) access method because of the cast?
> Show me a trace (do use the oqgraph-dev list)
> If that is the case, then perhaps returning an error if a numeric latch is seen might be the solution.


Following is a big dump of test and trace, you probably wont have enough context so I am bracing for clarifying questions :-)

But basically I dont yet have enough understanding of storage engine guts to know how to hook a query before the query optimiser bypasses us


I think the more important question, is how to handle (not) breaking existing deployments.



After all, new databases can just be forced to use the new syntax.


But if any existing db is upgraded, our code will need to properly handle the legacy form on upgrade....
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^





To repeat the issue on my branch:

DROP TABLE IF EXISTS graph;
DROP TABLE IF EXISTS graph_base;
DROP TABLE IF EXISTS graph2;

CREATE TABLE graph_base (
    from_id INT UNSIGNED NOT NULL,
    to_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (from_id,to_id),
    INDEX (to_id)
  ) ENGINE=MyISAM;

CREATE TABLE graph2 (
    latch   VARCHAR(32) NULL,
    origid  BIGINT    UNSIGNED NULL,
    destid  BIGINT    UNSIGNED NULL,
    weight  DOUBLE    NULL,
    seq     BIGINT    UNSIGNED NULL,
    linkid  BIGINT    UNSIGNED NULL,
    KEY (latch, origid, destid) USING HASH,
    KEY (latch, destid, origid) USING HASH
  ) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';

INSERT INTO graph_base(from_id, to_id) VALUES (1,2), (2,1);
INSERT INTO graph_base(from_id, to_id) VALUES (1,3), (3,1);
INSERT INTO graph_base(from_id, to_id) VALUES (3,4), (4,3);
INSERT INTO graph_base(from_id, to_id) VALUES (5,6), (6,5);

-- expected:
-- +-------+--------+--------+--------+------+--------+
-- | latch | origid | destid | weight | seq  | linkid |
-- +-------+--------+--------+--------+------+--------+
-- |     2 |      1 |   NULL |      1 |    3 |      3 |
-- |     2 |      1 |   NULL |      1 |    2 |      2 |
-- +-------+--------+--------+--------+------+--------+
-- reset query cache ; flush query cache;

SELECT * FROM graph2 WHERE latch = 'breadth_first' AND origid = 1 AND weight = 1;   -- works
SELECT * FROM graph2 WHERE latch = '2' AND origid = 1 AND weight = 1;               -- as above
SELECT * FROM graph2 WHERE latch = 2 AND origid = 1 AND weight = 1;                 -- currently, FAILs - returns empty set should be as above



I have a pile of debug output in ha_oqgraph, it can be turned on using 
mysqld <...> -#d,oq-debug
I build with:
cmake -DENABLE_ASSEMBLER=1 -DWITH_EXTRA_CHARSETS=complex -DENABLE_THREAD_SAFE_CLIENT=1 -DWITH_BIG_TABLES=1 -DWITH_PLUGIN_ARIA=1 -DWITH_ARIA_TMP_TABLES=1 -DWITH_READLINE=1 -DCMAKE_BUILD_TYPE=Debug -DWITH_SSL=bundled -DWITH_MAX=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_LIBEVENT=1 -DENABLE_LOCAL_INFILE=1

Log with string select:

THD::decide_logging_format: info: query: SELECT * FROM graph2 WHERE latch = '2' AND origid = 1 AND weight = 1
THD::decide_logging_format: info: variables.binlog_format: 1
THD::decide_logging_format: info: lex->get_stmt_unsafe_flags(): 0x0
THD::decide_logging_format: info: decision: no logging since mysql_bin_log.is_open() = 0 and (options & OPTION_BIN_LOG) = 0x40000 and binlog_format = 1 and binlog_filter->db_ok(db) = 1
Field_iterator_table_ref::set_field_iterator: info: field_it for 'graph2' is Field_iterator_table
my_malloc: info: memory_used: 32696  size: 8152
setup_fields: info: thd->mark_used_columns: 1
setup_fields: info: thd->mark_used_columns: 1
setup_conds: info: thd->mark_used_columns: 1

WHERE:(after remove) 0x2c59ab8 ((`test`.`graph2`.`weight` = 1) and multiple equal('2', `test`.`graph2`.`latch`) and multiple equal(1, `test`.`graph2`.`origid`))
my_malloc: info: memory_used: 40848  size: 1600
SQL_SELECT::test_quick_select: info: records: 8
SQL_SELECT::test_quick_select: info: Time to scan table: 1e9
my_malloc: info: memory_used: 42448  size: 4056
print_sel_tree: info: SEL_TREE: 0x2c8cb00 (tree scans)  scans: latch,latch_2
check_quick_select: oq-debug: records_in_range ::>> inx=0
check_quick_select: oq-debug: records_in_range ::>> key0=2.
check_quick_select: oq-debug: records_in_range ::>> N=10
check_quick_select: oq-debug: records_in_range ::>> inx=1
check_quick_select: oq-debug: records_in_range ::>> key0=2.
print_sel_tree: info: SEL_TREE: 0x2c8cb00 (ROR scans)  scans: (empty)
get_key_scans_params: info: Returning range plan for key latch, cost 3.01, records 10
SQL_SELECT::test_quick_select: info: No range reads possible, trying to construct index_merge
my_malloc: info: memory_used: 46504  size: 128
my_malloc: info: memory_used: 46632  size: 8
my_malloc: info: memory_used: 46640  size: 4056
my_free: info: memory_used: 50696  size: -4056
quick range select, key latch, length: 44
  2/1 <= X <= 2/1
other_keys: 0x0:
best_access_path: info: Considering ref access on key latch
best_access_path: info: Considering ref access on key latch_2
get_best_combination: info: type: 4
my_malloc: info: memory_used: 46640  size: 8152
make_join_select: info: select_cond changes 0x0 -> 0x2c8bc70 at line 8835 tab 0x2c8b308

Info about JOIN
graph2            type: ALL      q_keys: 3  refs: 0  key: -1  len: 0
                  quick select used:
                  quick range select, key latch, length: 44

JOIN::exec: info: Sending data
init_read_record: info: using rr_quick
handler::read_range_first: oq-debug: index_read_idx ::>> search(latch:breadth_first,1,-1)
handler::read_range_first: oq-debug: search() = 0
handler::read_range_first: oq-debug: fill_record() ::>> breadth_first,1,-1,2.000000,4,4
handler::multi_range_read_next: oq-debug: fill_record() ::>> breadth_first,1,-1,1.000000,3,3
handler::multi_range_read_next: oq-debug: fill_record() ::>> breadth_first,1,-1,1.000000,2,2
handler::multi_range_read_next: oq-debug: fill_record() ::>> breadth_first,1,-1,0.000000,1,1
my_free: info: memory_used: 54792  size: -128
my_free: info: memory_used: 54664  size: -4056
my_free: info: memory_used: 50608  size: -8
mi_update_status: info: updating status:  key_file: 3072  data_file: 72  rows: 8
mi_update_status: info: invalidator... './test/graph_base' (status update)
mi_lock_database: info: changed: 0  w_locks: 0
do_select: info: 2 records output
my_free: info: memory_used: 50600  size: -1600
handle_select: info: res: 0  report_error: 0
close_thread_tables: info: thd->open_tables: 0x2c84b00
MDL_context::release_locks_stored_before: info: found lock to release ticket=0x2b205c0
dispatch_command: info: query ready
net_send_eof: info: EOF sent, so no more error sending allowed
my_free: info: memory_used: 49000  size: -8152
my_free: info: memory_used: 40848  size: -8152
do_command: info: Command on socket (35) = 3 (Query)
dispatch_command: info: command: 3
st_select_lex::add_item_to_list: info: Item: 0x2c58d58
mysql_execute_command: info: derived: 0  view: 0
column_bitmaps_signal: info: read_set: 0x2c84c10  write_set: 0x2c84c30
get_lock_data: info: count 1
get_lock_data: info: sql_lock->table_count 1 sql_lock->lock_count 1
lock_external: info: count 1
mi_get_status: info: name: ./test/graph_base.MYI  key_file: 3072  data_file: 72  rows: 8  concurrent_insert: 0


Log with number:



THD::decide_logging_format: info: query: SELECT * FROM graph2 WHERE latch = 2 AND origid = 1 AND weight = 1
THD::decide_logging_format: info: variables.binlog_format: 1
THD::decide_logging_format: info: lex->get_stmt_unsafe_flags(): 0x0
THD::decide_logging_format: info: decision: no logging since mysql_bin_log.is_open() = 0 and (options & OPTION_BIN_LOG) = 0x40000 and binlog_format = 1 and binlog_filter->db_ok(db) = 1
Field_iterator_table_ref::set_field_iterator: info: field_it for 'graph2' is Field_iterator_table
my_malloc: info: memory_used: 32696  size: 8152
setup_fields: info: thd->mark_used_columns: 1
setup_fields: info: thd->mark_used_columns: 1
setup_conds: info: thd->mark_used_columns: 1

WHERE:(after remove) 0x2c59ab0 ((`test`.`graph2`.`latch` = 2) and (`test`.`graph2`.`weight` = 1) and multiple equal(1, `test`.`graph2`.`origid`))
my_malloc: info: memory_used: 40848  size: 1600
SQL_SELECT::test_quick_select: info: records: 8
SQL_SELECT::test_quick_select: info: Time to scan table: 1e9
my_malloc: info: memory_used: 42448  size: 4056
print_sel_tree: info: SEL_TREE: 0x2c8cd08 (tree scans)  scans: (empty)
print_sel_tree: info: SEL_TREE: 0x2c8cd08 (ROR scans)  scans: (empty)
get_key_scans_params: info: No 'range' table read plan found
SQL_SELECT::test_quick_select: info: No range reads possible, trying to construct index_merge
my_free: info: memory_used: 46504  size: -4056
get_best_combination: info: type: 0
make_join_select: info: select_cond changes 0x0 -> 0x2c8b898 at line 8835 tab 0x2c8b0b8

Info about JOIN
graph2            type: ALL      q_keys: 3  refs: 0  key: -1  len: 0
                  select used

JOIN::exec: info: Sending data
init_read_record: info: using rr_sequential
init_io_cache: info: init_io_cache: cachesize = 16384
sub_select: oq-debug: fill_record() ::>> -,1,2,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,2,1,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,1,3,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,3,1,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,3,4,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,4,3,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,5,6,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,6,5,1.000000,-1,-1
mi_update_status: info: updating status:  key_file: 3072  data_file: 72  rows: 8
mi_update_status: info: invalidator... './test/graph_base' (status update)
mi_lock_database: info: changed: 0  w_locks: 0
do_select: info: 0 records output
my_free: info: memory_used: 42448  size: -1600
handle_select: info: res: 0  report_error: 0
close_thread_tables: info: thd->open_tables: 0x2c84b00
MDL_context::release_locks_stored_before: info: found lock to release ticket=0x2b5ad50
dispatch_command: info: query ready
net_send_eof: info: EOF sent, so no more error sending allowed
my_free: info: memory_used: 40848  size: -8152
my_service_thread_sleep: info: sleeping 30000000000 nano seconds
Follow ups

Re: Lets get this moving again - recap, and what to do about integer latches
From: Antony T Curtis, 2013-04-28