- developers - lists.mariadb.org

[Maria-developers] Elena please test: (MDEV-4974) memory leak in 5.5.32-MariaDB-1~wheezy-log
by Sergei Petrunia 03 Jan '14

03 Jan '14

Hi Elena, Could you please test the below? The changed code may affect - queries that use GROUP BY/ORDER BY/DISTINCT, or a combination of those. - correlated/uncorrelated subqueries. The subquery should remain a separate SELECT, i.e. it should not be converted into semi-join or optimized away. - combination of the above. The code adds a cleanup, so I expect a possible problem to be where we cleaned up something we shouldn't have. Most likely effects are either crash or wrong result. I don't think this patch has a chance of introducing a memory leak. ----- Forwarded message from "Sergei Petrunia (JIRA)" <jira(a)mariadb.atlassian.net> ----- Date: Fri, 3 Jan 2014 21:26:24 +0200 (EET) From: "Sergei Petrunia (JIRA)" <jira(a)mariadb.atlassian.net> To: psergey(a)montyprogram.com Subject: [JIRA] (MDEV-4974) memory leak in 5.5.32-MariaDB-1~wheezy-log [ https://mariadb.atlassian.net/browse/MDEV-4974?page=com.atlassian.jira.plug… ] Sergei Petrunia commented on MDEV-4974: ---------------------------------------- Elena, could you please test 5.5 tree, patched with psergey-fix-mdev4954.diff ? > memory leak in 5.5.32-MariaDB-1~wheezy-log > ------------------------------------------ > > Key: MDEV-4974 > URL: https://mariadb.atlassian.net/browse/MDEV-4974 > Project: MariaDB Development > Issue Type: Bug > Affects Versions: 5.5.32 > Environment: Debian Wheezy x86_64 > Linux greeneggs.lentz.com.au 3.9.3-x86_64-linode33 #1 SMP Mon May 20 10:22:57 EDT 2013 x86_64 GNU/Linux > Reporter: Daniel Black > Assignee: Sergei Petrunia > Priority: Critical > Fix For: 5.5.35 > > Attachments: allqueries.sql, catinthehat_memory-day_no_indexmerge.png, drupal.sql, green-eggs-mysql_connections-day.png, greeneggs-memory-day.png, greeneggs-memory-week.png, greeneggs-mysql_bin_relay_log-day.png, greeneggs-mysql_commands-day.png, greeneggs-mysql_files_tables-day.png, greeneggs-mysql_innodb_bpool-day.png, greeneggs-mysql_innodb_semaphores-day.png, greeneggs-mysql_innodb_tnx-day.png, greeneggs-mysql_myisam_indexes-day.png, greeneggs-mysql_qcache-day.png, greeneggs-mysql_select_types-day.png, greeneggs-mysql_sorts-day.png, greeneggs-mysql_table_locks-day.png, greeneggs-mysql_tmp_tables-day.png, leaks-track-allqueries.sql, leaks-track.sql, psergey-fix-mdev4954.diff, psergey-mdev4974-xpl1.diff, valgrind.mysqld.27336 > > > After running mariadb-5.5.32 in a multimaster for a few days its almost out of memory on the active master (the one getting all the reads). > The replication slave (same version) doesn't suffer the memory leak (with or without the replication filters defined). > Disabling the query cache on the active master may (was slightly off peak) have slowed the memory leak however it wasn't stopped. In the graph attached the query cache was disabled from Wed 5:30 to Thursday 03:00 > For greeneggs-mysql_commands-day.png the first drop is when query cache was turned back on. At the end I moved the active master to the other server. Other graphs are for this same time interval. > Memory usage calculation: > From http://dev.mysql.com/doc/refman/5.5/en/memory-use.html > per connection: > @read_buffer_size + @read_rnd_buffer_size + @sort_buffer_size + @join_buffer_size + @binlog_cache_size + @thread_stack + @@tmp_table_size = 19070976 > Max_used_connections 15 > Static component: > @key_buffer_size + @query_cache_size + @innodb_buffer_pool_size + @innodb_additional_mem_pool_size + @@innodb_log_buffer_size > 322961408 > select 15 * 19070976 + 322961408; = 609026048 > 609M max > From top: > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > 4532 mysql 20 0 1999m 1.4g 6072 S 4.0 71.9 1017:21 mysqld > I've still got the server running if more status is required. > show engine innodb status > ===================================== > 130830 0:46:23 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 17 seconds > ----------------- > BACKGROUND THREAD > ----------------- > srv_master_thread loops: 372124 1_second, 372076 sleeps, 37113 10_second, 1716 background, 1715 flush > srv_master_thread log flush and writes: 350597 > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 999301, signal count 1149136 > Mutex spin waits 3647275, rounds 12020660, OS waits 198769 > RW-shared spins 896419, rounds 19893071, OS waits 574516 > RW-excl spins 68006, rounds 6887165, OS waits 204958 > Spin rounds per wait: 3.30 mutex, 22.19 RW-shared, 101.27 RW-excl > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for completed aio requests (insert buffer thread) > I/O thread 1 state: waiting for completed aio requests (log thread) > I/O thread 2 state: waiting for completed aio requests (read thread) > I/O thread 3 state: waiting for completed aio requests (read thread) > I/O thread 4 state: waiting for completed aio requests (write thread) > I/O thread 5 state: waiting for completed aio requests (write thread) > Pending normal aio reads: 0 [0, 0] , aio writes: 0 [0, 0] , > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 7906118 OS file reads, 39186717 OS file writes, 23493355 OS fsyncs > 0.65 reads/s, 16384 avg bytes/read, 66.29 writes/s, 29.06 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf: size 1, free list len 287, seg size 289, 111478 merges > merged operations: > insert 106175, delete mark 100854, delete 13481 > discarded operations: > insert 0, delete mark 0, delete 0 > Hash table size 553229, node heap has 584 buffer(s) > 244.87 hash searches/s, 22.53 non-hash searches/s > --- > LOG > --- > Log sequence number 1981376581482 > Log flushed up to 1981376581482 > Last checkpoint at 1981376562791 > Max checkpoint age 84223550 > Checkpoint age target 81591565 > Modified age 18691 > Checkpoint age 18691 > 0 pending log writes, 0 pending chkp writes > 22419510 log i/o's done, 26.00 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 275644416; in additional pool allocated 0 > Total memory allocated by read views 136 > Internal hash tables (constant factor + variable factor) > Adaptive hash index 13998304 (4425832 + 9572472) > Page hash 277432 (buffer pool 0 only) > Dictionary cache 10287074 (1107952 + 9179122) > File system 648160 (82672 + 565488) > Lock system 665688 (664936 + 752) > Recovery system 0 (0 + 0) > Dictionary memory allocated 9179122 > Buffer pool size 16383 > Buffer pool size, bytes 268419072 > Free buffers 1 > Database pages 15798 > Old database pages 5811 > Modified db pages 97 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages made young 10760065, not young 0 > 0.53 youngs/s, 0.00 non-youngs/s > Pages read 7903370, created 882751, written 16452888 > 0.65 reads/s, 0.00 creates/s, 39.47 writes/s > Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 > Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s > LRU len: 15798, unzip_LRU len: 0 > I/O sum[1550]:cur[322], unzip sum[0]:cur[0] > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > 1 read views open inside InnoDB > 0 transactions active inside InnoDB > 0 out of 1000 descriptors used > ---OLDEST VIEW--- > Normal read view > Read view low limit trx n:o 7D2D2F07 > Read view up limit trx id 7D2D2F07 > Read view low limit trx id 7D2D2F07 > Read view individually stored trx ids: > ----------------- > Main thread process no. 4532, id 140201824495360, state: sleeping > Number of rows inserted 1308400, updated 9736429, deleted 1227755, read 34888786828 > 1.00 inserts/s, 11.71 updates/s, 0.00 deletes/s, 249.22 reads/s > ------------------------ > LATEST DETECTED DEADLOCK > ------------------------ > 130830 0:27:15 > *** (1) TRANSACTION: > TRANSACTION 7D2C63D3, ACTIVE 0 sec starting index read > | Variable_name | Value | > | Aborted_clients | 57 | > | Aborted_connects | 0 | > | Access_denied_errors | 0 | > | Aria_pagecache_blocks_not_flushed | 0 | > | Aria_pagecache_blocks_unused | 15737 | > | Aria_pagecache_blocks_used | 3127 | > | Aria_pagecache_read_requests | 262055553 | > | Aria_pagecache_reads | 107163 | > | Aria_pagecache_write_requests | 69866678 | > | Aria_pagecache_writes | 0 | > | Aria_transaction_log_syncs | 0 | > | Binlog_commits | 12529081 | > | Binlog_group_commits | 12486299 | > | Binlog_snapshot_file | mariadb-bin.001408 | > | Binlog_snapshot_position | 57228334 | > | Binlog_bytes_written | 43273655871 | > | Binlog_cache_disk_use | 768128 | > | Binlog_cache_use | 12510967 | > | Binlog_stmt_cache_disk_use | 24 | > | Binlog_stmt_cache_use | 18077 | > | Busy_time | 0.000000 | > | Bytes_received | 60129853267 | > | Bytes_sent | 691695773018 | > | Com_admin_commands | 3809 | > | Com_assign_to_keycache | 0 | > | Com_alter_db | 0 | > | Com_alter_db_upgrade | 0 | > | Com_alter_event | 0 | > | Com_alter_function | 0 | > | Com_alter_procedure | 0 | > | Com_alter_server | 0 | > | Com_alter_table | 114 | > | Com_alter_tablespace | 0 | > | Com_analyze | 0 | > | Com_begin | 62278 | > | Com_binlog | 0 | > | Com_call_procedure | 0 | > | Com_change_db | 69728 | > | Com_change_master | 0 | > | Com_check | 1426 | > | Com_checksum | 0 | > | Com_commit | 45717 | > | Com_create_db | 1 | > | Com_create_event | 0 | > | Com_create_function | 0 | > | Com_create_index | 0 | > | Com_create_procedure | 1 | > | Com_create_server | 0 | > | Com_create_table | 42 | > | Com_create_trigger | 0 | > | Com_create_udf | 0 | > | Com_create_user | 0 | > | Com_create_view | 0 | > | Com_dealloc_sql | 24 | > | Com_delete | 388136 | > | Com_delete_multi | 5 | > | Com_do | 0 | > | Com_drop_db | 1 | > | Com_drop_event | 0 | > | Com_drop_function | 0 | > | Com_drop_index | 0 | > | Com_drop_procedure | 2 | > | Com_drop_server | 0 | > | Com_drop_table | 1 | > | Com_drop_trigger | 0 | > | Com_drop_user | 0 | > | Com_drop_view | 0 | > | Com_empty_query | 0 | > | Com_execute_sql | 24 | > | Com_flush | 6 | > | Com_grant | 0 | > | Com_ha_close | 0 | > | Com_ha_open | 0 | > | Com_ha_read | 0 | > | Com_help | 2 | > | Com_insert | 847948 | > | Com_insert_select | 551 | > | Com_install_plugin | 0 | > | Com_kill | 50 | > | Com_load | 0 | > | Com_lock_tables | 0 | > | Com_optimize | 0 | > | Com_preload_keys | 0 | > | Com_prepare_sql | 24 | > | Com_purge | 0 | > | Com_purge_before_date | 0 | > | Com_release_savepoint | 0 | > | Com_rename_table | 0 | > | Com_rename_user | 0 | > | Com_repair | 0 | > | Com_replace | 0 | > | Com_replace_select | 0 | > | Com_reset | 0 | > | Com_resignal | 0 | > | Com_revoke | 0 | > | Com_revoke_all | 0 | > | Com_rollback | 3 | > | Com_rollback_to_savepoint | 0 | > | Com_savepoint | 0 | > | Com_select | 47174988 | > | Com_set_option | 1189075 | > | Com_signal | 0 | > | Com_show_authors | 0 | > | Com_show_binlog_events | 3 | > | Com_show_binlogs | 1166 | > | Com_show_charsets | 0 | > | Com_show_client_statistics | 0 | > | Com_show_collations | 0 | > | Com_show_contributors | 0 | > | Com_show_create_db | 115 | > | Com_show_create_event | 0 | > | Com_show_create_func | 0 | > | Com_show_create_proc | 0 | > | Com_show_create_table | 4387 | > | Com_show_create_trigger | 0 | > | Com_show_databases | 7 | > | Com_show_engine_logs | 0 | > | Com_show_engine_mutex | 0 | > | Com_show_engine_status | 1167 | > | Com_show_events | 0 | > | Com_show_errors | 0 | > | Com_show_fields | 5721 | > | Com_show_function_status | 0 | > | Com_show_grants | 0 | > | Com_show_keys | 0 | > | Com_show_index_statistics | 0 | > | Com_show_master_status | 1 | > | Com_show_open_tables | 0 | > | Com_show_plugins | 0 | > | Com_show_privileges | 0 | > | Com_show_procedure_status | 0 | > | Com_show_processlist | 1039 | > | Com_show_profile | 0 | > | Com_show_profiles | 0 | > | Com_show_relaylog_events | 0 | > | Com_show_slave_hosts | 0 | > | Com_show_slave_status | 140780 | > | Com_show_status | 12670 | > | Com_show_storage_engines | 0 | > | Com_show_table_statistics | 0 | > | Com_show_table_status | 4287 | > | Com_show_tables | 6794 | > | Com_show_triggers | 4280 | > | Com_show_user_statistics | 0 | > | Com_show_variables | 2175 | > | Com_show_warnings | 0 | > | Com_slave_start | 10 | > | Com_slave_stop | 4 | > | Com_stmt_close | 24 | > | Com_stmt_execute | 24 | > | Com_stmt_fetch | 0 | > | Com_stmt_prepare | 24 | > | Com_stmt_reprepare | 0 | > | Com_stmt_reset | 0 | > | Com_stmt_send_long_data | 0 | > | Com_truncate | 0 | > | Com_uninstall_plugin | 0 | > | Com_unlock_tables | 2 | > | Com_update | 7082015 | > | Com_update_multi | 1 | > | Com_xa_commit | 0 | > | Com_xa_end | 0 | > | Com_xa_prepare | 0 | > | Com_xa_recover | 0 | > | Com_xa_rollback | 0 | > | Com_xa_start | 0 | > | Compression | OFF | > | Connections | 1164212 | > | Cpu_time | 0.000000 | > | Created_tmp_disk_tables | 2202858 | > | Created_tmp_files | 420678 | > | Created_tmp_tables | 5264753 | > | Delayed_errors | 0 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Empty_queries | 14900340 | > | Executed_events | 0 | > | Executed_triggers | 0 | > | Feature_dynamic_columns | 0 | > | Feature_fulltext | 2 | > | Feature_gis | 28 | > | Feature_locale | 0 | > | Feature_subquery | 135848 | > | Feature_timezone | 6850 | > | Feature_trigger | 586 | > | Feature_xml | 0 | > | Flush_commands | 4 | > | Handler_commit | 63318523 | > | Handler_delete | 748245 | > | Handler_discover | 0 | > | Handler_icp_attempts | 71118314 | > | Handler_icp_match | 71108704 | > | Handler_mrr_init | 0 | > | Handler_mrr_key_refills | 0 | > | Handler_mrr_rowid_refills | 0 | > | Handler_prepare | 14968970 | > | Handler_read_first | 1155850 | > | Handler_read_key | 466199920 | > | Handler_read_last | 55346 | > | Handler_read_next | 27258307498 | > | Handler_read_prev | 8491322 | > | Handler_read_rnd | 66364460 | > | Handler_read_rnd_deleted | 1194 | > | Handler_read_rnd_next | 8174479457 | > | Handler_rollback | 21990 | > | Handler_savepoint | 0 | > | Handler_savepoint_rollback | 0 | > | Handler_tmp_update | 15018397 | > | Handler_tmp_write | 515819305 | > | Handler_update | 6481729 | > | Handler_write | 849123 | > | Innodb_adaptive_hash_cells | 553229 | > | Innodb_adaptive_hash_heap_buffers | 581 | > | Innodb_adaptive_hash_hash_searches | 1107671046 | > | Innodb_adaptive_hash_non_hash_searches | 249488632 | > | Innodb_background_log_sync | 350697 | > | Innodb_buffer_pool_pages_data | 15800 | > | Innodb_buffer_pool_bytes_data | 258867200 | > | Innodb_buffer_pool_pages_dirty | 295 | > | Innodb_buffer_pool_bytes_dirty | 4833280 | > | Innodb_buffer_pool_pages_flushed | 16456234 | > | Innodb_buffer_pool_pages_LRU_flushed | 48422 | > | Innodb_buffer_pool_pages_free | 1 | > | Innodb_buffer_pool_pages_made_not_young | 0 | > | Innodb_buffer_pool_pages_made_young | 10760117 | > | Innodb_buffer_pool_pages_misc | 582 | > | Innodb_buffer_pool_pages_old | 5812 | > | Innodb_buffer_pool_pages_total | 16383 | > | Innodb_buffer_pool_read_ahead_rnd | 0 | > | Innodb_buffer_pool_read_ahead | 2072088 | > | Innodb_buffer_pool_read_ahead_evicted | 91126 | > | Innodb_buffer_pool_read_requests | 10973288017 | > | Innodb_buffer_pool_reads | 5715842 | > | Innodb_buffer_pool_wait_free | 9 | > | Innodb_buffer_pool_write_requests | 132620870 | > | Innodb_checkpoint_age | 88015 | > | Innodb_checkpoint_max_age | 84223550 | > | Innodb_checkpoint_target_age | 81591565 | > | Innodb_data_fsyncs | 23497491 | > | Innodb_data_pending_fsyncs | 0 | > | Innodb_data_pending_reads | 0 | > | Innodb_data_pending_writes | 0 | > | Innodb_data_read | 129493962752 | > | Innodb_data_reads | 7906173 | > | Innodb_data_writes | 39194030 | > | Innodb_data_written | 586483447808 | > | Innodb_dblwr_pages_written | 16456234 | > | Innodb_dblwr_writes | 182242 | > | Innodb_deadlocks | 432 | > | Innodb_dict_tables | 1315 | > | Innodb_have_atomic_builtins | ON | > | Innodb_history_list_length | 3523 | > | Innodb_ibuf_discarded_delete_marks | 0 | > | Innodb_ibuf_discarded_deletes | 0 | > | Innodb_ibuf_discarded_inserts | 0 | > | Innodb_ibuf_free_list | 287 | > | Innodb_ibuf_merged_delete_marks | 100855 | > | Innodb_ibuf_merged_deletes | 13481 | > | Innodb_ibuf_merged_inserts | 106192 | > | Innodb_ibuf_merges | 111495 | > | Innodb_ibuf_segment_size | 289 | > | Innodb_ibuf_size | 1 | > | Innodb_log_waits | 0 | > | Innodb_log_write_requests | 73751900 | > | Innodb_log_writes | 22385577 | > | Innodb_lsn_current | 1981377626218 | > | Innodb_lsn_flushed | 1981377626218 | > | Innodb_lsn_last_checkpoint | 1981377538203 | > | Innodb_master_thread_1_second_loops | 372239 | > | Innodb_master_thread_10_second_loops | 37124 | > | Innodb_master_thread_background_loops | 1716 | > | Innodb_master_thread_main_flush_loops | 1715 | > | Innodb_master_thread_sleeps | 372191 | > | Innodb_max_trx_id | 2100117045 | > | Innodb_mem_adaptive_hash | 13965536 | > | Innodb_mem_dictionary | 10287074 | > | Innodb_mem_total | 275644416 | > | Innodb_mutex_os_waits | 198788 | > | Innodb_mutex_spin_rounds | 12021403 | > | Innodb_mutex_spin_waits | 3647342 | > | Innodb_oldest_view_low_limit_trx_id | 2100116933 | > | Innodb_os_log_fsyncs | 22425791 | > | Innodb_os_log_pending_fsyncs | 0 | > | Innodb_os_log_pending_writes | 0 | > | Innodb_os_log_written | 47227117568 | > | Innodb_page_size | 16384 | > | Innodb_pages_created | 882752 | > | Innodb_pages_read | 7903425 | > | Innodb_pages_written | 16456234 | > | Innodb_purge_trx_id | 2100116933 | > | Innodb_purge_undo_no | 0 | > | Innodb_row_lock_current_waits | 0 | > | Innodb_current_row_locks | 0 | > | Innodb_row_lock_time | 3209772 | > | Innodb_row_lock_time_avg | 74 | > | Innodb_row_lock_time_max | 31935 | > | Innodb_row_lock_waits | 43208 | > | Innodb_rows_deleted | 1227755 | > | Innodb_rows_inserted | 1308502 | > | Innodb_rows_read | 34888819968 | > | Innodb_rows_updated | 9738262 | > | Innodb_read_views_memory | 136 | > | Innodb_descriptors_memory | 8000 | > | Innodb_s_lock_os_waits | 574527 | > | Innodb_s_lock_spin_rounds | 19893402 | > | Innodb_s_lock_spin_waits | 896432 | > | Innodb_truncated_status_writes | 0 | > | Innodb_x_lock_os_waits | 204987 | > | Innodb_x_lock_spin_rounds | 6888035 | > | Innodb_x_lock_spin_waits | 68006 | > | Key_blocks_not_flushed | 0 | > | Key_blocks_unused | 5353 | > | Key_blocks_used | 4170 | > | Key_blocks_warm | 141 | > | Key_read_requests | 6295929 | > | Key_reads | 9626 | > | Key_write_requests | 21159 | > | Key_writes | 10962 | > | Last_query_cost | 0.000000 | > | Max_used_connections | 15 | > | Not_flushed_delayed_rows | 0 | > | Open_files | 142 | > | Open_streams | 0 | > | Open_table_definitions | 397 | > | Open_tables | 595 | > | Opened_files | 9756380 | > | Opened_table_definitions | 6425 | > | Opened_tables | 8666 | > | Opened_views | 0 | > | Performance_schema_cond_classes_lost | 0 | > | Performance_schema_cond_instances_lost | 0 | > | Performance_schema_file_classes_lost | 0 | > | Performance_schema_file_handles_lost | 0 | > | Performance_schema_file_instances_lost | 0 | > | Performance_schema_locker_lost | 0 | > | Performance_schema_mutex_classes_lost | 0 | > | Performance_schema_mutex_instances_lost | 0 | > | Performance_schema_rwlock_classes_lost | 0 | > | Performance_schema_rwlock_instances_lost | 0 | > | Performance_schema_table_handles_lost | 0 | > | Performance_schema_table_instances_lost | 0 | > | Performance_schema_thread_classes_lost | 0 | > | Performance_schema_thread_instances_lost | 0 | > | Prepared_stmt_count | 0 | > | Qcache_free_blocks | 6616 | > | Qcache_free_memory | 18486664 | > | Qcache_hits | 75724207 | > | Qcache_inserts | 8304484 | > | Qcache_lowmem_prunes | 1719071 | > | Qcache_not_cached | 2443705 | > | Qcache_queries_in_cache | 8992 | > | Qcache_total_blocks | 24978 | > | Queries | 142821923 | > | Questions | 133827961 | > | Rows_read | 35275818740 | > | Rows_sent | 1787826753 | > | Rows_tmp_read | 592687087 | > | Rpl_status | AUTH_MASTER | > | Select_full_join | 268533 | > | Select_full_range_join | 6393 | > | Select_range | 6218451 | > | Select_range_check | 0 | > | Select_scan | 3587397 | > | Slave_heartbeat_period | 1800.000 | > | Slave_open_temp_tables | 0 | > | Slave_received_heartbeats | 0 | > | Slave_retried_transactions | 0 | > | Slave_running | ON | > | Slow_launch_threads | 0 | > | Slow_queries | 3796864 | > | Sort_merge_passes | 269513 | > | Sort_range | 170798 | > | Sort_rows | 1870364969 | > | Sort_scan | 4917255 | > | Sphinx_error | | > | Sphinx_time | | > | Sphinx_total | | > | Sphinx_total_found | | > | Sphinx_word_count | | > | Sphinx_words | | > | Ssl_accept_renegotiates | 0 | > | Ssl_accepts | 0 | > | Ssl_callback_cache_hits | 0 | > | Ssl_cipher | | > | Ssl_cipher_list | | > | Ssl_client_connects | 0 | > | Ssl_connect_renegotiates | 0 | > | Ssl_ctx_verify_depth | 0 | > | Ssl_ctx_verify_mode | 0 | > | Ssl_default_timeout | 0 | > | Ssl_finished_accepts | 0 | > | Ssl_finished_connects | 0 | > | Ssl_session_cache_hits | 0 | > | Ssl_session_cache_misses | 0 | > | Ssl_session_cache_mode | NONE | > | Ssl_session_cache_overflows | 0 | > | Ssl_session_cache_size | 0 | > | Ssl_session_cache_timeouts | 0 | > | Ssl_sessions_reused | 0 | > | Ssl_used_session_cache_entries | 0 | > | Ssl_verify_depth | 0 | > | Ssl_verify_mode | 0 | > | Ssl_version | | > | Subquery_cache_hit | 4512 | > | Subquery_cache_miss | 3930 | > | Syncs | 4171361 | > | Table_locks_immediate | 85865730 | > | Table_locks_waited | 15 | > | Tc_log_max_pages_used | 0 | > | Tc_log_page_size | 0 | > | Tc_log_page_waits | 46 | > | Threadpool_idle_threads | 0 | > | Threadpool_threads | 0 | > | Threads_cached | 13 | > | Threads_connected | 2 | > | Threads_created | 15 | > | Threads_running | 2 | > | Uptime | 349494 | > | Uptime_since_flush_status | 349494 | > | Variable_name | Value | > | aria_block_size | 8192 | > | aria_checkpoint_interval | 30 | > | aria_checkpoint_log_activity | 1048576 | > | aria_force_start_after_recovery_failures | 0 | > | aria_group_commit | none | > | aria_group_commit_interval | 0 | > | aria_log_file_size | 1073741824 | > | aria_log_purge_type | immediate | > | aria_max_sort_file_size | 9223372036853727232 | > | aria_page_checksum | ON | > | aria_pagecache_age_threshold | 300 | > | aria_pagecache_buffer_size | 134217728 | > | aria_pagecache_division_limit | 100 | > | aria_recover | NORMAL | > | aria_repair_threads | 1 | > | aria_sort_buffer_size | 134217728 | > | aria_stats_method | nulls_unequal | > | aria_sync_log_dir | NEWFILE | > | aria_used_for_temp_tables | ON | > | auto_increment_increment | 2 | > | auto_increment_offset | 2 | > | autocommit | ON | > | automatic_sp_privileges | ON | > | back_log | 50 | > | basedir | /usr | > | big_tables | OFF | > | binlog_annotate_row_events | OFF | > | binlog_cache_size | 32768 | > | binlog_checksum | NONE | > | binlog_direct_non_transactional_updates | OFF | > | binlog_format | MIXED | > | binlog_optimize_thread_scheduling | ON | > | binlog_stmt_cache_size | 32768 | > | bulk_insert_buffer_size | 1048576 | > | character_set_client | latin1 | > | character_set_connection | latin1 | > | character_set_database | latin1 | > | character_set_filesystem | binary | > | character_set_results | latin1 | > | character_set_server | latin1 | > | character_set_system | utf8 | > | character_sets_dir | /usr/share/mysql/charsets/ | > | collation_connection | latin1_swedish_ci | > | collation_database | latin1_swedish_ci | > | collation_server | latin1_swedish_ci | > | completion_type | NO_CHAIN | > | concurrent_insert | ALWAYS | > | connect_timeout | 5 | > | datadir | /var/lib/mysql/ | > | date_format | %Y-%m-%d | > | datetime_format | %Y-%m-%d %H:%i:%s | > | deadlock_search_depth_long | 15 | > | deadlock_search_depth_short | 4 | > | deadlock_timeout_long | 50000000 | > | deadlock_timeout_short | 10000 | > | debug_no_thread_alarm | OFF | > | default_storage_engine | InnoDB | > | default_week_format | 0 | > | delay_key_write | ON | > | delayed_insert_limit | 100 | > | delayed_insert_timeout | 300 | > | delayed_queue_size | 1000 | > | div_precision_increment | 4 | > | engine_condition_pushdown | OFF | > | event_scheduler | OFF | > | expensive_subquery_limit | 100 | > | expire_logs_days | 3 | > | extra_max_connections | 1 | > | extra_port | 0 | > | flush | OFF | > | flush_time | 0 | > | foreign_key_checks | ON | > | ft_boolean_syntax | + -><()~*:""&| | > | ft_max_word_len | 84 | > | ft_min_word_len | 4 | > | ft_query_expansion_limit | 20 | > | ft_stopword_file | (built-in) | > | general_log | OFF | > | general_log_file | greeneggs.log | > | group_concat_max_len | 1024 | > | have_compress | YES | > | have_crypt | YES | > | have_csv | YES | > | have_dynamic_loading | YES | > | have_geometry | YES | > | have_innodb | YES | > | have_ndbcluster | NO | > | have_openssl | DISABLED | > | have_partitioning | YES | > | have_profiling | YES | > | have_query_cache | YES | > | have_rtree_keys | YES | > | have_ssl | DISABLED | > | have_symlink | YES | > | hostname | greeneggs.lentz.com.au | > | ignore_builtin_innodb | OFF | > | ignore_db_dirs | | > | init_connect | | > | init_file | | > | init_slave | | > | innodb_adaptive_flushing | ON | > | innodb_adaptive_flushing_method | estimate | > | innodb_adaptive_hash_index | ON | > | innodb_adaptive_hash_index_partitions | 1 | > | innodb_additional_mem_pool_size | 8388608 | > | innodb_autoextend_increment | 8 | > | innodb_autoinc_lock_mode | 1 | > | innodb_blocking_buffer_pool_restore | OFF | > | innodb_buffer_pool_instances | 1 | > | innodb_buffer_pool_populate | OFF | > | innodb_buffer_pool_restore_at_startup | 600 | > | innodb_buffer_pool_shm_checksum | ON | > | innodb_buffer_pool_shm_key | 0 | > | innodb_buffer_pool_size | 268435456 | > | innodb_change_buffering | all | > | innodb_checkpoint_age_target | 0 | > | innodb_checksums | ON | > | innodb_commit_concurrency | 0 | > | innodb_concurrency_tickets | 500 | > | innodb_corrupt_table_action | assert | > | innodb_data_file_path | ibdata1:10M:autoextend | > | innodb_data_home_dir | | > | innodb_dict_size_limit | 0 | > | innodb_doublewrite | ON | > | innodb_doublewrite_file | | > | innodb_fake_changes | OFF | > | innodb_fast_checksum | OFF | > | innodb_fast_shutdown | 1 | > | innodb_file_format | Antelope | > | innodb_file_format_check | ON | > | innodb_file_format_max | Antelope | > | innodb_file_per_table | ON | > | innodb_flush_log_at_trx_commit | 1 | > | innodb_flush_method | O_DIRECT | > | innodb_flush_neighbor_pages | area | > | innodb_force_load_corrupted | OFF | > | innodb_force_recovery | 0 | > | innodb_ibuf_accel_rate | 100 | > | innodb_ibuf_active_contract | 1 | > | innodb_ibuf_max_size | 134201344 | > | innodb_import_table_from_xtrabackup | 0 | > | innodb_io_capacity | 1000 | > | innodb_kill_idle_transaction | 0 | > | innodb_large_prefix | OFF | > | innodb_lazy_drop_table | 0 | > | innodb_lock_wait_timeout | 50 | > | innodb_locking_fake_changes | ON | > | innodb_locks_unsafe_for_binlog | OFF | > | innodb_log_block_size | 512 | > | innodb_log_buffer_size | 4194304 | > | innodb_log_file_size | 52428800 | > | innodb_log_files_in_group | 2 | > | innodb_log_group_home_dir | ./ | > | innodb_max_bitmap_file_size | 104857600 | > | innodb_max_changed_pages | 1000000 | > | innodb_max_dirty_pages_pct | 75 | > | innodb_max_purge_lag | 0 | > | innodb_merge_sort_block_size | 1048576 | > | innodb_mirrored_log_groups | 1 | > | innodb_old_blocks_pct | 37 | > | innodb_old_blocks_time | 0 | > | innodb_open_files | 400 | > | innodb_page_size | 16384 | > | innodb_print_all_deadlocks | OFF | > | innodb_purge_batch_size | 20 | > | innodb_purge_threads | 1 | > | innodb_random_read_ahead | OFF | > | innodb_read_ahead | linear | > | innodb_read_ahead_threshold | 56 | > | innodb_read_io_threads | 2 | > | innodb_recovery_stats | OFF | > | innodb_recovery_update_relay_log | OFF | > | innodb_replication_delay | 0 | > | innodb_rollback_on_timeout | OFF | > | innodb_rollback_segments | 128 | > | innodb_show_locks_held | 10 | > | innodb_show_verbose_locks | 0 | > | innodb_spin_wait_delay | 6 | > | innodb_stats_auto_update | 1 | > | innodb_stats_method | nulls_equal | > | innodb_stats_on_metadata | ON | > | innodb_stats_sample_pages | 8 | > | innodb_stats_update_need_lock | 1 | > | innodb_strict_mode | OFF | > | innodb_support_xa | ON | > | innodb_sync_spin_loops | 30 | > | innodb_table_locks | ON | > | innodb_thread_concurrency | 0 | > | innodb_thread_concurrency_timer_based | OFF | > | innodb_thread_sleep_delay | 10000 | > | innodb_track_changed_pages | OFF | > | innodb_use_atomic_writes | OFF | > | innodb_use_fallocate | OFF | > | innodb_use_global_flush_log_at_trx_commit | ON | > | innodb_use_native_aio | ON | > | innodb_use_sys_malloc | ON | > | innodb_use_sys_stats_table | OFF | > | innodb_version | 5.5.32-MariaDB-30.2 | > | innodb_write_io_threads | 2 | > | interactive_timeout | 28800 | > | join_buffer_size | 131072 | > | join_buffer_space_limit | 2097152 | > | join_cache_level | 2 | > | keep_files_on_create | OFF | > | key_buffer_size | 8388608 | > | key_cache_age_threshold | 300 | > | key_cache_block_size | 1024 | > | key_cache_division_limit | 100 | > | key_cache_segments | 0 | > | large_files_support | ON | > | large_page_size | 0 | > | large_pages | OFF | > | lc_messages | en_US | > | lc_messages_dir | /usr/share/mysql | > | lc_time_names | en_US | > | license | GPL | > | local_infile | ON | > | lock_wait_timeout | 31536000 | > | locked_in_memory | OFF | > | log | OFF | > | log_bin | ON | > | log_bin_trust_function_creators | OFF | > | log_error | | > | log_output | FILE | > | log_queries_not_using_indexes | ON | > | log_slave_updates | ON | > | log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | > | log_slow_queries | ON | > | log_slow_rate_limit | 1 | > | log_slow_verbosity | query_plan | > | log_warnings | 2 | > | long_query_time | 3.000000 | > | low_priority_updates | OFF | > | lower_case_file_system | OFF | > | lower_case_table_names | 0 | > | master_verify_checksum | OFF | > | max_allowed_packet | 16777216 | > | max_binlog_cache_size | 18446744073709547520 | > | max_binlog_size | 104857600 | > | max_binlog_stmt_cache_size | 18446744073709547520 | > | max_connect_errors | 10 | > | max_connections | 100 | > | max_delayed_threads | 20 | > | max_error_count | 64 | > | max_heap_table_size | 16777216 | > | max_insert_delayed_threads | 20 | > | max_join_size | 18446744073709551615 | > | max_length_for_sort_data | 1024 | > | max_long_data_size | 16777216 | > | max_prepared_stmt_count | 16382 | > | max_relay_log_size | 0 | > | max_seeks_for_key | 4294967295 | > | max_sort_length | 1024 | > | max_sp_recursion_depth | 0 | > | max_tmp_tables | 32 | > | max_user_connections | 0 | > | max_write_lock_count | 4294967295 | > | metadata_locks_cache_size | 1024 | > | min_examined_row_limit | 0 | > | mrr_buffer_size | 262144 | > | multi_range_count | 256 | > | myisam_block_size | 1024 | > | myisam_data_pointer_size | 6 | > | myisam_max_sort_file_size | 9223372036853727232 | > | myisam_mmap_size | 18446744073709551615 | > | myisam_recover_options | BACKUP,QUICK | > | myisam_repair_threads | 1 | > | myisam_sort_buffer_size | 536870912 | > | myisam_stats_method | nulls_unequal | > | myisam_use_mmap | OFF | > | net_buffer_length | 16384 | > | net_read_timeout | 30 | > | net_retry_count | 10 | > | net_write_timeout | 60 | > | old | OFF | > | old_alter_table | OFF | > | old_passwords | OFF | > | open_files_limit | 2159 | > | optimizer_prune_level | 1 | > | optimizer_search_depth | 62 | > | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off | > | performance_schema | OFF | > | performance_schema_events_waits_history_long_size | 10000 | > | performance_schema_events_waits_history_size | 10 | > | performance_schema_max_cond_classes | 80 | > | performance_schema_max_cond_instances | 1000 | > | performance_schema_max_file_classes | 50 | > | performance_schema_max_file_handles | 32768 | > | performance_schema_max_file_instances | 10000 | > | performance_schema_max_mutex_classes | 200 | > | performance_schema_max_mutex_instances | 1000000 | > | performance_schema_max_rwlock_classes | 30 | > | performance_schema_max_rwlock_instances | 1000000 | > | performance_schema_max_table_handles | 100000 | > | performance_schema_max_table_instances | 50000 | > | performance_schema_max_thread_classes | 50 | > | performance_schema_max_thread_instances | 1000 | > | pid_file | /var/run/mysqld/mysqld.pid | > | plugin_dir | /usr/lib/mysql/plugin/ | > | plugin_maturity | unknown | > | port | 3306 | > | preload_buffer_size | 32768 | > | profiling | OFF | > | profiling_history_size | 15 | > | progress_report_time | 56 | > | protocol_version | 10 | > | query_alloc_block_size | 8192 | > | query_cache_limit | 131072 | > | query_cache_min_res_unit | 4096 | > | query_cache_size | 33554432 | > | query_cache_strip_comments | OFF | > | query_cache_type | ON | > | query_cache_wlock_invalidate | OFF | > | query_prealloc_size | 8192 | > | range_alloc_block_size | 4096 | > | read_buffer_size | 1048576 | > | read_only | ON | > | read_rnd_buffer_size | 524288 | > | relay_log | | > | relay_log_index | | > | relay_log_info_file | relay-log.info | > | relay_log_purge | ON | > | relay_log_recovery | OFF | > | relay_log_space_limit | 0 | > | replicate_annotate_row_events | OFF | > | replicate_do_db | | > | replicate_do_table | | > | replicate_events_marked_for_skip | replicate | > | replicate_ignore_db | | > | replicate_ignore_table | peoplesforum.cache | > | replicate_wild_do_table | | > | replicate_wild_ignore_table | peoplesforum.cache% | > | report_host | greeneggs | > | report_password | | > | report_port | 3306 | > | report_user | | > | rowid_merge_buff_size | 8388608 | > | rpl_recovery_rank | 0 | > | secure_auth | OFF | > | secure_file_priv | | > | server_id | 12302 | > | skip_external_locking | ON | > | skip_name_resolve | ON | > | skip_networking | OFF | > | skip_show_database | OFF | > | slave_compressed_protocol | OFF | > | slave_exec_mode | STRICT | > | slave_load_tmpdir | /tmp | > | slave_max_allowed_packet | 1073741824 | > | slave_net_timeout | 3600 | > | slave_skip_errors | 1062 | > | slave_sql_verify_checksum | ON | > | slave_transaction_retries | 10 | > | slave_type_conversions | | > | slow_launch_time | 2 | > | slow_query_log | ON | > | slow_query_log_file | /var/log/mysql/mariadb-slow.log | > | socket | /var/run/mysqld/mysqld.sock | > | sort_buffer_size | 262144 | > | sql_auto_is_null | OFF | > | sql_big_selects | ON | > | sql_big_tables | OFF | > | sql_buffer_result | OFF | > | sql_log_bin | ON | > | sql_log_off | OFF | > | sql_low_priority_updates | OFF | > | sql_max_join_size | 18446744073709551615 | > | sql_mode | NO_ENGINE_SUBSTITUTION | > | sql_notes | ON | > | sql_quote_show_create | ON | > | sql_safe_updates | OFF | > | sql_select_limit | 18446744073709551615 | > | sql_slave_skip_counter | 0 | > | sql_warnings | OFF | > | ssl_ca | | > | ssl_capath | | > | ssl_cert | | > | ssl_cipher | | > | ssl_key | | > | storage_engine | InnoDB | > | stored_program_cache | 256 | > | sync_binlog | 3 | > | sync_frm | ON | > | sync_master_info | 0 | > | sync_relay_log | 0 | > | sync_relay_log_info | 0 | > | system_time_zone | UTC | > | table_definition_cache | 400 | > | table_open_cache | 1024 | > | thread_cache_size | 128 | > | thread_concurrency | 10 | > | thread_handling | one-thread-per-connection | > | thread_pool_idle_timeout | 60 | > | thread_pool_max_threads | 500 | > | thread_pool_oversubscribe | 3 | > | thread_pool_size | 8 | > | thread_pool_stall_limit | 500 | > | thread_stack | 294912 | > | time_format | %H:%i:%s | > | time_zone | SYSTEM | > | timed_mutexes | OFF | > | tmp_table_size | 16777216 | > | tmpdir | /tmp | > | transaction_alloc_block_size | 8192 | > | transaction_prealloc_size | 4096 | > | tx_isolation | REPEATABLE-READ | > | unique_checks | ON | > | updatable_views_with_limit | YES | > | userstat | OFF | > | version | 5.5.32-MariaDB-1~wheezy-log | > | version_comment | mariadb.org binary distribution | > | version_compile_machine | x86_64 | > | version_compile_os | debian-linux-gnu | > | wait_timeout | 600 | > I'm planning on doing a debug build from MDEV-572 and maybe try to get valgrind to narrow it down (if that doesn't bring the server to a total halt). Better suggestions welcome. -- This message was sent by Atlassian JIRA (v6.2-OD-05-4#6207) ----- End forwarded message -----

1 0

[Maria-developers] MDEV-5115 review
by Kristian Nielsen 02 Jan '14

02 Jan '14

Hi Serg, Here is my review of your MDEV-5115 patch. First, a general comment: Another way to fix this would be a smaller patch. It would not attempt to implement anything related to the new event types. Instead, it would just have a small bit of code that allows to read the binary format of the new event types, but it would represent them internally as the old type, just ignoring any of the extra information, which appears to be currently unused outside of NDB. Probably then it would also make sense to not implement the renaming of the old event types. There would just be three extra cases in Log_event::read_log_event(), and a bit of extra code in the *_rows_log_event construstructors to be able to ignore any extra info. However, I do not really have much against your approach of doing a partial merge to get code that is closer to what is in MySQL, if you prefer that. I mention it because we discussed briefly already on IRC whether a smaller patch could be enough. A few detailed comments below. Otherwise, while I would have probably done the minimal patch myself, I am ok with this way also. So ok to push after fixing the few commit and code comments mentioned below. - Kristian. > ------------------------------------------------------------ > revno: 3921 > revision-id: sergii(a)pisem.net-20131203192301-afokgpslfq0f3xg8 > parent: sergii(a)pisem.net-20131201111624-xe3f1ul6otcyvyom > fixes bug: https://mariadb.atlassian.net/browse/MDEV-5115 > committer: Sergei Golubchik <sergii(a)pisem.net> > branch nick: 10.0 > timestamp: Tue 2013-12-03 20:23:01 +0100 > message: > MDEV-5115 RBR from MySQL 5.6 to MariaDB 10.0 does not work > > Patially merge WL#5917, to understand v2 row events "Partially merge WL#5917, which introduces new event types for row-based binlogging. This is needed to be able to replicate from a >=5.6 MySQL master to a MariaDB slave." > === modified file 'mysql-test/suite/binlog/t/binlog_old_versions.test' > --- mysql-test/suite/binlog/t/binlog_old_versions.test 2013-04-09 21:27:41 +0000 > +++ mysql-test/suite/binlog/t/binlog_old_versions.test 2013-12-03 19:23:01 +0000 > @@ -24,6 +24,17 @@ > > source include/not_embedded.inc; > > +--echo ==== Read binlog with v2 row events ==== > + > +# Read binlog. > +--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_trunk_row_v2.001 | $MYSQL --local-infile=1 > +# Show result. > +SELECT * FROM t1 ORDER BY a; > +SELECT * FROM t2 ORDER BY a; > +SELECT COUNT(*) FROM t3; > +# Reset. > +DROP TABLE t1, t2, t3; > + > > --echo ==== Read modern binlog (version 5.1.23) ==== > Is this the only test we have of reading the v2 row event types? I think it is not a particularly good test, as it tests mysqlbinlog reading them, while the interesting thing is a slave receiving them from the master. Even though some code is shared between the two there are lots of differences as well. The better test would be to install the pre-generated binlog file manually on a master and have the slave replicate it. However, it is easy to end up spending too much time on setting up these replication tests and getting them to work reliably on all platforms, so maybe not worth it. Maybe a good compromise is to instead ask Elena to test it manually against a real MySQL 5.6 server - after all, that is the actual case we want to work. > === modified file 'sql/log_event.h' > --- sql/log_event.h 2013-11-01 11:00:11 +0000 > +++ sql/log_event.h 2013-12-03 19:23:01 +0000 > @@ -659,11 +663,11 @@ enum Log_event_type > PRE_GA_DELETE_ROWS_EVENT = 22, > > /* > - These event numbers are used from 5.1.16 and forward > + These event numbers are used from 5.1.16 until mysql-trunk-xx Please change this comment to explain the actual situation. Ie. that they are used in MariaDB, but not in MySQL >= 5.6. > @@ -677,6 +681,20 @@ enum Log_event_type > HEARTBEAT_LOG_EVENT= 27, > > /* > + In some situations, it is necessary to send over ignorable > + data to the slave: data that a slave can handle in case there > + is code for handling it, but which can be ignored if it is not > + recognized. > + */ > + IGNORABLE_LOG_EVENT= 28, > + ROWS_QUERY_LOG_EVENT= 29, Add a comment that these are MySQL 5.6 events that are unimplemented in MariaDB. (Do we need another MDEV to be able to replicate against a MySQL master that produces ROWS_QUERY_LOG_EVENT? It is quite similar to our ANNOTATE_ROWS_EVENT, IIRC). > + /* Version 2 of the Row events */ > + WRITE_ROWS_EVENT = 30, > + UPDATE_ROWS_EVENT = 31, > + DELETE_ROWS_EVENT = 32, Add comment that these are only generated by MySQL 5.6.

2 1

[Maria-developers] review: [Commits] Rev 3917: MDEV-9095: Executing triggers on slave in row-based replication in file:///home/bell/maria/bzr/work-maria-10.0-MDEV-5095/
by Michael Widenius 19 Dec '13

19 Dec '13

Hi! > === modified file 'sql/log_event.cc' > === modified file 'sql/sql_delete.cc' > --- a/sql/sql_delete.cc 2013-10-16 09:38:42 +0000 > +++ b/sql/sql_delete.cc 2013-12-19 08:10:00 +0000 > @@ -525,12 +525,7 @@ bool mysql_delete(THD *thd, TABLE_LIST * > table->triggers->has_triggers(TRG_EVENT_DELETE, > TRG_ACTION_AFTER)) > { > - /* > - The table has AFTER DELETE triggers that might access to subject table > - and therefore might need delete to be done immediately. So we turn-off > - the batching. > - */ > - (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); > + table->prepare_triggers_for_delete_stmt_or_event(); > will_batch= FALSE; > } In the above code, you check for this twice: table->triggers->has_triggers(TRG_EVENT_DELETE, TRG_ACTION_AFTER)) Would be better to have prepare_triggers_for_delete_stmt_or_event() return 1 if we have delete triggers and do: if (table->prepare_triggers_for_delete_stmt_or_event()) will_batch= FALSE; else will_batch= ... <cut> > === modified file 'sql/sql_update.cc' > --- a/sql/sql_update.cc 2013-10-16 09:38:42 +0000 > +++ b/sql/sql_update.cc 2013-12-19 08:10:00 +0000 > @@ -707,12 +707,7 @@ int mysql_update(THD *thd, > table->triggers->has_triggers(TRG_EVENT_UPDATE, > TRG_ACTION_AFTER)) > { > - /* > - The table has AFTER UPDATE triggers that might access to subject > - table and therefore might need update to be done immediately. > - So we turn-off the batching. > - */ > - (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); > + table->prepare_triggers_for_update_stmt_or_event(); > will_batch= FALSE; > } Same here. Regards, Monty

