… objectClass TINYINT UNSIGNED Normalize, if necessary, to shrink the data sizes. RANGE partition on dates — 1 PARTITION per day. Then do… == you are lucky to make it work. How much RAM? RAID? SSD? What is key_buffer_size? Let’s see the actual CREATE TABLE, SHOW…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also note…-memory workload Now let’s see how effective are the join optimizations …much of a difference to in-memory workload. Also BKA relies on both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… information about last failed foreign key constraint: ———————— LATEST FOREIGN KEY ERROR ———————— 060717 4:29:00… been flushed – so how much data is unflushed in log buffer as well as when …_log_buffer_size is optimal – if you see more than 30% of log buffer size being …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… and how much performance gain there might be. HT: I guess the log writes themselves are much more a…this matter the other day – choosing innodb_buffer_pool_size Q36: There have been several MySQL bugs opened…inserted in an ascending order of the PRIMARY KEY, then InnoDB should have placed the rows in…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_based=off’ read_rnd_buffer_size=32M optimizer_switch=’batched_key_access=on’ join_buffer_size=32M MariaDB 5.5.20 … wins. But why does Hash Join take so much more time. The reason is that the rows … part of the PK in the second table. How does optimizer work with the different Join Algorithms …
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… bytes will be allocated) Question: How much space InnoDB allocates for each … larger than 16K – is exact size allocated or also segment based allocation… realistic workload. Making the doublewrite buffer bigger than 128 pages would…same order in the foreign key and the referenced key (this same order …
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…cases and how much performance it will give. As “SHOW TABLE STATUS” is not showing index sized in the…Falcon does not cluster data by Primary Key or any other key. This is good for some …the once and twice before Innodb Double Write Buffer was implemented and seeing some pages recovered from…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…are fixing symptoms, not the problem itself. No matter how much you want it to fit, somethings may not … performance. I know you’re thinking my simple key lookup should really benefit from memcached. So let… memcached size of 1GB, 2GB, and 4GB. For these tests I left Innodb with a 256M buffer pool,…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…how can we put this into practice, since this information isn’t available to most users? INSERT w/PRIMARY KEY… used only internally, then it is not so much of an issue and you can leave innodb_…commit = 1 innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb_thread_…
