- developers - lists.mariadb.org

[Maria-developers] Engine independent index statistics - infrastructure questions
by Sergey Petrunia 20 Mar '14

20 Mar '14

Hello Igor and others, I've been playing around with engine independent statistics, and got a few questions. == mysqldump == mysqldump dumps table' data, but besides that it can dump other related objects, e.g. it will dump triggers that are attached to the table. Should it dump table statistics values, also? This question is of interest for DBAs, but also for optimizer developers. If a user has a complaint about the optimizer, we frequently ask them to upload a dataset. How do we request table/column statistics? We can't request output of SELECT * ..., because histogram data is binary and will be corrupted. We should provide users a convinent way to pass us relevant part of the EITS tables. == dropping table statistics == How does one drop table statistics? are we satisfied with needing to run three statements DELETE FROM mysql.column_stats WHERE schema_name=$db AND table_name=$table_name; DELETE FROM mysql.index_stats WHERE table_name=$db AND table_name=$table_name; DELETE FROM mysql.column_stats WHERE table_name=$db AND table_name=$table_name; or we want a simpler SQL, like DROP STATISTICS FOR t1; == Permissions on statistic tables == What should be the permissions on statistics tables? I guess we should follow pattern from other system tables - a user is typically able to read the table but not write to it (so that he can't mess with other users' SPs/triggers/etc). However, if the user is not allowed to modify the table directly, we should provide enough ways to modify the table indirectly. For example, we should provide a way to drop statistics for one's tables (which brings us back to the previous item). We might also want to allow to make other modifications to statistics of tables that one has control of? BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] [GSoC 2014]
by Ivan Nikulin 20 Mar '14

20 Mar '14

Hello, I would like to take part into one of your projects on base of GSoC 2014. I am an experienced C/C++ programmer with wide knowledge of algorithms and data structures and huge interest into this sphere. I have some basic knowledge of SQL language, but unfortunatelly I don't have any database development experience. Nevertheless, gaining the latter is my essential purpose. Can I count on a chance of taking part in one of your projects? Sorry for writing to you that late. P.S. I tried writing to you a bit earlier, but the mail wouldn't appear on mailing list. -- Best regards, Ivan Nikulin

2 1

Re: [Maria-developers] [Commits] Rev 4053: MDEV-5864 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.free_tables
by Sergei Golubchik 19 Mar '14

19 Mar '14