1 0

[Maria-developers] review of mdev-5095 (executing triggers on slave)
by Michael Widenius 19 Dec '13

19 Dec '13

Hi! Here is the review of mdev-5095 I got the diff from: bzr diff -r3942.. > === modified file 'mysql-test/r/mysqld--help.result' > --- mysql-test/r/mysqld--help.result 2012-10-18 21:33:06 +0000 > +++ mysql-test/r/mysqld--help.result 2013-12-03 10:53:01 +0000 > @@ -747,6 +747,14 @@ > --slave-net-timeout=# > Number of seconds to wait for more data from a > master/slave connection before aborting the read > + --slave-run-triggers-for-rbr=name > + Modes for how triggers in row-base replication on slave > + side will be executed. Legal values are NO (default), YES > + and LOGGING. NO means that trigger for RBR will not be > + running on slave YES and LOGGING means that triggers will > + be running on slave (if there was not triggers running on > + the naster), LOGGING also mens that flag about executed > + triggers will be written to binlog. Suggested new text: --slave-run-triggers-for-rbr=name Modes for how triggers in row-base replication on slave side will be executed. Legal values are NO (default), YES and LOGGING. NO means that trigger for RBR will not be running on slave. YES and LOGGING means that triggers will be running on slave, if there was not triggers running on the master for the statement. LOGGING also means that the executed triggers will be written to binlog. <cut> > === modified file 'sql/log_event.cc' <cut> > -#if !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION) > +#if defined(MYSQL_SERVER) && defined(HAVE_REPLICATION) Was the above needed or mostly an optimization? > + > +static void restore_empty_query_table_list(LEX *lex) > +{ > + if (lex->first_not_own_table()) > + (*lex->first_not_own_table()->prev_global)= NULL; > + lex->query_tables= NULL; > + lex->query_tables_last= &lex->query_tables; > +} Add a comment of what is the purpose of the above function. > @@ -8340,6 +8351,22 @@ int Rows_log_event::do_apply_event(Relay > /* A small test to verify that objects have consistent types */ > DBUG_ASSERT(sizeof(thd->variables.option_bits) == sizeof(OPTION_RELAXED_UNIQUE_CHECKS)); > > + if (slave_run_triggers_for_rbr) > + { > + LEX *lex= thd->lex; > + uint8 new_trg_event_map= get_trg_event_map(); > + > + DBUG_ASSERT(lex->query_tables == NULL); > + if ((lex->query_tables= rli->tables_to_lock)) > + rli->tables_to_lock->prev_global= &lex->query_tables; Add a comment why we have to set prev_global (not obvious). It's also not obvious why restore_empty_tables() is not resetting rli->tables_to_lock->prev_global as it's reseting the the rest of the changed things. > @@ -8429,8 +8462,11 @@ int Rows_log_event::do_apply_event(Relay > */ > TABLE_LIST *ptr= rli->tables_to_lock; > for (uint i=0 ; ptr && (i < rli->tables_to_lock_count); ptr= ptr->next_global, i++) > + { > const_cast<Relay_log_info*>(rli)->m_table_map.set_table(ptr->table_id, ptr->table); > - What does this test really do ? (I was quickly checking the code to understand what m_table_id stands for, but it was not obvious). > + if (m_table_id == ptr->table_id) > + master_had_triggers= ((RPL_TABLE_LIST*)ptr)->master_had_triggers; <cut> > if (table) > { > bool transactional_table= table->file->has_transactions(); > @@ -8618,6 +8656,9 @@ int Rows_log_event::do_apply_event(Relay > */ > thd->reset_current_stmt_binlog_format_row(); > thd->is_slave_error= 1; > + /* remove trigger's tables */ > + if (slave_run_triggers_for_rbr) > + restore_empty_query_table_list(thd->lex); > DBUG_RETURN(error); > } You do the above 'restore_empty_query_table_list(thd->lex);' before every return in this function, except the last return. - Isn't it better to do a 'error:' tag at the end of the function to ensure we don't miss any cases ? - Add a comment why we don't need to do this at the very end of Rows_log_event::do_apply_event(). Something like: /* We don't have to call restore_empty_query_table_list() here as it's called in rows_event_stmt_cleanup(). */ When is lex->query_tables used? Is it only used by 'open' ? If yes, isn't it enough to always reset it after the 'open' call? > @@ -9773,6 +9831,29 @@ Write_rows_log_event::do_before_row_oper > from the start. > */ > } > + if (slave_run_triggers_for_rbr && !master_had_triggers && m_table->triggers ) > + { > + if (m_table->triggers->has_triggers(TRG_EVENT_DELETE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER DELETE triggers that might access to > + subject table and therefore might need delete to be done > + immediately. So we turn-off the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); > + } > + if (m_table->triggers->has_triggers(TRG_EVENT_UPDATE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER UPDATE triggers that might access to subject > + table and therefore might need update to be done immediately. > + So we turn-off the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); > + } > + } Good that you noticed and fixed the above. However, why not just call the function prepare_triggers_for_insert_stmt() that does exactly the same thing? <cut> > @@ -10749,6 +10894,17 @@ Delete_rows_log_event::do_before_row_ope > */ > return 0; > } > + if (slave_run_triggers_for_rbr && !master_had_triggers && m_table->triggers && > + m_table->triggers->has_triggers(TRG_EVENT_DELETE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER DELETE triggers that might access to subject table > + and therefore might need delete to be done immediately. So we turn-off > + the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); > + } Would be better if we could do a function 'prepare_triggers_for_delete_stmt()' and use the same code here and in sql_delete.cc <cut> > @@ -10877,6 +11049,18 @@ Update_rows_log_event::do_before_row_ope > > m_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; > > + if (slave_run_triggers_for_rbr && !master_had_triggers && m_table->triggers && > + m_table->triggers->has_triggers(TRG_EVENT_UPDATE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER UPDATE triggers that might access to subject > + table and therefore might need update to be done immediately. > + So we turn-off the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); > + } > + Would be better if we could do a function 'prepare_triggers_for_update_stmt()' and use the same code here and in sql_update.cc <cut> Regards, Monty

