October 2015 - discuss - lists.mariadb.org

[Maria-discuss] Can someone explain odd InnoDB locking behavior
by Yuri Karaban 09 Oct '15

09 Oct '15

Hi I'm trying to understand why InnoDB locks two next-key intervals higher than needed. If you try to run conn-a.sql from one connection, and then run conn-b.sql from other connection, conn-b.sql would be locked. I have a feeling that it locks two next-key intervals: (5 to 7 and 7 to positive infinity). Because I can insert values higher than 7 in conn-b only if condition is WHERE n <= 4 (or lower than 4). TokuDB locks just one next-key interval (with WHERE n <= 7, TokuDB locks from -infinity to +infinity, but with condition WHERE n <= 6, it locks from -infinity to 6). Please help me to understand why ever both engines need to lock gap after the upper end of range scan. And why InnoDB needs to lock even two next-key gaps. I tried this on MariaDB 10.0.21 ----------------------[conn-a.sql]-------------------------------- DROP TABLES IF EXISTS innosel, innoin; CREATE TABLE innosel (n INT UNSIGNED PRIMARY KEY) ENGINE InnoDB; CREATE TABLE innoin (n INT UNSIGNED PRIMARY KEY) ENGINE InnoDB; INSERT INTO innosel values (1), (3), (5), (7); SET tx_isolation = 'REPEATABLE-READ'; START TRANSACTION; INSERT IGNORE INTO innoin (n) SELECT n FROM innosel WHERE n <= 5; SELECT SLEEP(100); ROLLBACK; ----------------------[conn-a.sql]-------------------------------- ----------------------[conn-b.sql]-------------------------------- START TRANSACTION; INSERT INTO innosel VALUES (9); ROLLBACK; ----------------------[conn-b.sql]-------------------------------- -- Nemo iudex in causa sua.

2 2

[Maria-discuss] New Question: Problems with user access to db
by AskMonty KB 08 Oct '15

08 Oct '15

Hello, A new question has been asked in "Downloads" by mathman54. Please answer it at http://mariadb.com/kb/en/problems-with-user-access-to-db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have MariaDB 10.1 installed. I am trying to get some software installed but am running into problems. I have created a database and user for that database, but when I run the software it cannot connect to the database. The user I created was on the database sam I used the command MySQL -u sam -p'sam's password' sam's database I get access denied for user 'sam'@'localhost' I used the command "grant all privileges on newdb.* to sam@localhost;" I also did a "grant usage on *.* to sam@localhost identified by 'sams password'; Is there something I am doing wrong? Is there something I am missing? Your help is appreciated. Thank you, Mark -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/problems-with-user-access-to-db/

1 0

[Maria-discuss] New Question: Need help modifing the mysqld_multi script
by AskMonty KB 08 Oct '15

08 Oct '15

Hello, A new question has been asked in "MariaDB Community" by cisaksen. Please answer it at http://mariadb.com/kb/en/need-help-modifing-the-mysqld_multi-script/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I running a multi instance mariadb cluster with 3 nodes. The nodes are all SUSE (SLES) 12. I have copied the mysqld_multi script form /usr/bin to /etc/init.d to enable the startup and shutdown of all the databases when the system starts and stops. Problem I'm having is that if I use mysqld_mult stop/start (to stop/start them all) only one or two will actually stop/start the rest just won't work. But I f I start/stop them one at a time with a few seconds delay between commands it works just fine. So what I'm looking to do is to modify the mysqld_multi script to add a 5 sec delay in between each instances start/stop. Just not where the best place in the script to do this. Any ideas ? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/need-help-modifing-the-mysqld_multi-script/

1 0

[Maria-discuss] New Question: Galera Documentation
by AskMonty KB 07 Oct '15

07 Oct '15

Hello, A new question has been asked in "MariaDB Documentation" by cisaksen. Please answer it at http://mariadb.com/kb/en/galera-documentation/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Can someone point me in the right direction for setting up a MariaDB galera cluster. I see all the MariaDB documentation but I don't see any cluster info ? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/galera-documentation/

1 0

[Maria-discuss] MariaDB / Sysbench TPS question
by J. Cassidy 07 Oct '15

07 Oct '15

