- developers - lists.mariadb.org

[Maria-developers] Cost analysis: comparison of the two approaches
by Sergei Petrunia 08 Aug '12

08 Aug '12

So, we're comparing two plans that were mentioned earlier. FirstMatch plan has: (gdb) p *current_read_time $426 = 360931 (gdb) p *current_record_count $427 = 4500 which is much more greater than the SJ-Materialization plan, which has: (gdb) print current_record_count $634 = 4500 (gdb) print current_read_time $635 = 8663.1088587744125 Let's check whether the difference can be overcame by adding the cost of the scalar-subquery predicate. Cost of one execution of scalar subquery predicate is 4.5 ~= 5. SJ-Materialization plan will execute the subquery predicate 2378 times: (gdb) p join->best_read $575 = 6229.5578587744112 (gdb) p join->record_count $576 = 2387 which gives added cost of 2378 * 5 = 11890. 8663 + 11890 is still much less than 360931. Hence, just taking scalar-subquery cost into account is not sufficient for query plan to be changed. ps. psergey's opinion: too much unaccounted-for WHERE clauses. to be elaborated further... BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Review request: SHOW EXPLAIN
by Sergei Petrunia 08 Aug '12

08 Aug '12

Hi Sergei, Please find attached the combined SHOW EXPLAIN patch. It is ready for review of the concurrency part. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 12

[Maria-developers] Streamlining Buildbot builds
by Daniel Bartholomew 08 Aug '12

08 Aug '12

Serg brought this to my attention and asked me to look into it. Basically, over time we keep adding more and more builders to buildbot and we build an ever increasing number of packages. One way to keep turnaround times for builds reasonable is to add more hardware. Which we are doing by adding a third KVM build host. However, building every push on every KVM virtual machine is overkill. For example, if one Ubuntu build succeeds, most other Ubuntu builds will also succeed. We only really need to build "everything" when we are making a release. For other builds we can get away with building on a sub-set of builders. The main benefit of doing this is to speed up the turn-around time for builds. One potential issue is the possibility of not catching an error which, for some reason, only manifests on a certain OS version. There may be other issues. Let me know if you think of any. Assuming we want to move ahead with building most builds on a sub-set of our builders instead of the full set, the easiest thing to do is to configure buildbot to only build on every builder for pushes into the "-release" and top-level (5.3, 5.5, etc...) branches. All other branches would have an abridged set of builders assigned to them. First things first, we need to decide which builders will build every push in every tree (aka "build everything") and which builders will only build releases. Builders on dedicated machines don't need to be changed, only KVM builders. Here are the KVM builders I propose to be in the "build everything" set: "kvm-bintar-precise-amd64", "kvm-bintar-precise-x86", "kvm-deb-debian6-amd64", "kvm-deb-debian6-x86", "kvm-deb-precise-amd64", "kvm-deb-precise-x86", "kvm-rpm-fedora17-amd64", "kvm-rpm-fedora17-x86", "kvm-rpm-centos6-amd64", "kvm-rpm-centos6-x86", The reasoning for choosing the above is to have an x86 and amd64 builder for Ubuntu, CentOS, Fedora, and Debian. And also to always build binary tarballs. Thoughts? 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://gplus.to/mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

3 2

[Maria-developers] Mysql crashes with signal 11 after a select (or even explain) on a view with a subselect.
by kirchy＠gmx.de 03 Aug '12

03 Aug '12

