… Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… inside InnoDB 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: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication… the relationship between them. When someone joins a group, you run a transaction …1 RTT). The other transactions will lock wait until the lock(s) they need are available….
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… locks or auto increment table level lock (mostly with MySQL 5.0 and older MySQL versions). MySQL level locks are entirely different story. Explicitly locking tables on MySQL…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the …lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock…interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual…
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…_quick,sub_select,do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command…_quick,sub_select,do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…_result(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql… kind of error: lock order violation, thus a potential deadlock. The locks in question are LOCK_global_system_variables and LOCK_system_variables_hash…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…: sbtest Last_errno: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows… which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero… this case we actually join 2 tables but because the access type to the tables is “const” MySQL does not count it…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for… at what the problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you… you can see FLUSH TABLES WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse…
