… critical activities are overloading the system causing user experience to degrade. The first thing you need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the… by query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL. Now… pressure caused by increase in flushing and checkpointing activity. This caused spikes in write activity to the point that the insert rate…
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
… follow: Transaction1: root@localhost:test 14:33:38>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost:test… as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables in use 1, locked…(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 14 Comments
… exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(31); Query OK, 1 row affected (0.00 sec) After running the… 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: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… in ACTIVE state. Especially ACTIVE transactions which do not currently run any query are suspicious. There are however cases when screaming fire about ACTIVE… 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: Reasons for MySQL Replication Lag
July 29, 2011 By 6 Comments
… doing RAID validation, some centrally kicked of SAN backup, intensive activity on the other tenants sharing physical hardware with you in… be even system which queries mysql. I’ve seen monitoring updates which would add excessively expensive and frequent query on information_schema which…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… does for example). Query Cache is also one of the few cache implementations which I’d call an Active Cache meaning it… as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in…
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… generate quality control queries automatically, we can run them at any time: $ cat generate_foreign_key_checks.sql | mysql -N | mysql -E *************************** 1… same SQL queries for quality control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO…
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_…output 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-…
