- developers - lists.mariadb.org

Re: [Maria-developers] MariaDB 10.1 is now on github!
by Sergei Golubchik 26 May '14

26 May '14

Hi, Binod! First: please send questions like this to maria-developers(a)lists.launchpad.net it's a public mailing list dedicated to MySQL and MariaDB internals, source code, and related things. I am subscribed, so I'll see you mail there, and you may be sure I will, because it won't be accidentally catched by my spam filter, or sorted out in some obscure folder. Furthermore other subscribers will see your question and could reply if I will be not available (e.g. I could be travelling). Thank you. On May 26, Binod Bista wrote: > Hi Sergei, > > Thanks for the info. Just to interrupt, is there a page you recommend for > anyone starting out in MariaDB development? Particularly any database or > MySQL internals material that help understand the codebase. That depends on what you're trying to do. There are two books: http://shop.oreilly.com/product/9780596009571.do http://www.packtpub.com/mysql-51-plugin-development/book There's MySQL internals manual: http://dev.mysql.com/doc/internals/en/index.html There's MariaDB Knowledge Base that includes pages about MariaDB internals: http://kb.askmonty.org/ There're numerous blog posts and presentations about specific topics. Regards, Sergei

1 0

[Maria-developers] Getting started with the coding.
by sriram patil 23 May '14

23 May '14

Hi Alexander, I have started working on "CREATE OR REPLACE DATABASE" implementation. I thought this one has the least complexity among all the other commands. I am getting my hand-on debugging the source code too. I have made the required changes in sql_yacc.yy. The command is parsed properly. Handled the error where CREATE OR REPLACE and IF NOT EXISTS cannot occur at the same time. This is how I am thinking of proceeding with the implementation, In method mysql_create_db after checking the flag HA_LEX_CREATE_REPLACE. I am removing the database by calling mysql_rm_db and then the current implementation will take care of creating the new db with the same name. This seems correct? Or is there anything I need to consider? Thanks, Sriram

2 1

[Maria-developers] delete + offset
by Roberto Spadim 21 May '14

21 May '14

hi guys! i was thinking at maria-discuss, and i have a new "feature request" to mariadb DELETE statment, that's the OFFSET clausule, like sqlite my idea is... i have a table with history about for example temperature, and i will only record last 5000 records, my idea is execute a 'cleanup' for example every day instead of: select date_time from history where channel=1234 order by date_time desc limit 1 offset 5000 delete from history where channel=1234 and data_time<value from previous query i was considering something like delete from history where channel=1234 order by date_time desc limit 99999999999 offset 5000 what you think about this? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

1 0

[Maria-developers] Regarding debugging MariaDB
by sriram patil 17 May '14

17 May '14

Hi Alexander, I wanted to run mysqld in debug mode to check the trace file and get to know workflow of SQL commands. I tried following command mysqld --user=<username> --debug Then, after connecting I executed some commands. But, the trace file is not created in /tmp folder. Is there anything I need to do while building the source code in order for the debug mode to work? Thanks, Sriram

2 1

[Maria-developers] Merging FusionIO changes to 10.0.12
by Colin Charles 16 May '14

16 May '14

Hi all, MariaDB has worked actively with Fusion-io, and we did release some improvements in a preview release a little over a month ago: https://blog.mariadb.org/significant-performance-boost-with-new-mariadb-pag… This was originally planned to be included into 10.0.10, but when we released the GA, we thought that the feature was not tested thoroughly. Now the code has proven stable we are thinking of merging this into 10.0.12. Yes, we know its already a GA, but this feature will be turned off by default, and the code is safely encapsulated. If you are not using Fusion-io or do not turn it on, there is no effect to you. To check, the changes are in the XtraDB and InnoDB that we ship (storage/xtradb & storage/innobase). Naturally, for those using Fusion-io devices, the benefits are clear - you will see significant improvements in speed and you will increase the lifetime of your cards. Think of this like adding drivers to the Linux kernel from time to time ;) What does everyone think? Package maintainers? Thanks! -- Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles

3 2

[Maria-developers] LIMIT ROWS MATCHED
by Colin Charles 16 May '14

16 May '14

Hi! http://seonguck.blogspot.kr/2014/05/limit-rows-matched.html We have a potential contribution here for a LIMIT ROWS MATCHED feature - might be interesting for us to consider this for 10.1 ? cheers, -colin -- Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles

2 1

[Maria-developers] tokudb database - generated "should never happen" status when mysql log partition ENOSP
by Daniel Black 15 May '14

15 May '14

1 0

[Maria-developers] New to the list & question about CONNECT
by Martijn Tonies (Upscene Productions) 13 May '14

13 May '14

