January 2013 - developers - lists.mariadb.org

[Maria-developers] accelerating CREATE TABLE
by Toshikuni Fukaya 13 Mar '13

13 Mar '13

Hi, I made a patch to accelerate CREATE TABLE on the innodb plugin. To zero table spaces, I used fallocate instead of normal writes and sync. I compared MariaDB 5.5 trunk and the patched one by our own application, which creates > 1000 tables in series, with ext4 filesystem on Ubuntu 12.04. Results are shown below. Original MariaDB 5.5 trunk: 64.9 seconds (5 times avg.) Patched MariaDB 5.5 trunk: 36.0 seconds (5 times avg.) Any comments or suggestions? Thanks, Toshikuni Fukaya

3 8

[Maria-developers] Igor please review: EXPLAIN for DELETE
by Sergei Petrunia 07 Feb '13

07 Feb '13

Hello Igor, Hello Igor, Could you please review the following two patches: http://lists.askmonty.org/pipermail/commits/2012-December/003944.html http://lists.askmonty.org/pipermail/commits/2012-December/003945.html I think they implement the ideas about MDEV-3798 we've discussed earlier. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] mariadb-server not installable on Debian Testing AMD64
by Thomas Hackert 05 Feb '13

05 Feb '13

SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `<' and `>'). SEND-PR: From: thackert(a)nexgo.de To: maria-developers(a)lists.launchpad.net Subject: mariadb-server not installable on Debian Testing AMD64 >Description: Hello @ll, this is my first try to report a bug with mysqlbug. If I missed something and/or have something wrong, feel free to ask, but do not condemn me ... ;) I have tried to install mariadb-server on Debian Testing AMD64. I got the error message <quote> <snip> [ ok ] Stopping MariaDB database server: mysqld. 130130 17:28:50 [Note] Plugin 'InnoDB' is disabled. 130130 17:28:50 [Note] Plugin 'FEEDBACK' is disabled. [....] Starting MariaDB database server: mysqld . . . . . . . . . . . . . . . . . . . . FAIL . . . . . . . failed! invoke-rc.d: initscript mysql, action "start" failed. dpkg: Fehler beim Bearbeiten von mariadb-server-10.0 (--configure): Unterprozess installiertes post-installation-Skript gab den Fehlerwert 1 zurück dpkg: Abhängigkeitsprobleme verhindern Konfiguration von mariadb-server: mariadb-server hängt ab von mariadb-server-10.0; aber: Paket mariadb-server-10.0 ist noch nicht konfiguriert. dpkg: Fehler beim Bearbeiten von mariadb-server (--configure): Abhängigkeitsprobleme - verbleibt unkonfiguriert Trigger für menu werden verarbeitet ... Fehler traten auf beim Bearbeiten von: mariadb-server-10.0 mariadb-server </quote> ... :( I get the same message, if I try to install mysql-server from Debian's repository ... :( >How-To-Repeat: 1. Add " # MariaDB 10.0 repository list - created 2013-01-30 16:20 UTC # http://mariadb.org/mariadb/repositories/deb # http://mirror.netcologne.de/mariadb//repo/10.0/debian wheezy main deb-src http://mirror.netcologne.de/mariadb//repo/10.0/debian wheezy main" to your /etc/apt/sources.list. 2. Run "apt-get update" 3. Run "apt-get install mariadb-server" The error message appears nearly at the end of the installation ... :( >Fix: I do not know, sorry ... :( But maybe it is possible to check first, if mysql- or mariadb-server was installed before, and if it was, stop it. If it was not installed before, than it should not try to stop the server ... ;) >Submitter-Id: <submitter ID> >Originator: Thomas Hackert >Organization: <none> >MySQL support: [none | licence | email support | extended email support >] >Synopsis: mariadb-server not installable on Debian Testing AMD64 >Severity: serious >Priority: medium >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one >line)> >Release: mysql-10.0.0-mariadb1~wheezy (mariadb.org binary >distribution) >C compiler: gcc-4.7.real (Debian 4.7.1-2) 4.7.1 >C++ compiler: g++-4.7.real (Debian 4.7.1-2) 4.7.1 >Environment: <machine, os, target, libraries (multiple lines)> System: Linux neo 3.2.0-4-amd64 #1 SMP Debian 3.2.35-2 x86_64 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. COLLECT_GCC=/usr/bin/gcc COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/4.7/lto-wrapper Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.7.2-5' --with-bugurl=file:///usr/share/doc/gcc-4.7/README.Bugs --enable-languages=c,c++,go,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-4.7 --enable-shared --enable-linker-build-id --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --with-gxx-include-dir=/usr/include/c++/4.7 --libdir=/usr/lib --enable-nls --with-sysroot=/ --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --enable-gnu-unique-object --enable-plugin --enable-objc-gc --with-arch-32=i586 --with-tune=generic --enanable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --enable-gnu-unique-object --enable-plugin --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.7.2 (Debian 4.7.2-5) Compilation info (call): CC='/usr/bin/gcc' CFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1' CXX='/usr/bin/g++' CXXFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -fno-exceptions -fno-rtti -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='/usr/bin/gcc' CFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1' CXX='/usr/bin/g++' CXXFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -fno-exceptions -fno-rtti -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1' LDFLAGS='' ASFLAGS='' Perl: This is perl 5, version 14, subversion 2 (v5.14.2) built for x86_64-linux-gnu-thread-multi -- The way to make a small fortune in the commodities market is to start with a large fortune.

3 7

Re: [Maria-developers] Fwd: [Commits] Rev 3622: Analysis: in file:///home/tsk/mprog/src/5.3-md641/
by Igor Babaev 31 Jan '13

31 Jan '13

On 01/31/2013 04:41 AM, Timour Katchaounov wrote: > Igor, > > Let me know if I can push the patch below. It fixes the bug > according to our last discussion. If the number of group_list > elements was known, I could add a test already in the beginning > of the function, however such a counter doesn't exist, and I > didn't want to complicate the code just for this special case. > That's why the test is done where it is now. > > Timour > > > ------------------------------------------------------------ > revno: 3622 > revision-id: timour(a)askmonty.org-20130131120040-oan9zei8ojyco178 > parent: sergii(a)pisem.net-20130128081223-mp9rsd3t9soz8lly > fixes bug(s): https://mariadb.atlassian.net/browse/MDEV-641 > committer: timour(a)askmonty.org > branch nick: 5.3-md641 > timestamp: Thu 2013-01-31 14:00:40 +0200 > message: > Analysis: > Range analysis discoveres that the query can be executed via loose > index scan for GROUP BY. > Later, GROUP BY analysis fails to confirm that the GROUP operation can > be computed via an > index because there is no logic to handle duplicate field references > in the GROUP clause. > As a result the optimizer the optimizer produces an inconsistent plan. > It constructs a > temporary table, but on the other hand the group fields are not set to > point there. > > Solution: > Make loose scan analysis work in sync with order by analysis. In the > case of duplicate > columns loose scan will not be applicable. This limitation will be > lifted in 10.0 by > removing duplicate columns. > >

1 0

Re: [Maria-developers] what pros/cons of storing binary log in an InnoDB table?
by Kristian Nielsen 31 Jan '13

31 Jan '13

Zardosht Kasheff <zardosht(a)gmail.com> writes: > Instead of having the binary log be stored as a log file, a table with > a transactional engine (like InnoDB) is used. I have NOT done any Yes, this is a tempting idea, is it not? Drizzle even implemented this. But there are some subtle problems, and I think there are better solutions. One problem you will find if you try implementing this is - how are you going to keep the concept of binlog order? A relational table is unordered. You can use an increasing uint64 as the primary key, but then how are you going to ensure that the order in the table is the same as the commit order in InnoDB (InnoDB redo log)? If you take a lock from allocating the sequence number to doing the commit, then you have introduced a serialisation bottleneck and killed group commit. If you accept different order (as Drizzle does), then when you take an XtraBackup, it may not be consistent with the binlog. You will be writing your data *6* times: To the real table, when flushing its buffer pool pages. To the double-write buffer while flushing, and to the redo log before flushing. Then to the binlog table, and before that to the redo log and the doublewrite buffer. I could never reconcile myself with this. So it is tempting, but re-implementing binlog is a huge change (also in terms of end-user visibility), and my personal opinion is that for such huge a change we need to go to the best solution, not second-best. > investigation into whether this is possible, but I'm wondering what > people abstractly think of this idea. > My major motivation is to find a way to have users not be forced to > fsync on every transaction commit on slaves. With crash safety of My preference is MWL#164: http://askmonty.org/worklog/Server-RawIdeaBin/?tid=164 This reduces to one fsync() per group commit. But as you point out, this is still horrendously expensive for a single-threaded slave :-( Abstractly, the proper solution is to store the binlog and the InnoDB redo log (and all other transactional-type logs) in the *same* log. The upper layer would provide a general transactional log facility, and binlog, InnoDB, partitioning, etc. would use this instead of their own logs. But it is hard to see how we could arrive there from current code ... For MariaDB global transaction ID, I support crash-safe without binlog enabled, so there turning off fsync() will be possible. But this does not help if a slave also needs to act as a master. Parallel replication can help a lot, by allowing group commit, amortising the cost of the fsync(). But it would still be nice to solve the orginal problem. As you say, for a slave it is not a problem to loose transactions in a crash as they can be just replicated again. The problem is to be consistent between what is in the InnoDB tables and what is in the binlog. InnoDB already records in a crash-safe way the binlog position of the last commit. So if we can ensure that InnoDB is never ahead of the binlog, we can just cut the binlog after a crash if it has more events than what InnoDB recovered. We could do this by doing binlog writes as async I/O O_DIRECT writes, and sending a signal somehow to InnoDB so it would buffer its own redo log until binlog is safe on disk. Then no fsync() would be needed. But this is so far just a loose idea, it needs more thought to be sure it can work... Anyway, this is my current long-term plan: if we cannot have a proper single database-wide transaction log, then instead coordinate writes between the multiple logs that exist. Delay writes to logs so that they are never ahead of the master log. And at crash, either recover missing pieces of logs from the master log (MWL#164), or cut logs to be consistent. Then we can be consistent without any fsync() delays. > - As I understand it, for two phase commit, storage engines are > currently expected to fsync once after a transaction is prepared and > once after a transaction is committed. If the binary log is an InnoDB > table, then two-phase commit is unnecessary and the number of fsyncs > on the system is cut in half. In MariaDB 10 and MySQL 5.6, the fsync() at commit is no longer needed (InnoDB does not have it). [Note that IIRC, MySQL 5.6 breaks the storage engine API, you will need to implement the skip-fsync-at-commit-stage, or you will loose group commit.] > - With crash safe slaves on MySQL 5.6, one theoretically does not > need to enable fsyncs on transaction commit on slaves. If some data > gets lost after a crash, then the data may be replayed from the > master. However, to get GTIDs working, one must enable the binary log > on slaves, which requires two-phase commit, which require fsyncs. By Yes, requiring binlog on slaves is a serious mistake :-( Ok, as you can perhaps tell, this is something I have thought a lot about over the past years, so I have a lot of ideas but it is a bit difficult to put it all in a single mail reply without it becomming mostly a jumble of loose ideas. You are welcome to ask for more details on any specific points of interest. - Kristian.

5 9

Re: [Maria-developers] reducing fsyncs during handlerton->prepare and handlerton->commit in 5.6
by Kristian Nielsen 31 Jan '13

31 Jan '13

Zardosht Kasheff <zardosht(a)gmail.com> writes: > - in MySQL 5.6 and MariaDB 10.0, what are the fsyncing requirements > for storage engines during prepare and commit? > - are there new APIs that the storage engine must comply with in > order to get these benefits? In MariaDB 10.0, there is a new commit_checkpoint_request handlerton method. If the storage engine implements that, it need not do the fsync() at commit. It must also implement the commit_ordered method from 5.3. Check comments in sql/handler.h for these two methods, and let me know if there are any questions. The intention is that storage engines need not do much extra work for this functionality, eg. the patch for InnoDB support was a couple hundred lines at most IIRC. > - in MySQL 5.6, I see new handlerton methods commit_low and > prepare_low. What do these APIs do? What is their contract? I think MySQL developers should answer for this. But it is related to this new check in InnoDB: thd_requested_durability(trx->mysql_thd) == HA_IGNORE_DURABILITY And the existing flush_logs handlerton method is also used. My guess is that commit_low is just internal server stuff that the storage engine does not need to consider. The MySQL 5.6 mechanism is mostly the same as the MariaDB 10.0 one in what it actually does, but they did not bother to try to be compatible with the storage engine API in MariaDB 5.3. So looks like it is #ifdef time for you if you want to support both. - Kristian.

1 0

[Maria-developers] how to build mariadb RPMs
by Rich Prohaska 30 Jan '13

30 Jan '13

How do you build the mariadb RPMs? Are you using rpmbuild or cpack? Is there a top level script that uses rpmbuild or cpack that I should use? Thanks Rich Prohaska

3 11

[Maria-developers] MariaDB 5.1.67, 5.2.14, 5.3.12, 5.5.29 Release Prep
by Daniel Bartholomew 29 Jan '13

29 Jan '13

All, I've prepped changelog and release notes pages for MariaDB 5.1.67, 5.2.14, and 5.3.12. I'll shortly be doing the same for MariaDB 5.5.29. https://kb.askmonty.org/en/mariadb-5167-release-notes/ https://kb.askmonty.org/en/mariadb-5167-changelog/ https://kb.askmonty.org/en/mariadb-5214-release-notes/ https://kb.askmonty.org/en/mariadb-5214-changelog/ https://kb.askmonty.org/en/mariadb-5312-release-notes/ https://kb.askmonty.org/en/mariadb-5312-changelog/ If there is anything I should be sure to mention in the release notes, please let me know ASAP. I plan on announcing the releases once 5.5.29 has been uploaded to the mirrors. It's still building at the moment so the earliest the release could happen is tomorrow and it could be pushed back to Thursday if there's a delay of some sort. 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 3653: MDEV-3882 .deb upgrade from mysql to mariadb fails due to apt-get considering it a downgrade. in file:///home/hf/wmar/mdev-3882/
by Kristian Nielsen 29 Jan '13

29 Jan '13

<holyfoot(a)askmonty.org> writes: > message: > MDEV-3882 .deb upgrade from mysql to mariadb fails due to apt-get considering it a downgrade. > The libmysqlclient18 package and all the dependencies on it removed. > The libmariadbclient18 set to provide the libmysqlclient's functions and > conflicts with all the libmysqlclients. > > === modified file 'debian/dist/Debian/control' > --- a/debian/dist/Debian/control 2012-08-23 13:32:03 +0000 > +++ b/debian/dist/Debian/control 2013-01-29 09:56:54 +0000 > @@ -13,7 +13,9 @@ Vcs-Bzr: bzr://lp:maria > Package: libmariadbclient18 > Section: libs > Architecture: any > -Depends: mariadb-common, libmysqlclient18 (= ${source:Version}), ${shlibs:Depends}, ${misc:Depends} > +Depends: mariadb-common, ${shlibs:Depends}, ${misc:Depends} > +Provides: libmysqlclient18 > +Conflicts: libmysqlclient10, libmysqlclient12, libmysqlclient14, libmysqlclient15, libmysqlclient16, libmysqlclient18 Are you sure this works? The original reason for introducing a libmysqlclient18 package in the first place (with all the problems in introduces) is that there are standard packages with a versioned dependency on libmysqlclient18. And versioned dependencies can not (used not to?) be satisfied by a Provides: Eg. the following gives 143 packages with versioned dependencies on libmysqlclient18: apt-cache rdepends libmysqlclient18|(read x; read x; while read x; do apt-cache show $x|grep ^Depends: | perl -nle 'print "'"$x:"' $1" if /libmysqlclient18[ \t]*$([^)]+)$/' ; done) You need to test a number of these that they are still installable when MariaDB is installed, also on old .deb distros (ie. Ubuntu 8.04). On the other hand, if this works then it is a nice solution for sure, and could also solve other issues. A related issue is that we might get the distro mysql-common package or the MariaDB mysql-common package somewhat at random, depending on which has the newer version number on any given day. I believe we made it so we work with the distro mysql-common package as well as the MariaDB one, it it will not necessarily cause any problems. It does seem a bit of a mess though, maybe we should just drop the mysql-common package from our repos? - Kristian.

