… MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB options innodb… pool instances to reduce contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
Problem with kernel_mutex in MySQL 5.1 and MySQL 5.5 is known: Bug report. In fact in MySQL 5.6 there are…. 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: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… on MySQL server. Let’s take a recent 5.1 bzr version (pre-5.1.61) and a single test, innodb_plugin.innodb…: innobase_start_or_create_for_mysql (srv0start.c:1514) ==9090== by 0x6B48855: innobase_init(void*) (ha_innodb.cc:2284) ==9090== by 0x712F17… 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
Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably … the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that counter values called… integer value: create table mutex( i int not null primary key ); insert into mutex(i) values (1); Our InnoDB table with auto increment…
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… has ended. Don’t miss your opportunity to learn about MySQL in London from world-famous experts!
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
Post: InnoDB compression woes
May 20, 2011 By 14 Comments
…=16 –max-requests=0 –rand-type=uniform –max-time=1800 –mysql-user=root –report-interval=10 run this will report us… mutex serialization problem. Analyzing SHOW INNODB STATUS (SEMAPHORES) for workload with compression tables we can see 1 Mutex at 0xe13880 ‘&buf_pool_zip_mutex‘ 14 Mutex at 0xe13780…
Post: InnoDB, InnoDB-plugin vs XtraDB on fast storage
January 13, 2010 By 12 Comments
… wanted to check how MySQL / InnoDB performs here. For benchmark I took MySQL 5.1.42 with built-in InnoDB, InnoDB-plugin 1.0.6… room for improvements. As for InnoDB-plugin, I’d wish InnoDB team makes some actions on buffer_pool mutex problem. Finally I wanted… ( about 88% worse than all files on FusionIO) To summarize MySQL InnoDB/InnoDB-plugin/XtraDB is not fully able to utilize throughput of…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… the problem still existed. The Innodb Table Monitor has existed for basically forever in Innodb (the MySQL manual discusses it back in the… the Data dictionary to the mysql error log, which looks something like this: =========================================== 090420 12:09:32 INNODB TABLE MONITOR OUTPUT =========================================== ————————————– TABLE… Tables’ were waiting for the dict_sys->mutex, which is required for opening tables in Innodb. It would be better for the…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_…event_wait_low,sync_array_wait_event,mutex_spin_wait,mutex_enter_func,pfs_mute x_enter_…things a lot worse in this workload and innodb_adaptive_hash_index_partitions=16 available for …
