… using indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index accesses might be faster… radom IO if index ranges are scanned. There are also clustered keys in Innodb which combine index access with data access… with large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
…Hardware OS And Application optimization. I call this Innodb Performance Optimization Basics so these…MySQL out of memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size 70-80% of memory…can get advantage of Innodb properties – clustering by primary key, having primary key …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool …add index live as it can now do on Cluster ? Is it expected to rebuild index by sort …fields can’t work with in memory temporary table. The other thing – since MySQL 4.1 sorting can be …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…, rows are the worst case for performance. Optimal mathematical performance requires operations on reduced sets. … you have any limitation in resources in a cluster (cpu, memory, disk, power,etc) then split the … which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…. Due to memory requirements, the InnoDB versions are only available on 64 bit instances. MySQL will fail to… throughput over response time, get a good plan optimizer-prune-level=0 partition=ON port=3306 read…-COMMITTED user=mysql wait-timeout=86400 To be continued You can now set up a cluster from 1…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… In Memory storage engines vs Distributed ones. Trying to handle all different cases in Optimizer – such as in memory nature of MEMORY storage engine, clustering…transactions which require a lot of complications and performance overhead. MySQL has to do several fsync() calls per transaction commit to…
Post: Are you designing IO bound or CPU bound application ?
December 20, 2006 By 10 Comments
… for MySQL Performance Optimization. In fact I probably have to touch it in every second MySQL Consulting … database) fits in memory. What is fast when data is in memory can be extremely slow … ORDER BY .. LIMIT. For IO bound applications Clustering (data locality) also becomes very important – if …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… MySQL, you will need to aggregate your data. What you want to do is aggregate in ways that optimize… does this have to do with InnoDB? Data clustering. InnoDB’s primary keys define the physical order …run on many fewer disk drives with much less memory, or even with 10-15x fewer servers. Clever …
Post: Power of MySQL Storage Engines
March 5, 2007 By 3 Comments
…’d find in Oracle, PostgreSQL and other databases. MEMORY storage engine is old timer of this type, … it in simple efficient and innovative way. NDB cluster is yet another example I’d put in … interested in special techniques applying to optimizes certain types of queries with MySQL, it is not easy to…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… function, and treat any size cluster of Turing computers as a … by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(…sec) Since the data fits in memory speed is near constant and … BY NULL ) The other important optimization combines results from multiple queries …