>Description: we see some problems with views that include other views (I think that is the problem...) and selects on views with subselects. The log says after a crash: Aug 3 17:29:22 db7 mysqld: 120803 17:29:22 [ERROR] mysqld got signal 11 ; Aug 3 17:29:22 db7 mysqld: This could be because you hit a bug. It is also possible that this binary Aug 3 17:29:22 db7 mysqld: or one of the libraries it was linked against is corrupt, improperly built, Aug 3 17:29:22 db7 mysqld: or misconfigured. This error can also be caused by malfunctioning hardware. Aug 3 17:29:22 db7 mysqld: We will try our best to scrape up some info that will hopefully help diagnose Aug 3 17:29:22 db7 mysqld: the problem, but since we have already crashed, something is definitely wrong Aug 3 17:29:22 db7 mysqld: and this may fail. Aug 3 17:29:22 db7 mysqld: Aug 3 17:29:22 db7 mysqld: key_buffer_size=0 Aug 3 17:29:22 db7 mysqld: read_buffer_size=2097152 Aug 3 17:29:22 db7 mysqld: max_used_connections=100 Aug 3 17:29:22 db7 mysqld: max_threads=302 Aug 3 17:29:22 db7 mysqld: threads_connected=35 Aug 3 17:29:22 db7 mysqld: It is possible that mysqld could use up to Aug 3 17:29:22 db7 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5570184 K Aug 3 17:29:22 db7 mysqld: bytes of memory Aug 3 17:29:22 db7 mysqld: Hope that's ok; if not, decrease some variables in the equation. Aug 3 17:29:22 db7 mysqld: Aug 3 17:29:22 db7 mysqld: Thread pointer: 0x7f3521cba000 Aug 3 17:29:22 db7 mysqld: Attempting backtrace. You can use the following information to find out Aug 3 17:29:22 db7 mysqld: where mysqld died. If you see no messages after this, something went Aug 3 17:29:22 db7 mysqld: terribly wrong... Aug 3 17:29:22 db7 mysqld: stack_bottom = 0x7f1f899b4e98 thread_stack 0x3c000 Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x7f341e1f39de] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(handle_segfault+0x3e5) [0x7f341de306f5] Aug 3 17:29:22 db7 mysqld: /lib/libpthread.so.0(+0xef60) [0x7f341c1def60] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Field_dependency_recorder::visit_field(Field*)+0x1) [0x7f341dfb48c1] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Item_field::enumerate_field_refs_processor(unsigned char*)+0x16) [0x7f341dd779d6] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Item_subselect::enumerate_field_refs_processor(unsigned char*)+0x4c) [0x7f341ddeb6dc] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Dep_analysis_context::setup_equality_modules_deps(List<Dep_module>*)+0x13e) [0x7f341dfb406e] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x4e140d) [0x7f341dfb440d] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x4e15a0) [0x7f341dfb45a0] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x3d2eae) [0x7f341dea5eae] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(JOIN::optimize()+0x465) [0x7f341dea7a55] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0xda) [0x7f341deb04da] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x15d) [0x7f341deb0f5d] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x368baa) [0x7f341de3bbaa] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(mysql_execute_command(THD*)+0x40e) [0x7f341de3e05e] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2c1) [0x7f341de43711] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xceb) [0x7f341de4485b] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(do_command(THD*)+0xf4) [0x7f341de45364] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(handle_one_connection+0x13e) [0x7f341de3762e] Aug 3 17:29:22 db7 mysqld: /lib/libpthread.so.0(+0x68ba) [0x7f341c1d68ba] Aug 3 17:29:22 db7 mysqld: /lib/libc.so.6(clone+0x6d) [0x7f341b7a002d] Aug 3 17:29:22 db7 mysqld: This happens on all servers over here. At least all I tested it on... even if these queries are too complex, the mysqld should never die with signal 11 but give some kind of sql error! >How-To-Repeat: I have some queries over here that crash the server. Is there something like a drop-in binary for mysqld I could use to gather more information for you? or shall I run mysqld with strace or so? the database is far to big and complex to copy it (besides legal reasons, I can't send it to anybody), but I have replication slaves I can use for testing. >Fix: just don't run the query... no idea. >Submitter-Id: <submitter ID> >Originator: root >Organization: kirchy >MySQL support: none >Synopsis: Mysql crashes with signal 11 after a select (or even explain) on a view with a subselect. >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-5.2.7-MariaDB-mariadb101~squeeze ((MariaDB - http://mariadb.com/)) >Server: /usr/bin/mysqladmin Ver 9.0 Distrib 5.2.7-MariaDB, for debian-linux-gnu on x86_64 Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc, 2009 Monty Program Ab This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.2.7-MariaDB-mariadb101~squeeze-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 57 min 5 sec Threads: 23 Questions: 491608 Slow queries: 175 Opens: 3750 Flush tables: 1 Open tables: 460 Queries per second avg: 143.535 >C compiler: gcc-4.4.real (Debian 4.4.4-8) 4.4.5 20100728 (prerelease) >C++ compiler: g++-4.4.real (Debian 4.4.4-8) 4.4.5 20100728 (prerelease) >Environment: System: Linux db7 2.6.39-bpo.2-amd64 #1 SMP Tue Jul 26 10:35:23 UTC 2011 x86_64 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.4.5-8' --with-bugurl=file:///usr/share/doc/gcc-4.4/README.Bugs --enable-languages=c,c++,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-4.4 --enable-shared --enable-multiarch --enable-linker-build-id --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --with-gxx-include-dir=/usr/include/c++/4.4 --libdir=/usr/lib --enable-nls --enable-clocale=gnu --enable-libstdcxx-debug --enable-objc-gc --with-arch-32=i586 --with-tune=generic --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu Thread model: posix gcc version 4.4.5 (Debian 4.4.5-8) Compilation info (call): CC='gcc -static-libgcc' CFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' CXX='g++ -static-libgcc' CXXFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='gcc -static-libgcc' CFLAGS=' -O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -DUNIV_LINUX' CXX='g++ -static-libgcc' CXXFLAGS=' -O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -fno-implicit-templates -fno-exceptions -fno-rtti' LDFLAGS=' -rdynamic ' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 14 3. Mär 2011 /lib/libc.so.6 -> libc-2.11.2.so -rwxr-xr-x 1 root root 1432968 23. Jan 2011 /lib/libc-2.11.2.so -rw-r--r-- 1 root root 4433334 23. Jan 2011 /usr/lib/libc.a -rw-r--r-- 1 root root 247 23. Jan 2011 /usr/lib/libc.so Configure command: ../configure '--build=x86_64-linux-gnu' '--host=x86_64-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-mariadb101~squeeze' '--with-comment=(MariaDB - http://mariadb.com/)' '--with-system-type=debian-linux-gnu' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-pstack' '--with-fast-mutexes' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-big-tables' '--without-docs' '--with-extra-charsets=all' '--with-libwrap' '--with-ssl=/usr' '--with-libevent' '--without-readline' '--with-zlib-dir=' '--with-embedded-server' '--with-embedded-privilege-control' '--with-plugins=max-no-ndb' '--without-plugin-innodb_plugin' 'build_alias=x86_64-linux-gnu' 'host_alias=x86_64-linux-gnu' 'CC =gcc -static-libgcc' 'CFLAGS=-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' 'LDFLAGS=' 'CPPFLAGS=' 'CXX=g++ -static-libgcc' 'CXXFLAGS=-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized'

