- developers - lists.mariadb.org

[Maria-developers] MDEV-6111: Optimizer trace: let's get rid of "optimizer_trace_enabled"
by Sergey Petrunia 15 Jun '18

15 Jun '18

Hi Zhzhzoo, I see that the current patch uses @@optimizer_trace_enabled variable to control it. Meanwhile, in MySQL the name of the variable is "optimizer_trace". It is a flag set: mysql> select @@optimizer_trace; +-------------------------+ | @@optimizer_trace | +-------------------------+ | enabled=on,one_line=off | +-------------------------+ 1 row in set (0.00 sec) but one can set it to 1 or 0 to just set enabled on/off: mysql> set @@optimizer_trace=1; Query OK, 0 rows affected (0.00 sec) mysql> set @@optimizer_trace=0; Query OK, 0 rows affected (0.00 sec) This obviously carries some legacy but I think we should have it the same way, in order to be compatible with MySQL where possible. Please use the same name/values as MySQL does. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] MDEV-6111: Optimizer trace: on the first output
by Sergey Petrunia 15 Jun '18

15 Jun '18

On Tue, Jun 12, 2018 at 07:40:08AM +0800, zhzhzoo zhang wrote: > I've done > condition processing and some range analysis. > and for the test input ( > https://dev.mysql.com/doc/internals/en/tracing-example.html) It now > outputs something like > (not very readable from the email so I've made two pastes, from the current tree and from MySQL 8): https://gist.github.com/spetrunia/cb4a1a382a9e81a60491d524dbfc08f6 https://gist.github.com/spetrunia/62347cc3bf26e003909a32b378b6684f > ```{ > "join_optimization": { > "select #": 1, > "steps": { > "inner": { > "select #": 1, Two 'select #' :1 seem to be redundant. There are also two nested "steps" elements which seem redundant as well. > "steps": [ > { > "condition_processing": { > "condition": "WHERE", > "original_condition": "`test`.`alias1`.`pk` <> 0 and > `test`.`alias2`.`pk` = `test`.`alias1`.`col_int_key`", > "steps": [ > { > "transformation": "equality_propagation", > "subselect_evaluation": [], > "resulting_condition": "`test`.`alias1`.`pk` <> 0 and > multiple equal(`test`.`alias2`.`pk`, `test`.`alias1`.`col_int_key`)???" What are the "???" at the end? Please get them removed. also, if you check EXPLAIN FORMAT=JSON output, it prints conditions without overquoting, like so: "attached_condition": "alias1.pk <> 0 and alias1.col_int_key is not null" This is done by using QT_EXPLAIN parameter: item->print(&str, QT_EXPLAIN); Let's print them this way here as well. > }, > { > "transformation": "constant_propagation", > "subselect_evaluation": [], > "resulting_condition": "`test`.`alias1`.`pk` <> 0 and > multiple equal(`test`.`alias2`.`pk`, `test`.`alias1`.`col_int_key`)???" > }, BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] 46e0c8a9219: MDEV-11071: Assertion `thd->transaction.stmt.is_empty()' failed in Locked_tables_list::unlock_locked_table
by Sergei Golubchik 14 Jun '18

14 Jun '18

