- developers - lists.mariadb.org

[Maria-developers] Order by speed optimization
by Matthew Lagoe 13 Jan '13

13 Jan '13

I was talking with a few people about a query such as select * from table order by X, Y, Z limit G; > > As the "ORDER BY" for me could change to any number of 12 values in any order I am not able to use a multicolumn index as such I was looking at how to optimize in such case where sorting is dynamic. On my dataset the query takes ~7 seconds for a full table scan but only .3 seconds when using the column index and returns ~80,000 records for the query (before the LIMIT) I was wondering if there is any reason that the order by syntax doesn't use the following logic, I was thinking of looking at the code but was told such a simple optimization must have a reason that its not already in the code, so I am asking here before I spend hours being stupid. This logic block will be based on the above query. 1. If index exists for column X and there is a limit on the query then use the index and sort the list, then return the first G values, continue past G until you hit a different value for X then the value that was at G 2. Use this smaller subset of data for then ordering the data based on Y 3. Order the data again based on Z 4. Return the sorted data like normal If there is a reason this optimization isn't in the code please someone let me know, if not I think I will take a look at adding it. -Botanic

2 2

[Maria-developers] Kubuntu 12.04.1 and MariaDB
by Giacomo Picchiarelli 11 Jan '13

11 Jan '13

Hi! I'm having troubles installing mariadb 10.0 in Kubuntu 12.04.1. When I try to install it, dependencies are getting enraged and apt (through synaptic or muon) suggests to remove kde-plasma-desktop. This happens when I try to install mariadb-server-core 10.0. Of course if I remove kde-plasma-desktop I can't use my kde desktop, because it depends on mysql packages. I was trying to install it for educational purpose, and try to build and make some tests. Do you have any suggestions? Thank you -- Giacomo Picchiarelli <gpicchiarelli(a)gmail.com>

3 3

[Maria-developers] External replication proceeding
by md＠rpzdesign.com 10 Jan '13

10 Jan '13

Monty: I received your email and am re-posting to the maria developers list. It is truly amazing that maria developers are HAPPY with replication as it stands right now. binary logs, Single SLAVE reading from individual masters, bla-bla-bla..... All the current replication code is like walking on the edge of a cliff, anything goes wrong and the replication process STOPS with an error. And a HUMAN BEING must wake up from their blissful sleep to get the process started again and figure out what happened. And I just LOVE having to: LOCK TABLES; RESET MASTER SET SLAVE OFF; MYSQLDUMP; COPY FILES TO SLAVE; SET MASTER TO 'sql_log_bin=45678945"; SET SLAVE ON; SECONDS BEHIND MASTER... WRITE A THESIS FOR MY PHD IN ROCKET SCIENCE Oh my GOD!!!!!! What an absolute nightmare of a design, it works, just not well. I have not made an in depth study of galera, but I refuse to make replication another research project, replication should be EASY, auto-healing, self analyzing, N-MASTER and with simple source code. I will open source the files that I use to change the MYSQLD daemon. The best way to debug MariaDB on Linux is to run the program in Netbeans 7.1.2. You can even give it command line parameters to work against a currently installed system with the data/conf files in place. Your trace idea is very nice, but there is nothing like STEPPING THROUGH the code in NetBeans/GDB in a nice IDE instead of cryptic command line GDB. Cheers, md On 1/2/2013 5:21 AM, Michael Widenius wrote: > Hi! > > A better place to send emails like this is to > maria-developers(a)lists.launchpad.net; That way you can get all > MariaDB developers to answer you questions. > >>>>>> "md" == md <md(a)rpzdesign.com> writes: > md> Hello: > md> Does anybody know the name(s) of the source files I could find the code > md> that processes the Table/Procedure Drop/Create/Update, Alter Table, etc > md> statements. > > md> I want to intercept these SQL statement calls in mysqld before > md> they get sent to the specific storage engine like Aria or Xtradb. > > sql_parse.cc proably contains the code you are looking for. > Another place to look is in the the current replication code, > ie the sql_repl.cc > > md> I am working on an asynchronous external replication daemon > md> that handles all of the data and structure synchronization > md> replication issues (N-master-master, Hot Insert Master additions, DDL > md> changes, etc) > > Sounds very intersting. > Is this a open source project ? > > md> This is one of the missing pieces of the puzzle for MariaDB. > md> (oh yeah, mysql does not exist to me anymore, only Aria/Xtradb) > > :) > > md> I need to have the mysqld daemon issue a signal that a structural change > md> has taken place and the external replication daemon will pick it up > md> and handle all the changes processing across ALL the masters (WITHOUT A > md> HUMAN BEING INVOLVED!!!!) > > md> I figured I would ask since most of you are fluent in the source > md> code and just know where everything is, you would know right away. > > That's ok, but best way to get quick responce is to ask on the > maria-developers list or at #maria channel on Freenode. > > md> I am using MariaDB 5.5.28a from source. > > On thing you can do is: > > ./BUILD/compile-pentium-debug-max > mysqld --debug > > And then start 'mysql' and execute a CREATE TABLE command. > > You can find then find a full trace of all executed functions in: > /tmp/mysqld.trace > > That's probably one of the best ways to learn how MariaDB works. > > See: > > https://kb.askmonty.org/en/creating-a-trace-file/ > > Regards, > Monty >

