percona - How come I still get deadlocks even after setting wsrep_retry_autocommit really high? -

-

i have cluster of 3 percona xtradb 5.5.34-55 servers , since writable, deadlock errors under substantial load. increasing wsrep_retry_autocommit variable helped extent, er_lock_deadlock did not disappear completely. i've tried setting wsrep_retry_autocommit 10000 (seems maximum), thinking make queries slow, none of them fail er_lock_deadlock:

mysql-shm -ss -e 'show global variables "%wsrep_retry_auto%"' wsrep_retry_autocommit  10000  ------------------------ latest detected deadlock ------------------------ 140414 10:29:23 *** (1) transaction: transaction 72d8, active 0 sec inserting mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 mysql thread id 34, os thread handle 0x7f11840d4700, query id 982 localhost shm update replace metric(host, name, userid, sampleid, type, priority) values ('localhost','cpu-3/cpu-nice',8,0,0,0),('localhost','cpu-3/cpu-system',8,0,0,0), ('localhost','cpu-3/cpu-idle',8,0,0,0),('localhost','cpu-3/cpu-wait',8,0,0,0), ('localhost','cpu-3/cpu-interrupt',8,0,0,0),('localhost','cpu-3/cpu-softirq',8,0,0,0), ('localhost','cpu-3/cpu-steal',8,0,0,0),('localhost','cpu-4/cpu-user',8,0,0,0), ('localhost','cpu-4/cpu-nice',8,0,0,0),('localhost','cpu-4/cpu-system',8,0,0,0), ('localhost','cpu-4/cpu-idle',8,0,0,0),('localhost','cpu-4/cpu-wait',8,0,0,0), ('localhost','cpu-4/cpu-interrupt',8,0,0,0),('localhost','cpu-4/cpu-softirq',8,0,0,0), ('localhost','cpu-4/cpu-steal',8,0,0,0) *** (1) waiting lock granted: record locks space id 0 page no 344 n bits 488 index `unique-metric` of table `shm`.`metric` trx id 72d8 lock_mode x waiting *** (2) transaction: transaction 72d7, active 0 sec updating or deleting mysql tables in use 1, locked 1 7 lock struct(s), heap size 3112, 141 row lock(s), undo log entries 40 mysql thread id 50, os thread handle 0x7f1184115700, query id 980 localhost shm update replace metric(host, name, userid, sampleid, type, priority) values ('localhost','cpu-3/cpu-nice',8,0,0,0),('localhost','cpu-3/cpu-system',8,0,0,0), ('localhost','cpu-3/cpu-idle',8,0,0,0),('localhost','cpu-3/cpu-wait',8,0,0,0), ('localhost','cpu-3/cpu-interrupt',8,0,0,0),('localhost','cpu-3/cpu-softirq',8,0,0,0), ('localhost','cpu-3/cpu-steal',8,0,0,0),('localhost','cpu-4/cpu-user',8,0,0,0), ('localhost','cpu-4/cpu-nice',8,0,0,0),('localhost','cpu-4/cpu-system',8,0,0,0), ('localhost','cpu-4/cpu-idle',8,0,0,0),('localhost','cpu-4/cpu-wait',8,0,0,0), ('localhost','cpu-4/cpu-interrupt',8,0,0,0),('localhost','cpu-4/cpu-softirq',8,0,0,0), ('localhost','cpu-4/cpu-steal',8,0,0,0),('localhost','cpu-3/cpu-nice',8,0,0,0), ('localhost','cpu-3/cpu-system',8,0,0,0),('localhost','cpu-3/cpu-idle',8,0,0,0), ('localhost','cpu-3/cpu-wait',8,0,0,0),('localhost','cpu-3/cpu-interrupt',8,0,0,0), ('localhost','cpu-3/cpu-softirq',8,0,0,0),('localhost' *** (2) holds lock(s): record locks space id 0 page no 344 n bits 488 index `unique-metric` of table  `shm`.`metric` trx id 72d7 lock_mode x *** (2) waiting lock granted: record locks space id 0 page no 344 n bits 504 index `unique-metric` of table  `shm`.`metric` trx id 72d7 lock_mode x locks gap before rec insert intention waiting *** roll transaction (1)

shouldn't retried instead? there way verify percona retried query 10000 times?

don't have exact answer question, write-intensive loads (if you try insert same data damn drupal does), deadlocks happen, , solution me (still waiting confirm 100% ok solution)- use haproxy in front of galera nodes, , define first node (haproxy backend definition) used, , other 2 nodes used backup.

this way mysql traffic flow clients, via haproxy single galera node, , if node fails, other node used.

hope helps... andrija


Comments

Post a Comment

Popular posts from this blog

windows - Single EXE to Install Python Standalone Executable for Easy Distribution -

-
i used pyinstaller create standalone exe file pyqt project. did not use single file option because exe created single file option takes long open. therefore, pyinstaller created dist folder contains bunch of files including program.exe file. create setup.exe file place contents of dist folder in program directory , create shortcut in start menu , on desktop. want super simple user. maybe setup.exe files when download program cnet. found inno-setup, looks promising. however, not know if there special considerations because program standalone program create python/pyqt program. thanks! have experience task? there program using task not know about? inno-setup or nsis easiest use. tell them files include , put them , create nice installer you. wrote short tutorial on experiences using innosetup might find helpful: http://www.blog.pythonlibrary.org/2008/08/27/packaging-wxpymail-for-distribution/ note tutorial based around wxpython app wrote, concepts same.
Read more

c# - Access objects in UserControl from MainWindow in WPF -

-
i have simple xaml, how can change text property of textblock in usercontrol1 mainwindow ? <window x:class="refactorxaml.mainwindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:refactorxaml="clr-namespace:refactorxaml" title="mainwindow" height="350" width="525"> <grid> <grid.rowdefinitions> <rowdefinition height="100"></rowdefinition> <rowdefinition height="100"></rowdefinition> </grid.rowdefinitions> <grid> <refactorxaml:usercontrol1></refactorxaml:usercontrol1> </grid> <grid grid.row="1"> <button>change text</button> </grid> </grid> </window> <usercon...
Read more

javascript - How to name a jQuery function to make a browser's back button work? -

-
i'm trying write function ajaxyfy web site, works perfect, except function button. that's have now: function loading() { if (typeof history.pushstate !== "undefined") { var historycount = 0; $('.menu-item, .logo').on('click','a',function(e){ e.preventdefault(); $(this).data('clicked', true); if ($(this).is('.logo a')) { var homepage = $(this).attr('href'); function1(homepage); history.pushstate(null, null, homepage); } else if ($(this).is('.projects a')) { var projects = $(this).attr('href'); function2(projects); history.pushstate(null, null, projects); } else { var pages = $(this).attr('href'); function3(pages); history.pushstate(null, null, pages); ...
Read more