… 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 5 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 …
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 20 Comments
… fetched in memory as fast as possible to get good in memory access performance… rate or you would see MySQL becoming CPU bound if IO subsystem… both fragmentation issues is the same – OPTIMIZE TABLE tbl – this command recreates …and also it really only defragments clustered key but not the index. …
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…have been implemented outside of MySQL Storage Engine level for MySQL – reading rows in their …optimized for Innodb which does have clustering, this is why I have it with Minus sign. In my opinion Optional clustering…but also means you should have enough memory to hold all transaction changes. I agree…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…a secondary index reserves different extents from the clustered index. Q4: Does Innodb ever merges sequential… HT: No PZ: Another possible area of optimization. I frequently see batch jobs killing server… the system’s memory the kernel can decide (incorrectly) to swap out MySQL. There are two …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… in clustered key index is corrupted. It is worse compared to having data corrupted in secondary indexes, in which case simple OPTIMIZE…): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg_text | +———–+———-+———-+———————————-+ | test.test | optimize | error | Got error -1 from storage engine | | test.test | optimize…)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get…
Post: KISS KISS KISS
March 1, 2009 By 7 Comments
… away. I remember LiveJournal with 4GB of memory per box doing sharding 5 years ago…. … with single “cluster” even on the current hardware for several years. Replication Optimization People often get … the application is only related to the MySQL redundancy for very high quality/high scale …
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… separate problem which can be fixed in MySQL 5.1 Data Clustering This again applies to Innodb tables … lookups it should rather close for data in memory case or data on disk case with auto_… binary compression. Plus they should be stored in optimized sort order to minimize how random they are – …