2 4

[Maria-developers] FW: Rev 3758: MDEV-5470: Cost-based subquery item pushdown must take into account semi-joins
by Sergei Petrunia 18 Dec '13

18 Dec '13

Hi Timour, Please find below a patch that should implement execution-time protection against malfunction between condition moving and semi-joins. There needs also to be an optimization-time counterpart, which should have the same logic. Will you code it yourself or want me to? ----- Forwarded message from Sergei Petrunia <psergey(a)askmonty.org> ----- Date: Thu, 19 Dec 2013 02:49:22 +0400 From: Sergei Petrunia <psergey(a)askmonty.org> To: commits(a)mariadb.org Subject: Rev 3758: MDEV-5470: Cost-based subquery item pushdown must take into account semi-joins At http://bazaar.launchpad.net/~maria-captains/maria/10.0-mdev83 ------------------------------------------------------------ revno: 3758 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 10.0-mdev83 timestamp: Thu 2013-12-19 06:50:28 +0400 message: MDEV-5470: Cost-based subquery item pushdown must take into account semi-joins - When moving expensive conditions, take into account that semi-joins limit where they can be moved. - This is only execution part in JOIN::setup_dynamic_conditions(). Optimization part is not yet present. - No testcases yet. - Moving inside SJ-Materialization nests doesn't seem to be supported (either before this patch or after. This patch itself does take SJ-Materialization into account) === modified file 'sql/item_cmpfunc.cc' --- sql/item_cmpfunc.cc 2013-11-01 11:10:38 +0000 +++ sql/item_cmpfunc.cc 2013-12-19 02:50:28 +0000 @@ -6591,6 +6591,8 @@ void Item_func_collect_stats::dbug_print } #endif /*DBUG_OFF*/ +JOIN_TAB *find_last_tab_for_dynamic_cond(table_map cond_tables, + JOIN *join, JOIN_TAB *join_tab); Item_func_dynamic_cond::Item_func_dynamic_cond(Item *cond, JOIN_TAB *atab, JOIN_TAB **ctab) : Item_func_collect_stats(cond) @@ -6624,13 +6626,15 @@ Item_func_dynamic_cond::Item_func_dynami if (active_tab->bush_root_tab) { first_tab= active_tab->bush_root_tab->bush_children->start; - last_tab= active_tab->bush_root_tab->bush_children->end; + //last_tab= active_tab->bush_root_tab->bush_children->end; } else { first_tab= join->join_tab + join->const_tables; - last_tab= join->join_tab + join->top_join_tab_count; + //last_tab= join->join_tab + join->top_join_tab_count; } + last_tab= find_last_tab_for_dynamic_cond(cond->used_tables(), + active_tab->join, active_tab); DBUG_ASSERT(active_tab >= first_tab && active_tab < last_tab); === modified file 'sql/item_cmpfunc.h' --- sql/item_cmpfunc.h 2013-10-18 19:56:55 +0000 +++ sql/item_cmpfunc.h 2013-12-19 02:50:28 +0000 @@ -561,14 +561,20 @@ class Item_func_collect_stats : public I class Item_func_dynamic_cond : public Item_func_collect_stats { -protected: +protected: // as if there were any ancestor classes? List<Item_subselect> subqueries; /* The join_tab where the condition is currently 'pushed'.*/ st_join_table *active_tab; /* The join_tab currently being executed. */ st_join_table **exec_tab; /* The boundaries of the sub-plan where this predicate can be pushed. */ - st_join_table *first_tab, *last_tab; + st_join_table *first_tab; + st_join_table *last_tab; //psergey-todo: this should point to the last tab. + // conditions depending on semi-join tables have + // limits re. how far they can be moved. +public: + inline st_join_table * get_last_tab() { return last_tab; } +protected: /* The relative access cost based on optimizer estimates. This is the cost of all access methods per one record from the first table in the plan. === modified file 'sql/opt_subselect.cc' --- sql/opt_subselect.cc 2013-11-04 15:56:09 +0000 +++ sql/opt_subselect.cc 2013-12-19 02:50:28 +0000 @@ -1480,6 +1480,7 @@ static bool convert_subq_to_sj(JOIN *par sj_nest->sj_subq_pred= subq_pred; sj_nest->original_subq_pred_used_tables= subq_pred->used_tables() | subq_pred->left_expr->used_tables(); + sj_nest->sj_strategys_join_tab= uint(-1); /* Nests do not participate in those 'chains', so: */ /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/ emb_join_list->push_back(sj_nest); @@ -4321,6 +4322,21 @@ int init_dups_weedout(JOIN *join, uint f /* + For each SJ-inner table in [start_tab; end_tab], set emb_sj_nest to idx +*/ +static void update_sj_strategys_join_tab(JOIN_TAB *start_tab, + JOIN_TAB *end_tab, + uint idx) +{ + for (JOIN_TAB *j= start_tab; j < end_tab; j++) + { + TABLE_LIST *emb_nest; + if ((emb_nest= j->emb_sj_nest)) + emb_nest->sj_strategys_join_tab= idx; + } +} + +/* Setup the strategies to eliminate semi-join duplicates. SYNOPSIS @@ -4448,6 +4464,10 @@ int setup_semijoin_dups_elimination(JOIN for (uint j= i; j < i + pos->n_sj_tables; j++) join->join_tab[j].inside_loosescan_range= TRUE; + + update_sj_strategys_join_tab(join->join_tab + i, + join->join_tab + i + pos->n_sj_tables, + i + pos->n_sj_tables - 1); /* Calculate key length */ keylen= 0; @@ -4461,6 +4481,7 @@ int setup_semijoin_dups_elimination(JOIN tab[pos->n_sj_tables - 1].do_firstmatch= tab; i+= pos->n_sj_tables; pos+= pos->n_sj_tables; + break; } case SJ_OPT_DUPS_WEEDOUT: @@ -4503,6 +4524,9 @@ int setup_semijoin_dups_elimination(JOIN break; } } + update_sj_strategys_join_tab(join->join_tab + i, + join->join_tab + i + pos->n_sj_tables, + i + pos->n_sj_tables - 1); init_dups_weedout(join, first_table, i, i + pos->n_sj_tables - first_table); i+= pos->n_sj_tables; @@ -4558,6 +4582,9 @@ int setup_semijoin_dups_elimination(JOIN } } j[-1].do_firstmatch= jump_to; + update_sj_strategys_join_tab(join->join_tab + i, + join->join_tab + i + pos->n_sj_tables, + i + pos->n_sj_tables - 1); i+= pos->n_sj_tables; pos+= pos->n_sj_tables; === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2013-11-08 12:36:02 +0000 +++ sql/sql_select.cc 2013-12-19 02:50:28 +0000 @@ -24608,8 +24608,8 @@ double JOIN::static_pushdown_cost(uint i @param pred the predicate to be wrapped in Item_func_dynamic_cond @param init_tab the initial 'active' JOIN_TAB where the dynamic predicate is pushed to - @param cur_tab pointer to the JOIN_TAB* that stores the current JOIN_TAB - during execution + @param active_tab_ptr pointer to the JOIN_TAB* that stores the current JOIN_TAB + during execution */ static Item_func_collect_stats * wrap_with_dynamic_conds(Item *pred, JOIN_TAB *init_tab, JOIN_TAB **active_tab_ptr, @@ -24674,6 +24674,81 @@ wrap_with_dynamic_conds(Item *pred, JOIN } +/* + @brief Find the last JOIN_TAB that a condition can be attached. + + + @param cond_tables used_tables() of the condition that we intend to move. + @param join_tab The left-most join tab where the condition can be + attached (in other words, default attachment point) + + @detail + In a regular inner join, one can take a condition that is attached to a + table $T, and move it to any table that comes after $T. + + When semi-joins are present, this is no longer true. Detailed explanation + can be found in maria-developers@, email "Movable conditions and semi-joins". + In short: + + - Inside an SJ-Materialization nest, a condition can be moved to any table to + the right, but it must stay within the nest. (We don't support nested + semi-joins, so one will not find semi-joins inside an SJ-M nest) + + - All other Semi-Join strategies have a "range" where they apply. If a + condition depends on a table that is within a semi-join, it must not be + moved over the right edge of the range. +*/ + +JOIN_TAB *find_last_tab_for_dynamic_cond(table_map cond_tables, + JOIN *join, JOIN_TAB *join_tab) +{ + if (join_tab->bush_root_tab) + { + /* + We are inside SJ-Materialization nest. There are no semi-joins here, + return the last tab in the nest. + */ + return join_tab->bush_root_tab->bush_children->end; + } + + /* Start from the right-most bound */ + uint last_tab = join->top_join_tab_count; + + cond_tables= cond_tables & ~(PSEUDO_TABLE_BITS | join->const_table_map); + /* + Walk through semi-joins that this table depends on, and check their last + join tabs. The smallest last join tab sets the limit about how far right + the condition can be moved. + */ + Table_map_iterator tm_it(cond_tables); + int tableno; + while ((tableno = tm_it.next_bit()) != Table_map_iterator::BITMAP_END) + { + TABLE_LIST *sj_nest; + if ((sj_nest= join->map2table[tableno]->emb_sj_nest)) + { + if (sj_nest->is_active_sjm()) + { + /* + Whoa this condition also depends on a table from within + SJ-Materialization nest. (Can happen possible because of equality + propagation/substitution). If we didn't return earlier in this + function, we're not inside a semi-join nest. A value that depends + on a table within a SJ-Materialization nest means that it depends on + the materialized table columns. + */ + continue; + } + + if (sj_nest->sj_strategys_join_tab != uint(-1) && + sj_nest->sj_strategys_join_tab < last_tab) + last_tab= sj_nest->sj_strategys_join_tab; + } + } + return join->join_tab + last_tab; +} + + /** Make expensive subqueries dynamically pushdownable. */ @@ -24703,7 +24778,12 @@ bool JOIN::setup_dynamic_conditions() Update the least cardinality join_tab for each tab for the chosen join order. Check if there are any conditions at all that need to be made dynamic. */ - min_tab= NULL; + min_tab= NULL; + /* + psergey-todo: the following loop should walk inside SJ-Materialization + nests, too. Maybe, this whole function should be invoked for each + SJ-Materilization nest. + */ for (tab= last_tab - 1; tab >= first_tab; tab--) { if (optimizer_flag(thd, OPTIMIZER_SWITCH_STATIC_COND_PUSHDOWN)) @@ -24746,7 +24826,8 @@ bool JOIN::setup_dynamic_conditions() */ for (tab= first_tab; tab < last_tab; tab++) { - Item_func_collect_stats *wrapped_select_cond= NULL, *cur_wrapped_cond; + Item_func_collect_stats *wrapped_select_cond= NULL; + Item_func_dynamic_cond *cur_wrapped_cond; if (tab->select_cond && !(wrapped_select_cond= @@ -24800,14 +24881,29 @@ bool JOIN::setup_dynamic_conditions() Add all dynamic conditions pushed to previous JOIN_TABs also to the current JOIN_TAB. This allows to "move" a dynamic condition from one tab to another by enabling it for the corrseponding tab. + + psergey-todo: conditions have limits on how far to the right they can be + moved (Item_func_dynamic_cond::last_tab). Don't attach conditions to + where they can't be moved. */ if (!prev_dynamic_conds.is_empty()) { - List_iterator_fast<Item_func_dynamic_cond> dyn_cond_it(prev_dynamic_conds); - if (!wrapped_select_cond) - wrapped_select_cond= dyn_cond_it++; + List_iterator<Item_func_dynamic_cond> dyn_cond_it(prev_dynamic_conds); while ((cur_wrapped_cond= dyn_cond_it++)) - wrapped_select_cond->add_pred(cur_wrapped_cond); + { + if (!wrapped_select_cond) + wrapped_select_cond= cur_wrapped_cond; + else + wrapped_select_cond->add_pred(cur_wrapped_cond); + + /* + psergey-todo: conditions have limits on how far to the right they can be + moved (Item_func_dynamic_cond::last_tab). Don't attach conditions to + where they can't be moved. + */ + if (cur_wrapped_cond->get_last_tab() == tab) + dyn_cond_it.remove(); + } } prev_dynamic_conds.concat(&cur_dynamic_conds); cur_dynamic_conds.empty(); === modified file 'sql/table.h' --- sql/table.h 2013-09-25 18:07:06 +0000 +++ sql/table.h 2013-12-19 02:50:28 +0000 @@ -1721,6 +1721,9 @@ struct TABLE_LIST table_map sj_inner_tables; /* Number of IN-compared expressions */ uint sj_in_exprs; + + //psergey-todo: add the index of last table in the join order + uint sj_strategys_join_tab; /* If this is a non-jtbm semi-join nest: corresponding subselect predicate */ Item_in_subselect *sj_subq_pred; ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB r3955 make fail; " error: ‘isfinite’ was not declared in this scope"
by darx＠sent.com 18 Dec '13

