… happened – for XFS file system on a raw partition I was using full partition size which was slightly over 1TB in size… were placed in this case). When I have mounted the partition with inode64 option however, the effect disappeared and performance without… performance with different IO alignment, not different MySQL configurations, I didn’t try out different MySQL versions or settings. Moreover, I have…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… are still relevant to current versions of MySQL. Q: How do the upcoming changes in MySQL 5.6 change the best practices… use every column in the table’s partitioning expression.” It might even be impossible to partition a table while using UNIQUE KEY… will be the same with a partitioned table as with a non-partitioned table. But since each partition is physically stored as a…
Post: Aligning IO on a hard disk RAID – the Theory
June 9, 2011 By 18 Comments
… –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…) Disk /dev/sda: 2339840000s Sector size (logical/physical): 512B/512B Partition Table: msdos Number Start End Size Type File system Flags…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… that seem “less advanced” than using a database. Sharding and Partitioning Sharding is inevitable if your write workload exceeds the capacity… change the lifecycle of your application in advance. What about partitioning in MySQL 5.1? I know there are some cases… There are already inconvenient limitations on some key parts of partitioning, such as maintenance and repair commands, that essentially negate the…
Post: Reading MySQL Enterprise future...
December 9, 2008 By 13 Comments
…. For MySQL Enterprise Server – Advanced Edition: %define PARTITION_BUILD 1 %define INNODB_BUILD 1 For MySQL Enterprise Server – Pro Edition: %define PARTITION_BUILD 0 %define INNODB_BUILD 1 So this one comes without Partitions. And MySQL Enterprise Server – Classic Edition %define PARTITION_BUILD 1…
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 17 Comments
…_buffer_pool_instances=8 was very important. Second thing – Partitioning. Unfortunately MySQL 5.5 leaves the huge bottleneck for write workloads in… would expect gains can be higher with more cores. PARTITION BY HASH(col) PARTITIONS 8 is what we used. This looks like a good workaround but remember partitioning can impact performance of your select queries dramatically. The inserts…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
… test to use the mysql server: [root@percona1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is…-2491595e99bb,2) memb { 86928728-d56d-11e1-0800-f7c4916d8330, } joined { } left { } partitioned { 7e6d285b-d56d-11e1-0800-2491595e99bb, }) 120724 10:58:09 [Note…
Post: Make your file system error resilient
August 24, 2011 By 8 Comments
… not needed for partition you store MySQL data on, as chances are MySQL server is only one doing writes on this partition anyway it… once per so many mounts or so many days. Large partitions with many files can take a lot of time to… for your data partition and just check it manually as needed, same as you would every so often check MySQL tables for…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… data upon which we will operate: create table words2 partition by hash(bucket) partitions 12 as select id % 6 bucket, chars words… the serial version as run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2… it were smarter it would ask the data dictionary about partitioning. Array ( [word] => Congress: [md5(word)] => 873c419d2c2139bc8bbc3cbaffcc3473 [md5(reverse(word))] => ff592a4dac2aa93c8a0589898885fe48…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… Shard-Query works on sets, and features pluggable partition mapping, it allows partitioning resources to any depth and distributing a query over… levels of result set reduction. Finally, Shard-Query can automatically partition sets into subsets using basic relational algebra substitution rules that… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the…