3 2

[Maria-developers] High Priest of Humility
by md＠rpzdesign.com 08 Jan '13

08 Jan '13

Arjen: I would not go so far as to call it "raving" or boasting about anything. It was a private email message to you only. Not broadcast publicly. For a MariaDB captain, that is pretty bold offering a negative opinion to a potential contributor. Negative comments like that are not the best support for any level of enthusiasm for openness, collaboration, and sharing. Hint: Stop giving hints. marco On 1/7/2013 5:05 PM, Arjen Lentz wrote: > Hi Marco > >> Nice to see your response. >> >> I will be providing the code that I use to modify the MariaDB core >> daemon soon. > I presume you'll put it up in a branch on Launchpad? > > >> You will be amazed how little I am changing the core code. > Hint: modesty/humility are a good traits for an OSS developer. Let other people be amazed and impressed when you deliver code - raving about yourself beforehand is less cool... > > >> Where is the open source for Galera? (could not find it readily) > At Codership, but also in the 5.5 Galera repository of MariaDB. > > > Regards, > Arjen. > >

1 0

[Maria-developers] Missing man/mysql_plugin.1 in tar ball
by Honza Horak 07 Jan '13

07 Jan '13

Hi all, I've noticed that man page for mysql_plugin utility (man/mysql_plugin.1) is not present in mariadb-5.5.28a.tar.gz while it is present in mysql-5.5.28.tar.gz. However, I failed to find the reason why in case there is one. Or is that a mistake? Does anybody know? Regards, Honza

2 1

Re: [Maria-developers] API Documentaion for Replication Features
by Lixun Peng 31 Dec '12

31 Dec '12

Hi Monty, On Tue, Dec 25, 2012 at 8:21 PM, Michael Widenius < michael.widenius(a)gmail.com> wrote: > > Hi! > > Sorry that I missed this email before. > <cut> > Lixun> I can try to implement it. Are you doing it? > > Please try to implemement it? > You can look at the keycache code how we have done this before! > > OK, I'm trying to do it. Thanks, Lixun -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com

2 1

Re: [Maria-developers] Rev 3480: Post review changes (total (really))
by Sergei Golubchik 24 Dec '12

24 Dec '12