18 Dec '13

hi, upgrading a MariaDB 10/git src build from r3911 -> r3955, on x86_64 after configure, make fails with a warning & an error @, ... [ 19%] Building CXX object storage/connect/CMakeFiles/connect.dir/ha_connect.cc.o cd /usr/local/src/mariadb/bld/storage/connect && /usr/bin/g++-4.8 -DFORCE_INIT_OF_VARS -DHAVE_CONFIG_H -DHUGE_SUPPORT -DLIBXML2_SUPPORT -DLINUX -DMARIADB -DMYSQL_DYNAMIC_PLUGIN -DMYSQL_SUPPORT -DODBC_SUPPORT -DPIVOT_SUPPORT -DUBUNTU -DUNIX -DZIP_SUPPORT -Dconnect_EXPORTS -Wall -O2 -fmessage-length=0 -D_FORTIFY_SOURCE=2 -fstack-protector -march=amdfam10 -mtune=amdfam10 -std=c++11 -felide-constructors -fno-exceptions -fno-rtti -Wall -Wno-unused-parameter -fno-exceptions -fno-rtti -fpermissive -fexceptions -fPIC -O2 -g -DNDEBUG -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -fPIC -I/usr/local/src/mariadb/bld/include -I/usr/include/libxml2 -I/usr/local/src/mariadb/include -I/usr/local/src/mariadb/sql -I/usr/local/src/mariadb/bld/pcre -I/usr/local/src/mariadb/pcre -I/usr/local/ssl/include -O2 -fmessage-length=0 -D_FORTIFY_SOURCE=2 -fstack-protector -march=amdfam10 -mtune=amdfam10 -Wall -Wmissing-declarations -Wno-write-strings -Wno-unused-variable -Wno-unused-value -Wno-unused-function -Wno-parentheses -o CMakeFiles/connect.dir/ha_connect.cc.o -c /usr/local/src/mariadb/storage/connect/ha_connect.cc In file included from /usr/local/src/mariadb/sql/gcalc_tools.h:21:0, from /usr/local/src/mariadb/sql/spatial.h:28, from /usr/local/src/mariadb/sql/item.h:3667, from /usr/local/src/mariadb/sql/sql_lex.h:26, from /usr/local/src/mariadb/sql/sql_class.h:463, from /usr/local/src/mariadb/storage/connect/ha_connect.cc:104: /usr/local/src/mariadb/sql/gcalc_slicescan.h:29:40: warning: invalid suffix on literal; C++11 requires a space between literal and identifier [-Wliteral-suffix] #define GCALC_DBUG_ENTER(a) DBUG_ENTER("Gcalc "a) ^ In file included from /usr/local/src/mariadb/sql/item.h:3669:0, from /usr/local/src/mariadb/sql/sql_lex.h:26, from /usr/local/src/mariadb/sql/sql_class.h:463, from /usr/local/src/mariadb/storage/connect/ha_connect.cc:104: /usr/local/src/mariadb/sql/item_func.h: In member function ‘double Item_func::check_float_overflow(double)’: /usr/local/src/mariadb/sql/item_func.h:281:26: error: ‘isfinite’ was not declared in this scope return isfinite(value) ? value : raise_float_overflow(); ^ /usr/local/src/mariadb/sql/item_func.h:281:26: note: suggested alternative: In file included from /usr/include/c++/4.8/random:38:0, from /usr/include/c++/4.8/bits/stl_algo.h:65, from /usr/include/c++/4.8/algorithm:62, from /usr/local/src/mariadb/sql/mdl.h:32, from /usr/local/src/mariadb/sql/table.h:22, from /usr/local/src/mariadb/sql/field.h:29, from /usr/local/src/mariadb/sql/unireg.h:172, from /usr/local/src/mariadb/sql/sql_class.h:24, from /usr/local/src/mariadb/storage/connect/ha_connect.cc:104: /usr/include/c++/4.8/cmath:596:5: note: ‘std::isfinite’ isfinite(_Tp __x) ^ make[2]: *** [storage/connect/CMakeFiles/connect.dir/ha_connect.cc.o] Error 1 make[2]: Leaving directory `/usr/local/src/mariadb/bld' make[1]: *** [storage/connect/CMakeFiles/connect.dir/all] Error 2 make[1]: Leaving directory `/usr/local/src/mariadb/bld' make: *** [all] Error 2

