…” method reduces the impact of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that, I have also… read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and… pool instances to reduce contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush…
Post: Internals of InnoDB mutexes
September 7, 2006 By 4 Comments
… 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…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
…. Meantime the problem with kernel_mutex is raising, I had three customer problems related to performance drops during the last month. So…_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: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… 40% of effective CPU time is spent in pthread_mutex_lock / pthread_mutex_unlock. We could not get oprofile call tree to… t1 into InnoDB, and results: Threads Queries/sec 1 296 2 341 4 544 8 493 16 498 InnoDB both performs much better… can see Falcon perform extremely poorly when single query executed being 1/3rd of MyISAM and 1/6th of Innodb. On other…
Post: InnoDB benchmarks
January 3, 2007 By 15 Comments
… and also compare overall performance of InnoDB in 5.0 and 5.1 before and after fixes. Problems in InnoDB that were fixed: Thread trashing issues with count of theads 100+. In this case performance of InnoDB degraded dramatically. The problem was in the mutex impelementation and was fixed in 5.1.12 (more info about InnoDB mutexes…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… and performance parameters may vary a lot in next releases) Compilation parameters: For MyISAM / InnoDB ./configure –prefix=/usr/local/mysqltest/mysql- –with-innodb… we see bad performance. We hope the performance of LIMIT queries will be fixed before release. MyISAM shows stable result. InnoDB is better… scale good enough. Perhaps there is still a problem with InnoDB mutexes. READ_KEY_POINT_NO_DATA Query: SELECT state_id FROM…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… a light-weight mutex instead of a table lock on AUTO-INC. We have recovered the concurrency and the performance but with… the traditional method changing the innodb_autoinc_lock_mode to 0. But with a loss of performance and concurrency. How can I… integer value: create table mutex( i int not null primary key ); insert into mutex(i) values (1); Our InnoDB table with auto increment…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance counters… printed about wait. “lock var” is current value for the mutex object (locked=1/free=0) , “waiters flag” is current number…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…-5.1.61) and a single test, innodb_plugin.innodb_bug53674. The test is chosen for no …the reads and writes of mutex_t::waiters in mutex_get_waiters and mutex_set_waiters. Interestingly, there …memory after the reset. A speculative processor might perform the read first, which could leave a waiting…
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… you like to come hear Peter Zaitsev talk about InnoDB architecture and performance optimization in London on October 24th? Well, you can…
