- discuss - lists.mariadb.org

[Maria-discuss] New Question: "wsrep_local_replays" greater than zero
by AskMonty KB 10 May '16

10 May '16

Hello, A new question has been asked in "MariaDB FAQ" by alexandraarreaza. Please answer it at http://mariadb.com/kb/en/wsrep_local_replays-greater-than-zero/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi there, I've been having this situation with two different Galera DB Clusters (let's say Cluster A and Cluster B) for two different customers, where the "wsrep_local_replays" shows "1" on one of the DB nodes on each DB Cluster. I'd like to know: 1. Which is reason why this variable is now 1. 2. How can this be fixed. 3. As Cluster A and Cluster B are in Production, I'm worried that this may cause a problem in the long term... Would it cause it? One fix that worked for me was restarting mysql service on the node with this "issue" in Cluster A, which thankfully joined the cluster with an IST. However, I cannot do the same for Cluster B, since DBs are massive ( > 300GB). Even though gcache is big enough, that won't guarantee an IST and I cannot afford to do an SST. Also, the donor is the one having "wsrep_local_replays=1". The app doesn't write to the donor (so how did it happen?). Cluster A: 10.0.19-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r4144 Cluster B: 5.5.41-MariaDB-wsrep-log MariaDB Server, wsrep_25.11.r4026 Please let me know if you need any more details. Thanks in advance! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/wsrep_local_replays-greater-than-zero/

1 0

[Maria-discuss] MariaDB 10.1.14 and Connector/J 1.4.4 now available
by Daniel Bartholomew 10 May '16

10 May '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.14 and MariaDB Connector/J 1.4.4. These are both Stable (GA) releases. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.1.14 - Release Notes: https://mariadb.com/kb/en/mdb-10114-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10114-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.1.14 About MariaDB 10.1: - https://mariadb.com/kb/en/what-is-mariadb-101/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/J 1.4.4 - Release Notes: https://mariadb.com/kb/en/mcj-144-rn/ - Changelog: https://mariadb.com/kb/en/mcj-144-cl/ - Downloads: https://downloads.mariadb.org/connector-java/1.4.4/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] New Question: Poor performance on inserts MariaDB
by AskMonty KB 09 May '16

09 May '16