1 0

Re: [Maria-developers] metadata_lock_info plugin
by Sergey Vojtovich 13 Dec '13

13 Dec '13

Hi Kentoku, thanks for your contribution and sorry for this long delay. I finally managed to go through your code. The most important thing I'm worried about is that there is no protection of ticket lists: lists may be updated while we iterate them and it may cause server crash in concurrent environment. From what I can see MDL_context is mostly supposed to be accessed only by thread which owns this context. As such it is not protected by any locks. I believe we should use global lists instead, something like: mysql_mutex_lock(&mdl_locks.m_mutex); iterate mdl_locks.m_locks (hash of MDL_lock) { mysql_prlock_rdlock(&lock->m_rwlock); iterate lock->m_granted (list of MDL_ticket) { // store data } mysql_prlock_unlock(&lock->m_rwlock); } mysql_mutex_lock(&mdl_locks.m_mutex); A few more suggestions in no particular order: - instead of making m_tickets public I'd suggest to try to make i_s_metadata_lock_info_fill_table() a friend function (shouldn't be relevant anymore) - why check for thd->killed? (shouldn't be relevant anymore) - metadata_lock_info_lock_mode_length[] includes ending zero, and then we store e.g. "MDL_SHARED\0" whereas we should store "MDL_SHARED" I'd suggest to declare these arrays as following: static const LEX_STRING metadata_lock_info_lock_mode_str[]= { STRING_WITH_LEN("MDL_INTENTION_EXCLUSIVE"), // or C_STRING_WITH_LEN() ... }; - Roberto suggests to rename ID to THREAD_ID and I agree - Roberto suggests to add QUERY_ID column and I agree - Roberto suggests to add LOCK_DURATION column and I agree - Roberto suggests to use m_namespace_to_wait_state_name instead of metadata_lock_info_lock_names, but I tend to disagree because the former says "Waiting for...". Though I'd suggest to make array of LEX_STRING's to avoid calling strdup(). - Roberto suggests to replace DB with KEY and NAME with SUB_KEY, but I tend to disagree since in most cases it is db and table name. I'd vote for TABLE_SCHEMA and TABLE_NAME to stay in line with the rest I_S tables. Regards, Sergey On Mon, Jul 01, 2013 at 03:46:22AM +0900, kentoku wrote: > Hi Sergey, > Sorry. I think this plugin needs more locks "mdl_locks.m_mutex", > "mdl_locks.m_global_lock" and "mdl_locks.m_commit_lock". Is it right? Would > you please teach me how can I use it? > Thanks, > Kentoku > > > 2013/7/1 kentoku <kentokushiba(a)gmail.com> > > > Hi Sergey, > > > > I made new information schema plugin which named "metadata_lock_info" > > plugin. This plugin makes it possible knowing "who has metadata locks". In > > development stage, sometimes DBAs meet metadata locks when using alter > > table statement. This metadata locks are sometimes caused by GUI tools. In > > service stage, sometimes DBAs meet metadata locks when using alter table > > statement. This metadata locks are sometimes caused by long batch > > processing. In many cases, the DBAs need to judge immediately. So I made it > > for all DBAs. > > Would you please review this plugin? > > lp:~kentokushiba/maria/10.0.3-metadata_lock_info > > Note: This plugin needs a change of sql/mdl.h > > > > Thanks, > > Kentoku > >