Hi, Oleksandr! On Dec 23, Oleksandr Byelkin wrote: > 22.12.2012 21:28, Sergei Golubchik пишет: > >> @@ -100,6 +101,8 @@ struct st_dynamic_column_value > >> > >> typedef struct st_dynamic_column_value DYNAMIC_COLUMN_VALUE; > >> > >> +/* old functions (deprecated) */ > > I usually prefer code to comments. Kind of a comment that a compiler > > can make use of. That means an assert(aaa == 0) instead of /* aaa > > should be 0 here */ and instead of comment above, I'd rather have > > __attribute__((deprecated)) > > __attribute__((deprecated)) is GCC, we also use MS compiler. So ifdef > is better until we get appropriate multi-platform macro. 1. __attribute__ is defined to be empty for other compilers (somewhere in my_global.h, I guess), so it won't break the compilation. 2. I did not mean to use it instead of the #ifdef, I mean to use it instead of a /* old functions (deprecated) */ comment. Because a compiler cannot read the comment. 3. And anyway, it was just a suggestion, you can keep your current code and not change anything. As you prefer. > everything else will be fixed. Thanks! Regards, Sergei

1 0

Re: [Maria-developers] Rev 3480: Post review changes (total (really))
by Sergei Golubchik 22 Dec '12

22 Dec '12