Hello everyone, My name is Martijn Tonies and I’m a Windows tools developer, we have a tool called “Database Workbench”, a developer tool for several database engines. For it’s next major version, we’re planning to officially support MariaDB in the MySQL module. I’ve investigated several of the features, implemented support for Roles, Virtual Columns etc, but I’m having trouble with the CONNECT engine. Here’s what I did, using MariaDB 10.0.10 1) registered a server with CREATE SERVER: CREATE SERVER mysql55_at_local FOREIGN DATA WRAPPER mysql OPTIONS(HOST 'MT-XP-VM-MYSQL', PORT 3355, DATABASE 'test', USER 'root', PASSWORD ‘secret’); 2) created a table in the MySQL database: CREATE TABLE sfdata ( mark1 Integer(11), mark2 Integer(11), mark3 Integer(11), mark4 Integer(11), `name` VarChar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; 3) created a table in the MariaDB database: CREATE TABLE `sfdata_proxy3` ( `mark1` int(11) DEFAULT NULL, `mark2` int(11) DEFAULT NULL, `mark3` int(11) DEFAULT NULL, `mark4` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='mysql55_at_local/sfdata' I then expected that table “sfdata_proxy3” has the rows as “sfdata” has, but I got nothing returned. Any clue where to look? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird!

1 1

Re: [Maria-developers] Question about GTIDs in MariaBD
by Kristian Nielsen 12 May '14

12 May '14

"AL13N" <alien(a)rmail.be> writes: >> Peter Laursen <peter_laursen(a)webyog.com> writes: >> So yes, there are others that consider Oracle's design of GTID 'a dirty >> hack', >> but MariaDB 10.0 GTID is not affected by that design. > What about mariadb being a slave of a Oracle MySQL GTID? The restrictions are on the master, not on the slaves. So what do you mean? Surely, connecting a MariaDB slave to an Oracle MySQL master will not remove those restrictions. Note that when a MariaDB server connects as a slave to an Oracle MySQL master server, it is not possible to use GTID for that connection (because the GTID implementations are not compatible). Old style non-GTID replication must be used. - Kristian.

2 1

Re: [Maria-developers] Question about GTIDs in MariaBD
by Peter Laursen 12 May '14

12 May '14

With autocommit=1 every statement runs in its own transaction, so obviousluy .. yes! MyISAM is/used to be completely 'transaction-agnosstic'. But GTIDs seem to confict with non-transactional storage engines (as far as I can understand). MySQL/Oracle have "solved" (!) this by not allowing updates to tables using transactional engines and non-transactional storage engines in same transaction. However to me it is nonsense that transactions apply (have any effect at all) for non-transactional storage engines. As a consequence I consider this solution (what must have been enforced in the server layer and not the storeage engine layer) by Oracle 'a dirty hack' (unless somewone can explain me why it it necessary). -- Peter On Sun, May 11, 2014 at 4:42 PM, Roberto Spadim <roberto(a)spadim.com.br>wrote: > With autoclmmit=1 works? > > Em domingo, 11 de maio de 2014, Peter Laursen <peter_laursen(a)webyog.com> > escreveu: > > Thanks for the reply, but I am afraid I don't fully understand. >> >> Here is a simple test case with mySQL 5.6.17: >> >> 1) >> You need all those in configuration [mysqld) section >> >> log-bin >> log-slave-updates >> gtid_mode = ON >> enforce_gtid_consistency = ON >> >> >> 2) >> Then this small script will reproduce: >> >> >> DROP DATABASE IF EXISTS `gtid_test`; >> CREATE DATABASE `gtid_test`; >> USE `gtid_test`; >> >> CREATE TABLE `tab1` ( >> `a` INT DEFAULT NULL >> ) ENGINE=MYISAM ; >> >> CREATE TABLE `tab2` ( >> `b` INT >> ) ENGINE=INNODB; >> >> --start a transaction >> SET autocommit=0; >> >> INSERT INTO `tab1` VALUES (1); >> INSERT INTO `tab2` VALUES (1); >> >> -- now the first UPDATE to the MyISAM table in the transaction triggers >> this error: >> >> UPDATE `tab1` SET `a` = 5 WHERE `a` = 1; >> >> -- and you'll get: >> -- >> -- Error Code: 1785 >> -- When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to >> non-transactional tables can only be done in either autocommitted >> statements or single-statement transactions, and never in the same >> statement as updates to transactional tables. >> >> >> 3) >> ShouldI understand that MariaDB dos not prevent this (does not raise an >> error), but it is still unsafe as binlog - and thus replication - may be >> corrupted? >> >> >> Thanks! >> >> -- Peter >> >> On Sat, May 10, 2014 at 6:09 PM, Pavel Ivanov <pivanof(a)google.com> wrote: >> >>> There is no such limitation in MariaDB as well as no limitation on >>> CREATE TABLE ... SELECT. >>> >>> Although beware that mixing statements changing MyISAM and InnoDB >>> tables in statement binlog mode you may get different results from the >>> statements on slaves. >>> >>> Pavel >>> >>> On Sat, May 10, 2014 at 3:48 AM, Peter Laursen <peter_laursen(a)webyog.com> >>> wrote: >>> > GTID implementation in MySQL 5.6 has the limitation as described here: >>> > >>> https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html >>> > >>> > >>> > "nontransactional storage engines such as MyISAM cannot be made in the >>> same >>> > statement or transaction as updates to tables using transactional >>> storage >>> > engines such as InnoDB." >>> > >>> > If you try you will get the error: >>> > Error Code: 1785 >>> > When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to >>> non-transactional >>> > tables can only be done in either autocommitted statements or >>> > single-statement transactions, and never in the same statement as >>> updates to >>> > transactional tables. >>> > >>> > Is there same or similar/other limitations with GTID in MariaDB 10? >>> > >>> > -- Peter Laursen >>> > -- Webyog >>> > >>> > _______________________________________________ >>> > Mailing list: https://launchpad.net/~maria-developers >>> > Post to : maria-developers(a)lists.launchpad.net >>> > Unsubscribe : https://launchpad.net/~maria-developers >>> > More help : https://help.launchpad.net/ListHelp >>> > >>> >> >> > > -- > Roberto Spadim > SPAEmpresarial > Eng. Automação e Controle > >