Hello all, I am running MariaDB 10.1.5 with Sysbench 0.5 (lua) against an IBM Blade (Haswell) using Hitachi 15K SAS Hdd's. Operating system is RHEL 7. Hyperthreading is turned off. System memory is 48GB. Up to now, I have ran extensive Sysbench tests against MariaDB using RAM as the datastore with no appreciable problems. Now I have switched the datastore to normal disk (see above) and am seeing the Sysbench R/W TPS rate take a hit after approximately 3 minutes (180 seconds) into the run (runtime is 600 seconds) and every three minutes thereafter. I am seeing nothing in the MariaDB logs (error and Slow queries), nor in Linux itself, nor is Sysbench telling me anything (I have debug rurned on and verbosity set to 5). Here is an excerpt from one of the R/W Sysbench runs [ 168s] threads: 128, tps: 26262.00, reads: 367679.02, writes: 105188.51, response time: 50.12ms (99%), errors: 0.00, reconnects: 0.00 [ 170s] threads: 128, tps: 25913.49, reads: 362826.86, writes: 103670.96, response time: 49.08ms (99%), errors: 0.00, reconnects: 0.00 [ 172s] threads: 128, tps: 26271.51, reads: 367736.58, writes: 105009.52, response time: 50.34ms (99%), errors: 0.00, reconnects: 0.00 [ 174s] threads: 128, tps: 26059.52, reads: 364822.73, writes: 104187.07, response time: 44.69ms (99%), errors: 0.00, reconnects: 0.00 [ 176s] threads: 128, tps: 26394.00, reads: 369573.99, writes: 105652.50, response time: 52.28ms (99%), errors: 0.00, reconnects: 0.00 [ 178s] threads: 128, tps: 26248.49, reads: 367422.90, writes: 104927.97, response time: 49.85ms (99%), errors: 0.00, reconnects: 0.00 [ 180s] threads: 128, tps: 26549.00, reads: 371726.52, writes: 106166.01, response time: 60.01ms (99%), errors: 0.00, reconnects: 0.00 [ 182s] threads: 128, tps: 21493.92, reads: 301020.88, writes: 86033.18, response time: 47.29ms (99%), errors: 0.00, reconnects: 0.00 [ 184s] threads: 128, tps: 174.00, reads: 2435.96, writes: 706.99, response time: 2245.86ms (99%), errors: 0.00, reconnects: 0.00 [ 186s] threads: 128, tps: 185.50, reads: 2597.00, writes: 760.50, response time: 2334.28ms (99%), errors: 0.00, reconnects: 0.00 [ 188s] threads: 128, tps: 269.00, reads: 3766.00, writes: 1066.50, response time: 2314.11ms (99%), errors: 0.00, reconnects: 0.00 [ 190s] threads: 128, tps: 153.50, reads: 2149.00, writes: 619.50, response time: 2121.06ms (99%), errors: 0.00, reconnects: 0.00 [ 192s] threads: 128, tps: 552.00, reads: 7728.00, writes: 2199.50, response time: 1440.33ms (99%), errors: 0.00, reconnects: 0.00 [ 194s] threads: 128, tps: 615.00, reads: 8610.00, writes: 2450.00, response time: 1414.69ms (99%), errors: 0.00, reconnects: 0.00 [ 196s] threads: 128, tps: 908.00, reads: 12712.00, writes: 3650.00, response time: 950.93ms (99%), errors: 0.00, reconnects: 0.00 [ 198s] threads: 128, tps: 1579.00, reads: 22052.47, writes: 6283.99, response time: 932.61ms (99%), errors: 0.00, reconnects: 0.00 [ 200s] threads: 128, tps: 1562.00, reads: 21921.53, writes: 6257.01, response time: 748.43ms (99%), errors: 0.00, reconnects: 0.00 [ 202s] threads: 128, tps: 4166.00, reads: 58323.95, writes: 16652.49, response time: 445.78ms (99%), errors: 0.00, reconnects: 0.00 [ 204s] threads: 128, tps: 11574.99, reads: 161931.90, writes: 46264.97, response time: 263.77ms (99%), errors: 0.00, reconnects: 0.00 [ 206s] threads: 128, tps: 17420.50, reads: 244004.98, writes: 69714.50, response time: 103.69ms (99%), errors: 0.00, reconnects: 0.00 [ 208s] threads: 128, tps: 19833.49, reads: 277626.90, writes: 79424.97, response time: 93.88ms (99%), errors: 0.00, reconnects: 0.00 [ 210s] threads: 128, tps: 24994.00, reads: 349910.48, writes: 99975.49, response time: 48.12ms (99%), errors: 0.00, reconnects: 0.00 [ 212s] threads: 128, tps: 25129.50, reads: 351825.03, writes: 100549.51, response time: 52.12ms (99%), errors: 0.00, reconnects: 0.00 [ 214s] threads: 128, tps: 24982.99, reads: 349739.36, writes: 99821.46, response time: 47.66ms (99%), errors: 0.00, reconnects: 0.00 My.cnf - [mysqld_safe] user=root password='' [mysqld] open_files_limit = 102400 max_allowed_packet = 768M connect_timeout = 120 # was 60 performance-schema = false net_read_timeout = 720 log-warnings=2 #####files and sockets pid-file=/tmp/mysqld.pid.sysbench log_warnings=2 log-error=/var/log/mariadb/mariadb-error-john.log long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mariadb/mariadb-slowquery.log log_queries_not_using_indexes = 1 # general-log # general-log-file=/var/log/mariadb/mariadb-general.log # general_log_file=/var/log/mariadb/mariadb-general.log ##### MariaDB temporary tables tmpdir = /mnt/ramdisk #####non innodb options (fixed) max_connections = 8000 # was 4000 back_log = 150 table_open_cache = 4000 # was 8000 key_buffer_size = 16M query_cache_type = 1 # was 0 join_buffer_size = 32K sort_buffer_size = 32K #####use InnoDB plugin #ignore-builtin-innodb #plugin-load=innodb=ha_innodb.so #####fixed innodb options innodb_file_per_table = true innodb_open_files = 8192 # was 4096 innodb_data_file_path = ibdata1:250M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT_NO_FSYNC innodb_log_buffer_size = 256M innodb_log_file_size = 4G innodb_log_files_in_group = 2 innodb_buffer_pool_size = 40G innodb_buffer_pool_instances = 32 innodb_adaptive_hash_index_partitions = 32 innodb_thread_concurrency = 0 #####Power8 specific #innodb_spin_wait_delay = 0 #####tuning for SAN storage innodb_adaptive_flushing = 1 innodb_flush_neighbors = 1 innodb_io_capacity = 4000 innodb_io_capacity_max = 6000 innodb_lru_scan_depth = 4096 innodb_purge_threads = 2 innodb_read_io_threads = 8 # was 8 innodb_write_io_threads = 16 ####tuning for RAM disk #innodb_adaptive_flushing = 1 #innodb_flush_neighbors = 0 #innodb_io_capacity = 20000 #innodb_io_capacity_max = 40000 #innodb_lru_scan_depth = 4096 #innodb_purge_threads = 2 #innodb_read_io_threads = 2 #innodb_write_io_threads = 2 Would anyone be able to enlighten me as to why I am getting this TPS performance hit after approximately three minutes? Any pointers much appreciated. Regards, Aubrey