Hi, Sanja! See below. Just a couple of small API-related comments. And one JSON bug. On Dec 21, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-10.0-cassandra/ > > ------------------------------------------------------------ > revno: 3480 > revision-id: sanja(a)askmonty.org-20121221192211-93tc13j3a6c85qea > parent: sanja(a)montyprogram.com-20121119121604-5h5tu0zn11em0sb3 > committer: sanja(a)askmonty.org > branch nick: work-maria-10.0-cassandra > timestamp: Fri 2012-12-21 21:22:11 +0200 > message: > Post review changes (total (really)). > > === modified file 'include/ma_dyncol.h' > --- a/include/ma_dyncol.h 2012-09-28 12:27:16 +0000 > +++ b/include/ma_dyncol.h 2012-12-21 19:22:11 +0000 > @@ -38,12 +38,13 @@ > how the offset are stored. > */ > #define MAX_DYNAMIC_COLUMN_LENGTH 0X1FFFFFFFL > +#define MAX_DYNAMIC_COLUMN_LENGTH_NM 0XFFFFFFFFFL Hm. Two questions. 1. Where does the number come from? 2. You've introduced a constant, but you aren't using it anywhere. What's the point? > /* > Limits of implementation > */ > -#define MAX_NAME_LENGTH 255 > #define MAX_TOTAL_NAME_LENGTH 65535 > +#define MAX_NAME_LENGTH (MAX_TOTAL_NAME_LENGTH/4) > > /* NO and OK is the same used just to show semantics */ > #define ER_DYNCOL_NO ER_DYNCOL_OK > @@ -100,6 +101,8 @@ struct st_dynamic_column_value > > typedef struct st_dynamic_column_value DYNAMIC_COLUMN_VALUE; > > +/* old functions (deprecated) */ I usually prefer code to comments. Kind of a comment that a compiler can make use of. That means an assert(aaa == 0) instead of /* aaa should be 0 here */ and instead of comment above, I'd rather have __attribute__((deprecated)) > +#ifdef MADYNCOL_DEPRECATED > enum enum_dyncol_func_result > dynamic_column_create(DYNAMIC_COLUMN *str, > uint column_nr, DYNAMIC_COLUMN_VALUE *value); > @@ -133,73 +121,105 @@ dynamic_column_update_many(DYNAMIC_COLUM ... > enum enum_dyncol_func_result > -dynamic_column_exists_fmt(DYNAMIC_COLUMN *str, void *key, my_bool string_keys); > +dynamic_column_get(DYNAMIC_COLUMN *org, uint column_nr, > + DYNAMIC_COLUMN_VALUE *store_it_here); > +#endif > > -/* List of not NULL columns */ > +/* new functions */ > enum enum_dyncol_func_result > -dynamic_column_list(DYNAMIC_COLUMN *org, DYNAMIC_ARRAY *array_of_uint); > +mariadb_dyncol_create_many(DYNAMIC_COLUMN *str, > + uint column_count, > + uint *column_numbers, > + DYNAMIC_COLUMN_VALUE *values, > + my_bool new_string); > enum enum_dyncol_func_result > -dynamic_column_list_str(DYNAMIC_COLUMN *str, DYNAMIC_ARRAY *array_of_lexstr); > +mariadb_dyncol_create_many_named(DYNAMIC_COLUMN *str, > + uint column_count, > + LEX_STRING *column_keys, > + DYNAMIC_COLUMN_VALUE *values, > + my_bool new_string); > + > + > enum enum_dyncol_func_result > -dynamic_column_list_fmt(DYNAMIC_COLUMN *str, DYNAMIC_ARRAY *array, my_bool string_keys); > +mariadb_dyncol_update_many(DYNAMIC_COLUMN *str, > + uint add_column_count, > + uint *column_keys, > + DYNAMIC_COLUMN_VALUE *values); > +enum enum_dyncol_func_result > +mariadb_dyncol_update_many_named(DYNAMIC_COLUMN *str, > + uint add_column_count, > + LEX_STRING *column_keys, > + DYNAMIC_COLUMN_VALUE *values); > + > + > +enum enum_dyncol_func_result > +mariadb_dyncol_exists(DYNAMIC_COLUMN *org, uint column_nr); > +enum enum_dyncol_func_result > +mariadb_dyncol_exists_named(DYNAMIC_COLUMN *str, LEX_STRING *name); > + > +/* List of not NULL columns */ > +enum enum_dyncol_func_result > +mariadb_dyncol_list(DYNAMIC_COLUMN *org, DYNAMIC_ARRAY *array_of_uint); Sanja, let's be constistent. If mariadb_dyncol_list_named() returns an array of LEX_STRING's, than mariadb_dyncol_list should return an array of integers, not a dynamic array. Besides you don't use DYNAMIC_ARRAY *anywhere* else in the new (not deprecated) API. > +enum enum_dyncol_func_result > +mariadb_dyncol_list_named(DYNAMIC_COLUMN *str, uint *count, LEX_STRING **names); > > /* > if the column do not exists it is NULL > */ > enum enum_dyncol_func_result > -dynamic_column_get(DYNAMIC_COLUMN *org, uint column_nr, > +mariadb_dyncol_get(DYNAMIC_COLUMN *org, uint column_nr, > DYNAMIC_COLUMN_VALUE *store_it_here); > enum enum_dyncol_func_result > -dynamic_column_get_str(DYNAMIC_COLUMN *str, LEX_STRING *name, > - DYNAMIC_COLUMN_VALUE *store_it_here); > +mariadb_dyncol_get_named(DYNAMIC_COLUMN *str, LEX_STRING *name, > + DYNAMIC_COLUMN_VALUE *store_it_here); > > -my_bool dynamic_column_has_names(DYNAMIC_COLUMN *str); > +my_bool mariadb_dyncol_has_names(DYNAMIC_COLUMN *str); > > enum enum_dyncol_func_result > -dynamic_column_check(DYNAMIC_COLUMN *str); > +mariadb_dyncol_check(DYNAMIC_COLUMN *str); > > enum enum_dyncol_func_result > -dynamic_column_json(DYNAMIC_COLUMN *str, DYNAMIC_STRING *json); > +mariadb_dyncol_json(DYNAMIC_COLUMN *str, DYNAMIC_STRING *json); > > #define dynamic_column_initialize(A) memset((A), 0, sizeof(*(A))) > #define dynamic_column_column_free(V) dynstr_free(V) > > /* conversion of values to 3 base types */ > enum enum_dyncol_func_result > -dynamic_column_val_str(DYNAMIC_STRING *str, DYNAMIC_COLUMN_VALUE *val, > +mariadb_dyncol_val_str(DYNAMIC_STRING *str, DYNAMIC_COLUMN_VALUE *val, > CHARSET_INFO *cs, my_bool quote); > enum enum_dyncol_func_result > -dynamic_column_val_long(longlong *ll, DYNAMIC_COLUMN_VALUE *val); > +mariadb_dyncol_val_long(longlong *ll, DYNAMIC_COLUMN_VALUE *val); > +enum enum_dyncol_func_result > +mariadb_dyncol_val_double(double *dbl, DYNAMIC_COLUMN_VALUE *val); > + > + > enum enum_dyncol_func_result > -dynamic_column_val_double(double *dbl, DYNAMIC_COLUMN_VALUE *val); > +mariadb_dyncol_unpack(DYNAMIC_COLUMN *str, > + uint *count, > + LEX_STRING **names, DYNAMIC_COLUMN_VALUE **vals); > > +int mariadb_dyncol_column_cmp_named(const LEX_STRING *s1, const LEX_STRING *s2); > enum enum_dyncol_func_result > -dynamic_column_vals(DYNAMIC_COLUMN *str, > - DYNAMIC_ARRAY *names, DYNAMIC_ARRAY *vals, > - char **free_names); > +mariadb_dyncol_column_count(DYNAMIC_COLUMN *str, uint *column_count); > > /*************************************************************************** > Internal functions, don't use if you don't know what you are doing... > ***************************************************************************/ > > -#define dynamic_column_reassociate(V,P,L, A) dynstr_reassociate((V),(P),(L),(A)) > +#define mariadb_dyncol_reassociate(V,P,L, A) dynstr_reassociate((V),(P),(L),(A)) I'd remove it. You can use dynstr_reassociate() directly in Item_func_dyncol_create::val_str(), if you need it, but don't encourage others to do it, by pussing this macro here. > -#define dynamic_column_value_init(V) (V)->type= DYN_COL_NULL > +#define dyncol_value_init(V) (V)->type= DYN_COL_NULL 1. you forgot the mariadb_ prefix 2. This macro seems to be unused. Perhaps you can simply delete it? > === modified file 'mysql-test/r/dyncol.result' > --- a/mysql-test/r/dyncol.result 2012-09-28 11:01:17 +0000 > +++ b/mysql-test/r/dyncol.result 2012-12-21 19:22:11 +0000 > @@ -1577,3 +1587,53 @@ COLUMN_CHECK('') > SELECT COLUMN_CHECK(NULL); > COLUMN_CHECK(NULL) > NULL > +# > +# escaping check > +# > +select column_json(column_create("string", "'\"/\\`.,whatever")),hex(column_create("string", "'\"/\\`.,whatever")); > +column_json(column_create("string", "'\"/\\`.,whatever")) hex(column_create("string", "'\"/\\`.,whatever")) > +[{"string":"'\"/\\`.,whatever"}] 040100060000000300737472696E670827222F5C602E2C7768617465766572 Please, add a test with two values. What it will look like: [{"a":1},{"b":b}] ? or { "a" : 1, "b" : 2 } ? I don't see a reason why it should be an array of one-value objects, instead of one object, with many values. But perhaps I'm missing something, why did you prefer an array? > +# > +# embedding test > +# > +select column_json(column_create("val", "val", "emb", column_create("val2", "val2"))); > +column_json(column_create("val", "val", "emb", column_create("val2", "val2"))) > +[{"emb":[{"val2":"val2"}],{"val":"val"}] ouch. invalid json, curly braces aren't properly paired. > +select column_json(column_create(1, "val", 2, column_create(3, "val2"))); > +column_json(column_create(1, "val", 2, column_create(3, "val2"))) > +[{"1":"val"},{"2":[{"3":"val2"}]] same here. > === modified file 'sql/item_func.h' > --- a/sql/item_func.h 2012-09-27 23:06:56 +0000 > +++ b/sql/item_func.h 2012-12-21 19:22:11 +0000 > @@ -58,7 +58,7 @@ public: > NOW_FUNC, TRIG_COND_FUNC, > SUSERVAR_FUNC, GUSERVAR_FUNC, COLLATE_FUNC, > EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC, > - NEG_FUNC, GSYSVAR_FUNC }; > + NEG_FUNC, GSYSVAR_FUNC, DYNCOL }; DYNCOL_FUNC > enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL, > OPTIMIZE_EQUAL }; > enum Type type() const { return FUNC_ITEM; } Regards, Sergei