3 3

[Maria-developers] Question about GTIDs in MariaBD
by Peter Laursen 11 May '14

11 May '14

GTID implementation in MySQL 5.6 has the limitation as described here: https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html "nontransactional storage engines such as MyISAM<https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html> cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB<https://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html> ." If you try you will get the error: Error Code: 1785 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. Is there same or similar/other limitations with GTID in MariaDB 10? -- Peter Laursen -- Webyog

2 3

[Maria-developers] MariaDB 10.0.11 release prep
by Daniel Bartholomew 10 May '14

10 May '14

I've started the release prep for MariaDB 10.0.11. Draft release notes and changelog pages are here: - https://mariadb.com/kb/en/mariadb-10011-release-notes/ - https://mariadb.com/kb/en/mariadb-10011-changelog/ Please make necessary additions/modifications/etc... The release is still building at the moment, but when finished I'll upload to the mirrors later today or tomorrow. Current plan, if everything goes well, is to activate and announce the release on Monday, 12 May. Thanks! -- Daniel Bartholomew, MariaDB Release Manager MariaDB | http://mariadb.com

1 0

[Maria-developers] 10.0 -> 10.1 merge
by Sergey Petrunia 08 May '14

08 May '14

Hello Igor, As discussed, I've done the 10.0 -> 10.1 merge. I didn't get any warnings or new test failures in the 'main' test suite, so I've pushed the merge into 10.1 tree. BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] connect.alter fails
by Alexander Barkov 07 May '14

07 May '14

Hello Olivier, connect.alter fails when built without XML support complaining about unsupported table type XML. I guess it should be skipped. connect.alter also fails with libxml2 with these symptoms: --- /home/bar/maria-bzr/maria-10.0-connect/storage/connect/mysql-test/connect/r/alter.result 2014-03-23 11:32:34.519624000 +0400 +++ /home/bar/maria-bzr/maria-10.0-connect/storage/connect/mysql-test/connect/r/alter.reject 2014-03-23 11:48:43.251680097 +0400 @@ -1,3 +1,5 @@ +Warnings: +Warning 1105 No file name. Table will use t1.xml Please fix. A remainder: please check every push in BuildBot: http://buildbot.askmonty.org/buildbot/grid?branch=10.0-connect

3 4

[Maria-developers] Barkov: report for the week starting Apr 29, 2014
by Alexander Barkov 05 May '14

05 May '14

Apr 28 - May 04 =============== Apr 28 Apr 29 Apr 30 May 01 Holiday May 02 Holiday May 03 Weekend May 04 Weekend Vacation days taken: 10/35 - MDEV-5702 Incorrect results are returned with NULLIF() Pushed into 5.5 - MDEV-5459 Illegal mix of collations for datetime Pushed into 5.5 - MDEV-6146 Can't mix (latin1_swedish_ci,NUMERIC) and (utf8_unicode_ci,IMPLICIT An after-fix pushed (the original patch broke "mtr --ps fulltext" - Merging gb18030 from MySQL-5.7 In progress - Discussion the manual changes with Ian

1 0

[Maria-developers] sources won't compile
by AugustQ 03 May '14

03 May '14

