May 2014 - developers - lists.mariadb.org

[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