…query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL…-axis. The reason why I have chosen to show “millions of rows” on the x-axis so… by increase in flushing and checkpointing activity. This caused spikes in write activity to the point that the…
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… follow: Transaction1: root@localhost:test 14:33:38>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost:test… SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables…(s), heap size 1216, 1 row lock(s) MySQL thread id 31929, query id 484616 localhost root update insert into t values…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…query blocks any READ queries to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+… and you’re not actively changing users, stored procedures …
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G… from the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot…-flush-method=O_DIRECT innodb-ibuf-active-contract=1 innodb-import-table-from-…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…, OS thread id 1152440672 MySQL thread id 8080, query id 728900 localhost root show innodb status —TRANSACTION 0 80157600, ACTIVE 4 sec, process no… shows activity on the row basics and some system information. It shows innodb thread queue status – how many threads are waiting and being active…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… be very useful. With simple MySQL SHOW command it gives you the basic statistics on the database activity broken down by users, tables…: 254 Other_commands: 85 Commit_transactions: 0 Rollback_transactions: 0 mysql> SHOW TABLE_STATISTICS LIKE ‘hosting2%’; +——————————–+———–+————–+————————-+ | Table | Rows_read | Rows_changed | Rows… as: list users by the number of queries issued or any other information find active tables which do not have any indexes…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… disk saturated? Is the machine swapping actively? (This is more important than …tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value …queries, in total execution time. By the way, the slow query logs in the stock MySQL…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… always look at SHOW ENGINE INNODB STATUS to see if there are any transactions spending very long time in ACTIVE state. In… you have long enough ACTIVE transactions which have some locks, like in this case: MySQL thread id 2059, query id 1014425 10.10…), undo log entries 1 MySQL thread id 1720, query id 1011283 10.10.10.10 user —TRANSACTION 2 1418397881, ACTIVE 317 sec, process…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… our database in a strange state where RI constraints are active, but we cannot assume that all our data satisfies RI… run this query and it returns a non-empty result set, it shows us which rows in Bar are orphans. mysql> INSERT INTO… generate quality control queries automatically, we can run them at any time: $ cat generate_foreign_key_checks.sql | mysql -N | mysql -E *************************** 1…