2 1

Re: [Maria-developers] [Commits] Rev 3353: Performed re-factoring and re-structuring of the code for mwl#248: in file:///home/igor/maria/maria-5.5-mwl248-changes/
by Kristian Nielsen 03 Aug '12

03 Aug '12

Davi Arnaut <davi(a)twitter.com> writes: > On Thu, Jul 26, 2012 at 5:50 PM, Igor Babaev <igor(a)askmonty.org> wrote: >> + bzero(column_stats, sizeof(Column_statistics) * cnt); > > That's not allowed for non-POD types. Can you elaborate on exactly when bzero() (or memset() or similar) is allowed or not allowed? I searched in my C++ reference, and did not really find anything ... in fact I did not find anything that said memset(p, 0, sizeof(*p)) would _ever_ be allowed for a class. It is my understanding that even for C, memset(..., 0, ...) is only valid for integer types. Initialising with memset() floating-point and even pointers values is not well-defined! Or did I miss something? I think this is an important issue, as memset() of a C++ class is certainly wrong in many cases, and can lead to subtle and platform-dependent failures. So let's point out clearly the rules that apply, or define such rules if necessary: 1. Does the C and C++ standards define suitable rules? If so, we should use them. However, I suspect that memset() is invalid in standard C and C++ except for arrays of integers, which is excessively restrictive. 2. If C/C++ standard is not sufficient, we need to agree on a coding style (and stay within what works on all reasonable compilers/platforms). I would much prefer to find an existing standards rule or convention. But failing that I would suggest: - Only memset() and memcpy() structs. - Virtual functions are definitely out (in struct as well as members). - Constructors and destructors also must not be defined for such structs (or its members). - Derived classes also should never be memset()'ed. Class inheritance is likely to need extra info on some (present or future) platforms and compilers. - Do not have private: methods in such memset() structs. I do not think it matters to the code generated, but private: is meaningless if arbitrary code is manipulating the memory of the struct directly. Usually I would have no methods at all in structs that I plan to do memset() on. But I suppose we also need to look at what existing code does. Comments? - Kristian.

2 1

Re: [Maria-developers] [Commits] Rev 3353: Performed re-factoring and re-structuring of the code for mwl#248: in file:///home/igor/maria/maria-5.5-mwl248-changes/
by Michael Widenius 31 Jul '12

