… by a PK UPDATE statement in each thread in a loop, which is probably a bit closer to real life workloads…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… to hang. This was happening due to prepared statement execution loop, which does not honor wsrep status codes correctly. Bug fixed…
Post: Infinite Replication Loop
October 10, 2011 By 2 Comments
… other master should also replicate events : Closer look: Infinite Replication Loop flow: If you have master-master with slaves or if… 1. How to identify an infinite replication loop ? When you have an infinite replication loop, the value of Seconds_behind_master will… master. Then wait until the passive master has executed the looping statements and pick up a position from SHOW MASTER STATUS…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
… InnoDB starts the transaction, usually there is loop through ALL active transactions, and this loop is inside kernel_mutex. That is to… complex handling with spin loops, and there are two variables that affects mutex loops: innodb_sync_spin_loops and innodb_spin_wait_delay… 200. So there is final results with innodb_sync_spin_loops=200 for 1-1024 threads. Threads Throughput Throughput spin 200…
Comment: MySQL Stored Procedures problems and use practices
September 23, 2009 By 21 Comments
… name from a cursor loop in an update statement within said loop … in a trigger? OPEN cursor1; cursor_loop:LOOP FETCH cursor1 INTO l…_name; IF l_last_row_fetched=1 THEN LEAVE cursor_loop; END IF; UPDATE l_customer_name SET active = 1 WHERE customer_id = NEW.id; END LOOP cursor_loop; CLOSE cursor1; right now, when i run this, i get…
Post: Internals of InnoDB mutexes
September 7, 2006 By 4 Comments
…. Let’s look on InnoDB mutex (schematic for simplification): spin_loop: for (i=0; i< innodb_spin_locks;i++) if (mutex_try_lock()) return; // success // we are here if spin loop was not successful reserve_cell in array of cell; wait… variable innodb_sync_spin_loops (default value is 20) There we have: 1. Spin-loop – InnoDB uses spin-loop in hopes thread locked…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… the Classic Hash Join. But its known as Block Nested Loop Hash (BNLH) Join in MariaDB. The Classic Hash Join Algorithm… buffer pool size of 6G. The benchmarks compare Block Nested Loop (BNL) Join of MySQL 5.5.24, Batched Key Access… the right-side table becomes costly in comparison. Block Nested Loop Join would perform better than Hash Join when you are…
Comment: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
… wakes it comes back to SPIN LOOP Simplifying, InnoDB mutex looks like: mutex_enter(): { spin_loop: from i:=1 to innodb_sync_spin_loops: try acquire mutex; i++; sleep(rand_time); if mutex is not granted: pthread_cond_wait() goto: spin_loop; } mutex_exit: { pthread_cond_broadcast() } That is all threads in…
Comment: Understand InnoDB spin waits, win a Percona Live ticket
October 24, 2012 By 2 Comments
… a spin-wait. Rounds: is the number of times threads looped in the spin-wait cycle, checking the mutex. Then you… was checked and it was locked? Moreover, every how many Loops the mutex is checked? If my interpretation of Round is correct and using your numbers: In 19812448 Loops , 5870888 times the mutex was found locked. So the mutex…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means… has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join… Hash Join as compared to Nested Loop Join, but for now it looks like Nested Loop Join is a much better general…