Hi, I encountered a problem by compiling the sources of MariaDB 10.04. Some days ago I upgraded my system from Ubuntu 13.10 to 14.04 and now I encounter these problems: gcc used is this: gcc version 4.8.2 (Ubuntu 4.8.2-19ubuntu1) a minor glitch: cmake complains: Syntax Warning in cmake code at /home/august/workspace/MariaDB/mariadb-10.0.4/contrib_AQ_01/cmake/ssl.cmake:210:55 Argument not separated from preceding token by whitespace. This is the reason for this: ELSE() MESSAGE(SEND_ERROR "Wrong option for WITH_SSL. Valid values are : "${WITH_SSL_DOC}) ENDIF() I added a space between "$(WITH and this works now. But in compiling the sources this error was reported: Scanning dependencies of target innobase [ 45%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/row/row0log.cc.o /home/august/workspace/MariaDB/mariadb-10.0.4/contrib_AQ_01/storage/innobase/row/row0log.cc: In function ‘dberr_t row_log_table_apply_ops(que_thr_t*, row_merge_dup_t*)’: /home/august/workspace/MariaDB/mariadb-10.0.4/contrib_AQ_01/storage/innobase/row/row0log.cc:2201:11: error: ‘srv_buf_size’ was not declared in this scope ofs, srv_buf_size); ^ By looking into the code of row0log.cc I found that FALLOC_FL_PUNCH_HOLE seems to be defined now. This is defined in /usr/include/linux/falloc.h. So what happened here during the upgrade of Linux? Regards AugustQ PS: by looking into Ubuntu 12.04 I found that this file also existed here.

2 2

[Maria-developers] Analysing degraded performance at high concurrency in sysbench OLTP
by Kristian Nielsen 02 May '14

02 May '14

At the Barcelona meeting in January, I promised to take a look at the high-concurrency sysbench OLTP benchmarks, and now I finally had the time do do this. There was a lot of work on LOCK_open by Svoj and Serg. If I have understood correctly, the basic problem was that at high concurrency (like, 512 threads), the TPS is only a small fraction of the peak throughput at lower concurrency. Basically, the server "falls over" and starts trashing instead of doing real work, due to some kind of inter-processor communication overhead. I started from Axel' OLTP sysbench runs and scripts, using 10.0 from bzr revno:4151 (revid:svoj@mariadb.org-20140415072957-yeir4jvokyilw5hp). I compiled without performance schema and with PGO, and ran sysbench 0.5 OLTP. (I just realised that my runs are with 32 tables, while I think the benchmarks in January focused on single-table runs. Maybe I need to re-do my analysis with the single-table benchmark, or perhaps it is too artificial to matter much?). In the read-only sysbench, the server mostly does not fall over. I guess this is due to the work by Svoj on eliminating LOCK_open? But in read-write, performance drops dramatically at high concurrency. TPS drops to 2600 at 512 threads compared to a peak of around 13000 (numbers here are approximate only, they vary somewhat between different runs). So I analysed the r/w benchmark with the linux `perf` tool. It turns out two-thirds of the time is spent in a single kernel function _raw_spin_lock(): - 66.26% mysqld [kernel.kallsyms] [k] _raw_spin_lock Digging further using --call-graph, this turns out to be mostly futex waits (and futex wakeups) from inside InnoDB locking primitives. Calls like sync_array_get_and_reserve_cell() and sync_array_wait_event() stand out in particular. So this is related to the non-scalable implementation in InnoDB of locking primitives, which is a known problem. I think Mark Callaghan has written about it a couple of times. Last time I looked at the code, every single mutex wait has to take a global mutex protecting some global arrays and stuff. I even remember seeing code that at mutex release would pthread_signal_broadcast() _every_ waiter, all of them waking up, only to all (except one) go do another wait. This is a kiler for scalability. While investigating, I discovered the variable innodb_sync_array_size, which I did not know about. It seems to split the mutex for some of the synchronisation operations. So I tried to re-run the benchmark with innodb_sync_array_size set to 8 and 64. In both cases, I got significant improvement, TPS increase to 5900, twice the value with innodb_sync_array_size set to the default of 1. So it is clear that the main limitation in this benchmark was the non-scalable InnoDB synchronisation implementation. After tuning innodb_sync_array_size, time spent in _raw_spin_lock() is down to half what it was before (33% of total time): + 33.77% mysqld [kernel.kallsyms] [k] _raw_spin_lock Now investigating call-graphs show that the sync_array operations are much less visible. Instead mutex_create_func(), called from dict_mem_table_create(), is the one that turns up prominently in the profile. I am not familiar with what this part of the InnoDB code is doing, but what I saw from a quick look is that it creates a mutex - and there is another global mutex needed for this, which again limits scalability. It is a bit surprising to see mutex creation being the most significant bottleneck in the benchmark. I would have assumed that most mutexes could be created up-front and re-used? It is possible that this is a warm-up thing, maybe the code is filling up the buffer pool or some table-cache like thing inside InnoDB? Because I see TPS being rather low for the first 150 seconds of the run (around 3000), and then increasing suddenly to around 8000-9000 for the rest. This might be worth investigating further. So in summary, my investigations found that the bottleneck in this benchmark, and the likely cause of the fall-over, is a scalability problem with InnoDB locking primitives. The sync_array part seems to be mitigated to some degree by innodb_sync_array_size, the mutex creation part still needs to be investigated. I wonder if the InnoDB team @ Oracle is doing something for this in 5.7? Does anyone know? I vaguely recall reading something about it, but I am not sure. It would seem a waste to duplicate their efforts. In any case, I hope this was useful. As part of this investigation, I installed a new 3.14 kernel on the lizard2 machine and a new `perf` installation, which seems to work well to do more detailed investigations of these kind of issues. So let me know if there are other benchmarks that I should look into. One thing that could be interesting is to look for false sharing; there are some performance counters that Intel manuals describe can be used for this. As an aside: In my tests, once concurrency becomes high enough that the server falls over, the actual TPS number becomes mostly meaningless. Eg. I saw putting dummy pause loops into the code increasing TPS. If TPS stabilises at N% of peak throughput as concurrency goes to infinity, then we can compare N. But if N goes to zero as concurrency goes to infinite, I think it is meaningless to compare actual TPS numbers - we should instead focus on removing the fall-over behaviour. (Maybe this is already obvious to you, I have not followed the previous benchmark efforts that closely). Hope this helps, - Kristian.