31 Jul '12

Hi! >>>>> "Davi" == Davi Arnaut <davi(a)twitter.com> writes: Davi> On Mon, Jul 30, 2012 at 7:34 AM, Oleksandr Byelkin Davi> <sanja(a)montyprogram.com> wrote: >> 27.07.2012 20:57, Davi Arnaut пишет: >>> >>> Also, bzero is deprecated. >> AFAIK bzero is macro in our sources. Davi> It is a macro if not available. Which then becomes a problem because Davi> the bzero interface is not well defined (first argument begin char Davi> pointer versus void pointer). It should be pretty simple, let's not Davi> use deprecated and broken interfaces. Actually, in mariadb code (and original mysql code) we use bzero a lot and it's a stable, well tested, well known and well defined function. Better to always use the same code to reset memory than doing it many different ways. In MySQL code bzero() has been the offical and recommended way to reset memory. Regards, Monty

1 0

Re: [Maria-developers] [Commits] Rev 3453: MWL#182: Explain running statements
by Sergei Golubchik 28 Jul '12

28 Jul '12

Hi, Sergey! On Jul 17, Sergey Petrunya wrote: > ------------------------------------------------------------ > revno: 3453 > revision-id: psergey(a)askmonty.org-20120717192703-bgt3xgkbs8qvz3tc > parent: psergey(a)askmonty.org-20120717175208-ruovz91shg0ph875 > committer: Sergey Petrunya <psergey(a)askmonty.org> > branch nick: 5.5-show-explain-r21 > timestamp: Tue 2012-07-17 23:27:03 +0400 > message: > MWL#182: Explain running statements > Address feedback from the second code review: > - Make SHOW EXPLAIN code convert the query string before pushing it as a warning text. > @@ -2135,8 +2135,35 @@ int fill_show_explain(THD *thd, TABLE_LI > } > else > { > + /* > + Push the query string as a warning. The query may be in a different > + charset than the charset that's used for error messages, so, convert it > + if needed. > + */ there's no test case for this. was it too difficult to create? Regards, Sergei

1 0

[Maria-developers] question about MTR
by Rich Prohaska 26 Jul '12

26 Jul '12

Hello, It appears that MTR looks at a test and figures out the appropriate mysqld configuration needed to run the test. For example, if I have lines like "source include/have_XYZ.inc" in my test, MTR will run the test with the right storage engines loaded (and only those storage engines). Is this accurate? Thanks Rich Prohaska

2 1

[Maria-developers] Please test: BUG#992942 & MDEV-325 patch
by Sergei Petrunia 20 Jul '12

20 Jul '12

Hi Elena, I've made a patch that fixes both BUG#992942 and MDEV-325. It is rather risky, as it touches the part of the code whose interaction with other parts of the code is not very well defined. This is why I request RQG testing. Could you please run some test that would use ORDER BY ... LIMIT, GROUP BY, subqueries, tables that may or may not be read with range/index_merge access, index condition pushdown, etc? (Yes, you may really need all of the above to trigger the problem. Check out analysis comments in the bug). The code is at: bzr+ssh://bazaar.launchpad.net/~maria-captains/maria/5.5-show-explain-test1 it is based on 5.5-show-explain, so SHOW EXPLAIN part can be tested, too. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

[Maria-developers] Statistics tables show up in userstat - intentional?
by Sergei Petrunia 12 Jul '12

12 Jul '12

Hello Igor, I've noticed that engine-independent statistics show up in userstats: +--------------+-------------+-----------+--------------+-------------------------+ | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +--------------+-------------+-----------+--------------+-------------------------+ | bug929732 | c2 | 249 | 0 | 0 | | mysql | index_stat | 84 | 0 | 0 | | bug929732 | v | 276 | 0 | 0 | | mysql | column_stat | 44 | 0 | 0 | | bug929732 | c | 249 | 0 | 0 | | bug929732 | v3 | 837280 | 0 | 0 | | mysql | table_stat | 12 | 0 | 0 | | bug929732 | v4 | 3994 | 0 | 0 | | bug929732 | v2 | 53506 | 0 | 0 | +--------------+-------------+-----------+--------------+-------------------------+ Is this intentional? I've also started collecting some minimal docs at http://kb.askmonty.org/en/engine-independent-table-statistics/ BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0