…MySQL 5.6.10, both with default settings using only the InnoDB storage engine. In my testing I discovered that innodb_old_blocks_time…innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
…MySQL world, except maybe MyISAM files on top of a clustered filesystem with an external lock manager. You might say that MySQL… try to install MySQL. It failed miserably on the mysql_install_db process. Every time I tried,…-grant-tables and insert a few rows into an InnoDB table, but trying to run a…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…Innodb node we can have all sorts modifications floating around as long as they don’t touch the same row. Row locks… change in Innodb at a time is a single row, so the most any single row can be… we take when deploying conventional stand-alone MySQL Innodb with asynchronous slaves. We may not think …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, …Innodb_row_lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log_fsyncs for … median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 8338s 1us 284s 5ms 13ms 298ms 185us # Lock time 71s 0 3ms 39us 54us 28us 35us # Rows sent …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably … to MySQL 5.1.22 InnoDB used a method to access that counter values called “Traditional”. This one uses a special table lock… row affected (0.00 sec) Insert the same value multiple times. As you will see, the INSERT is ignored and no rows are…
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…, OS thread id 1224980800 3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1 MySQL thread id 1720, query… lock struct(s), heap size 368, 1 row lock(s), undo log entries 2 In this case we see transactions are holding some locks…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
…_errno: 0 Killed: 0 # Query_time: 12.277786 Lock_time: 0.000068 Rows_sent: 294 Rows_examined: 294 Rows_affected: 0 Rows_read: 294 # Bytes_sent: 34187… Server with the variable innodb_stats_auto_update (from MySQL Community 5.1.17 a similar variable exist called innodb_stats_on_metadata…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 3396, OS thread id 1150142816 fetching rows, thread declared inside InnoDB 166 mysql tables in use 1, locked 0 MySQL thread id 8078, query id… 3396, OS thread id 1147980128 fetching rows, thread declared inside InnoDB 114 mysql tables in use 1, locked 0 MySQL thread id 8077, query id… many threads competing to enter innodb queue at the same time Innodb makes thread to sleep for some time before trying to wait (if…
