… of node2) through Xtrabackup SST (state snapshot transfer). Node3 subsequently joins the cluster with node1 and node2 in its gcomm:// (since… restarted (and there are others when it comes up) it joins the cluster seamlessly. I would like to note here that… SST is paramount. We recommend Xtrabackup SST for its reduced locking period from its use (which means the donor is blocked…
Post: Percona XtraDB Cluster (PXC) in the real world: Share your use cases!
June 17, 2013 By Leave a Comment
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38…(1) as installed_count from client_app_inventory cai inner join device d on (cai.client_id = d.client_id) inner join label_to_device ld on (ld.device_id = d.id) …
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… performs better when a larger number of rows must be joined together (Q4.1, Q4.2) but worse when small amounts…=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…_groups table to define the relationship between them. When someone joins a group, you run a transaction that adds the relationship… will take the 1 RTT). The other transactions will lock wait until the lock(s) they need are available. The application just… so often? INSERT INTO users_groups (user_id, group_id, joined) VALUES (100, 1, NOW()); Conclusion Choosing a system to replicate…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
… for the lock Before the FLUSH TABLES WITH READ LOCK command can successfully acquire the lock, anything else that currently holds the lock must… | 80 | Sending data | select count(*) from t t1 join t t2 join t t3 join t t4 where t1.b=0 | | 5 | root | localhost | test | Query | 62 | Flushing tables | flush tables with read lock…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… joins the table so we observe 2 table instances (note – same table gets counted twice) in use but zero tables are locked… locked explicitly Innodb “converts” table lock to “no lock” hence eliminating conflicts on MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table Level locks…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement… you can see FLUSH TABLES WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse… reason (other than code design) for read lock to wait for other read lock in order to be set. As a workaround…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock…idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… counter values called “Traditional”. This one uses a special table lock called AUTO-INC that remains until the end of the… problem. A “mutex” table is a clever trick that allows joining tables while keeping them independent of each other in a…` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…_event_wait_low,sync_array_wait_event,rw_lock_s_lock_spin,pfs_rw_lock_s_lock_fu nc,btr_search_build_page_hash…_RANGE_SELECT::get _next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute…_event_wait_low,sync_array_wait_event,rw_lock_x_lock_func,pfs_rw_lock_x_lock_fu nc,btr_search_build_page_hash…
