… used: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive…pool instances to reduce contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… Google Patches II, where InnoDB file writes can be altogether disabled using a global variable setting? For InnoDB based databases, this allows… not aware of? Would such a feature introduce a global mutex on which all writes would need to serialize or anything?
Post: Internals of InnoDB mutexes
September 7, 2006 By 4 Comments
InnoDB uses its own mutexes and read-write locks instead of POSIX-mutexes pthread_mutex*, the main reason for that is performance, but InnoDB‘s implementation isn’t ideal and on modern SMP boxes can cause serious performance problems. Let’s look on InnoDB mutex (schematic for simplification): spin_loop: for (i=0; i< innodb_spin_locks;i++) if (mutex_try_lock()) return…
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…_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: InnoDB benchmarks
January 3, 2007 By 15 Comments
… of InnoDB degraded dramatically. The problem was in the mutex impelementation and was fixed in 5.1.12 (more info about InnoDB mutexes) Scalabilty…-innodb and next startup options were used: libexec/mysqld –no-defaults –user=root –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… was a popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has an… MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread… scale good enough. Perhaps there is still a problem with InnoDB mutexes. READ_KEY_POINT_NO_DATA Query: SELECT state_id FROM…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… version (pre-5.1.61) and a single test, innodb_plugin.innodb_bug53674. The test is chosen for no particular reason except… (srv0start.c:1514) ==9090== by 0x6B48855: innobase_init(void*) (ha_innodb.cc:2284) ==9090== by 0x712F17: ha_initialize_handlerton(st_plugin… is between the reads and writes of mutex_t::waiters in mutex_get_waiters and mutex_set_waiters. Interestingly, there are comments next…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… integer value: create table mutex( i int not null primary key ); insert into mutex(i) values (1); Our InnoDB table with auto increment…` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer join…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No gaps at all…
Post: Understand InnoDB spin waits, win a Percona Live ticket
September 2, 2011 By Leave a Comment
… INNODB STATUS mean? Mutex spin waits 5870888, rounds 19812448, OS waits 375285 To understand this text, you have to understand how InnoDB handles mutexes. It tries a two-step approach to getting a lock on a mutex. First, a thread tries to lock the mutex… INNODB STATUS. Here’s what it means. Mutex spin waits 5870888 is the number of times a thread tried to get a mutex…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… printed about wait. “lock var” is current value for the mutex object (locked=1/free=0) , “waiters flag” is current number… tickets to use. Innodb tries to limit thread concurrency allowing only innodb_thread_concurrency threads to run inside Innodb kernel at the same… thread is not runniing inside innodb kernel status could be “waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is…
