- discuss - lists.mariadb.org

[Maria-discuss] TokuDB performance hit after 10.0.25
by Alessandro Ren 24 Jul '17

24 Jul '17

Hello, I've noticed a great performance hit after I upgraded my MariaDB install to 10.0.28, 10.0.29, 10.0.30 and 10.0.31. I even tried upgrading to MariaDB 10.2.7 and had the same problem. Bellow de details. MariaDB 10.0.25 - 13 rows in set (1.67 sec) MariaDB 10.0.31 - 13 rows in set (29.06 sec) The query: SELECT metric_id,date_format(entry_time, '%m:%Y') as date_group, unix_timestamp(entry_time) as entry_time, entry_time as datetime, avg(perf_value) as perf_value, warning, critical, baseline, lower_limit, upper_limit from service_perf_651 where service_id='56551' and metric_id='90183701' and entry_time>='2016-07-24 09:41:42' and entry_time<='2017-07-24 09:41:42' and ( (date_format(entry_time,'%w')=0 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H') < 24))) or (date_format(entry_time,'%w')=1 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=2 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=3 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=4 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=5 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=6 and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24) )) ) group by date_group order by entry_time Explain query: +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | service_perf_651 | range | PRIMARY,service_perf_1_idx | PRIMARY | 16 | NULL | 1 | Using where; Using temporary; Using filesort | +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+ 1 row in set (0.06 sec) The table: CREATE TABLE `service_perf_651` ( `entry_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `service_id` int(11) unsigned NOT NULL DEFAULT '0', `metric_id` int(11) unsigned NOT NULL DEFAULT '0', `perf_value` float(13,3) DEFAULT NULL, `warning` float(13,3) DEFAULT NULL, `critical` float(13,3) DEFAULT NULL, `baseline` float(13,3) DEFAULT NULL, `lower_limit` float(13,3) DEFAULT NULL, `upper_limit` float(13,3) DEFAULT NULL, `reserved0` float(13,3) DEFAULT NULL, `reserved1` float(13,3) DEFAULT NULL, `reserved2` float(13,3) DEFAULT NULL, PRIMARY KEY (`entry_time`,`service_id`,`metric_id`), KEY `service_perf_1_idx` (`service_id`,`metric_id`,`entry_time`) ) ENGINE=TokuDB DEFAULT CHARSET=utf8 `compression`='tokudb_snappy' The size: 32497415 records Any idea what could be wrong? I even tried everything above on a different HW where the databse fit in memory in TokuDB, with the same performance hit. Tks for the help Alessandro Ren

6 8

[Maria-discuss] How spy database alterations
by lemmel 21 Jul '17

21 Jul '17

Hi. Working with a web application (PHP, Nginx, MariaDB) that doesn't really work as expected, I'm looking for a way to monitor/spy database alterations done when some action is performed on the FrontEnd. It would be faster by far than to read the application's source code. So is there a non-invasive way to monitor/spy database alterations performed after a given time, or triggered manually ? Thanks ! P.S. : I know that I could add triggers on all database operation, but that would require adding a awful quantity of triggers and would not be easy to realize.

4 6

[Maria-discuss] -DWITH_SYSTEMD=ON is the same as -DWITH_SYSTEMD=OFF
by Reindl Harald 14 Jul '17

14 Jul '17

previously i had -DWITH_SYSTEMD=OFF and while trying to switch to Type=notify i switched that to -DWITH_SYSTEMD=ON - well, nothing mentions systemd (yes, systemd-devel is installed) remove the option completly and oh look: -- Looking for include file systemd/sd-daemon.h -- Looking for include file systemd/sd-daemon.h - found did i say that cmake is a bunch of crap already? a sane buildsystem won't need ON/OFF at all with params started by "WITH" or "WITHOUT".... like the old ./configure where on some projects like PHP you just start with --dsiable-all followed by --enable-x, --enable-y and all is fine and clear

3 6

[Maria-discuss] MariaDB 10.2.7 and libmysql compatibility
by Reindl Harald 13 Jul '17

13 Jul '17

i am missing something (probably simple like symbol versioning however done with some hack) or is it really not possible by providing symlinks to avoid "libmysqlclient.so.18 => not found" and all clients linked against 10.1.x failing? attached my current rpm-spec - the server itself works fine and i really want to avoid building a "server-only" package for the whole lifetime of Fedora25/26 as well as not wait for Fedora 27 to get 10.2.x [root@testserver:/usr/lib64/mysql]$ ls insgesamt 308K lrwxrwxrwx 1 root root 13 2017-07-13 13:15 libmysqlclient.so.18.0.0 -> libmariadb.so lrwxrwxrwx 1 root root 13 2017-07-13 13:15 libmysqlclient.so.18 -> libmariadb.so -rwxr-xr-x 1 root root 305K 2017-07-13 13:15 libmariadb.so.3 lrwxrwxrwx 1 root root 15 2017-07-13 13:15 libmariadb.so -> libmariadb.so.3 lrwxrwxrwx 1 root root 17 2017-07-13 13:15 libmysqlclient_r.so -> libmysqlclient.so lrwxrwxrwx 1 root root 13 2017-07-13 13:15 libmysqlclient.so -> libmariadb.so [root@testserver:/usr/lib64/mysql]$ ldd /usr/sbin/dbmail-imapd linux-vdso.so.1 (0x00007fff2fdfd000) libgmime-2.6.so.0 => /lib64/libgmime-2.6.so.0 (0x00007f0b0fd94000) libgobject-2.0.so.0 => /lib64/libgobject-2.0.so.0 (0x00007f0b0fb42000) libglib-2.0.so.0 => /lib64/libglib-2.0.so.0 (0x00007f0b0f82f000) libevent-2.0.so.5 => /lib64/libevent-2.0.so.5 (0x00007f0b0f7e5000) libzdb.so.11 => /lib64/libzdb.so.11 (0x00007f0b0f7d0000) libdbmail.so.0 => /usr/lib64/dbmail/libdbmail.so.0 (0x00007f0b0f759000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0b0f53b000) libc.so.6 => /lib64/libc.so.6 (0x00007f0b0f175000) libgio-2.0.so.0 => /lib64/libgio-2.0.so.0 (0x00007f0b0ede2000) libz.so.1 => /lib64/libz.so.1 (0x00007f0b0ebcc000) libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f0b0e95a000) libffi.so.6 => /lib64/libffi.so.6 (0x00007f0b0e750000) libmysqlclient.so.18 => not found libm.so.6 => /lib64/libm.so.6 (0x00007f0b0e447000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f0b0e243000) libssl.so.10 => /lib64/libssl.so.10 (0x00007f0b0dfd1000) libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f0b0db70000) libgmodule-2.0.so.0 => /lib64/libgmodule-2.0.so.0 (0x00007f0b0d96a000) libgthread-2.0.so.0 => /lib64/libgthread-2.0.so.0 (0x00007f0b0d768000) libmhash.so.2 => /lib64/libmhash.so.2 (0x00007f0b0d53c000) libevent_pthreads-2.0.so.5 => /lib64/libevent_pthreads-2.0.so.5 (0x00007f0b0d538000) libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f0b0d302000) /lib64/ld-linux-x86-64.so.2 (0x0000559e18ffe000) libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f0b0d0db000) libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f0b0cebe000) libmount.so.1 => /lib64/libmount.so.1 (0x00007f0b0cc74000) libgomp.so.1 => /lib64/libgomp.so.1 (0x00007f0b0ca46000) libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f0b0c7f9000) libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f0b0c513000) libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f0b0c30f000) libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f0b0c0dc000) libfreebl3.so => /lib64/libfreebl3.so (0x00007f0b0bed9000) libblkid.so.1 => /lib64/libblkid.so.1 (0x00007f0b0bc96000) libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f0b0ba91000) librt.so.1 => /lib64/librt.so.1 (0x00007f0b0b889000) libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f0b0b678000) libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f0b0b474000)

2 3

[Maria-discuss] 10.2.7: main.subselect_no_opts fails
by Reindl Harald 13 Jul '17

13 Jul '17

Completed: Failed 1/1865 tests, 99.95% were successful _________________________________________ [root@testserver:~]$ cat /usr/local/bin/mysql-test.sh #!/usr/bin/dash /usr/bin/su -c 'cd /usr/share/mysql-test; ./mysql-test-run.pl --parallel=4 --max-test-fail=0 --mysqld=--binlog-format=row --force --suites="main-,binlog-,csv-,funcs_1-,funcs_2-,handler-,heap-,maria-,optimizer_unfixed_bugs-,parts-,percona-,roles-,rpl-,sys_vars-,unit-,vcol-" --skip-test-list=/var/lib/mysql/skip-tests.txt' mysql rm -rf /usr/share/mysql-test/var/* _________________________________________ CURRENT_TEST: main.subselect_no_opts /usr/bin/mysqltest: Error on delete of '/usr/share/mysql-test/var/4/tmp/subselect.out.file.1' (Errcode: 2 "No such file or directory") mysqltest: In included file "/usr/share/mysql-test/t/subselect.test": included from /usr/share/mysql-test/t/subselect_no_opts.test at line 12: At line 5903: query 'Select (Select Sum(`TestCase`.Revenue) From mysql.slow_log E Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) ) As `ControlRev` From (Select 3 as Revenue, 4 as TemplateID) As `TestCase` Group By TestCase.Revenue, TestCase.TemplateID' failed: 1242: Subquery returns more than 1 row The result from queries just before the failure was: < snip > 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # # MDEV-5991: crash in Item_field::used_tables # create table t1 (c int); select exists(select 1 from t1 group by `c` in (select `c` from t1)); exists(select 1 from t1 group by `c` in (select `c` from t1)) 0 drop table t1; # # MDEV-7565: Server crash with Signal 6 (part 2) # Select (Select Sum(`TestCase`.Revenue) From mysql.slow_log E Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2) ) As `ControlRev` From (Select 3 as Revenue, 4 as TemplateID) As `TestCase` Group By TestCase.Revenue, TestCase.TemplateID; More results from queries before failure can be found in /usr/share/mysql-test/var/4/log/subselect_no_opts.log - saving '/usr/share/mysql-test/var/4/log/main.subselect_no_opts/' to '/usr/share/mysql-test/var/log/main.subselect_no_opts/' ***Warnings generated in error logs during shutdown after running tests: main.grant main.subselect3_jcl6 main.subselect_mat main.strict_autoinc_3heap main.func_time_hires main.failed_auth_3909 main.subselect_extra main.gis-json main.gis main.gis-precise main.subselect_no_opts main.explain main.get_diagnostics main.str_to_datetime_457 main.function_defaults main.execution_constants main.func_weight_string main.empty_server_name-8224 main.func_timestamp main.subselect_cache main.subselect_gis main.gcc296 main.flush_table 2017-07-13 15:35:31 140219388360448 [Warning] Sort aborted, host: localhost, user: root, thread: 164, query: select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1) 2017-07-13 15:35:31 140219388360448 [Warning] Sort aborted, host: localhost, user: root, thread: 164, query: SELECT a FROM t1 GROUP BY a 2017-07-13 15:35:31 140219388360448 [Warning] Sort aborted, host: localhost, user: root, thread: 164, query: SELECT a FROM t1 GROUP BY a 2017-07-13 15:35:31 140219388360448 [Warning] Sort aborted, host: localhost, user: root, thread: 164, query: SELECT a FROM t1 2017-07-13 15:35:31 140219388360448 [Warning] Sort aborted, host: localhost, user: root, thread: 164, query: SELECT a FROM t1

1 0

Re: [Maria-discuss] 10.2.7 and xtradb in changelog
by Federico Razzoli 13 Jul '17

13 Jul '17

I don't find this extremely confusing (if it's a change to the code, it's not weird to see it in the changelog; filtering out things exposes us to bugs in the filtering process, which may hide important information) but I think that there is another confusing part. In this page: https://mariadb.com/kb/en/mariadb/why-does-mariadb-102-use-innodb-instead-o… I read: "Note, we still consider incorporating XtraDB optimizations, but as patches, rather than XtraDB as a whole" But then, it is not clear what exactly is or is not in MariaDB's InnoDB. Percona has a nice list of features: https://www.percona.com/doc/percona-server/LATEST/index.html It would be useful to have a list of which changes are applied to MariaDB's InnoDB. Hope it is not too hard to maintain. Cheers, Federico -------------------------------------------- Gio 13/7/17, Sergei Golubchik <serg(a)mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] 10.2.7 and xtradb in changelog A: "Reindl Harald" <h.reindl(a)thelounge.net> Cc: "Mailing-List mariadb" <maria-discuss(a)lists.launchpad.net> Data: Giovedì 13 luglio 2017, 12:35 Hi, Reindl! On Jul 13, Reindl Harald wrote: > Am 13.07.2017 um 11:55 schrieb Sergei Golubchik: > > On Jul 13, Reindl Harald wrote: > >> https://mariadb.com/kb/en/mariadb/mariadb-1027-changelog/ > >> > >> i thought xtradb is gone in 10.2.x > >> > >> is it back now with 10.2.7 or is the changelog a random wild mix of > >> commits with no context to the version of the release? > > > > Neither. Changelog is generated as "all commits that are present in the > > 10.2.7 history, but were not present in the 10.2.6 history". And all > > commits in 10.1 are merged into 10.2 eventually (because many 10.1 > > bugfixes apply to 10.2 too). > > > > So whatever was done to XtraDB in 10.1 it will be part of 10.2 revision > > history, even though XtraDB itself is disabled in 10.2 (and does not > > even compile there). > > > > I agree that it's confusing, but what can we do? Manually filter out all > > commits that contain the string "xtradb" from the changelog? > > well, at least some filters make sense because things like "Update > xtradb and innodb version to 5.6.36" are pure nonsense when innodb in > 10.2 AFAIK is 5.7 and so the complete changelog feels very useless and > instead beeing helpful it spreads doubt how that whole stuff works at > all when random pieces seems to get merged bewteen versions or at least > the changelog says so Okay. https://jira.mariadb.org/browse/MDEV-13312 Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

4 3

[Maria-discuss] mariadb-covscan-stroverflow.patch (Fedora)
by Reindl Harald 13 Jul '17

13 Jul '17

how long does Fedora need to ship that patch which seems to apply still to 10.2.7 where i am asking mysqlf for years if i should include it in my own builds or trust upstream

2 1

[Maria-discuss] 10.2.7: innodb_flush_method = ALL_O_DIRECT
by Reindl Harald 13 Jul '17

13 Jul '17

it's one thing that xtradb is gone but when the server refuses to start instead just warn about no longer supported options this is *very bad* 2017-07-13 12:22:57 140560655323200 [ERROR] InnoDB: Unrecognized value ALL_O_DIRECT for innodb_flush_method 2017-07-13 12:22:57 140560655323200 [Note] InnoDB: Number of pools: 1 2017-07-13 12:22:57 140560655323200 [Note] InnoDB: Using SSE2 crc32 instructions 2017-07-13 12:22:57 140560655323200 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2017-07-13 12:22:57 140560655323200 [Note] InnoDB: Starting shutdown... 2017-07-13 12:22:57 140560655323200 [ERROR] Plugin 'InnoDB' init function returned error. 2017-07-13 12:22:57 140560655323200 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-07-13 12:22:57 140560655323200 [ERROR] Unknown/unsupported storage engine: innodb _______________________________ BTW: "InnoDB: Cannot open '/Volumes/dune/mysql_dbmail/ib_buffer_pool' for reading: No such file or directory" - who cares when "innodb_buffer_pool_dump_at_shutdown = 0" is set on purpose because writing some GB buffer_pool at shutdown takes longer than the whole reboot of a typical server these days

1 0

[Maria-discuss] JSON in MariaDB 10.2.7 and MySQL
by Peter Laursen 13 Jul '17

13 Jul '17

-- MariaDB 10.2.7 CREATE TABLE `jsontst`( `id` INT NOT NULL AUTO_INCREMENT, `str` JSON, PRIMARY KEY (`id`) ) ENGINE=Aria CHARSET=latin1; SHOW CREATE TABLE `jsontst`; /* returns CREATE TABLE `jsontst2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 */ readings: https://jira.mariadb.org/browse/MDEV-9144 https://bugs.mysql.com/bug.php?id=86875 As you see except for JSON in MariaDB just being a synonym of TEXT there are two more significant differences 1) length: in MySQL JSON has the lenght of a LONGTEXT - in MariaDB it is a (plain) TEXT 2) encoding: in MySQL the utf8mb4 character set is enforced for JSON data - in MariaDB you may specify any character set. I think in particular 2nd point could cause problems (unicode garbling) when loading a dump with JSON data from MySQL. Basically because latin1 is still default character set in MariaDB. Are you aware of the 2 differences? I think the major reason for introducing JSON in MariaDB is compability concerns with MySQL. But the 2 points here are IMO incompabilites that could have been avoided?

2 4

[Maria-discuss] 10.2.7 and xtradb in changelog
by Reindl Harald 13 Jul '17

13 Jul '17

https://mariadb.com/kb/en/mariadb/mariadb-1027-changelog/ i thought xtradb is gone in 10.2.x is it back now with 10.2.7 or is the changelog a random wild mix of commits with no context to the version of the release? ______________________________ Revision #546a89ca58 2017-05-18 16:16:18 +0300 Update xtradb and innodb version to 5.6.36 Revision #f880200974 2017-05-20 17:47:07 +0300 After-merge fix: Correct the 32-bit XtraDB result diff. Revision #fe291c687d 2017-05-20 08:43:25 +0300 After-merge fix: Correct the XtraDB result diff. Revision #4df726e180 2017-07-10 00:05:06 +0300 storage_engine subsuites should also be disabled for XtraDB

3 5