…) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `uu` (`uu`), ) ENGINE=InnoDB The access pattern to this table is to lookup data… this problem partitioning was a good choice – PARTITION BY HASH (id div 10000000) PARTITIONS 32 – This allows to partition data to 32 partitions placing sequential…
Post: InnoDB, InnoDB-plugin vs XtraDB on fast storage
January 13, 2010 By 13 Comments
… configured in software RAID0 to store InnoDB datafiles. For InnoDB logs and system tablespace I used partition on regular RAID10 with regular hard… for InnoDB-plugin. Here we observe innodb_adaptive_flushing in action (which is ON by default), and I think innodb_adaptive_flushing in InnoDB…. You may want to use InnoDB-plugin or XtraDB to get better results. Putting logs on separate partition may be good idea…
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
… was lost: MySQL had a dedicated partition on XFS file system Server was running innodb_file_per_table There was a production…?) – 10G partition was scanned in like 5 minutes and all 4G of innodb pages were successfully written to a separate partition. That’s the easy part though – you run page parser, wait and see what you get. If InnoDB Data…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… fight with this problem, you may need to partition (manually or using 5.1 partitions) table with big index into couple smaller… key select queries and write queries at the same time. InnoDB again uses single global mutex for adaptive_search (single mutex… variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to perform much more operations reading secondary keys. How…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL… handled on storage engine level making them not working for partitioned tables as well as making foreign keys performed row by…_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO parent(parent_id) VALUES(1…
Post: Percona Testing: Innodb crash / recovery tests available
December 21, 2011 By Leave a Comment
Not everyone may know this, but there are precious few innodb crash recovery tests available. Some folks have noticed this and…). The team at Percona has been known to tinker with Innodb now and again, and were also very interested in having… passing without incident, but I also haven’t dug into partitions or blobs >: ) If you’d like to try these out…
Comment: Using LVM for MySQL Backup and Replication Setup
August 7, 2007 By 53 Comments
This kind of makes it difficult for people using raw InnoDB partitions, huh? 
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
… happened – for XFS file system on a raw partition I was using full partition size which was slightly over 1TB in size…:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 8M innodb_buffer_pool_size = 128M innodb_log_file_size = 64M innodb_log_files…
Post: Aligning IO on a hard disk RAID – the Theory
June 9, 2011 By 18 Comments
… about each of them briefly: InnoDB page You don’t need to do anything to align InnoDB pages – file system takes care… –dataalignmentoffset. If you have set the offset correctly when creating partitions (see below), you don’t need to use –dataalignmentoffset, otherwise… RAID with 256k stripe element size and a misalignment in partition table – partition /dev/sdb1 starts 11 sectors ahead of the stripe…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
…-current) innodb_io_capacity = 4000 innodb_support_xa = false innodb_file_per_table = true innodb_buffer_pool_size = 16G innodb_read_io_threads = 8 innodb_write…) PARTITIONS 16″ Now the performance became to “+partitioned” in the graph. Looking the SEMAPHORES section again, “has waited at handler/ha_innodb.cc…). It may be partition specific lock contention. The current XtraDB has the variable to tune the contention. innodb_stats_update_need_lock…