1 0

Re: [Maria-developers] Fwd: [Commits] Rev 3616: Fix for bug in file:///home/tsk/mprog/src/5.3-md641/
by Igor Babaev 28 Jan '13

28 Jan '13

Timour, It's not clear for me: 1. why group by queries with GROUP BY col1, col2, where col1,col2 are defined over the same table, are considered as possibly good for for loose scan optimization. 2. how it comes that unique index is considered as a candidate for a loose scan (see your test case). (The second issue, of course, are unrelated to bug mdev-641). Besides, didn't you notice that you removes duplicates in group by under any circumstances. How it will affect the following query: select a, sum(c) from t1 group by a, a with rollup; ? In general, removing duplicates from GROUP BY and ORDER BY lists is a good optimization, but why it should be done in connection with this bug? Regards, Igor. On 01/24/2013 06:38 AM, Timour Katchaounov wrote: > Igor, > > Could you please review the following patch (we discussed it at the team > call): > > ------------------------------------------------------------ > revno: 3616 > revision-id: timour(a)askmonty.org-20130124143526-bpaiyushble1ieek > parent: timour(a)askmonty.org-20130117140805-4kyoq7azx4v2irhq > fixes bug: https://mariadb.atlassian.net/browse/MDEV-614 > committer: timour(a)askmonty.org > branch nick: 5.3-md641 > timestamp: Thu 2013-01-24 16:35:26 +0200 > message: > Fix for bug > MDEV-641 LP:1002108 - Wrong result (or crash) from a query with > duplicated field in the group list and a limit clause > Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' > WITH GROUP BY ON DUPLICATED FIELDS > > Analysis > Range analysis discoveres that the query can be executed via loose > index scan for GROUP BY. > Later, GROUP BY analysis fails to confirm that the GROUP operation can > be computed via an > index because there is no logic to handle duplicate field references > in the GROUP clause. > As a result the optimizer the optimizer produces an inconsistent plan. > It constructs a > temporary table, but on the other hand the group fields are not set to > point there. > > Solution: > Make order by analysis be in sync with loose scan analysis for group > by. Instead of > changing the logic of how indexes are analyzed for order by, the patch > takes the same > approach as MySQL - normalize the query by removing duplicate column > references. The > difference from MySQL is that we remove duplicates earlier, by reusing > remove_const() > which already removed unneeded ORDER elements. > >

1 0