Hello, A new question has been asked in "MariaDB Documentation" by aferrett. Please answer it at http://mariadb.com/kb/en/poor-performance-on-inserts-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are running MariaDB Community Edition 10.1.11. We are benchmarking inserts for an application that requires high volume inserts. With MariaDB, we can insert about 476 rows for second. The same job is able to load 2,500 rows/ second on Oracle. Both environments are VMware with RedHat Linux. We have the same number of vCPUs and memory. Overall performance declines if we try multiple jobs. I tried increasing innodb_autoinc_lock_mode from 1 to 2 and bouncing without seeing improvement. I see a of processes waiting on system locks such as this one: SQL thread id 14, OS thread handle 0x7f9237bff700, query id 6743478 zlx70603.vci.att.com 130.9.221.47 pedadba System lock. I see lots of WRITES for each insert: 20160509 09:16:29,zlx70756,pedadba,zlx70603.vci.att.com,32,11812785,QUERY,ped,'insert into ped_location_load (SRC_CPL_ID, STR_ADDR, CITY_NM, ST_CD, ZIP_CD, LAT_COORD, LONG_COORD, CLLI_8, FIBR_LIT_IND, FIBR_LIT_BLDG_IND, EGIS_FIBR_LIT_BLDG_IND, SRVG_WC_CLLI_8, LEIS_WC_CD, DA_PSA_NAME, DA_PSA_ID, DSTNC_TO_FIBR_FEET_MSRMT, DSTNC_TO_FIBR_MILE_MSRMT, GIS_LAT_COORD, GIS_LONG_COORD, SAG_HSE_NBR, SAG_STR_PRFX, SAG_STR_NM, SAG_CITY_NM, SAG_ST_CD, SAG_ZIP_CD, SAG_MTCH_CD, SITE_STS_CD, AREA_MGR_PLNG_NM, AREA_MGR_DSGN_NM, RGN_CD, SUB_TURF_CD, TERR_TYPE_CD, LATA_CD, BUS_UNIT_CNT, PRRTY_NBR, PRRTY_YR, ALT_SRVG_WC_CLLI_8, PROJ_MGT_TOOL_NBR, BLDG_NM, PROJ_ACTVN_DT, REACTVN_DT, UVERSE_GREEN_AREA_IND, CELL_SITE_IN_BLDG_IND, CELL_SITE_N_BLDG_FXD_ASSET_NBR, VCNTY_CELL_SITE_FXD_ASSET_NBR, EXSTG_ORD_NBR, SMALL_CELL_PLAN_FOR_BLDG_IND, LOC_CMNT_TXT, CRTD_DT, CRTD_BY, LAST_UPDTD_DT, LAST_UPDTD_BY, SVC_ORD_NBR, MKTG_STR_PRFX, MKTG_STR_DRCTN, RVISD_BUS_UNIT_CNT, BUS_JSTFCN, BLD_YR, BLDG_PASSED, BLDG_ON _NET, FAP_STS, FAP_LOC, FDA_NM, FAA_NM, FAP_JOB_NBR, CLDS_REQUEST_ID, CLDS_STATUS, FIBR_RISER_RQRD, SAG_MTCH_MSG,',0 20160509 09:16:29,zlx70756,pedadba,zlx70603.vci.att.com,32,11812788,QUERY,ped,'SELECT FLOOR(RAND()*(50-1)+50) into st_rand',0 20160509 09:16:29,zlx70756,pedadba,zlx70603.vci.att.com,32,11812789,QUERY,ped,'SELECT FLOOR(RAND()*(6-1)+6) into reg_rand',0 20160509 09:16:29,zlx70756,isaac_all,%,32,11812791,WRITE,ped,ped_location_load, 20160509 09:16:29,zlx70756,isaac_all,%,32,11812791,WRITE,ped,ped_location_load, < many more WRITES> Here is the full innodb engine status: to shorten it, I put the full statement only once: -------------- SHOW ENGINE INNODB STATUS -------------- *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-05-09 08:43:37 7f92323ff700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 34 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2785 srv_active, 0 srv_shutdown, 461 srv_idle srv_master_thread log flush and writes: 3246 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8038 OS WAIT ARRAY INFO: signal count 9256 Mutex spin waits 5940, rounds 76361, OS waits 1957 RW-shared spins 2417, rounds 70030, OS waits 2234 RW-excl spins 5242, rounds 133769, OS waits 3264 Spin rounds per wait: 12.86 mutex, 28.97 RW-shared, 25.52 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 88326197 Purge done for trx's n:o < 88326196 undo n:o < 0 state: running but idle History list length 180 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 20, OS thread handle 0x7f92323ff700, query id 6743576 zlx70603.vci.att.com 130.9.221.47 isaac_all init SHOW ENGINE INNODB STATUS ---TRANSACTION 88325944, not started MySQL thread id 5, OS thread handle 0x7f92781db700, query id 955255 vpn-135-70-5-32.vpn.west.att.com 135.70.5.32 isaac_all cleaning up ---TRANSACTION 0, not started MySQL thread id 4, OS thread handle 0x7f9281962700, query id 955574 vpn-135-70-5-32.vpn.west.att.com 135.70.5.32 isaac_all cleaning up ---TRANSACTION 88322307, not started MySQL thread id 1, OS thread handle 0x7f92b3d84700, query id 0 Waiting for background binlog tasks ---TRANSACTION 88326196, ACTIVE 52 sec mysql tables in use 131, locked 131 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 2238 MySQL thread id 13, OS thread handle 0x7f92459f3700, query id 6743448 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, STR_ADDR, CITY_NM, ST_CD, ZIP_CD, LAT_COORD, LONG_COORD, CLLI_8, FIBR_LIT_IND, FIBR_LIT_BLDG_IND, EGIS_FIBR_LIT_BLDG_IND, SRVG_WC_CLLI_8, LEIS_WC_CD, DA_PSA_NAME, DA_PSA_ID, DSTNC_TO_FIBR_FEET_MSRMT, DSTNC_TO_FIBR_MILE_MSRMT, GIS_LAT_COORD, GIS_LONG_COORD, SAG_HSE_NBR, SAG_STR_PRFX, SAG_STR_NM, SAG_CITY_NM, SAG_ST_CD, SAG_ZIP_CD, SAG_MTCH_CD, SITE_STS_CD, AREA_MGR_PLNG_NM, AREA_MGR_DSGN_NM, RGN_CD, SUB_TURF_CD, TERR_TYPE_CD, LATA_CD, BUS_UNIT_CNT, PRRTY_NBR, PRRTY_YR, ALT_SRVG_WC_CLLI_8, PROJ_MGT_TOOL_NBR, BLDG_NM, PROJ_ACTVN_DT, REACTVN_DT, UVERSE_GR Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326191, ACTIVE 83 sec mysql tables in use 52, locked 52 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3584 MySQL thread id 16, OS thread handle 0x7f9235fff700, query id 6743430 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, STR_ADDR, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326190, ACTIVE 83 sec mysql tables in use 5, locked 5 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3658 MySQL thread id 19, OS thread handle 0x7f92333ff700, query id 6743606 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326188, ACTIVE 86 sec mysql tables in use 59, locked 59 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 3697 MySQL thread id 15, OS thread handle 0x7f9236fff700, query id 6743327 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326187, ACTIVE 98 sec mysql tables in use 66, locked 66 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 4320 MySQL thread id 12, OS thread handle 0x7f924ddee700, query id 6743575 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326186, ACTIVE 99 sec mysql tables in use 33, locked 33 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 4506 MySQL thread id 17, OS thread handle 0x7f92353ff700, query id 6743630 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326185, ACTIVE 116 sec mysql tables in use 59, locked 59 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 5086 MySQL thread id 11, OS thread handle 0x7f9281a63700, query id 6743446 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326184, ACTIVE 172 sec mysql tables in use 7, locked 7 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 7919 MySQL thread id 18, OS thread handle 0x7f92343ff700, query id 6743538 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326183, ACTIVE 203 sec mysql tables in use 52, locked 52 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 9102 MySQL thread id 8, OS thread handle 0x7f92393ff700, query id 6743600 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC ---TRANSACTION 88326178, ACTIVE 212 sec mysql tables in use 75, locked 75 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 9706 MySQL thread id 14, OS thread handle 0x7f9237bff700, query id 6743478 zlx70603.vci.att.com 130.9.221.47 pedadba System lock insert into ped_location_load (SRC_CPL_ID, <complete statement same as above> Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 4272 OS file reads, 78139 OS file writes, 20899 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 21.68 writes/s, 4.32 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 62, seg size 64, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 5981.97 hash searches/s, 406.66 non-hash searches/s --- LOG --- Log sequence number 375449925793 Log flushed up to 375449653000 Pages flushed up to 375438050350 Last checkpoint at 375435460923 Max checkpoint age 84223550 Checkpoint age target 81591565 Modified age 11875443 Checkpoint age 14464870 0 pending log writes, 0 pending chkp writes 6332 log i/o's done, 1.91 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 553648128; in additional pool allocated 0 Total memory allocated by read views 288 Internal hash tables (constant factor + variable factor) Adaptive hash index 8888016 (8851048 + 36968) Page hash 553976 (buffer pool 0 only) Dictionary cache 46879130 (2214224 + 44664906) File system 1307376 (812272 + 495104) Lock system 1334216 (1329176 + 5040) Recovery system 0 (0 + 0) Dictionary memory allocated 44664906 Buffer pool size 32767 Buffer pool size, bytes 536854528 Free buffers 1014 Database pages 31748 Old database pages 11699 Modified db pages 500 Percent of dirty pages(LRU & free pages): 1.526 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3251, not young 49414 0.06 youngs/s, 0.00 non-youngs/s Pages read 2853, created 52673, written 60872 0.00 reads/s, 16.56 creates/s, 18.56 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 31748, unzip_LRU len: 0 I/O sum[982]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 10 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 10 out of 1000 descriptors used Main thread process no. 2676, id 140266283443968, state: sleeping Number of rows inserted 1123943, updated 0, deleted 0, read 20 423.72 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/poor-performance-on-inserts-mariadb/