2 1

[Maria-discuss] New Question: PHP source for UDF in Maria
by AskMonty KB 07 Oct '15

07 Oct '15

Hello, A new question has been asked in "Plugins" by ronijoven. Please answer it at http://mariadb.com/kb/en/php-source-for-udf-in-maria/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Is there a way to create a UDF in Maria using the PHP method in a class. There are a lot of PHP sites, this is in relation to encrypt data in MariaDB and the encryption algorithm is based on PHP. Thanks. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/php-source-for-udf-in-maria/

1 0

[Maria-discuss] Group Concat and Sub Query
by Roberto Spadim 04 Oct '15

04 Oct '15

Hi guys I have a problem and a possible solution, that i think is relative easy to develop (i didn't tested but i think the source is easy to change) i have some queries like: SELECT date, (SELECT value FROM some_table WHERE date=a.date ORDER BY date ASC LIMIT 1) AS first, (SELECT value FROM some_table WHERE date=a.date ORDER BY date DESC LIMIT 1) AS last FROM some_table AS a GROUP BY date i want to "convert" the complex sub query, to a agregate function, and i thinking about group concat: from mysql docs: GROUP_CONCAT([DISTINCT] *expr* [,*expr* ...] [ORDER BY {*unsigned_integer* | *col_name* | *expr*} [ASC | DESC] [,*col_name* ...]] [SEPARATOR *str_val*]) we have ORDER BY if we could include a "LIMIT" clause, i could rewrite this query to: SELECT date, GROUP_CONCAT(value ORDER BY date ASC LIMIT 1) AS first, GROUP_CONCAT(value ORDER BY date DESC LIMIT 1) AS last FROM some_table AS a GROUP BY date i know that i could have stats tables / materialized views (with flexview, i already tested :) ), but i want something more "easy to use" and not "very fast", just a "feature" to solve small problems it's a nice idea? does anyone have this "problem" and solve with other solutions? -- Roberto Spadim

3 11

[Maria-discuss] spider connections
by Federico Razzoli 01 Oct '15

01 Oct '15

Hi all. I'm trying to troubleshoot a weird situation, and I have processes like this: *************************** 3. row *************************** Id: 122884 User: root Host: 127.0.0.1:52222 db: dpp_ela Command: Query Time: 276 State: After opening tables Info: show table status from `dpp_ela` like 'access' Progress: 0.000 The query seems to be issued by SPIDER when "SHOW CREATE TABLE $spider_table" is issued. But why should this require a connection at all? I'd expect that query to show the local table definition. Am I missing something? Regards Federico

1 0

[Maria-discuss] what is adding the -g compile parm?
by Reindl Harald 01 Oct '15

01 Oct '15

"-O3 -g" appears in the cmake output, that likely breaks ld.gold / -flto and so the question: who the explain the build *not* add that useless param for production builds like most oterh software don't without one could also remove the strip-calls in rpm-spec-files!

2 4

[Maria-discuss] Error when migrating from TokuDB 7.5.2 to MariaDB 5.5.44 (works with 5.5.41)
by Fernando Marcelo 01 Oct '15

01 Oct '15

I'm trying to migrate a TokuDB 7.5.2 server to MariaDB 5.5.44. Since I can't stop the production server, I got a snapshot of the running instance. So MariaDB will have to recover. Unfortunately i get an error when TokuDB recovery is running in version 5.5.44. Previously I tried with MariaDB 5.5.41 which worked fine. But since I'm upgrading, I would like to use the latest version. Mon Sep 28 03:53:37 2015 TokuFT recovery bw_begin_checkpoint at 158147092471 timestamp 1443133470008593 (bw_between) Mon Sep 28 03:53:37 2015 TokuFT recovery turning around at begin checkpoint 158147092471 time 181293960 Mon Sep 28 03:53:37 2015 TokuFT recovery starts scanning forward to 158147993496 from 158147092471 left 901025 (fw_between) /home/buildbot/buildbot/build/mariadb-5.5.44/storage/tokudb/ft-index/ft/serialize/ft-serialize.cc:275 deserialize_ft_versioned: Assertion `readsz >= translation_size_on_disk' failed (errno=2) : No such file or directory Backtrace: (Note: toku_do_assert=0x0x7f769b49cf30) /usr/lib64/mysql/plugin/ha_tokudb.so(_Z19db_env_do_backtraceP8_IO_FILE+0x1b)[0x7f769b4996fb] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x80823)[0x7f769b499823] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x8089a)[0x7f769b49989a] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z24deserialize_ft_versionediP4rbufPP2ftj+0xc32)[0x7f769b525622] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z24toku_deserialize_ft_fromi10__toku_lsnPP2ft+0x229)[0x7f769b525949] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z35toku_read_ft_and_store_in_cachefileP9ft_handleP9cachefile10__toku_lsnPP2ft+0x76)[0x7f769b525bb6] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x11e20a)[0x7f769b53720a] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z28toku_ft_handle_open_recoveryP9ft_handlePKciiP10cachetableP7tokutxn7FILENUM10__toku_lsn+0x2d)[0x7f769b53938d] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x1204aa)[0x7f769b5394aa] /usr/lib64/mysql/plugin/ha_tokudb.so(_Z14tokuft_recoverP13__toku_db_envPFvS0_P7tokutxnEPFvS0_P10cachetableEP10tokuloggerPKcSC_PFiP9__toku_dbPK10__toku_dbtSH_EPFiSE_SH_SH_SH_PFvSH_PvESK_EPFiSE_SE_P9DBT_ARRAY SQ_SH_SH_EPFiSE_SE_SQ_SH_SH_Em+0x2538)[0x7f769b5017b8] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x1107b0)[0x7f769b5297b0] /usr/lib64/mysql/plugin/ha_tokudb.so(+0x5195f)[0x7f769b46a95f] /usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x6e4018] /usr/sbin/mysqld[0x5b254d] /usr/sbin/mysqld(_Z11plugin_initPiPPci+0x833)[0x5b7573] /usr/sbin/mysqld[0x51c638] /usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x47f)[0x52119f] /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f769e590af5] /usr/sbin/mysqld[0x518239] Engine status function not available Memory usage: Arena 0: system bytes = 0 in use bytes = 0 Total (incl. mmap): system bytes = 0 in use bytes = 0 max mmap regions = 0 max mmap bytes = 0 150928 3:53:37 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Any ideas about how to fix this issue?

2 1