4 4

Re: [Maria-developers] MDEV-5792 - Deadlock between SELECTs from METADATA_LOCK_INFO and another
by Sergei Golubchik 01 May '14

01 May '14

Hi, Sergey! On Apr 23, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 4155 > revision-id: svoj(a)mariadb.org-20140423073406-o20n7jvwfjinvxxr > parent: psergey(a)askmonty.org-20140418100754-v5b5sdqbavbcznh7 > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0-mdev5792 > timestamp: Wed 2014-04-23 11:34:06 +0400 > message: > MDEV-5792 - Deadlock between SELECTs from METADATA_LOCK_INFO and another > I_S table > > mdl_iterate() helper function (which is used by the plugin to iterate mdl > locks) acquired mutexes in reverse order. > > Fixed by iterating MDL locks in two stages: > 1. Iterate locks hash under the protection of hash mutex, store all > lock pointers in a thread local array and increment reference counter > for the lock. > 2. Iterate local array without protection of hash mutex, handle destroyed > locks. > > It somewhat echoes hack in MDL_map_partition::move_from_hash_to_lock_mutex. Looks ok to me. Regards, Sergei

1 0

[Maria-developers] Next steps in improving single-threaded performance
by Kristian Nielsen 01 May '14

01 May '14

I have been analysing CPU bottlenecks in single-threaded sysbench read-only load. I found that icache misses is the main bottleneck, and that profile-guided compiler optimisation (PGO) with GCC gives a large speedup, 25% or more. (More details in my blog posts: http://kristiannielsen.livejournal.com/17676.html http://kristiannielsen.livejournal.com/18168.html ) Now I would like to ask for some discussions/help in how to get this implemented in practice. It involves changing the build process for our binaries: First compile with gcc --coverage, then run some profile workload, then recompile with -fprofile-use. I implemented a simple program to generate some profile load: https://github.com/knielsen/gen_profile_load It runs a bunch of simple insert/select/update/delete, with different combinations of storage engine, binlog format, and client API. It is designed to run inside the build tree and handle starting and stopping the server being tested, so it is pretty close to a working setup. These commands work to generate a binary that is faster due to PGO: mkdir bld cd bld cmake -DWITHOUT_PERFSCHEMA_STORAGE_ENGINE=1 -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCMAKE_C_FLAGS_RELWITHDEBINFO="-Wno-maybe-uninitialized -g -O3 --coverage" -DCMAKE_CXX_FLAGS_RELWITHDEBINFO="-Wno-maybe-uninitialized -g -O3 --coverage" .. make tests/gen_profile_load cmake -DWITHOUT_PERFSCHEMA_STORAGE_ENGINE=1 -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCMAKE_C_FLAGS_RELWITHDEBINFO="-Wno-maybe-uninitialized -g -O3 -fprofile-use -fprofile-correction" -DCMAKE_CXX_FLAGS_RELWITHDEBINFO="-Wno-maybe-uninitialized -g -O3 -fprofile-use -fprofile-correction" make So all the pieces really are there, it should be possible to implement it. But we need to find a good way to integrate it into our build system. The best would be to integrate it into our cmake files. The gen_profile_load.c could go into tests/, ideally we would build both a static and dynamically linked version (so we get PGO for both libmysqlclient.a and libmysqlclient.so). Anyone can help me get cmake to do that? And it would be cool if we could get the above procedure to work completely within cmake, so that the user could just do: cmake -DWITH_PGO ... ; make and cmake would itself handle first building with --coverage, then running gen_profile_load.static and gen_profile_load.dynamic, then rebuilding with -fprofile-use. Anyone know if this is possible with cmake, and if so could help implement it? But alternatively, we could integrate a double build, like the commands above, into the buildbot scripts (.deb, .rpm, bintar). Any comments? Here are some more points: - I tested that gen_profile_load gives a good speedup of sysbench read-only (around 30%, so still very significant even though it generates a different and more varied load). - As another test, I removed all SELECT from gen_profile_load, and ran the resulting PGO binary with sysbench read-only. This still gave a fair speedup, despite the PGO load being completely different from the benchmark load. This gives me confidence that the PGO should not cause performance regressions in cases not covered well by gen_profile_load - More tests would be nice, of course. Axel, would you be able to build some binaries following above procedure, and test some different random benchmarks? Anything that is easy to run could be interesting, both to test for improvement, and to check against regressions. - We probably need a recent GCC version to get good results. I used GCC version 4.7.2. Maybe we should install this GCC version in all the VMs we use to build binaries? - Should we do this in 5.5? I think we might want to. The speedup is quite significant, and it seems very safe - no code modifications are involved, only different compiler options. Any thoughts? Volunteeres for helping with the cmake or buildbot parts? - Kristian.

4 14

[Maria-developers] valgrind failure in main.statistics
by Sergey Petrunia 29 Apr '14

29 Apr '14

Hello Igor, Valgrind build in buildbot currently fails in main.statistics test: http://buildbot.askmonty.org/buildbot/builders/work-amd64-valgrind/builds/5… I have tracked it down: the failure appeared after this push by you: revno: 4157 [merge] committer: Igor Babaev <igor(a)askmonty.org> branch nick: maria-10.0 timestamp: Tue 2014-04-22 14:43:13 -0700 message: Merge. ------------------------------------------------------------ revno: 4152.1.1 committer: Igor Babaev <igor(a)askmonty.org> branch nick: maria-10.0-bugs timestamp: Tue 2014-04-22 14:39:57 -0700 message: Fixed the problem of mdev-5947. Back-ported from the mysql 5.6 code line the patch with the following comment: Fix for Bug#11757108 CHANGE IN EXECUTION PLAN FOR COUNT_DISTINCT_GROUP_ON_KEY CAUSES PEFORMANCE REGRESSION I suppose it would take you much less time than anybody else to fix this? The failure is not repeatable on every machine: it doesn't repeat on my machines, but it can be repeated on work.askmonty.org. BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] 10.1 planning
by Kristian Nielsen 29 Apr '14

29 Apr '14

Sergei Golubchik <serg(a)mariadb.org> writes: > See below. > What do you think about it? > > On Apr 23, Elena Stepanova wrote: >> >> There is also https://mariadb.atlassian.net/browse/MDEV-4487 >> (Replication from 5.6 with enabled GTID) to be considered. It's >> currently Minor and has 10.0.x as a target version, both of which are >> questionable: recently Colin said on IRC that it should be much more >> than a minor FR, and yet I doubt that somebody will get to it in 10.0. >> Maybe it's more realistic to raise the priority but to re-target it for >> 10.1? I think Monty had some ideas for doing this, IIRC it was discussed at the meeting in Barcelona in January. If what I remember is true, it is actually just that a 5.6 master refuses to replicate if the slave does not say it is configured for MySQL 5.6 GTID. The slave does not need to actually _use_ GTID. So I think Monty's plan was for the MariaDB slave to just lie to the master and say that it's a 5.6 slave with appropriate config. And the MariaDB slave would need to recognise and ignore the new MySQL 5.6 events, such as GTID. - Kristian.

2 2

[Maria-developers] Old autotools cruft in source tree?
by Otto Kekäläinen 28 Apr '14

28 Apr '14

Hello The Debian Lintian QA scanner yields this warning: W outdated-autotools-helper-file (http://lintian.debian.org/tags/outdated-autotools-helper-file.html) extra/jemalloc/config.guess 2012-02-10 extra/jemalloc/config.sub 2012-02-10 storage/tokudb/ft-index/third_party/xz-4.999.9beta/build-aux/config.guess 2009-04-27 storage/tokudb/ft-index/third_party/xz-4.999.9beta/build-aux/config.sub 2009-04-17 Are these still needed in MariaDB or is this simply old cruft from old autotools time that could be removed? -- Check out our blog at http://seravo.fi/blog and follow @ottokekalainen

2 1

[Maria-developers] "SELECT ... LIMIT 0" is now deterministic
by Alexander Barkov 28 Apr '14

28 Apr '14

Hello Ian, A fix for MDEV-6170 fixed another problem as a side effect: "SELECT ... LIMIT 0" is now deterministic for statement based binary log. Queries with "LIMIT 0" do not return ER_BINLOG_UNSAFE_STATEMENT any more. Can you please mention this in the manual? Some discussion is here: https://mariadb.atlassian.net/browse/MDEV-6170 Related articles in the MySQL manual: https://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html https://dev.mysql.com/doc/refman/5.6/en/replication-features-limit.html Thanks.

1 0

[Maria-developers] a Query_log_event charset bug in parallel replication
by nanyi607rao 25 Apr '14

25 Apr '14

hi, If character_set in different Query_log_events changed, worker threads may apply them with wrong character_set. the codes leading this problem is in Query_log_event::do_apply_event, that is: if (charset_inited) { if (rli->cached_charset_compare(charset)) { /* Verify that we support the charsets found in the event. */ if (!(thd->variables.character_set_client= get_charset(uint2korr(charset), MYF(MY_WME))) || !(thd->variables.collation_connection= get_charset(uint2korr(charset+2), MYF(MY_WME))) || !(thd->variables.collation_server= get_charset(uint2korr(charset+4), MYF(MY_WME)))) { There is a charset[6] in rli, which cached last Query_log_event's charset in serial replication. But in parallel replication it would lead mistake, because every worker thread can read and set rli->charset[6], so rli->charset[6] isn't any worker threads' last Query_log_event charset. but rli->charset[6] can affect every worker threads' thd->variables.character_set_* setting. 2014-04-17 nanyi607rao

2 7

[Maria-developers] About the query performance issue reported on IRC (Percona 5.5 vs MariaDB 10.0)
by Elena Stepanova 24 Apr '14

24 Apr '14

Hi all, Over the last couple of days, some of you have seen or even participated in a "thread" on #maria IRC channel regarding the performance problem on query execution that james_woods observed on MariaDB 10.0 instance comparing to a Percona 5.5 instance. This email is for those who are curious about the outcome/current status. Summary ------- The original complaint sounded like this: "After switching to MariaDB 10 I see 5-10 very long running queries - which are not using indices as they do on Percona 5.5." The data in both instances is exactly the same. The HW is essentially different, but it's better on the machine with MariaDB 10. After some back-and-forth, the complaint got split into three main parts: - the query is considerably slower on MariaDB 10.0 instance than on Percona 5.5 instance; - the query plans show very different row counts, although the data is identical; - Percona uses indices when MariaDB doesn't. Sergei Petrunia investigated the alleged issues with query plans and found that there was no problem: - the row counts that Percona 5.5 reported for derived tables were smaller because EXPLAIN in Percona 5.5 had actually executed the query. - the difference in index usage was an effect of table elimination. A table was removed, hence no index use for it was shown in the explain. He also broke down the query and got a simple flat one, without subqueries, which still revealed ~4x difference in execution time. Upon his request, I ran some tests locally to see if I could reproduce such a difference between Percona 5.5 and MariaDB 10.0 and found that I couldn't (of course I don't have the HW that james_woods uses, the but the difference this big was likely to be seen anywhere). Meanwhile, james_woods installed Percona 5.5 on the machine where MariaDB 10.0 was running, and didn't get any performance gain -- that is, the difference was not between Percona 5.5 and MariaDB 10.0, but was caused by something else (HW, environment, ...). As of now, we don't know what it is. (end of summary). Sergei asked me to share the numbers I got while running the local tests. I will also put here the initial queries and explains, for the reference. It's mainly for the record, there is nothing particularly interesting in here; if you stop reading now, you won't lose much. Queries and plans ----------------- The initial offending query: SELECT SUM(visits) FROM ( SELECT IFNULL(ref.probename_unique_visits,'tr_trafficother2_uv') AS probename, SUM(x.value) AS visits, IFNULL(ref.category,'Other') AS category FROM ( SELECT aggregated_data._getSource(source, source2, source3, fb_source, request_ids) AS source, COUNT(DISTINCT u.fbuserid) AS value FROM g9_tracker.app_logins u WHERE u.created_date = '2014-04-22' GROUP BY 1 ) x LEFT JOIN aggregated_data.ref_parameter_mappings ref ON (ref.value_of_ref_parameter = x.source) GROUP BY 1,3 ) y; EXPLAINs (that's where row counts were off; note EXPLAIN execution time): MariaDB 10.0: +------+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3394453 | | | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 3394453 | Using temporary; Using filesort | | 2 | DERIVED | ref | ref | value_of_ref_parameter_UNIQUE | value_of_ref_parameter_UNIQUE | 603 | x.source | 1 | Using where | | 3 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 3394453 | Using where; Using filesort | +------+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+---------------------------------+ 4 rows in set (0,03 sec) Percona 5.5: +----+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | | | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 75 | Using temporary; Using filesort | | 2 | DERIVED | ref | ref | value_of_ref_parameter_UNIQUE | value_of_ref_parameter_UNIQUE | 603 | x.source | 1 | | | 3 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 3669609 | Using filesort | +----+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+---------------------------------+ 4 rows in set (1 min 40,20 sec) Example of the indices difference (caused by table elimination): Query: SELECT COUNT(*) FROM ( SELECT aggregated_data._getSource(source, source2, source3, fb_source, request_ids) AS source, COUNT(DISTINCT u.fbuserid) AS value FROM g9_tracker.app_logins u WHERE u.created_date = '2014-04-22' GROUP BY 1 ) x LEFT JOIN aggregated_data.ref_parameter_mappings ref ON (ref.value_of_ref_parameter = x.source); EXPLAINs: MariaDB 10.0: +------+-------------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3394453 | 100.00 | | | 2 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 3394453 | 100.00 | Using where; Using filesort | +------+-------------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ 2 rows in set, 1 warning (0,02 sec) Percona 5.5: +----+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+----------+----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 75 | 100.00 | | | 1 | PRIMARY | ref | ref | value_of_ref_parameter_UNIQUE | value_of_ref_parameter_UNIQUE | 603 | x.source | 1 | 100.00 | Using index | | 2 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 3669609 | 100.00 | Using filesort | +----+-------------+------------+------+-------------------------------+-------------------------------+---------+----------+---------+----------+----------------+ 3 rows in set, 1 warning (1 min 43,62 sec) Numbers ------- The query that Sergei suggested to run in local tests: SELECT SUM(varchar_non_indexed_column_with_non_empty_different_values='foo') FROM table_with_3_mio_rows I ran tests on Percona 5.5/5.6, MariaDB 5.5/10.0, MySQL 5.5/5.6. Only the relevant results are here, I'll skip the rest. For every run the caches were cleaned, the server was started, and then the queries were executed, 3 times each. Initially servers were run with the default options (not server default, but packaging default, how they are installed on Wheezy). The difference between 5.5 and 5.6/10.0 was astonishing, but it was explained by Performance Schema. It went like this: 5.5 select sum(c2='foo') from t_innodb; 1 row in set (8.17 sec) 1 row in set (0.99 sec) 1 row in set (0.95 sec) select sum(c2='foo') from t_myisam; 1 row in set (3.04 sec) 1 row in set (0.56 sec) 1 row in set (0.55 sec) 5.6 / 10.0 select sum(c2='foo') from t_innodb; 1 row in set (24.24 sec) 1 row in set (21.65 sec) 1 row in set (23.43 sec) select sum(c2='foo') from t_myisam; 1 row in set (18.83 sec) 1 row in set (17.23 sec) 1 row in set (16.05 sec) Pretty much the same picture for MariaDB, Percona and MySQL. It would explain everything, but it turned out that james_woods already had Performance Schema turned off. (Note: we *really* should consider switching off Performance schema, at least in configs provided upon the new package installation). Then I ran another test, with the PS turned off, and otherwise default options (now really default, without any pre-installed config). There was still a difference between 5.5 and 5.6/10.0, although not that big, and for InnoDB only: Percona 5.5 select sum(c2='foo') from t_innodb; 1 row in set (5.97 sec) 1 row in set (0.79 sec) 1 row in set (0.79 sec) select sum(c2='foo') from t_myisam; 1 row in set (3.68 sec) 1 row in set (0.56 sec) 1 row in set (0.56 sec) Percona 5.6 select sum(c2='foo') from t_innodb; 1 row in set (7.33 sec) 1 row in set (2.32 sec) 1 row in set (1.48 sec) select sum(c2='foo') from t_myisam; 1 row in set (2.90 sec) 1 row in set (0.59 sec) 1 row in set (0.59 sec) MariaDB 5.5 select sum(c2='foo') from t_innodb; 1 row in set (8.17 sec) 1 row in set (0.99 sec) 1 row in set (0.95 sec) select sum(c2='foo') from t_myisam; 1 row in set (3.04 sec) 1 row in set (0.56 sec) 1 row in set (0.55 sec) MariaDB 10.0 select sum(c2='foo') from t_innodb; 1 row in set (7.34 sec) 1 row in set (2.07 sec) 1 row in set (2.07 sec) select sum(c2='foo') from t_myisam; 1 row in set (3.49 sec) 1 row in set (0.58 sec) 1 row in set (0.57 sec) MySQL 5.6 select sum(c2='foo') from t_innodb; 1 row in set (7.88 sec) 1 row in set (1.95 sec) 1 row in set (2.11 sec) select sum(c2='foo') from t_myisam; 1 row in set (4.11 sec) 1 row in set (0.55 sec) 1 row in set (0.57 sec) So, on 5.6/10.0 with default options the query from the "warm" InnoDB table was approximately 2x slower than on 5.5. Then I took non-default options from james_woods and applied them, as much as I could, considering the HW difference, to my modest VM. In fact, only innodb_buffer_pool_size mattered. After doubling it on 5.6/10.0, I got pretty much the same results as on 5.5. Percona 5.5 (default options) select sum(c2='foo') from t1; 1 row in set (5.97 sec) 1 row in set (0.79 sec) 1 row in set (0.79 sec) Percona 5.6 (default options + no PS + increased buffer pool) select sum(c2='foo') from t1; 1 row in set (8.33 sec) 1 row in set (0.83 sec) 1 row in set (0.82 sec) MariaDB 5.5 (default options) select sum(c2='foo') from t1; 1 row in set (8.17 sec) 1 row in set (0.99 sec) 1 row in set (0.95 sec) MariaDB 10.0 (default options + no PS + increased buffer pool) select sum(c2='foo') from t1; 1 row in set (7.85 sec) 1 row in set (0.86 sec) 1 row in set (0.86 sec) There are still some minor differences, but my VM is not suitable for fine-grained performance testing. If somebody wants to investigate it further, it should be done in a proper way; in any case I'm sure it can be tuned and made to perform better on 5.6/10.0, nobody who cares about differences this small actually runs the server with default options. Regards, /E

1 0