1 0

[Maria-discuss] New Question: Mr
by AskMonty KB 09 May '16

09 May '16

Hello, A new question has been asked in "Replication, Cluster, & Multi-Master" by Adetunji. Please answer it at http://mariadb.com/kb/en/mr/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- My company will like to use mariadb for our next project as we're currently using mysql, we'll like to know how the clustering and the replication works. Thanks -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mr/

1 0

[Maria-discuss] New Question: Trigger error
by AskMonty KB 07 May '16

07 May '16

Hello, A new question has been asked in "Triggers" by shadowfoxx_49588. Please answer it at http://mariadb.com/kb/en/trigger-error/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, i'm trying to create check trigger between two columns. But get query error. Structure of columns is: ,,`start_t` (time) , `end_t` (time),, The goal is hours in `end_t` don't be same with `start_t` (e.g. 11:00 > 11:00) Here is query code: ,,CREATE TRIGGER `check_times` BEFORE INSERT ON `schedule` FOR EACH ROW BEGIN DECLARE v_end_time time; SELECT end_t INTO v_end_time FROM `schedule` WHERE fn_stud = NEW.fn_stud; IF NEW.start_t > v_end_time THEN SET NEW.fn_stud = NULL; END IF; END;,, MySQL says: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 Don't understand what is wrong. Can anyone explain? Thanks. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/trigger-error/

