… when these tables were small and first created to use auto-inc primary key. At the time (over 2 years ago), I… these tables get parallel inserts, I’m getting lots of auto-inc waits. Now that these tables are huge, I’ve got a… PC software. If this works, then I can push the auto-inc‘ing up into my application and relieve MySQL of this…
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… being… many of which SHOW INNODB STATUS showed as waiting on famous Innodb AUTO-INC lock. That would be an easy excuse to… busy it is mostly selects which were stuck waiting. The next symptom was AUTO-INC lock which again was a false one – because single insert slows down a lot of inserts pile up waiting on the AUTO-INC…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto_inc; Compare that to Percona Server with log_slow_verbosity=full…_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to complete. If you’re unlucky… the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes… of: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
… –num-threads=32 –init-rng=on –max-requests=0 –oltp-auto-inc=off –max-time=3000 run Results become as follows: Summary….00 sec) When this option is enabled the cluster will wait for the data to be actually replicated (committed) before serving… as follows: root@dpe01:/etc/mysql# sysbench –test=oltp –oltp-auto-inc=off –oltp-test-mode=nontrx –oltp-nontrx-mode=update_key…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… in 75 pct cases) Using “BEGIN” for starting transactions Using auto_inc on the id column Threads started! Screen1: root@localhost:/var…. For that we have to stop any further writes and wait until InnoDB flushes all pages. To stop write activity we… i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads…