3 15

Re: [Maria-developers] Rev 3915: MDEV-5388 - Reduce usage of LOCK_open: unused_tables
by Sergei Golubchik 12 Dec '13

12 Dec '13

Hi, Sergey! Did you benchmark the improvement you get with this patch? On Dec 05, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 3915 > revision-id: svoj(a)mariadb.org-20131205084430-7rt735tbanw0i6uq > parent: svoj(a)mariadb.org-20130827121233-b9mguuxctnjlq5wd > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0 > timestamp: Thu 2013-12-05 12:44:30 +0400 > message: > MDEV-5388 - Reduce usage of LOCK_open: unused_tables > > Removed unused_tables, find LRU object by timestamp instead. > === modified file 'sql/table_cache.cc' > --- a/sql/table_cache.cc 2013-08-27 12:12:33 +0000 > +++ b/sql/table_cache.cc 2013-12-05 08:44:30 +0000 > @@ -324,20 +248,44 @@ void tc_add_table(THD *thd, TABLE *table > DBUG_ASSERT(table->in_use == thd); > mysql_mutex_lock(&LOCK_open); > table->s->tdc.all_tables.push_front(table); > - tc_count++; > /* If we have too many TABLE instances around, try to get rid of them */ > - if (tc_count > tc_size && unused_tables) > + if (tc_count == tc_size) I'd rather put (tc_count >= tc_size), looks safer. > { > - TABLE *purge_table= unused_tables; > - tc_remove_table(purge_table); > - mysql_rwlock_rdlock(&LOCK_flush); > + TDC_iterator tdc_it; > mysql_mutex_unlock(&LOCK_open); > - intern_close_table(purge_table); > - mysql_rwlock_unlock(&LOCK_flush); > - check_unused(thd); > + > + tdc_it.init(); > + mysql_mutex_lock(&LOCK_open); > + if (tc_count == tc_size) and here > + { > + TABLE *purge_table= 0; > + TABLE_SHARE *share; I think a status variable for this would be nice. So that one could make an informed decision about the desired size of the cache. > + while ((share= tdc_it.next())) > + { > + TABLE_SHARE::TABLE_list::Iterator it(share->tdc.free_tables); > + TABLE *entry; > + while ((entry= it++)) > + if (!purge_table || entry->tc_time < purge_table->tc_time) > + purge_table= entry; > + } > + if (purge_table) > + { > + tdc_it.deinit(); > + purge_table->s->tdc.free_tables.remove(purge_table); > + purge_table->s->tdc.all_tables.remove(purge_table); > + mysql_rwlock_rdlock(&LOCK_flush); > + mysql_mutex_unlock(&LOCK_open); > + intern_close_table(purge_table); > + mysql_rwlock_unlock(&LOCK_flush); > + check_unused(thd); > + return; > + } > + } > + tdc_it.deinit(); Okay, so you only purge one table at time? Since purge is kind of an expensive operation, it's better to amortize the cost of it by purging many tables at once. > } > - else > - mysql_mutex_unlock(&LOCK_open); > + /* Nothing to evict, increment tc_count. */ > + tc_count++; > + mysql_mutex_unlock(&LOCK_open); > } > 2 Regards, Sergei