1 0

[Maria-discuss] New Question: Which OS to use for MariaDB?
by AskMonty KB 06 May '16

06 May '16

Hello, A new question has been asked in "General FAQ" by dbamaria. Please answer it at http://mariadb.com/kb/en/which-os-to-use-for-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, What is the best OS environnement for MariaDB database ?(in term of varity of features, stability, performance..) I've seen that ,for instance, the Galera solution couldn't be installed under Windows..Are there any kind of such limitations and constraints to install it in Linux or Windows platform? Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/which-os-to-use-for-mariadb/

1 0

[Maria-discuss] SST with 10.1.13 + xtrabackup-v2 is failing
by Erik Mathis 06 May '16

06 May '16

I cant seem to get past this problem when using xtrabackup-v2 to preform an SST. Everything works fine when I use rsync method. This is a new cluster. I know the sst user/password is working which I confirmed. 2016-05-06 14:34:31 140258805017344 [Note] WSREP: State transfer required: Group state: b5013090-12e9-11e6-ba99-bf6bb7612fa3:8 Local state: 00000000-0000-0000-0000-000000000000:-1 2016-05-06 14:34:31 140258805017344 [Note] WSREP: New cluster view: global state: b5013090-12e9-11e6-ba99-bf6bb7612fa3:8, view# 43: Primary, number of nodes: 3, my index: 0, protocol version 3 2016-05-06 14:34:31 140258805017344 [Warning] WSREP: Gap in state sequence. Need state transfer. 2016-05-06 14:34:31 140258796631808 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.1.5.42' --datadir '/var/lib/mysql/' --parent '28455' --binlog 'mysql-bin' ' WSREP_SST: [INFO] Streaming with xbstream (20160506 14:34:32.597) WSREP_SST: [INFO] Using socat as streamer (20160506 14:34:32.605) WSREP_SST: [INFO] Evaluating timeout -s9 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160506 14:34:32.740) 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Prepared SST request: xtrabackup-v2|10.1.5.42:4444/xtrabackup_sst//1 2016-05-06 14:34:32 140258805017344 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2016-05-06 14:34:32 140258805017344 [Note] WSREP: REPL Protocols: 7 (3, 2) 2016-05-06 14:34:32 140258905679616 [Note] WSREP: Service thread queue flushed. 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Assign initial position for certification: 8, protocol version: 3 2016-05-06 14:34:32 140258905679616 [Note] WSREP: Service thread queue flushed. 2016-05-06 14:34:32 140258805017344 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (b5013090-12e9-11e6-ba99-bf6bb7612fa3): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. 2016-05-06 14:34:32 140258832279296 [Note] WSREP: Member 0.0 () requested state transfer from '*any*'. Selected 1.0 ()(SYNCED) as donor. 2016-05-06 14:34:32 140258832279296 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 8) 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Requesting state transfer: success, donor: 1 WSREP_SST: [INFO] Proceeding with SST (20160506 14:34:33.153) WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160506 14:34:33.152) WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20160506 14:34:33.163) removed `/var/lib/mysql/ib_logfile0' removed `/var/lib/mysql/aria_log_control' removed `/var/lib/mysql/aria_log.00000001' removed `/var/lib/mysql/mysql-bin.index' removed `/var/lib/mysql/ib_logfile1' removed `/var/lib/mysql/ibdata1' removed `/var/lib/mysql/mysql.sock' removed `/var/lib/mysql/mysql-bin.state' removed `/var/lib/mysql/mysql-bin.000001' WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160506 14:34:33.252) 2016-05-06 14:34:34 140258842769152 [Note] WSREP: (a4dbb726, 'tcp://0.0.0.0:4567') turning message relay requesting off 2016-05-06 14:34:44 140258832279296 [Note] WSREP: 1.0 (): State transfer to 0.0 () complete. 2016-05-06 14:34:44 140258832279296 [Note] WSREP: Member 1.0 () synced with group. ls: cannot access mysql-bin.[0-9]*: No such file or directory WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql//.sst (20160506 14:34:45.012) WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20160506 14:34:45.021) rm: cannot remove `/var/lib/mysql//innobackup.prepare.log': No such file or directory rm: cannot remove `/var/lib/mysql//innobackup.move.log': No such file or directory WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ (20160506 14:34:50.126) WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --move-back --force-non-empty-directories ${DATA} &>${DATA}/innobackup.move.log (20160506 14:34:50.135) WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160506 14:34:50.167) 2016-05-06 14:34:50 140258796631808 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.1.5.42' --datadir '/var/lib/mysql/' --parent '28455' --binlog 'mysql-bin' : 2 (No such file or directory) 2016-05-06 14:34:50 140258796631808 [ERROR] WSREP: Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script. 2016-05-06 14:34:50 140260071184416 [ERROR] WSREP: SST failed: 2 (No such file or directory) 2016-05-06 14:34:50 140260071184416 [ERROR] Aborting Error in my_thread_global_end(): 1 threads didn't exit The Joiner innobackup.move.log has this error innobackupex version 2.4.2 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 8e86a84) Error: Move file ib_logfile0 to ./ib_logfile0 failed: Destination file exists 14:34:50 UTC - xtrabackup got signal 11 ; This could be because you hit a bug or data is corrupted. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x10000 innobackupex(my_print_stacktrace+0x35)[0xedd1a5] innobackupex(handle_fatal_signal+0x273)[0xa3fce3] /lib64/libpthread.so.0(+0xf7e0)[0x7f5527d2b7e0] /lib64/libc.so.6(cfree+0x1c)[0x7f552618593c] innobackupex(_Z9copy_backv+0x377)[0x74f437] innobackupex(main+0xc0f)[0x73e00f] /lib64/libc.so.6(__libc_start_main+0xfd)[0x7f5526128d5d] innobackupex[0x72db39]