1 0

[Maria-developers] MariaDB Galera 5.5.28a Release Prep
by Daniel Bartholomew 21 Dec '12

21 Dec '12

All, looks like my first message on this never reached the list, so sending again. I've prepped the release notes and changelog pages for this release and will be activating it in a few hours (barring any show stoppers). - https://kb.askmonty.org/en/mariadb-galera-5528a-release-notes/ - https://kb.askmonty.org/en/mariadb-galera-5528a-changelog/ I've updated the "What is MariaDB Galera Cluster?" and "Getting Started..." pages. If other updates are needed, let me know. https://kb.askmonty.org/en/what-is-mariadb-galera-cluster/ https://kb.askmonty.org/en/getting-started-with-mariadb-galera-cluster/ Also please let me know if updates are needed on the "About" and "Known Limitations" pages https://kb.askmonty.org/en/about-galera-replication/ https://kb.askmonty.org/en/mariadb-galera-cluster-known-limitations/ Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

Re: [Maria-developers] [Commits] Rev 3484: postreview fixes (part 5)
by Sergei Golubchik 21 Dec '12

21 Dec '12

Hi, Sanja! On Dec 21, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-10.0-cassandra/ > ------------------------------------------------------------ > revno: 3484 > revision-id: sanja(a)askmonty.org-20121221010609-cnr2z3fg99kpxzma > committer: sanja(a)askmonty.org > branch nick: work-maria-10.0-cassandra > timestamp: Fri 2012-12-21 03:06:09 +0200 > message: > postreview fixes (part 5) > === modified file 'include/ma_dyncol.h' > --- a/include/ma_dyncol.h 2012-12-20 23:35:38 +0000 > +++ b/include/ma_dyncol.h 2012-12-21 01:06:09 +0000 > @@ -169,7 +169,8 @@ madyncol_exists_named(DYNAMIC_COLUMN *st > enum enum_dyncol_func_result > madyncol_list(DYNAMIC_COLUMN *org, DYNAMIC_ARRAY *array_of_uint); > enum enum_dyncol_func_result > -madyncol_list_named(DYNAMIC_COLUMN *str, DYNAMIC_ARRAY *array_of_lexstr); > +madyncol_list_named(DYNAMIC_COLUMN *str, DYNAMIC_ARRAY *array_of_lexstr, > + void **memory_to_free); Same here. You can put this memory in the dynarray and don't burden the caller with these nuisanses. Or you can use a normal array, not a dynarray. > /* > if the column do not exists it is NULL > > === modified file 'mysql-test/r/dyncol.result' > --- a/mysql-test/r/dyncol.result 2012-12-20 23:48:20 +0000 > +++ b/mysql-test/r/dyncol.result 2012-12-21 01:06:09 +0000 > @@ -1587,3 +1587,7 @@ COLUMN_CHECK('') > SELECT COLUMN_CHECK(NULL); > COLUMN_CHECK(NULL) > NULL > +# quating check "quoting" But in fact, it's not quoting, it's escaping. > +select column_json(column_create("string", "'\"/\\`.,whatever")),hex(column_create("string", "'\"/\\`.,whatever")); > +column_json(column_create("string", "'\"/\\`.,whatever")) hex(column_create("string", "'\"/\\`.,whatever")) > +[{"string":"'\"/\\`.,whatever"}] 040100060000000300737472696E670827222F5C602E2C7768617465766572 > Regards, Sergei

1 0