…found: The fragmentation we speak in this article is filesystem fragmentation or internal table fragmentation which affects performance of full table scan….learn from these results – Concurrent growth of many tables causes data fragmentation and affects table scan performance nadly – MyISAM suffers …
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… … well table locks was the problem at very early stage but it was solved by using this significant number of tables. I still… not block anything” – it still may cause total block if there are long selects running. Fragmentation. This is common problem for pretty… causing unexpected slowdowns. delay_key_writes Whatever way you enable this option – globally, for table or for set of statements (by using LOCK TABLES…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… we switch to Oracle? HT: You are right, bugs that cause data corruption, crashes, or wrong query results get the highest… in contiguous blocks of 64 pages == 1 MB. Then a full table scan would be fast. Maybe the table has become fragmented? Or maybe you have not inserted in the ascending order of the…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… is now 64MB instead of 8MB which should help to reduce fragmentation and make file growth…files_limit was also raised. Unlikely to cause significant gains unless opening and closing files…of tables or if you do many create/drop of innodb tables. optimizer_switch is the catch all variable for a lot of…
Comment: Learning about MySQL Table Fragmentation
May 12, 2008 By 20 Comments
Peter, Good catch though it does not have anything to do with table fragmentation. Simply if you’re querying number of rows in table for Innodb it performs estimation – this estimation requires several random dives in the clustered index, which causes IO for large table sizes.
Post: Make your file system error resilient
August 24, 2011 By 8 Comments
…Block size: 4096 Fragment size: 4096 Reserved GDT blocks: 316 Blocks per group: 32768 Fragments per group:…looking at hardware issues which can cause all kind of other nasty problems especially for database server… you would every so often check MySQL tables for corruption. To change those options you…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…leaf nodes. The first 32 pages of a segment are allocated from ‘fragment extents’ where any individual … overtaking buffer pool. Though full table scan is only one of replacement policy optimizations possible. Q8: … whole 16 kB pages. That will cause a lot of waste of space if the fields are less …
Comment: Full table scan vs full index scan performance
November 24, 2012 By 15 Comments
… table may be fragmented so full table scan will not be sequential, yet same is true for MyISAM which over time will get fragmented rows. Freshly created Innodb table will most likely result in a lot of sequential reads. Though even in the most fragmented case… causing up to 500 different pages to be fetched. For row size of 100 you might be looking at 10-100 times of…
Post: Why you don't want to shard.
August 6, 2009 By 34 Comments
…sense, but in many we’ve seen that it causes architectures to be prematurely complicated. So let me …work for most cases unless you have a single table which by itself can’t fit into the …is to have shards all store fragments of data, and to cross backup those fragments across shards.) Why is it so…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… for small ranges. Q: In the case of a junction table, would indexing on (foreignkey1,foreignkey2) AND on … cause data clustering done by these columns which can be helpful if you’re doing a lot of… down your inserts and make primary key significantly fragmented. I also would note there are some MySQL…
