1result_format: 2 2 3 4SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE, 5 PLUGIN_LIBRARY,PLUGIN_LIBRARY_VERSION,PLUGIN_AUTHOR,PLUGIN_DESCRIPTION 6FROM information_schema.plugins WHERE PLUGIN_NAME = 'ndbinfo'; 7PLUGIN_NAME PLUGIN_VERSION PLUGIN_STATUS PLUGIN_TYPE PLUGIN_LIBRARY PLUGIN_LIBRARY_VERSION PLUGIN_AUTHOR PLUGIN_DESCRIPTION 8ndbinfo 0.1 ACTIVE STORAGE ENGINE NULL NULL Sun Microsystems Inc. MySQL Cluster system information storage engine 9 10## Creation of temporary tables should not be supported by NDBINFO engine 11CREATE TEMPORARY TABLE `t1` ( 12 `dummy` INT UNSIGNED 13) ENGINE=NDBINFO; 14ERROR HY000: Table storage engine 'ndbinfo' does not support the create option 'TEMPORARY' 15 16USE ndbinfo; 17 18SELECT node_id, memory_type, used>0 AND used < total/20 AS used_ok, 19 used_pages>0 AND used_pages < total_pages/20 AS used_pages_ok, 20 total, total_pages 21 FROM ndbinfo.memoryusage WHERE memory_type="Long message buffer" 22 ORDER BY node_id; 23node_id memory_type used_ok used_pages_ok total total_pages 241 Long message buffer 0 0 4194304 16384 252 Long message buffer 0 0 4194304 16384 26 27SELECT node_id, block_number, block_instance, pool_name, 28 used>0 AND used < total/20 AS used_ok, 29 total, high>=used AND high < total/20 AS high_ok, entry_size, config_param1, 30 config_param2, config_param3, config_param4 31 FROM ndbinfo.ndb$pools WHERE pool_name="Long message buffer" 32 ORDER BY node_id; 33node_id block_number block_instance pool_name used_ok total high_ok entry_size config_param1 config_param2 config_param3 config_param4 341 254 0 Long message buffer 0 16384 0 256 157 0 0 0 352 254 0 Long message buffer 0 16384 0 256 157 0 0 0 36 37SHOW CREATE TABLE ndb$tables; 38Table Create Table 39ndb$tables CREATE TABLE `ndb$tables` ( 40 `table_id` int(10) unsigned DEFAULT NULL, 41 `table_name` varchar(512) DEFAULT NULL, 42 `comment` varchar(512) DEFAULT NULL 43) ENGINE=NDBINFO DEFAULT CHARSET=latin1 COMMENT='metadata for tables available through ndbinfo' 44 45SELECT * FROM ndb$tables; 46table_id table_name comment 470 tables metadata for tables available through ndbinfo 481 columns metadata for columns available through ndbinfo 492 test for testing 503 pools pool usage 514 transporters transporter status 525 logspaces logspace usage 536 logbuffers logbuffer usage 547 resources resources usage (a.k.a superpool) 558 counters monotonic counters 569 nodes node status 5710 diskpagebuffer disk page buffer info 5811 threadblocks which blocks are run in which threads 5912 threadstat Statistics on execution threads 6013 transactions transactions 6114 operations operations 6215 membership membership 6316 dict_obj_info Dictionary object info 6417 frag_mem_use Per fragment space information 6518 disk_write_speed_base Actual speed of disk writes per LDM thread, base data 6619 disk_write_speed_aggregate Actual speed of disk writes per LDM thread, aggregate data 6720 frag_operations Per fragment operational information 6821 restart_info Times of restart phases in seconds and current state 69SELECT COUNT(*) FROM ndb$tables; 70COUNT(*) 7122 72SELECT * FROM ndb$tables WHERE table_id = 2; 73table_id table_name comment 742 test for testing 75SELECT * FROM ndb$tables WHERE table_id > 5; 76table_id table_name comment 776 logbuffers logbuffer usage 787 resources resources usage (a.k.a superpool) 798 counters monotonic counters 809 nodes node status 8110 diskpagebuffer disk page buffer info 8211 threadblocks which blocks are run in which threads 8312 threadstat Statistics on execution threads 8413 transactions transactions 8514 operations operations 8615 membership membership 8716 dict_obj_info Dictionary object info 8817 frag_mem_use Per fragment space information 8918 disk_write_speed_base Actual speed of disk writes per LDM thread, base data 9019 disk_write_speed_aggregate Actual speed of disk writes per LDM thread, aggregate data 9120 frag_operations Per fragment operational information 9221 restart_info Times of restart phases in seconds and current state 93SELECT * FROM ndb$tables WHERE table_name = 'LOGDESTINATION'; 94table_id table_name comment 95SELECT COUNT(*) FROM ndb$tables t1, ndb$tables t2 WHERE t1.table_id = t1.table_id; 96COUNT(*) 97484 98 99SELECT table_id, table_name, comment from ndb$tables 100 WHERE table_id > 2 AND table_id <= 5 ORDER BY table_id; 101table_id table_name comment 1023 pools pool usage 1034 transporters transporter status 1045 logspaces logspace usage 105SELECT table_id FROM ndb$tables WHERE table_id = 2 ORDER BY table_name; 106table_id 1072 108SELECT table_id, table_name FROM ndb$tables ORDER BY table_name; 109table_id table_name 1101 columns 1118 counters 11216 dict_obj_info 11310 diskpagebuffer 11419 disk_write_speed_aggregate 11518 disk_write_speed_base 11617 frag_mem_use 11720 frag_operations 1186 logbuffers 1195 logspaces 12015 membership 1219 nodes 12214 operations 1233 pools 1247 resources 12521 restart_info 1260 tables 1272 test 12811 threadblocks 12912 threadstat 13013 transactions 1314 transporters 132 133SELECT table_id, column_id, column_name FROM ndb$columns LIMIT 7; 134table_id column_id column_name 1350 0 table_id 1360 1 table_name 1370 2 comment 1381 0 table_id 1391 1 column_id 1401 2 column_name 1411 3 column_type 142 143UPDATE ndb$tables SET table_id=2 WHERE table_id=3; 144ERROR HY000: Table 'ndb$tables' is read only 145 146UPDATE ndb$tables SET table_id=9 WHERE 1=0; 147ERROR HY000: Table 'ndb$tables' is read only 148 149UPDATE ndb$tables SET table_id=9 WHERE table_id > 1; 150ERROR HY000: Table 'ndb$tables' is read only 151 152DELETE FROM ndb$tables WHERE table_id=3; 153ERROR HY000: Table 'ndb$tables' is read only 154 155DELETE FROM ndb$tables WHERE 1=0; 156ERROR HY000: Table 'ndb$tables' is read only 157 158DELETE FROM ndb$tables WHERE table_id > 1; 159ERROR HY000: Table 'ndb$tables' is read only 160 161ALTER TABLE ndb$test ADD COLUMN another_col varchar(255); 162ERROR HY000: Table storage engine for 'ndb$test' doesn't have this option 163 164FLUSH TABLES; 165SELECT table_id FROM ndb$tables; 166table_id 1670 1681 1692 1703 1714 1725 1736 1747 1758 1769 17710 17811 17912 18013 18114 18215 18316 18417 18518 18619 18720 18821 189 190TRUNCATE ndb$tables; 191ERROR HY000: Table 'ndb$tables' is read only 192 193## Variables and status 194SHOW GLOBAL STATUS LIKE 'ndbinfo\_%'; 195Variable_name Value 196SHOW GLOBAL VARIABLES LIKE 'ndbinfo\_%'; 197Variable_name Value 198ndbinfo_database ndbinfo 199ndbinfo_max_bytes 0 200ndbinfo_max_rows 10 201ndbinfo_offline OFF 202ndbinfo_show_hidden OFF 203ndbinfo_table_prefix ndb$ 204ndbinfo_version NDB_VERSION_D 205 206SELECT counter, HEX(counter2) FROM ndb$test LIMIT 10; 207counter HEX(counter2) 2080 0 2091 100000000 2102 200000000 2113 300000000 2124 400000000 2135 500000000 2146 600000000 2157 700000000 2168 800000000 2179 900000000 218 219SHOW TABLES LIKE 'ndb$te%'; 220Tables_in_ndbinfo (ndb$te%) 221set @@ndbinfo_show_hidden=TRUE; 222SHOW TABLES LIKE 'ndb$te%'; 223Tables_in_ndbinfo (ndb$te%) 224ndb$test 225set @@ndbinfo_show_hidden=default; 226 227set @@ndbinfo_table_prefix="somethingelse"; 228ERROR HY000: Variable 'ndbinfo_table_prefix' is a read only variable 229 230set @@ndbinfo_database="somethingelse"; 231ERROR HY000: Variable 'ndbinfo_database' is a read only variable 232 233SELECT count(*) >= 20 FROM blocks; 234count(*) >= 20 2351 236 237## 1) More columns in NDB -> allowed, with warning 238DROP TABLE ndb$test; 239CREATE TABLE ndb$test (node_id int unsigned) ENGINE = ndbinfo; 240SELECT node_id != 0 FROM ndb$test LIMIT 1; 241node_id != 0 2421 243Warnings: 244Note 40001 Table 'ndb$test' is defined differently in NDB, there are more columns available. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 245DROP TABLE ndb$test; 246 247## 2) Column does not exist in NDB -> allowed, with warning, non existing 248## column(s) return NULL 249## 2a) Extra column at end 250CREATE TABLE ndb$test (node_id int, non_existing int) ENGINE = ndbinfo; 251SELECT DISTINCT node_id, non_existing FROM ndb$test; 252node_id non_existing 2531 NULL 2542 NULL 255Warnings: 256Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' does not exist. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 257Note 40001 Table 'ndb$test' is defined differently in NDB, there are more columns available. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 258DROP TABLE ndb$test; 259 260## 2b) Extra column(s) in middle 261CREATE TABLE ndb$test ( 262 node_id int unsigned, 263 non_existing int unsigned, 264 block_number int unsigned, 265 block_instance int unsigned, 266 counter int unsigned, 267 counter2 bigint unsigned 268) ENGINE = ndbinfo; 269SELECT DISTINCT node_id, non_existing, block_number FROM ndb$test; 270node_id non_existing block_number 2711 NULL 249 2722 NULL 249 273Warnings: 274Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' does not exist. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 275DROP TABLE ndb$test; 276 277## 2c) Extra column first 278CREATE TABLE ndb$test (non_existing int, node_id int) ENGINE = ndbinfo; 279SELECT DISTINCT node_id, non_existing FROM ndb$test; 280node_id non_existing 2811 NULL 2822 NULL 283Warnings: 284Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' does not exist. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 285Note 40001 Table 'ndb$test' is defined differently in NDB, there are more columns available. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 286SELECT DISTINCT non_existing, node_id FROM ndb$test; 287non_existing node_id 288NULL 1 289NULL 2 290DROP TABLE ndb$test; 291 292## 3) Incompatible column type -> error, with warning 293## 3a) int instead of bigint 294CREATE TABLE ndb$test (counter2 int) ENGINE = ndbinfo; 295SELECT * FROM ndb$test; 296ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO 297SHOW WARNINGS; 298Level Code Message 299Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'counter2' is not compatible. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 300Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO 301## 3b) bigint instead of int 302DROP TABLE ndb$test; 303CREATE TABLE ndb$test (node_id bigint) ENGINE = ndbinfo; 304SELECT * FROM ndb$test; 305ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO 306SHOW WARNINGS; 307Level Code Message 308Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'node_id' is not compatible. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 309Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO 310## 3c) varchar instead of int 311DROP TABLE ndb$test; 312CREATE TABLE ndb$test (node_id varchar(255)) ENGINE = ndbinfo; 313SELECT * FROM ndb$test; 314ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO 315SHOW WARNINGS; 316Level Code Message 317Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'node_id' is not compatible. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 318Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO 319DROP TABLE ndb$test; 320## 3d) column which is NOT NULL 321CREATE TABLE ndb$test (node_id int unsigned NOT NULL) ENGINE = ndbinfo; 322SELECT * FROM ndb$test; 323ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO 324SHOW WARNINGS; 325Level Code Message 326Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'node_id' is NOT NULL. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 327Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO 328DROP TABLE ndb$test; 329## 3e) non existing column which is NOT NULL 330CREATE TABLE ndb$test ( 331 block_number int unsigned, 332 non_existing int NOT NULL) ENGINE = ndbinfo; 333SELECT * FROM ndb$test; 334ERROR HY000: Got error 40001 'Incompatible table definitions' from NDBINFO 335SHOW WARNINGS; 336Level Code Message 337Warning 40001 Table 'ndb$test' is defined differently in NDB, column 'non_existing' is NOT NULL. The SQL to regenerate is: 'CREATE TABLE `ndbinfo`.`ndb$test` (`node_id` INT UNSIGNED, `block_number` INT UNSIGNED, `block_instance` INT UNSIGNED, `counter` INT UNSIGNED, `counter2` BIGINT UNSIGNED) ENGINE=NDBINFO' 338Error 1296 Got error 40001 'Incompatible table definitions' from NDBINFO 339DROP TABLE ndb$test; 340 341## 4) Table with primary key/indexes not supported 342CREATE TABLE ndb$test (node_id int, block_number int PRIMARY KEY) ENGINE = ndbinfo; 343ERROR 42000: Too many keys specified; max 0 keys allowed 344 345## 5) Table with blobs not supported 346CREATE TABLE ndb$test (node_id int, block_number blob) ENGINE = ndbinfo; 347ERROR 42000: The used table type doesn't support BLOB/TEXT columns 348 349## 6) Table with autoincrement not supported 350CREATE TABLE ndb$test (node_id int AUTO_INCREMENT) ENGINE = ndbinfo; 351ERROR 42000: The used table type doesn't support AUTO_INCREMENT columns 352 353select distinct node_id 354from ndbinfo.diskpagebuffer; 355node_id 3561 3572 358 359set @@ndbinfo_offline=1; 360ERROR HY000: Variable 'ndbinfo_offline' is a GLOBAL variable and should be set with SET GLOBAL 361 362SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id; 363node_id 3641 3652 366 367desc threadblocks; 368Field Type Null Key Default Extra 369node_id int(10) unsigned YES NULL 370thr_no int(10) unsigned YES NULL 371block_name varchar(512) YES NULL 372block_instance int(10) unsigned YES NULL 373select distinct block_name from threadblocks order by 1; 374block_name 375BACKUP 376CMVMI 377DBACC 378DBDICT 379DBDIH 380DBINFO 381DBLQH 382DBSPJ 383DBTC 384DBTUP 385DBTUX 386DBUTIL 387LGMAN 388NDBCNTR 389NDBFS 390PGMAN 391QMGR 392RESTORE 393SUMA 394THRMAN 395TRIX 396TRPMAN 397TSMAN 398desc threadstat; 399Field Type Null Key Default Extra 400node_id int(10) unsigned YES NULL 401thr_no int(10) unsigned YES NULL 402thr_nm varchar(512) YES NULL 403c_loop bigint(20) unsigned YES NULL 404c_exec bigint(20) unsigned YES NULL 405c_wait bigint(20) unsigned YES NULL 406c_l_sent_prioa bigint(20) unsigned YES NULL 407c_l_sent_priob bigint(20) unsigned YES NULL 408c_r_sent_prioa bigint(20) unsigned YES NULL 409c_r_sent_priob bigint(20) unsigned YES NULL 410os_tid bigint(20) unsigned YES NULL 411os_now bigint(20) unsigned YES NULL 412os_ru_utime bigint(20) unsigned YES NULL 413os_ru_stime bigint(20) unsigned YES NULL 414os_ru_minflt bigint(20) unsigned YES NULL 415os_ru_majflt bigint(20) unsigned YES NULL 416os_ru_nvcsw bigint(20) unsigned YES NULL 417os_ru_nivcsw bigint(20) unsigned YES NULL 418select count(*) > 0 block_name from threadstat; 419block_name 4201 421 422desc disk_write_speed_aggregate; 423Field Type Null Key Default Extra 424node_id int(10) unsigned YES NULL 425thr_no int(10) unsigned YES NULL 426backup_lcp_speed_last_sec bigint(20) unsigned YES NULL 427redo_speed_last_sec bigint(20) unsigned YES NULL 428backup_lcp_speed_last_10sec bigint(20) unsigned YES NULL 429redo_speed_last_10sec bigint(20) unsigned YES NULL 430std_dev_backup_lcp_speed_last_10sec bigint(20) unsigned YES NULL 431std_dev_redo_speed_last_10sec bigint(20) unsigned YES NULL 432backup_lcp_speed_last_60sec bigint(20) unsigned YES NULL 433redo_speed_last_60sec bigint(20) unsigned YES NULL 434std_dev_backup_lcp_speed_last_60sec bigint(20) unsigned YES NULL 435std_dev_redo_speed_last_60sec bigint(20) unsigned YES NULL 436slowdowns_due_to_io_lag bigint(20) unsigned YES NULL 437slowdowns_due_to_high_cpu bigint(20) unsigned YES NULL 438disk_write_speed_set_to_min bigint(20) unsigned YES NULL 439current_target_disk_write_speed bigint(20) unsigned YES NULL 440select count(*) from disk_write_speed_aggregate; 441count(*) 4428 443 444desc disk_write_speed_base; 445Field Type Null Key Default Extra 446node_id int(10) unsigned YES NULL 447thr_no int(10) unsigned YES NULL 448millis_ago bigint(20) unsigned YES NULL 449millis_passed bigint(20) unsigned YES NULL 450backup_lcp_bytes_written bigint(20) unsigned YES NULL 451redo_bytes_written bigint(20) unsigned YES NULL 452target_disk_write_speed bigint(20) unsigned YES NULL 453select node_id from disk_write_speed_base group by node_id, thr_no; 454node_id 4551 4561 4571 4581 4592 4602 4612 4622 463 464select count(*) from disk_write_speed_aggregate_node; 465count(*) 4662 467 468desc restart_info; 469Field Type Null Key Default Extra 470node_id int(10) unsigned YES NULL 471node_restart_status varchar(256) YES NULL 472node_restart_status_int int(10) unsigned YES NULL 473secs_to_complete_node_failure int(10) unsigned YES NULL 474secs_to_allocate_node_id int(10) unsigned YES NULL 475secs_to_include_in_heartbeat_protocol int(10) unsigned YES NULL 476secs_until_wait_for_ndbcntr_master int(10) unsigned YES NULL 477secs_wait_for_ndbcntr_master int(10) unsigned YES NULL 478secs_to_get_start_permitted int(10) unsigned YES NULL 479secs_to_wait_for_lcp_for_copy_meta_data int(10) unsigned YES NULL 480secs_to_copy_meta_data int(10) unsigned YES NULL 481secs_to_include_node int(10) unsigned YES NULL 482secs_starting_node_to_request_local_recovery int(10) unsigned YES NULL 483secs_for_local_recovery int(10) unsigned YES NULL 484secs_restore_fragments int(10) unsigned YES NULL 485secs_undo_disk_data int(10) unsigned YES NULL 486secs_exec_redo_log int(10) unsigned YES NULL 487secs_index_rebuild int(10) unsigned YES NULL 488secs_to_synchronize_starting_node int(10) unsigned YES NULL 489secs_wait_lcp_for_restart int(10) unsigned YES NULL 490secs_wait_subscription_handover int(10) unsigned YES NULL 491total_restart_secs int(10) unsigned YES NULL 492select * from restart_info; 493node_id node_restart_status node_restart_status_int secs_to_complete_node_failure secs_to_allocate_node_id secs_to_include_in_heartbeat_protocol secs_until_wait_for_ndbcntr_master secs_wait_for_ndbcntr_master secs_to_get_start_permitted secs_to_wait_for_lcp_for_copy_meta_data secs_to_copy_meta_data secs_to_include_node secs_starting_node_to_request_local_recovery secs_for_local_recovery secs_restore_fragments secs_undo_disk_data secs_exec_redo_log secs_index_rebuild secs_to_synchronize_starting_node secs_wait_lcp_for_restart secs_wait_subscription_handover total_restart_secs 494 495desc cluster_transactions; 496Field Type Null Key Default Extra 497node_id int(10) unsigned YES NULL 498block_instance int(10) unsigned YES NULL 499transid bigint(22) unsigned YES NULL 500state varchar(256) YES NULL 501count_operations int(10) unsigned YES NULL 502outstanding_operations int(10) unsigned YES NULL 503inactive_seconds int(10) unsigned YES NULL 504client_node_id bigint(21) unsigned YES NULL 505client_block_ref bigint(21) unsigned YES NULL 506desc server_transactions; 507Field Type Null Key Default Extra 508mysql_connection_id bigint(21) unsigned NO 0 509node_id int(10) unsigned YES NULL 510block_instance int(10) unsigned YES NULL 511transid bigint(22) unsigned YES NULL 512state varchar(256) YES NULL 513count_operations int(10) unsigned YES NULL 514outstanding_operations int(10) unsigned YES NULL 515inactive_seconds int(10) unsigned YES NULL 516client_node_id bigint(21) unsigned YES NULL 517client_block_ref bigint(21) unsigned YES NULL 518desc cluster_operations; 519Field Type Null Key Default Extra 520node_id int(10) unsigned YES NULL 521block_instance int(10) unsigned YES NULL 522transid bigint(22) unsigned YES NULL 523operation_type varchar(9) YES NULL 524state varchar(256) YES NULL 525tableid int(10) unsigned YES NULL 526fragmentid int(10) unsigned YES NULL 527client_node_id bigint(21) unsigned YES NULL 528client_block_ref bigint(21) unsigned YES NULL 529tc_node_id bigint(21) unsigned YES NULL 530tc_block_no bigint(21) unsigned YES NULL 531tc_block_instance bigint(21) unsigned YES NULL 532desc server_operations; 533Field Type Null Key Default Extra 534mysql_connection_id bigint(21) unsigned NO 0 535node_id int(10) unsigned YES NULL 536block_instance int(10) unsigned YES NULL 537transid bigint(22) unsigned YES NULL 538operation_type varchar(9) YES NULL 539state varchar(256) YES NULL 540tableid int(10) unsigned YES NULL 541fragmentid int(10) unsigned YES NULL 542client_node_id bigint(21) unsigned YES NULL 543client_block_ref bigint(21) unsigned YES NULL 544tc_node_id bigint(21) unsigned YES NULL 545tc_block_no bigint(21) unsigned YES NULL 546tc_block_instance bigint(21) unsigned YES NULL 547 548create table t1 (a int primary key) engine = ndb; 549begin; 550insert into t1 values (1); 551select state, count_operations, outstanding_operations, 552IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>") 553 client_node_id 554from server_transactions; 555state count_operations outstanding_operations client_node_id 556Started 1 0 <client_node_id> 557select node_id, operation_type, state, 558IF(tc_node_id <= 48, "<tc_node_id>", "<incorrect nodeid>") tc_node_id, 559IF(client_node_id <= 255, "<client_node_id>", "<incorrect node id>") 560 client_node_id 561from server_operations 562order by 1; 563node_id operation_type state tc_node_id client_node_id 5641 INSERT Prepared <tc_node_id> <client_node_id> 5652 INSERT Prepared <tc_node_id> <client_node_id> 566 567select st.state, st.count_operations, st.outstanding_operations, 568 so.node_id, so.state, so.operation_type 569from server_transactions st, 570 server_operations so 571where st.transid = so.transid 572 and so.tc_node_id = st.node_id 573 and so.tc_block_instance = st.block_instance 574 and so.client_node_id = st.client_node_id 575 and so.client_block_ref = st.client_block_ref; 576state count_operations outstanding_operations node_id state operation_type 577Started 1 0 1 Prepared INSERT 578Started 1 0 2 Prepared INSERT 579rollback; 580drop table t1; 581 582select distinct resource_name 583from resources 584order by 1; 585resource_name 586DATA_MEMORY 587DISK_OPERATIONS 588DISK_PAGE_BUFFER 589DISK_RECORDS 590FILE_BUFFERS 591JOBBUFFER 592QUERY_MEMORY 593RESERVED 594SCHEMA_TRANS_MEMORY 595TRANSPORTER_BUFFERS 596 597select distinct counter_name 598from counters 599order by 1; 600counter_name 601ABORTS 602ATTRINFO 603COMMITS 604CONST_PRUNED_RANGE_SCANS_RECEIVED 605LOCAL_RANGE_SCANS_SENT 606LOCAL_READS 607LOCAL_READS_SENT 608LOCAL_TABLE_SCANS_SENT 609LOCAL_WRITES 610LQHKEY_OVERLOAD 611LQHKEY_OVERLOAD_NODE_PEER 612LQHKEY_OVERLOAD_READER 613LQHKEY_OVERLOAD_SUBSCRIBER 614LQHKEY_OVERLOAD_TC 615LQHSCAN_SLOWDOWNS 616OPERATIONS 617PRUNED_RANGE_SCANS_RECEIVED 618RANGE_SCANS 619RANGE_SCANS_RECEIVED 620READS 621READS_NOT_FOUND 622READS_RECEIVED 623REMOTE_RANGE_SCANS_SENT 624REMOTE_READS_SENT 625SCAN_BATCHES_RETURNED 626SCAN_ROWS_RETURNED 627SIMPLE_READS 628TABLE_SCANS 629TABLE_SCANS_RECEIVED 630TRANSACTIONS 631WRITES 632 633set @@global.ndbinfo_offline=TRUE; 634select @@ndbinfo_offline; 635@@ndbinfo_offline 6361 637 638CREATE TABLE ndb$does_not_exist_in_ndb( 639 node_id int, 640 message varchar(255) 641) ENGINE = ndbinfo; 642 643CREATE VIEW view_on_table_which_does_not_exist_in_ndb AS 644 SELECT node_id, message 645 FROM ndbinfo.ndb$does_not_exist_in_ndb; 646 647SHOW CREATE TABLE ndb$does_not_exist_in_ndb; 648Table Create Table 649ndb$does_not_exist_in_ndb CREATE TABLE `ndb$does_not_exist_in_ndb` ( 650 `node_id` int(11) DEFAULT NULL, 651 `message` varchar(255) DEFAULT NULL 652) ENGINE=NDBINFO DEFAULT CHARSET=latin1 653 654SELECT * FROM view_on_table_which_does_not_exist_in_ndb; 655node_id message 656Warnings: 657Note 1 'NDBINFO' has been started in offline mode since the 'NDBCLUSTER' engine is disabled or @@global.ndbinfo_offline is turned on - no rows can be returned 658SELECT * FROM ndb$does_not_exist_in_ndb; 659node_id message 660Warnings: 661Note 1 'NDBINFO' has been started in offline mode since the 'NDBCLUSTER' engine is disabled or @@global.ndbinfo_offline is turned on - no rows can be returned 662SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id; 663node_id 664Warnings: 665Note 1 'NDBINFO' has been started in offline mode since the 'NDBCLUSTER' engine is disabled or @@global.ndbinfo_offline is turned on - no rows can be returned 666 667DROP VIEW view_on_table_which_does_not_exist_in_ndb; 668DROP TABLE ndb$does_not_exist_in_ndb; 669 670set @@global.ndbinfo_offline = FALSE; 671 672SELECT DISTINCT(node_id) FROM ndbinfo.counters ORDER BY node_id; 673node_id 6741 6752 676 677Verify there are no ndbinfo views without 'root@localhost' as definer 678select table_schema, table_name, definer from information_schema.views 679 where table_schema = 'ndbinfo' and 680 definer != 'root@localhost'; 681table_schema table_name definer 682 683create table numbers( 684 i int 685); 686 687insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 688 689create table t1( 690 a int not null primary key, 691 b int not null, 692 c1 char(255), 693 vc1 varchar(1024), 694 tx1 text 695)engine=ndbcluster; 696 697insert into t1 select x1.i+x2.i*10+x3.i*100, x1.i-x2.i*10-x3.i*100, repeat('x', 1+x1.i), repeat('y', 1000+x1.i), repeat('z', x1.i*100) from numbers as x1, numbers as x2, numbers as x3; 698 699drop table numbers; 700 701create index ix1 on t1(c1); 702create unique index ix2 on t1(b) using hash; 703 704set @t1_id = (select cast(id as signed integer) from ndbinfo.ndb$dict_obj_info where fq_name REGEXP "def/t1$"); 705 706select type, state, parent_obj_type, replace(fq_name, cast(@t1_id as char(10)), "<t1 id>") as fq_name, if(parent_obj_id=@t1_id, "child_of_t1", if(parent_obj_id=0, "root", "error!")) as nesting from ndbinfo.ndb$dict_obj_info where id >= @t1_id and type <= 6 order by type, id; 707type state parent_obj_type fq_name nesting 7082 4 0 ndbinfo/def/t1 root 7092 4 2 ndbinfo/def/NDB$BLOB_<t1 id>_4 child_of_t1 7103 4 2 sys/def/<t1 id>/ix2$unique child_of_t1 7116 4 2 sys/def/<t1 id>/PRIMARY child_of_t1 7126 4 2 sys/def/<t1 id>/ix1 child_of_t1 713 714 715create view frag_sum as 716select 717sum(fixed_elem_alloc_bytes) as fixed_elem_alloc_bytes, 718sum(var_elem_alloc_bytes) as var_elem_alloc_bytes, 719sum(hash_index_alloc_bytes) as hash_index_alloc_bytes 720from ndbinfo.memory_per_fragment; 721 722create view pool_sum as 723select pool_name, block_number, max(entry_size) * sum(used) as used_bytes 724from ndbinfo.ndb$pools where pool_name <> "Data memory" 725or block_number=249 group by pool_name, block_number; 726 727create view check_l2pmap_pools as 728select min(floor(1000*nodes.total*nodes.entry_size/ 729 (pages.used*pages.entry_size))) as l2pmap_ratio 730from ndbinfo.ndb$pools as pages join ndbinfo.ndb$pools as nodes 731where pages.node_id=nodes.node_id 732and pages.block_instance=nodes.block_instance 733and pages.block_number=nodes.block_number 734and pages.pool_name="L2PMap pages" 735and nodes.pool_name="L2PMap nodes"; 736 737create view l2p_frag_sum as 738select sum(tuple_l2pmap_alloc_bytes) as l2p_sum, 'TUP' as block 739from ndbinfo.ndb$frag_mem_use 740union 741select sum(hash_index_l2pmap_alloc_bytes) as l2p_sum, 'ACC' as block 742from ndbinfo.ndb$frag_mem_use; 743 744select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum 745where pool_name='Index memory'; 746used_bytes - hash_index_alloc_bytes 7470 748 749select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes 750 from pool_sum, frag_sum 751 where pool_name='Data memory'; 752used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes 7530 754 755select l2p_sum-used_bytes from l2p_frag_sum, pool_sum 756 where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or 757 (block='TUP' and block_number=249)); 758l2p_sum-used_bytes 7590 7600 761 762select * from check_l2pmap_pools; 763l2pmap_ratio 764996 765 766select * from sum_per_tab; 767fq_name parent_fq_name type fixed_elem_count fixed_elem_size_bytes fixed_elem_alloc_ok fixed_elem_free_bytes_ok var_elem_count var_bytes_ok 768ndbinfo/def/t1 NULL User table 2000 292 1.0 1 2000 1 769ndbinfo/def/NDB$BLOB_<t1 id>_4 ndbinfo/def/t1 User table 1400 36 1.0 1 1400 1 770sys/def/<t1 id>/PRIMARY ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1 771sys/def/<t1 id>/ix1 ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1 772sys/def/<t1 id>/ix2$unique ndbinfo/def/t1 Unique hash index 2000 28 1.0 1 0 1 773 774delete from t1 where a%10=0; 775 776select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum 777where pool_name='Index memory'; 778used_bytes - hash_index_alloc_bytes 7790 780 781select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes 782 from pool_sum, frag_sum 783 where pool_name='Data memory'; 784used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes 7850 786 787select l2p_sum-used_bytes from l2p_frag_sum, pool_sum 788 where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or 789 (block='TUP' and block_number=249)); 790l2p_sum-used_bytes 7910 7920 793 794select * from check_l2pmap_pools; 795l2pmap_ratio 796996 797 798select * from sum_per_tab; 799fq_name parent_fq_name type fixed_elem_count fixed_elem_size_bytes fixed_elem_alloc_ok fixed_elem_free_bytes_ok var_elem_count var_bytes_ok 800ndbinfo/def/t1 NULL User table 1800 292 1.0 1 1800 1 801ndbinfo/def/NDB$BLOB_<t1 id>_4 ndbinfo/def/t1 User table 1400 36 1.0 1 1400 1 802sys/def/<t1 id>/PRIMARY ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1 803sys/def/<t1 id>/ix1 ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1 804sys/def/<t1 id>/ix2$unique ndbinfo/def/t1 Unique hash index 1800 28 1.0 1 0 1 805 806update t1 set vc1=repeat('x', a%300), tx1=repeat('y', a*2); 807 808select used_bytes - hash_index_alloc_bytes from pool_sum, frag_sum 809where pool_name='Index memory'; 810used_bytes - hash_index_alloc_bytes 8110 812 813select used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes 814 from pool_sum, frag_sum 815 where pool_name='Data memory'; 816used_bytes - fixed_elem_alloc_bytes - var_elem_alloc_bytes 8170 818 819select l2p_sum-used_bytes from l2p_frag_sum, pool_sum 820 where pool_name='L2PMap nodes' and ((block='ACC' and block_number=248) or 821 (block='TUP' and block_number=249)); 822l2p_sum-used_bytes 8230 8240 825 826select * from check_l2pmap_pools; 827l2pmap_ratio 828996 829 830select * from sum_per_tab; 831fq_name parent_fq_name type fixed_elem_count fixed_elem_size_bytes fixed_elem_alloc_ok fixed_elem_free_bytes_ok var_elem_count var_bytes_ok 832ndbinfo/def/t1 NULL User table 1800 292 1.0 1 1800 1 833ndbinfo/def/NDB$BLOB_<t1 id>_4 ndbinfo/def/t1 User table 1568 36 1.0 1 1568 1 834sys/def/<t1 id>/PRIMARY ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1 835sys/def/<t1 id>/ix1 ndbinfo/def/t1 Ordered index -1 272 1.0 1 0 1 836sys/def/<t1 id>/ix2$unique ndbinfo/def/t1 Unique hash index 1800 28 1.0 1 0 1 837 838drop table t1; 839drop view l2p_frag_sum; 840drop view check_l2pmap_pools; 841drop view sum_per_tab; 842drop view pool_sum; 843drop view frag_sum; 844 845create view frag_ops as select 846 if(type in ('Ordered index', 'Unique hash index'), 847 substring(fq_name from 1+locate('/', fq_name, 9)), 848 if(fq_name like '%BLOB%', left(fq_name, locate('_', fq_name)), fq_name)) 849 as fq_name, 850 parent_fq_name, 851 type, 852 count(fragment_num) as frag_count, 853 sum(tot_key_reads) as tot_key_reads, 854 sum(tot_key_inserts) as tot_key_inserts, 855 sum(tot_key_updates) as tot_key_updates, 856 sum(tot_key_writes) as tot_key_writes, 857 sum(tot_key_deletes) as tot_key_deletes, 858 sum(tot_key_refs) as tot_key_refs, 859 # Attrinfo for read operations is a bit longer when running with embedded 860 # mysqld, which is why we do the rounding below. 861 round(log2(1+sum(tot_key_attrinfo_bytes))) as log_tot_key_attrinfo_bytes, 862 sum(tot_key_keyinfo_bytes) as tot_key_keyinfo_bytes, 863 sum(tot_key_prog_bytes) as tot_key_prog_bytes, 864 sum(tot_key_inst_exec) as tot_key_inst_exec, 865 sum(tot_key_bytes_returned) as tot_key_bytes_returned, 866 sum(tot_frag_scans) as tot_frag_scans, 867 sum(tot_scan_rows_examined) as tot_scan_rows_examined, 868 sum(tot_scan_rows_returned) as tot_scan_rows_returned, 869 sum(tot_scan_bytes_returned) as tot_scan_bytes_returned, 870 sum(tot_scan_prog_bytes) as tot_scan_prog_bytes, 871 sum(tot_scan_bound_bytes) as tot_scan_bound_bytes, 872 sum(tot_scan_inst_exec) as tot_scan_inst_exec, 873 sum(conc_frag_scans) as conc_frag_scans, 874 sum(conc_qd_frag_scans) as conc_qd_frag_scans, 875 sum(tot_commits) as tot_commits 876 from ndbinfo.operations_per_fragment 877 where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%' 878 group by table_id, type, operations_per_fragment.fq_name, parent_fq_name 879 order by table_id; 880 881create table t1 ( 882 a int not null, 883 b int not null, 884 c int not null, 885 d int not null, 886 t text not null, 887 primary key (b,a) 888) engine=ndbcluster 889partition by key(b,a) partitions 8; 890 891create unique index ix1 on t1(d) using hash; 892 893create table numbers( 894 i int 895); 896 897insert into numbers values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 898 899insert into t1 select x1.i+x2.i*10, 1, 1+x1.i+x2.i*10, 1+x1.i+x2.i*10, 900 repeat('x', 512) 901 from numbers as x1, numbers as x2; 902 903drop table numbers; 904 905create table t2 ( 906 a int, 907 b int, 908 primary key(a) using hash) 909engine = ndb 910partition by key(a) partitions 8; 911 912insert into t2 values (1, 2); 913insert into t2 values (2, 3); 914insert into t2 values (3, 1); 915 916select * from frag_ops;; 917fq_name ndbinfo/def/t1 918parent_fq_name NULL 919type User table 920frag_count 16 921tot_key_reads 0 922tot_key_inserts 200 923tot_key_updates 200 924tot_key_writes 0 925tot_key_deletes 0 926tot_key_refs 0 927log_tot_key_attrinfo_bytes 16 928tot_key_keyinfo_bytes 3200 929tot_key_prog_bytes 0 930tot_key_inst_exec 0 931tot_key_bytes_returned 0 932tot_frag_scans 24 933tot_scan_rows_examined 0 934tot_scan_rows_returned 0 935tot_scan_bytes_returned 0 936tot_scan_prog_bytes 64 937tot_scan_bound_bytes 0 938tot_scan_inst_exec 0 939conc_frag_scans 0 940conc_qd_frag_scans 0 941tot_commits 400 942fq_name ndbinfo/def/NDB$BLOB_ 943parent_fq_name ndbinfo/def/t1 944type User table 945frag_count 16 946tot_key_reads 0 947tot_key_inserts 200 948tot_key_updates 0 949tot_key_writes 0 950tot_key_deletes 0 951tot_key_refs 0 952log_tot_key_attrinfo_bytes 16 953tot_key_keyinfo_bytes 2400 954tot_key_prog_bytes 0 955tot_key_inst_exec 0 956tot_key_bytes_returned 0 957tot_frag_scans 0 958tot_scan_rows_examined 0 959tot_scan_rows_returned 0 960tot_scan_bytes_returned 0 961tot_scan_prog_bytes 0 962tot_scan_bound_bytes 0 963tot_scan_inst_exec 0 964conc_frag_scans 0 965conc_qd_frag_scans 0 966tot_commits 200 967fq_name PRIMARY 968parent_fq_name ndbinfo/def/t1 969type Ordered index 970frag_count 16 971tot_key_reads 0 972tot_key_inserts 0 973tot_key_updates 0 974tot_key_writes 0 975tot_key_deletes 0 976tot_key_refs 0 977log_tot_key_attrinfo_bytes 0 978tot_key_keyinfo_bytes 0 979tot_key_prog_bytes 0 980tot_key_inst_exec 0 981tot_key_bytes_returned 0 982tot_frag_scans 0 983tot_scan_rows_examined 0 984tot_scan_rows_returned 0 985tot_scan_bytes_returned 0 986tot_scan_prog_bytes 0 987tot_scan_bound_bytes 0 988tot_scan_inst_exec 0 989conc_frag_scans 0 990conc_qd_frag_scans 0 991tot_commits 0 992fq_name ix1$unique 993parent_fq_name ndbinfo/def/t1 994type Unique hash index 995frag_count 16 996tot_key_reads 0 997tot_key_inserts 200 998tot_key_updates 0 999tot_key_writes 0 1000tot_key_deletes 0 1001tot_key_refs 0 1002log_tot_key_attrinfo_bytes 12 1003tot_key_keyinfo_bytes 800 1004tot_key_prog_bytes 0 1005tot_key_inst_exec 0 1006tot_key_bytes_returned 0 1007tot_frag_scans 0 1008tot_scan_rows_examined 0 1009tot_scan_rows_returned 0 1010tot_scan_bytes_returned 0 1011tot_scan_prog_bytes 0 1012tot_scan_bound_bytes 0 1013tot_scan_inst_exec 0 1014conc_frag_scans 0 1015conc_qd_frag_scans 0 1016tot_commits 200 1017fq_name ndbinfo/def/t2 1018parent_fq_name NULL 1019type User table 1020frag_count 16 1021tot_key_reads 0 1022tot_key_inserts 6 1023tot_key_updates 0 1024tot_key_writes 0 1025tot_key_deletes 0 1026tot_key_refs 0 1027log_tot_key_attrinfo_bytes 7 1028tot_key_keyinfo_bytes 24 1029tot_key_prog_bytes 0 1030tot_key_inst_exec 0 1031tot_key_bytes_returned 0 1032tot_frag_scans 8 1033tot_scan_rows_examined 0 1034tot_scan_rows_returned 0 1035tot_scan_bytes_returned 0 1036tot_scan_prog_bytes 32 1037tot_scan_bound_bytes 0 1038tot_scan_inst_exec 0 1039conc_frag_scans 0 1040conc_qd_frag_scans 0 1041tot_commits 6 1042explain select count(*) from t1 where d<10; 1043id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10441 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL ix1 NULL NULL NULL 100 33.33 Using where with pushed condition (`ndbinfo`.`t1`.`d` < 10) 1045Warnings: 1046Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` where (`ndbinfo`.`t1`.`d` < 10) 1047select count(*) from t1 where d<10; 1048count(*) 10499 1050 1051explain select count(*) from t1 where b=1 and a<10; 1052id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10531 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 8 NULL 3 100.00 Using where with pushed condition ((`ndbinfo`.`t1`.`b` = 1) and (`ndbinfo`.`t1`.`a` < 10)); Using MRR 1054Warnings: 1055Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` where ((`ndbinfo`.`t1`.`b` = 1) and (`ndbinfo`.`t1`.`a` < 10)) 1056select count(*) from t1 where b=1 and a<10; 1057count(*) 105810 1059 1060explain select * from t1 where b=1 and a = -5; 1061id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10621 SIMPLE t1 p4 eq_ref PRIMARY PRIMARY 8 const,const 1 100.00 NULL 1063Warnings: 1064Note 1003 /* select#1 */ select `ndbinfo`.`t1`.`a` AS `a`,`ndbinfo`.`t1`.`b` AS `b`,`ndbinfo`.`t1`.`c` AS `c`,`ndbinfo`.`t1`.`d` AS `d`,`ndbinfo`.`t1`.`t` AS `t` from `ndbinfo`.`t1` where ((`ndbinfo`.`t1`.`a` = -(5)) and (`ndbinfo`.`t1`.`b` = 1)) 1065select * from t1 where b=1 and a = -5; 1066a b c d t 1067 1068insert into t1 values (1,1,1,1,''); 1069ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 1070 1071replace t1 values (-1,-1,-1,-1,''); 1072replace t1 values (1,1,5,500,''); 1073 1074update t1 set d = -d, t = repeat('a', 300) where b=1 and a > 90; 1075 1076delete from t1 where a%30=0; 1077 1078explain select count(*) from t1 as x1 join t1 as x2 on x1.c=x2.b; 1079id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10801 SIMPLE x1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 97 100.00 Parent of 2 pushed join@1 10811 SIMPLE x2 p0,p1,p2,p3,p4,p5,p6,p7 ref PRIMARY PRIMARY 4 ndbinfo.x1.c 1 100.00 Child of 'x1' in pushed join@1 1082Warnings: 1083Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t1` `x1` join `ndbinfo`.`t1` `x2` where (`ndbinfo`.`x2`.`b` = `ndbinfo`.`x1`.`c`) 1084select count(*) from t1 as x1 join t1 as x2 on x1.c=x2.b; 1085count(*) 10861 1087 1088select 1089 if ((select version() like '%debug'), 1090 sum(tot_qd_frag_scans), 1091 4) as tot_qd_frag_scans 1092 from ndbinfo.operations_per_fragment 1093 where fq_name like 'ndbinfo%' or parent_fq_name like 'ndbinfo%'; 1094tot_qd_frag_scans 10954 1096 1097select * from frag_ops;; 1098fq_name ndbinfo/def/t1 1099parent_fq_name NULL 1100type User table 1101frag_count 16 1102tot_key_reads 19 1103tot_key_inserts 205 1104tot_key_updates 236 1105tot_key_writes 0 1106tot_key_deletes 10 1107tot_key_refs 4 1108log_tot_key_attrinfo_bytes 16 1109tot_key_keyinfo_bytes 3760 1110tot_key_prog_bytes 0 1111tot_key_inst_exec 0 1112tot_key_bytes_returned 4220 1113tot_frag_scans 56 1114tot_scan_rows_examined 306 1115tot_scan_rows_returned 215 1116tot_scan_bytes_returned 7116 1117tot_scan_prog_bytes 288 1118tot_scan_bound_bytes 0 1119tot_scan_inst_exec 305 1120conc_frag_scans 0 1121conc_qd_frag_scans 0 1122tot_commits 450 1123fq_name ndbinfo/def/NDB$BLOB_ 1124parent_fq_name ndbinfo/def/t1 1125type User table 1126frag_count 16 1127tot_key_reads 1 1128tot_key_inserts 218 1129tot_key_updates 0 1130tot_key_writes 0 1131tot_key_deletes 28 1132tot_key_refs 0 1133log_tot_key_attrinfo_bytes 16 1134tot_key_keyinfo_bytes 2964 1135tot_key_prog_bytes 0 1136tot_key_inst_exec 0 1137tot_key_bytes_returned 268 1138tot_frag_scans 0 1139tot_scan_rows_examined 0 1140tot_scan_rows_returned 0 1141tot_scan_bytes_returned 0 1142tot_scan_prog_bytes 0 1143tot_scan_bound_bytes 0 1144tot_scan_inst_exec 0 1145conc_frag_scans 0 1146conc_qd_frag_scans 0 1147tot_commits 246 1148fq_name PRIMARY 1149parent_fq_name ndbinfo/def/t1 1150type Ordered index 1151frag_count 16 1152tot_key_reads 0 1153tot_key_inserts 0 1154tot_key_updates 0 1155tot_key_writes 0 1156tot_key_deletes 0 1157tot_key_refs 0 1158log_tot_key_attrinfo_bytes 0 1159tot_key_keyinfo_bytes 0 1160tot_key_prog_bytes 0 1161tot_key_inst_exec 0 1162tot_key_bytes_returned 0 1163tot_frag_scans 80 1164tot_scan_rows_examined 20 1165tot_scan_rows_returned 20 1166tot_scan_bytes_returned 588 1167tot_scan_prog_bytes 768 1168tot_scan_bound_bytes 9888 1169tot_scan_inst_exec 58 1170conc_frag_scans 0 1171conc_qd_frag_scans 0 1172tot_commits 0 1173fq_name ix1$unique 1174parent_fq_name ndbinfo/def/t1 1175type Unique hash index 1176frag_count 16 1177tot_key_reads 3 1178tot_key_inserts 222 1179tot_key_updates 0 1180tot_key_writes 0 1181tot_key_deletes 28 1182tot_key_refs 3 1183log_tot_key_attrinfo_bytes 12 1184tot_key_keyinfo_bytes 1012 1185tot_key_prog_bytes 0 1186tot_key_inst_exec 0 1187tot_key_bytes_returned 0 1188tot_frag_scans 0 1189tot_scan_rows_examined 0 1190tot_scan_rows_returned 0 1191tot_scan_bytes_returned 0 1192tot_scan_prog_bytes 0 1193tot_scan_bound_bytes 0 1194tot_scan_inst_exec 0 1195conc_frag_scans 0 1196conc_qd_frag_scans 0 1197tot_commits 250 1198fq_name ndbinfo/def/t2 1199parent_fq_name NULL 1200type User table 1201frag_count 16 1202tot_key_reads 0 1203tot_key_inserts 6 1204tot_key_updates 0 1205tot_key_writes 0 1206tot_key_deletes 0 1207tot_key_refs 0 1208log_tot_key_attrinfo_bytes 7 1209tot_key_keyinfo_bytes 24 1210tot_key_prog_bytes 0 1211tot_key_inst_exec 0 1212tot_key_bytes_returned 0 1213tot_frag_scans 8 1214tot_scan_rows_examined 0 1215tot_scan_rows_returned 0 1216tot_scan_bytes_returned 0 1217tot_scan_prog_bytes 32 1218tot_scan_bound_bytes 0 1219tot_scan_inst_exec 0 1220conc_frag_scans 0 1221conc_qd_frag_scans 0 1222tot_commits 6 1223explain select count(*) 1224from t2 as tx, t2 as ty 1225where tx.a in (1,3,5) 1226and ty.a = tx.b; 1227id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12281 SIMPLE tx p2,p3,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Parent of 2 pushed join@1; Using where with pushed condition ((`ndbinfo`.`tx`.`a` in (1,3,5)) and (`ndbinfo`.`tx`.`b` is not null)); Using MRR 12291 SIMPLE ty p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 ndbinfo.tx.b 1 100.00 Child of 'tx' in pushed join@1 1230Warnings: 1231Note 1003 /* select#1 */ select count(0) AS `count(*)` from `ndbinfo`.`t2` `tx` join `ndbinfo`.`t2` `ty` where ((`ndbinfo`.`ty`.`a` = `ndbinfo`.`tx`.`b`) and (`ndbinfo`.`tx`.`a` in (1,3,5))) 1232 1233create table save_counters as select * from frag_ops; 1234 1235select count(*) 1236from t2 as tx, t2 as ty 1237where tx.a in (1,3,5) 1238and ty.a = tx.b; 1239count(*) 12402 1241 1242create view changes as select 1243new.fq_name, 1244new.parent_fq_name, 1245new.type, 1246new.frag_count, 1247new.tot_key_reads - old.tot_key_reads, 1248new.tot_key_inserts - old.tot_key_inserts, 1249new.tot_key_updates - old.tot_key_updates, 1250new.tot_key_writes - old.tot_key_writes, 1251new.tot_key_deletes - old.tot_key_deletes, 1252new.tot_key_refs - old.tot_key_refs, 1253new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes, 1254new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes, 1255new.tot_key_prog_bytes - old.tot_key_prog_bytes, 1256new.tot_key_inst_exec - old.tot_key_inst_exec, 1257new.tot_key_bytes_returned - old.tot_key_bytes_returned, 1258new.tot_frag_scans - old.tot_frag_scans, 1259new.tot_scan_rows_examined - old.tot_scan_rows_examined, 1260new.tot_scan_rows_returned - old.tot_scan_rows_returned, 1261new.tot_scan_bytes_returned - old.tot_scan_bytes_returned, 1262new.tot_scan_prog_bytes - old.tot_scan_prog_bytes, 1263new.tot_scan_bound_bytes - old.tot_scan_bound_bytes, 1264new.tot_scan_inst_exec - old.tot_scan_inst_exec, 1265new.tot_commits - old.tot_commits 1266from frag_ops as new, save_counters as old 1267where new.fq_name=old.fq_name and new.fq_name like '%t2'; 1268 1269select * from changes; 1270fq_name ndbinfo/def/t2 1271parent_fq_name NULL 1272type User table 1273frag_count 16 1274new.tot_key_reads - old.tot_key_reads 5 1275new.tot_key_inserts - old.tot_key_inserts 0 1276new.tot_key_updates - old.tot_key_updates 0 1277new.tot_key_writes - old.tot_key_writes 0 1278new.tot_key_deletes - old.tot_key_deletes 0 1279new.tot_key_refs - old.tot_key_refs 1 1280new.log_tot_key_attrinfo_bytes - old.log_tot_key_attrinfo_bytes 2 1281new.tot_key_keyinfo_bytes - old.tot_key_keyinfo_bytes 20 1282new.tot_key_prog_bytes - old.tot_key_prog_bytes 168 1283new.tot_key_inst_exec - old.tot_key_inst_exec 7 1284new.tot_key_bytes_returned - old.tot_key_bytes_returned 88 1285new.tot_frag_scans - old.tot_frag_scans 0 1286new.tot_scan_rows_examined - old.tot_scan_rows_examined 0 1287new.tot_scan_rows_returned - old.tot_scan_rows_returned 0 1288new.tot_scan_bytes_returned - old.tot_scan_bytes_returned 0 1289new.tot_scan_prog_bytes - old.tot_scan_prog_bytes 0 1290new.tot_scan_bound_bytes - old.tot_scan_bound_bytes 0 1291new.tot_scan_inst_exec - old.tot_scan_inst_exec 0 1292new.tot_commits - old.tot_commits 0 1293 1294drop view changes; 1295drop table save_counters; 1296drop view frag_ops; 1297drop table t1; 1298drop table t2; 1299 1300## Check that the new tables return data and exists. 1301select count(*) > 10 from ndb$blocks; 1302count(*) > 10 13031 1304select param_number, param_name from ndb$config_params 1305 where param_name = "DataMemory"; 1306param_number param_name 1307112 DataMemory 1308select * from ndb$dict_obj_types where type_id < 3; 1309type_id type_name 13101 System table 13112 User table 1312select * from ndb$dbtc_apiconnect_state; 1313select * from ndb$dblqh_tcconnect_state; 1314## Check that the new views return data and exists. 1315select block_number from blocks where block_name = "DBTC"; 1316block_number 1317245 1318select param_name from config_params where param_number = 101; 1319param_name 1320NoOfReplicas 1321select type_name from ndb$dict_obj_types where type_id = 3; 1322type_name 1323Unique hash index 1324 1325## All tables in ndbinfo/ should now be using engine=NDBINFO 1326select count(*) from information_schema.tables 1327 where table_schema = 'ndbinfo' and engine != 'ndbinfo'; 1328count(*) 13290 1330 1331set @@optimizer_switch='block_nested_loop=off'; 1332create temporary table old_count 1333 select counter_name, sum(val) as val 1334 from ndbinfo.counters 1335 where block_name='DBSPJ' 1336 group by counter_name; 1337drop table old_count; 1338set @@optimizer_switch=default; 1339 1340select * from ndbinfo.config_params order by param_number; 1341UPDATE ndb$config_params SET param_number=1 WHERE param_name = "NoOfReplicas"; 1342ERROR HY000: Table 'ndb$config_params' is read only 1343 1344UPDATE config_params SET param_number=1 WHERE param_name = "NoOfReplicas"; 1345ERROR HY000: Table 'ndb$config_params' is read only 1346 1347