One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… 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…
Post: Testing STEC SSD MACH16 200GB SLC
April 25, 2012 By 7 Comments
… use sysbench fileio, 16KiB block size (to match workload from InnoDB, as this is primary usage for me), and recently I… async IO mode. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database load, and…/sec, which I guess is related to internal garbage collector activity. The results for random read case: Very stable throughput on…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB options innodb… 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
… SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables… lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929, query id 484616 localhost root update insert…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL… need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host | db | … just using Innodb tables and you’re not actively changing users…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… look at SHOW ENGINE INNODB STATUS to see if there are any transactions spending very long time in ACTIVE state. In the… is when you have long enough ACTIVE transactions which have some locks, like in this case: MySQL thread id 2059, query id… log entries 1 MySQL thread id 1720, query id 1011283 10.10.10.10 user —TRANSACTION 2 1418397881, ACTIVE 317 sec, process…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
… MySQL 5.1 this is done by the master InnoDB thread. Since then, InnoDB has been moving towards the parallelized purge: in MySQL 5… activity could be throttled better as to not to penalize further TPS. MySQL 5.6.2 Results Now let’s test MySQL 5… post suggests a fix: in 5.6.2 the purge activity is interspersed with short sleeps and apparently those sleep delays…
Post: Product to try: MySQL/MariaDB-Galera 0.8
June 13, 2011 By 34 Comments
… be in an acceptable range. It opens the possibility for active master – active master setups. You can write on both masters and… testing MySQL/Galera 0.8, binaries are available from Launchpad. The downside of MySQL/Galera is that it is based on standard InnoDB 5.1 instead of InnoDB-plugin. Standard InnoDB is seriously behind InnoDB-plugin…
Page: Consulting for MySQL
July 31, 2006 By
… using MySQL Replication, Active-Passive clustering, DRBD, MySQL Cluster, Load balancing and other tools. MySQL Replication Advanced MySQL Replication …Innodb Storage Engine Running transaction applications with MySQL ? Need help with deadlocks, understanding how Innodb locking works or having Innodb…