Hi, Oleksandr! Looks good, but please see a few comments below. On May 08, Oleksandr Byelkin wrote: > revision-id: 46e0c8a921978e7b95adf3f17f3c85b18d9f9ef6 (mariadb-10.2.14-78-g46e0c8a9219) > parent(s): 9bcd0f5fea8ca26742b10d37b95a966c69909ff1 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-05-08 15:26:26 +0200 > message: > > MDEV-11071: Assertion `thd->transaction.stmt.is_empty()' failed in Locked_tables_list::unlock_locked_table > > fix_length_and_dec now return result (error/OK) > diff --git a/sql/item.h b/sql/item.h > index 8921ee76f6a..e9713730a1c 100644 > --- a/sql/item.h > +++ b/sql/item.h > @@ -4210,7 +4210,7 @@ class Item_func_or_sum: public Item_result_field, > also to make printing of items inherited from Item_sum uniform. > */ > virtual const char *func_name() const= 0; > - virtual void fix_length_and_dec()= 0; > + virtual bool fix_length_and_dec()= 0; 1. I wonder, if you change fix_length_and_dec to void in one of the derived classes, will you get a compiler warning for that? 2. may be also __attribute__ ((warn_unused_result)) here? I don't know how it'll work on virtual methods, may be it won't. both questions are for the use case "what if we merge some fix_length_and_dec related changes from 10.1, will the compiler tell us to update them?" > bool const_item() const { return const_item_cache; } > table_map used_tables() const { return used_tables_cache; } > Item* build_clone(THD *thd, MEM_ROOT *mem_root); > diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc > index 44cc4f3cae9..46cf412e229 100644 > --- a/sql/item_cmpfunc.cc > +++ b/sql/item_cmpfunc.cc > @@ -535,8 +535,9 @@ void Item_bool_rowready_func2::fix_length_and_dec() > we have to check for out of memory conditions here > */ > if (!args[0] || !args[1]) > - return; > - setup_args_and_comparator(current_thd, &cmp); > + return FALSE; > + bool res= setup_args_and_comparator(current_thd, &cmp); > + return res; not that it matters much (no need to change), but why didn't you return setup_args_and_comparator(current_thd, &cmp); directly? > } > > > diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h > index de1b27cff1a..5efb98bc81d 100644 > --- a/sql/item_cmpfunc.h > +++ b/sql/item_cmpfunc.h > @@ -911,10 +911,12 @@ class Item_func_strcmp :public Item_int_func > longlong val_int(); > uint decimal_precision() const { return 1; } > const char *func_name() const { return "strcmp"; } > - void fix_length_and_dec() > + bool fix_length_and_dec() > { > - agg_arg_charsets_for_comparison(cmp_collation, args, 2); > + if (agg_arg_charsets_for_comparison(cmp_collation, args, 2)) > + return TRUE; > fix_char_length(2); // returns "1" or "0" or "-1" this comment is kinda weird. remove? > + return FALSE; > } > Item *get_copy(THD *thd, MEM_ROOT *mem_root) > { return get_item_copy<Item_func_strcmp>(thd, mem_root, this); } > @@ -1987,10 +1997,10 @@ class Item_func_like :public Item_bool_func2 > const char *func_name() const { return "like"; } > enum precedence precedence() const { return CMP_PRECEDENCE; } > bool fix_fields(THD *thd, Item **ref); > - void fix_length_and_dec() > + bool fix_length_and_dec() > { > max_length= 1; > - agg_arg_charsets_for_comparison(cmp_collation, args, 2); should this one also be __attribute__ ((warn_unused_result)) ? > + return agg_arg_charsets_for_comparison(cmp_collation, args, 2); > } > void cleanup(); > > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 4214980c36c..ca49e06a9e7 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -904,9 +905,10 @@ Item::Type Item_subselect::type() const > } > > > -void Item_subselect::fix_length_and_dec() > +bool Item_subselect::fix_length_and_dec() > { > engine->fix_length_and_dec(0); what about subselect_engine::fix_length_and_dec() ? should return bool too, I guess, right? > + return FALSE; > } > > > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 9e7973b745c..f3cb85f01d3 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -4894,7 +4894,13 @@ int create_table_impl(THD *thd, > file= mysql_create_frm_image(thd, orig_db, orig_table_name, create_info, > alter_info, create_table_mode, key_info, > key_count, frm); > - if (!file) > + /* > + We have to check thd->is_error() here because it can be set by > + Item::val* for example, and before it will be cought accidentally by > + Item_func::fix_fields() of the next call. Now we removed the check > + from Item_func::fix_fields() > + */ this is a very confusing comment, don't write in comments that "it used to be this way, but now we've changed it and it is that way" and I still don't understand why do you need to check for thd->is_error() here > + if (!file || thd->is_error()) > goto err; > if (rea_create_table(thd, frm, path, db, table_name, create_info, > file, frm_only)) > @@ -7377,7 +7383,8 @@ static bool mysql_inplace_alter_table(THD *thd, > */ > if (mysql_rename_table(db_type, alter_ctx->new_db, alter_ctx->tmp_name, > alter_ctx->db, alter_ctx->alias, > - FN_FROM_IS_TMP | NO_HA_TABLE)) > + FN_FROM_IS_TMP | NO_HA_TABLE) || > + thd->is_error()) and here > { > // Since changes were done in-place, we can't revert them. > (void) quick_rm_table(thd, db_type, Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 6

Re: [Maria-developers] 0918ae42a83: MDEV-14014 Multi-Slave Replication Fail: bogus data in log event
by Sergei Golubchik 13 Jun '18

13 Jun '18

Hi, Andrei! On Jun 13, andrei.elkin(a)pp.inet.fi wrote: > revision-id: 0918ae42a8394b6faf28f67b70550894851be3bb (mariadb-10.1.33-36-g0918ae42a83) > parent(s): 26be5072429082e634b8fc102609370975443439 > author: Andrei Elkin > committer: Andrei Elkin > timestamp: 2018-06-13 16:27:00 +0300 > message: > > MDEV-14014 Multi-Slave Replication Fail: bogus data in log event > > MDEV-7257 made a dump thread to read from binlog concurrently with > writers as long as the read bytes are below a water-mark > (MYSQL_BIN_LOG::binlog_end_pos). However it appeared to be possible a > dump thread reader reach out for bytes past the water mark through a > feature of IO_CACHE that fills in the internal buffer and while doing > so it could read what the reader is not supposed to see (the bytes > above MYSQL_BIN_LOG::binlog_end_pos). > > The issue is fixed with constraining the IO_CACHE buffer fill to respect > the watermark. > An added test simulates potentially unconstrained buffer fill and an > assert guards this is not the case anymore. Looks good, thanks! Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] unbundle RocksDB
by Michal Schorm 13 Jun '18

13 Jun '18

Hello, Since we have a rocksdb package ready in Fedora, I tried to unbundle it, using the system package instead. I wasn't successful though. I haven't found any CMake argument which seemed related and from the build log it seems, the code tries to link against the static libraries in any case. Before I'll start to dig further, I'm stopping by on this list, making sure it is possible. - is it currently supported? - have I overlooked any related CMake argument? Links: MariaDB in Fedora: https://src.fedoraproject.org/rpms/mariadb/tree/master -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

3 3

Re: [Maria-developers] d9913834ceb: MDEV-14014 Multi-Slave Replication Fail: bogus data in log event
by Sergei Golubchik 13 Jun '18

13 Jun '18

Hi, andrei.elkin! On May 30, andrei.elkin(a)pp.inet.fi wrote: > revision-id: d9913834cebe4ed8494b3d76762cd882788a8fd5 (mariadb-10.1.33-28-gd9913834ceb) > parent(s): c1698e8dc50f0c342c1a6886426ba1d43396cd1e > author: Andrei Elkin > committer: Andrei Elkin > timestamp: 2018-05-30 20:06:44 +0300 > message: > > MDEV-14014 Multi-Slave Replication Fail: bogus data in log event > > MDEV-7257 made a dump thread to read from binlog concurrently with > writers as long as the read bytes are below a water-mark > (MYSQL_BIN_LOG::binlog_end_pos). However it appeared to be possible a > dump thread reader reach out for bytes past the water mark through a > feature of IO_CACHE that fills in the internal buffer and while doing > so it could read what the reader is not supposed to see (the bytes > above MYSQL_BIN_LOG::binlog_end_pos). > > The issue is fixed with constraining the IO_CACHE buffer fill to respect > the watermark. > An added test simulates potentially unconstrained buffer fill and an > assert guards this is not the case anymore. What about a much simpler one-liner fix: --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -2549,6 +2549,7 @@ static int send_events(binlog_send_info *info, IO_CACHE* return 1; info->last_pos= linfo->pos; + log->end_of_file= end_pos; error= Log_event::read_log_event(log, packet, info->fdev, opt_master_verify_checksum ? info->current_checksum_alg : BINLOG_CHECKSUM_ALG_OFF); It doesn't change IO_CACHE structure and doesn't add any overhead to the IO_CACHE. Did you already test that your commit actually fixes the issue? Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 3

Re: [Maria-developers] 20f3479: MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>.
by Sergei Golubchik 12 Jun '18

12 Jun '18

Hi, Alexey! On Jun 11, Alexey Botchkov wrote: > revision-id: 20f347974f6a9b4e2c65a5ca79aefb8e118ba6cd (mariadb-10.1.33-33-g20f3479) > parent(s): 3627dd7f6a1cf5bd7151ff23290d64cb6dffea90 > committer: Alexey Botchkov > timestamp: 2018-06-11 14:58:46 +0400 > message: > > MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>. > > The reload_acl_and_cache() now looks into the VIEW definition > to check the involved tables if the locked_tables_mode. That's a pretty neat solution :) But it seems that - contrary to what I expected - that MariaDB actually distinguishes between locking a view a locking all its tables. Consider this test case: ---- create table t1 (a int); create view v1 as select * from t1; lock table v1 write; insert v1 values (1); select * from t1; unlock tables; drop view v1; drop table t1; ---- this works and inserts 1 just fine. If you change the lock table statement to lock t1, it'll with Table 'v1' was not locked with LOCK TABLES It'd be great if FLUSH TABLES will exhibit the same behavior, it needs to know if the view is locked, not only if all underlying tables are locked. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Maria-discuss] MariaDB crashes because of "long semaphore wait" after migrating from 10.1 to 10.3
by Vicențiu Ciorbaru 11 Jun '18

11 Jun '18

Hi! I have cc'ed the maria-developers mailing list as this is potentially a dev issue and also the devs responsible for Windows and InnoDB development. There might be some people on vacation right now, but this should get looked into in a week or two at most. Let's find a solution for your problem. :) Vicentiu On Sun, 10 Jun 2018, 19:11 TD, <tdcf(a)gmx.net> wrote: > Hi, > > I have a Windows server that has been running MariaDB 10.1 successfully > for over a year. The server remains mostly idle for long times with some > read access, but occasionally there are transactions that add data (about > 500k rows per commit). There can be up to 10 such transactions (one per > database) at the same time and during those times the server is under quite > some load (the code processing the data resides on the same server as the > database). We're using an 8 core AMD Ryzen, 32 GB RAM and a Samsung SSD. > > Like I said, this worked fine with MariaDB 10.1 all the time. > > We're currently in the process of setting up a new server (specs similar > to the ones above) and because MariaDB 10.3 has been released recently, I > was curious and installed it. The database now constantly shuts down > because of "[FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We > intentionally crash the server because it appears to be hung." whenever > there is one of the load situations described above. If I install MariaDB > 10.1 on the new server, it runs as fine as the old one. The crashes also > happen when I start with a clean database (no data migrated from 10.1 > databases). > > I'm not a huge MariaDB config pro, as it's only a tool to store the data, > it just needs to run with reasonable performance and has always worked > until now. As far as I know and can judge from comments in the file, the > my.ini in use is (mostly?) based on my-innodb-heavy-4G.cnf, but coworkers > might have made modifications. I've attached the contents below. > > I don't know how I can debug this issue further. Windows event viewer > contains several log warnings with the text "InnoDB: A long semaphore > wait:" but no further information why this wait happens. I couldn't find > any other error logs. I guess it is some configuration error (I read about > MariaDB switching from XtraDB to InnoDB, so I guess that the issue might be > related). > > Migrating to 10.3 is not an absolute must, but it would be nice to be able > to fix the problem. > > Any help would be greatly appreciated and if you need more information, > I'll gladly try to provide it. > > Thanks, > Tom. > > my.ini: > [mysqld] > max_connections = 100 > max_allowed_packet = 16M > binlog_cache_size = 1M > max_heap_table_size = 64M > read_buffer_size = 2M > read_rnd_buffer_size = 16M > sort_buffer_size = 8M > join_buffer_size = 8M > thread_cache_size = 10 > query_cache_size = 64M > query_cache_limit = 2M > thread_stack = 240K > tmp_table_size = 64M > slow_query_log > expire_logs_days = 14 > log-bin = mysql-bin > character-set-server=utf8 > collation-server=utf8_general_ci > > #*** MyISAM Specific options > key_buffer_size = 32M > bulk_insert_buffer_size = 64M > myisam_sort_buffer_size = 128M > myisam_max_sort_file_size = 10G > myisam_repair_threads = 1 > myisam_recover > > # *** INNODB Specific options *** > #innodb_fatal_semaphore_wait_threshold = 6000 #tried that, didn't help > #innodb_adaptive_hash_index = 0 #tried that (suggested in some stack > overflow post, didn't help) > innodb_buffer_pool_size = 2G > innodb_data_file_path = ibdata1:10M:autoextend > innodb_write_io_threads = 8 > innodb_read_io_threads = 8 > innodb_thread_concurrency = 16 > innodb_log_buffer_size = 8M > innodb_log_file_size = 256M > innodb_log_files_in_group = 3 > innodb_max_dirty_pages_pct = 10 > innodb_io_capacity = 2000 > innodb_lock_wait_timeout = 120 > > [mysql] > no-auto-rehash > > [myisamchk] > key_buffer_size = 512M > sort_buffer_size = 512M > read_buffer = 8M > write_buffer = 8M > > [mysqld_safe] > open-files-limit = 8192 > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >

3 2

[Maria-developers] MariaDB Developers Unconference in a few weeks!
by Otto Kekäläinen 08 Jun '18

08 Jun '18

Hello everybody! The next developers unconference will be in a few weeks: https://mariadb.org/2018-2-developers-unconference-in-finland/ There are still open slots so we encourage you to sign up now. https://www.meetup.com/MariaDB-Developers/events/249024232/ We have plenty of break-out rooms this time and there is space, so we also encourage you to suggest sessions/discussions you would like to have to ian(a)mariadb.org. Around mid-summer is a great time to visit Finland. The nights that don't get dark are worth experiencing! Getting to the venue in Tampere., Finlnd is also very easy. https://mariadb.org/travel-tips-to-tampere-finland/#why-visit Hope to see you there! - Otto

1 0

Re: [Maria-developers] [Commits] 56d339111a4: MDEV-16374: Filtered shows 0 for materilization scan for a semi join, which makes optimizer always picks
by Sergey Petrunia 05 Jun '18

05 Jun '18

Hi Varun, On Mon, Jun 04, 2018 at 12:34:49PM +0530, Varun wrote: > revision-id: 56d339111a4e5c868ffa3b1f2538c5f101f67767 (mariadb-10.0.30-366-g56d339111a4) > parent(s): a61724a3ca187f6eb44fc67948acb76a94efa783 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-06-04 12:26:56 +0530 > message: > > MDEV-16374: Filtered shows 0 for materilization scan for a semi join, which makes optimizer always picks > materialization scan over materialization lookup > > For non-mergeable semi-joins we don't store the estimates of the IN subquery in table->file->stats.records. > In the function TABLE_LIST::fetch_number_of_rows, we store the number of rows in the tables > (estimates in case of derived table/views). > Currently we don't store the estimates for non-mergeable semi-joins, which leads to a problem of selecting > materialization scan over materialization lookup. > Fixed this by storing these estimated appropriately > * This is a change in the optimizer. Should we really do it in 10.0? In my opinion, this should go into 10.3 or 10.4. Let's discuss this, and I think Igor may have something to say, too. * The patch updates selectivity.test but not selectivity_innodb.result (which includes selectivity.test). * Please see my last comments at https://jira.mariadb.org/browse/MDEV-16374 . Here is an alternative patch (it passes the testsuite, and produces the same result for selectivity* tes): diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1285835..884997c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6602,7 +6602,7 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, { TABLE *table= s->table; double sel= table->cond_selectivity; - double table_records= (double)table->stat_records(); + double table_records= s->records; // psergey-10 dbl_records= table_records * sel; return dbl_records; } What do you think about it? I would like to get Igor's opinion also because I'm not certain which member variable should be changed here. (Why does derived table modify table->stats.records while JTBM semi-joins modify JOIN_TAB::records? These seem like two solutions for the same problem?) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0