…without death-by-swapping, which would be extremely slow on this machine, so we need to …INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ———- SEMAPHORES ———- OS WAIT ARRAY INFO: reservation count 39125236, signal count…
Post: My "hot" list for next InnoDB features
March 30, 2009 By 18 Comments
Many InnoDB scalability problems seem fixed in InnoDB-plugin-1.0.3 and I expect InnoDB-plugin will run fine on 16-24… especially bad at start time, when InnoDB takes probes during opening table, as it is slow operation. See also http://www.mysqlperformanceblog… fixed by recent patches by enabling / disabling probes and changing count of probes, but still the solution is far from perfect…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
… very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where clause which… but instantly break upon upgrading to Innodb. Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and…) for logging. Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really…
Post: InnoDB benchmarks
January 3, 2007 By 15 Comments
… 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… comparing 5.1.14 with 5.0.32 For example slow down by 12% in READ_PK_POINT with 1 thread…
Post: Innodb Fuzzy checkpointing woes
May 10, 2006 By Leave a Comment
… being spread over time at certain time Innodb starts to flush pages very agressively seriously slowing down or even almost stalling forÂ…. Here is what is happening. Then planing fuzzy checkpoints Innodb assumes kind of uniform distribution of LSN (Log Sequence Number… IO. One more related problem Innodb has – too many hard coded numbers. For example Innodb will count number of IOs done to…
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 12 Comments
… available. This means insert buffer can slow down read operations. The other way … very surprising. Stats about Innodb Insert Merge Buffer are available in SHOW INNODB STATUS output: ————————————- … a temptation to use this number as count of unmerged rows in insert buffer …
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: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… test; SET timestamp=1325145746; select count(*) from auto_inc; Compare that to Percona Server with log_slow_verbosity=full: # Time: 111229 3…: 0.001414 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto… memory consuming, so ideally you would want to download the “slow query log” to another machine and run it there. Analyzing…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… than data and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated runs test…_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb_buffer…_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb_buffer…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… index the table I get the following record in the slow query log: # Time: 120911 17:18:05 # User@Host: root…_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F14 SET timestamp=1347398285; select * from sbtest where…_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only…