2 6

Re: [Maria-developers] Rev 3914: MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables
by Sergei Golubchik 12 Dec '13

12 Dec '13

Hi, Sergey! On Dec 10, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 3914 > revision-id: svoj(a)mariadb.org-20131210150036-0hwh1gy4z3jalwsg > parent: bar(a)mnogosearch.org-20131203101253-mbz05d9jtkb3iycp > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0-mdev4956 > timestamp: Tue 2013-12-10 19:00:36 +0400 > message: > MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables > > - tc_acquire_table and tc_release_table do not access > TABLE_SHARE::tdc.used_tables anymore > - in tc_acquire_table(): release LOCK_tdc after we relase LOCK_open > (saves a few CPU cycles in critical section) > - in tc_release_table(): if we reached table cache threshold, evict > to-be-released table without moving it to unused_tables. unused_tables > must be empty at this point. Looks ok to me Regards, Sergei

1 0

[Maria-developers] Issue with index condition pushdown being used with no end_range set
by Zardosht Kasheff 12 Dec '13

12 Dec '13

Hello all, I've noticed a scenario with index condition pushdown (ICP) in MariaDB that leads to really bad performance in TokuDB. I don't know if it is a bug in ICP or if TokuDB is misusing/misunderstanding the API. Here is the problem. Suppose we run the following query on the following schema: SELECT * FROM foo WHERE col1 = '7' ORDER BY b desc | foo | CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), CLUSTERING KEY `col1_2` (`col1`,`b`) ) ENGINE=TokuDB AUTO_INCREMENT=8001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=TOKUDB_ZLIB | The output of explain is: MariaDB [test]> explain SELECT * FROM foo WHERE col1 = '7' ORDER BY b desc; +------+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE | foo | ref | col1_2 | col1_2 | 302 | const | 1320 | Using where | +------+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (2.32 sec) The query is doing a reverse range scan, as it should. We get handler::idx_cond_push called, but end_range is not set. As a result, TokuDB thinks it can use index condition pushdown to filter rows. As we do this and get to the end, because end_range is not set, we never get a result of ICP_OUT_OF_RANGE, and always get a result of ICP_NO_MATCH. So, when we go to retrieve that first row past the end of the range, the row that will tell MySQL it should stop searching, TokuDB never finds a row and never gets ICP_NO_MATCH. It scans to the beginning of the index (because it is running in reverse order), getting ICP_NO_MATCH for every row it encounters. Although my index is clustering, I've seen this with a normal key as well. If col1 is an int instead of the funky varchar, handler::idx_cond_push is never called, so this problem does not exist. It seems to me that if end_range is not set and we cannot reliably learn when we go out of range, we should not have handler::idx_cond_push called, otherwise we can get bad performance such as the example above. Thoughts? Thanks --Zardosht

1 1