2 2

[Maria-discuss] New Question: Deadlock with foreign key constraints
by AskMonty KB 05 May '16

05 May '16

Hello, A new question has been asked in "MariaDB Community" by sjangra. Please answer it at http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are using MariaDB 10.1 and facing some weird deadlock issues. While investigating we ran a sample program to test the foreign keys and found a deadlock error. Can someone please explain this in bit detail so we understand how locking works in galera. We used a 2 node cluster for this testing. Session 1 (Node1): ------------------------ create table dl1(pk int primary key, data varchar(100)); create table dl2(pk int primary key, pk1 int not null, constraint dl2_fk foreign key(pk1) references dl1(pk), data varchar(100)); set autocommit=off; insert into dl1 values(1, 'a'); Session 2 (Node2): ------------------------ set autocommit=off; insert into dl2(pk, pk1, data) values(10, 1, 'a0'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demoApp`.`dl2`, CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`)) -- as expected select * from dl1; Empty set (0.00 sec) -- as expected Session 1 (Node1): ------------------------ commit; Session 2 (Node2): ------------------------ select * from dl1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction --- strange Once I got this error I ran the 'SHOW ENGINE INNODB STATUS\G' on node2 and here is the truncated output. ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2016-05-04 17:46:34 7ff98ead8b00 Transaction: TRANSACTION 3852, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1 --------------------> (LOOK HERE) MySQL thread id 2507, OS thread handle 0x7ff98ead8b00, query id 73514 localhost root update insert into dl2(pk, pk1, data) values(10, 1, 'a0') Foreign key constraint fails for table `test`.`dl2`: , CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`) Trying to add in child table, in index `dl2_fk` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000001; asc ;; 1: len 4; hex 8000000a; asc ;; But in parent table `test`.`dl1`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;; -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 1 RW transactions active inside InnoDB ------------------> (LOOK HERE) 0 RO transactions active inside InnoDB 1 out of 1000 descriptors used Main thread process no. 1242, id 140706194630400, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/

1 0

[Maria-discuss] configuration changes to consider in 10.2
by Daniel Black 05 May '16

05 May '16

As major versions are really the only time to consider changing default settings perhaps getting onto the following MDEV soon. https://jira.mariadb.org/browse/MDEV-7635 My main objective is to move the required configuration file down to approximately 0 settings and to work for the majority of people. The sql_mode will cause pain because years of bad practice haven't been fixed, however MySQL-5.7 has gone first and common frameworks are beginning to fix their implementations. They are usually fairly simple fixes to work with the sql_mode of "ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION". Finding/making some documentation to describe how to fix this would be good so the google answer doesn't become set sql_mode=''. Lots of suggestions here model MySQL-5.7 defaults with a few of my additions. Please let the list know which changes you agree with, disagree with (any why).

1 0

[Maria-discuss] New Question: Excessive contexting running MariaDB CE Query
by AskMonty KB 04 May '16

04 May '16

Hello, A new question has been asked in "MariaDB Community" by aferrett. Please answer it at http://mariadb.com/kb/en/excessive-contexting-running-mariadb-ce-query/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm working on a MariaDB vs. Oracle query performance issue. We have about 25 queries that we run on MariaDB 10.1.11 vs. Oracle 11.2.0.4. In all cases Oracle is several times faster than MariaDB. The environments are similar: running RHEL 6.5 on VMware guest. I focused on one representative query to drill down. This query runs in 13 seconds on Oracle and 30 seconds on MariaDB. The access plans look the same using the index with columns in the where clause to access the data. I don't see heavy I/O. The longest running step in the profile is below: | Sending data | 29.728961 | 32.786016 | 1.111831 | 100822 | 67799 | The 100822 is Context_voluntary and the 67799 is context involuntary. A the "cs" field in a vmstat jumps up to around 8,000 as soon as I start the query and goes back to hundreds when it ends: procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 3 89744 74424 103624 6447400 0 0 0 12 2322 9256 56 3 38 4 0 << query running 0 0 89744 74424 103636 6447404 0 0 0 0 152 241 1 0 91 9 0 << query ends My assessment is that the problem is excessive context switches. I tried increasing thread_pool_oversubscribe from 3 to 10, thread_pool_size from 2 to 10, and thread_pool_stall_limit to 1,000 each parameter one at a time and bouncing the instance. In all cases the query runs at a consistent 27-29 seconds. We are also doing heavy parallel insert testing. When we run the job, vmstat cs jumps to the same 8,000 or so. Our Linux SA cannot see any context related configuration differences between the Oracle and MariaDB servers. Any ideas will be appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/excessive-contexting-running-mariadb-ce-query/

1 0