… 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, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t…) group by ld.label_id, cai.inventory_id All 4 tables in the query are locked!
Comment: MySQL Performance Forums
June 9, 2013 By 18 Comments
… the resultset is huge, it eventually starts creating a temp table on disk. Now during those 300 second, other ‘innocuous’ queries which perform small insert or updates into completely different table (unrelated to the select query) also get hang up. If… as ‘null’ and Info says ‘commit’ The ‘Table_locks_waited’ remains zero while the ‘Table_locks_immediate’ keeps increasing I have disabled the query…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k… innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… replicate a specific database, a set of databases, or specific tables; you can also configure the slave to replicate everything EXCEPT a specified set of databases and tables. With this method, every event is still written to the… files on top of a clustered filesystem with an external lock manager. You might say that MySQL/NDB Cluster is also…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
… have to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some time to…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… on any node by simply by running “FLUSH TABLES WITH READ LOCK“, or perhaps by “LOCK TABLE“, in which case flow control will kick… easily see flow control if I execute a FLUSH TABLES WITH READ LOCK on node3: [root@node3 ~]# myq_status wsrep Wsrep Cluster… blocked by the underlying SST method, i.e., by FLUSH TABLES WITH READ LOCK.
Comment: Innodb Table Locks
April 8, 2013 By 4 Comments
It’s slightly off topic, but can you elaborate on how to see existing table locks, similar to Postgres’ “pg_locks” command? We have an issue of locking tables, but cannot find which process is causing the lock…tnx
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…. When a player makes an achievement, your application updates this table with a statement like this: UPDATE achievements SET count = count… application. These are maintained in separate tables and there also exists a users_groups table to define the relationship between them. When… will take the 1 RTT). The other transactions will lock wait until the lock(s) they need are available. The application just…
Comment: Implications of Metadata Locking Changes in MySQL 5.5
March 27, 2013 By 9 Comments
… hold any row lock. The point was to show the implications of a transaction not holding metadata locks on tables that have been… that by executing an update query on the table, the transaction holds row locks on row with the id=3, which is… id=3 for update; — session 2 set innodb_lock_wait_timeout=1; alter table t1 engine=innodb; And the alter will timeout…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… mysqld mysqld [.] rw_lock_x_lock_func(rw_lock_t*, unsigned long, char const*, unsigned long) | — rw_lock_x_lock_func(rw_lock_t*, unsigned long…_capacity = 5000 sync_binlog=0 max_connections=5000 table_open_cache=5000 table-definition-cache=1000 query_cache_size=0 query_cache…_schema=0 #56only loose-innodb_flush_neighbors=0 loose-metadata_locks_hash_instances=256 innodb_buffer_pool_instances=16 # MySQL 5…