Hi, Sergey! On Mar 15, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 4053 > revision-id: svoj(a)mariadb.org-20140315185301-ce4r6br023co80dm > parent: sanja(a)montyprogram.com-20140314073116-nklb0jkjd4t1w40n > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0 > timestamp: Sat 2014-03-15 22:53:01 +0400 > message: > MDEV-5864 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.free_tables > > Let TABLE_SHARE::tdc.free_tables, TABLE_SHARE::tdc.all_tables, > TABLE_SHARE::tdc.flushed and corresponding invariants be protected by > per-share TABLE_SHARE::tdc.LOCK_table_share instead of global LOCK_open. This looks pretty good to me! A couple of comments, see below. > === modified file 'sql/sql_plist.h' > --- a/sql/sql_plist.h 2013-11-20 11:05:39 +0000 > +++ b/sql/sql_plist.h 2014-03-15 18:53:01 +0000 > @@ -142,6 +142,14 @@ class I_P_List : public C, public I > > return result; > } > + inline T *back() > + { > + T *result= m_first; > + if (result) > + while (*B::next_ptr(result)) > + result= *B::next_ptr(result); > + return result; > + } I think this is wrong. The correct way would be to use I_P_List<> with the I_P_List_fast_push_back policy, then you could use have get_last() method to get the last element in the list. May be as inline T *back() { return *I::get_last(); } > void swap(I_P_List<T, B, C> &rhs) > { > swap_variables(T *, m_first, rhs.m_first); > > === modified file 'sql/table_cache.cc' > --- a/sql/table_cache.cc 2014-03-06 12:19:12 +0000 > +++ b/sql/table_cache.cc 2014-03-15 18:53:01 +0000 > @@ -70,13 +70,6 @@ static int32 tc_count; /**< Number of TA > > > /** > - Protects TABLE_SHARE::tdc.free_tables, TABLE_SHARE::tdc.all_tables. > -*/ > - > -mysql_mutex_t LOCK_open; So, you've now removed LOCK_open completely? Cool! > - > - > -/** > Protects unused shares list. > > TABLE_SHARE::tdc.prev > @@ -360,12 +378,15 @@ bool tc_release_table(TABLE *table) > table->in_use= 0; > /* Add table to the list of unused TABLE objects for this share. */ > table->s->tdc.free_tables.push_front(table); > - mysql_mutex_unlock(&LOCK_open); > + mysql_mutex_unlock(&table->s->tdc.LOCK_table_share); > return false; > > purge: > + /* Wait for MDL deadlock detector */ > + while (table->s->tdc.all_tables_refs) > + mysql_cond_wait(&table->s->tdc.COND_release, &table->s->tdc.LOCK_table_share); you're using it many times, I'd put it in a small static inline function, like static wait_for_mdl_deadlock_detector(TABLE *table) { mysql_mutex_assert_owner(&table->s->tdc.LOCK_table_share); while (table->s->tdc.all_tables_refs) mysql_cond_wait(&table->s->tdc.COND_release, &table->s->tdc.LOCK_table_share); } > tc_remove_table(table); > - mysql_mutex_unlock(&LOCK_open); > + mysql_mutex_unlock(&table->s->tdc.LOCK_table_share); > table->in_use= 0; > intern_close_table(table); > return true; Regards, Sergei

2 2

[Maria-developers] [GSOC 2014] GIS Enhancement
by bang honam 19 Mar '14

19 Mar '14

Hi everybody. I am Honam Bang, a researcher of Korean Institute of Science Technology and I will be a master student of UCSD, USA from this Sep. I have been working with GIS research for about one year. Therefore, I decided to work on the 'GIS Enhancement topic' because I have a lot of interest for dealing with geospatial data. My topic is 'implementing shp to sql command tool for MariaDB'. #MDEV-5813. Most shptosql tools are enable to convert 3D coordinates as I know. The shape file white paper( http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf) specifies a lot of 3D coordinates like POINT Z, MULTIPOINTZ which does not exist in MariaDB's Geometry type. Does my topic include implementing those type converting as well? Thank you Honam Bang

2 1

[Maria-developers] first results from replication benchmarks
by Axel Schwenke 18 Mar '14

18 Mar '14

Hi everybody, here are some first results from my benchmarks of replication / group commit performance. The benchmark is sysbench OLTP rw, data set fitting in memory. I tested a total of 27 configurations, 9 each for Maria-5.5.36, Maria-10.0.9 and MySQL-5.6.16. Variations were for binlog on/off, SBR vs. RBR and sync_binlog=0/1. For MariaDB I also tested XtraDB vs. InnoDB, for MySQL it was GTID on vs. GTID off. Not surprisingly turning on the binlog comes with a performance penalty. Also sync_binlog=1 adds another penalty. Quite consistently RBR was a little bit slower than SBR for all combinations (I also observed the binlogs to be bigger for RBR by a factor of nearly 2). The only real surprise was the cost of GTID in MySQL as can be seen in the attached plot. MariaDB-10.0 has GTID enabled by default (you cannot even turn it off) Right now the same benchmark is running again, this time using a single disk for binlogs (was a fast RAID before). Next step is applying binlogs to a slave with different settings for number of slave threads etc. XL

2 2

[Maria-developers] Gsoc proposal submitted for user defined event
by Savita Agrawal 18 Mar '14

18 Mar '14

Hi, I have submitted my proposal for user defined event support project. I am still working on the proposal. Please give feedback. Regards, Savita

1 0

[Maria-developers] Group commit id in mysqlbinlog output
by Kristian Nielsen 18 Mar '14

18 Mar '14

There was a question on the output of mysqlbinlog related to group commit. In 10.0, if two transactions group commit together on the master, their GTID event contains a "commit id" - a 64-bit number. This is used by parallel replication; if two transactions have the same commit id, they can be executed in parallel. In mysqlbinlog output, this looks like this: #140314 13:42:56 server id 1 end_log_pos 772 GTID 0-1-12 cid=180 ... #140314 13:42:56 server id 1 end_log_pos 1027 GTID 0-1-13 cid=180 But if a transaction commits alone on the master (no other transactions participate in the group commit), the mysqlbinlog outpus has no commit id: #140314 13:42:56 server id 1 end_log_pos 437 GTID 0-1-10 The question was why there is no cid=X in the second case, as it makes scripting/grepping the output harder. The reason is that there is no commit id in the event in the binlog in this case (to reduce the size of the binlog). So there is no valid number to put in there. One option could be to make the output something like this: #140314 13:42:56 server id 1 end_log_pos 437 GTID 0-1-10 cid=<none> This would allow to grep for "cid=" and catch everything. I do not have a strong opinion one way or the other, if there are people who find this useful, then let me know and I can change it. - Kristian.

4 6

Re: [Maria-developers] rbr triggers (all)
by Sergei Golubchik 18 Mar '14

18 Mar '14

Hi, Oleksandr! Ok to push. See a couple of comments below. On Mar 18, Oleksandr Byelkin wrote: > === added file 'mysql-test/suite/rpl/t/rpl_row_triggers.test' > --- mysql-test/suite/rpl/t/rpl_row_triggers.test 1970-01-01 00:00:00 +0000 > +++ mysql-test/suite/rpl/t/rpl_row_triggers.test 2014-03-11 08:49:31 +0000 > @@ -0,0 +1,280 @@ > +-- source include/have_binlog_format_row.inc > +-- source include/have_rbr_triggers.inc > +-- source include/master-slave.inc > +-- source include/have_innodb.inc Note that master-slave.inc should always be the last in the series of includes. Everything that can fail should be before it, otherwise you can abort and skip the test after configuring replication, but without a corresponding replication cleanup. It will affect following tests. > === added file 'mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test' > --- mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test 1970-01-01 00:00:00 +0000 > +++ mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test 2014-03-11 08:49:48 +0000 > @@ -0,0 +1,43 @@ > +--source include/master-slave.inc > +--source include/have_binlog_format_statement.inc > +--source include/have_rbr_triggers.inc Same here. > === modified file 'sql/log_event.cc' > --- sql/log_event.cc 2014-03-05 22:20:10 +0000 > +++ sql/log_event.cc 2014-03-11 18:39:51 +0000 > @@ -11121,6 +11233,13 @@ Rows_log_event::write_row(rpl_group_info > TABLE *table= m_table; // pointer to event's table > int error; > int UNINIT_VAR(keynum); > + > +#ifdef RBR_TRIGGERS > + bool invoke_triggers= > + slave_run_triggers_for_rbr && !master_had_triggers && table->triggers; > +#else > +#define invoke_triggers 0 > +#endif //RBR_TRIGGERS I think you don't need this ifdef, just declare invoke_triggers as const: const bool invoke_triggers= slave_run_triggers_for_rbr && !master_had_triggers && table->triggers; > auto_afree_ptr<char> key(NULL); > > prepare_record(table, m_width, Regards, Sergei

1 0

Re: [Maria-developers] [Commits] Rev 3994: MDEV-5138 Numerous test failures in "mtr --ps --embedded"
by Sergei Golubchik 18 Mar '14

18 Mar '14

Hi, Holyfoot! On Dec 08, holyfoot(a)askmonty.org wrote: > ------------------------------------------------------------ > revno: 3994 > revision-id: holyfoot(a)askmonty.org-20131208143921-3veatgg9wngyzb47 > parent: holyfoot(a)askmonty.org-20131202104753-srqqm7hv1rtcurx4 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-5138 > timestamp: Sun 2013-12-08 18:39:21 +0400 > message: > MDEV-5138 Numerous test failures in "mtr --ps --embedded". > Thread can be disconnected internally for example after COMMIT statements. What do you mean by that? > So we should check this for the statement execution. Regards, Sergei

1 0

Re: [Maria-developers] [Commits] Rev 3993: MDEV-5138 Numerous test failures in "mtr --ps --embedded"
by Sergei Golubchik 18 Mar '14

18 Mar '14

Hi, holyfoot! On Dec 02, holyfoot(a)askmonty.org wrote: > At file:///home/hf/wmar/mdev-5138/ > > ------------------------------------------------------------ > revno: 3993 > revision-id: holyfoot(a)askmonty.org-20131202104753-srqqm7hv1rtcurx4 > parent: holyfoot(a)askmonty.org-20131130142537-7j272euoaoy0svg1 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-5138 > timestamp: Mon 2013-12-02 14:47:53 +0400 > message: > MDEV-5138 Numerous test failures in "mtr --ps --embedded". > mysqltest in the 'embedded-server' mode runs queries in a separate thread, > but it didn't do so for the statements - they were run in the main thread. > That leads to inconsistencies. > When a test sets SESSION 'dbug' variable like > SET SESSION debug_dbug="+d,warn_during_ha_commit_trans"; > it is run as a plain query in that separate thread, so the main thread remains unaffected. > After that the statement run in the main thread doesn't produce expected 'dbug' errors, > so the test fail. > To fix that I made statement to be run in that special thread along with the plain queries. > That makes the environment consistent. Ok to push. But sayng "statements" in the comment is a big misleading, it'd be cleared if you'd say "prepared statements". Regards, Sergei

1 0