…ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (800000000) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (900000000) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB… I have chosen to show “millions of rows” …caused by buffer pool mutexes, using “estimate” checkpoint method…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
…. But some theory before benchmarks. InnoDB uses kernel_mutex when it starts/stop transactions, and when InnoDB starts the transaction, usually there is… you may guess, is kernel_mutex. How you can see it ? It is easy. In SHOW ENGINE INNODB STATUS\G you will see a lot…_mutex (and all InnoDB mutexes) has complex handling with spin loops, and there are two variables that affects mutex loops: innodb_sync_spin_loops and innodb…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…`uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer …affected (0.00 sec) Now check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: …
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… (while Selects are running) for MyISAM storage engine. Other Storage engine ether always have it (ie Innodb) or do not support it (HEAP… all storage engines. timed_mutexes Show statistics by mutexes in SHOW MUTEX STATUS. Was intended to work for all storage engines but currently implemented only for Innodb. tmp…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… of LIMIT queries will be fixed before release. MyISAM shows stable result. InnoDB is better than MyISAM by 58% in case with… scale good enough. Perhaps there is still a problem with InnoDB mutexes. READ_KEY_POINT_NO_DATA Query: SELECT state_id FROM… from PK. The results for InnoDB and MyISAM are comparable and I think this shows both engines are maximally optimized and the result…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… you choose), like this: CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB; This, primitively, tells Innodb that it should start the Table monitor… the SHOW PROCESSLIST connections stuck in ‘Opening Tables’ were waiting for the dict_sys->mutex, which is required for opening tables in Innodb…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…show_databasesblogpercona.commanual Com_show_engine_logsblogpercona.commanual Com_show_engine_mutexblogpercona.commanual Com_show_engine_statusblogpercona.commanual Com_show_errorsblogpercona.commanual Com_show_eventsblogpercona.commanual Com_show…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
…, PRIMARY KEY (`id`), KEY `id` (`id`,`stat`) ) ENGINE=MyISAM; CREATE TABLE `t2` ( `id` int(11)… CPU time is spent in pthread_mutex_lock / pthread_mutex_unlock. We could not get oprofile…Innodb. On other hand it scales quite nicely as number of threads increase. The interesting thing is it shows…
Post: Percona Server 5.1.49-rel12.0
September 15, 2010 By 6 Comments
… from MySQL 5.5) show_engines – Changes SHOW STORAGE ENGINES to report XtraDB when…innodb_flush_log_at_trx_commit. (Yasufumi Kinoshita) innodb_fast_index_creation – Allows disabling of fast index creation. (Yasufumi Kinoshita) innodb… of the query cache mutex status in SHOW PROCESSLIST. Bug #606965 …
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…for different levels of concurrency, specifically around mutexes and contention? How does this fit into… established. I have assigned Inaam as the InnoDB scalability engineer. We do not have much resources…or not showed up in “show innodb statusâ€. select * from table where id=5 show innodb status:…
