context ... I had proposed writing a new backend for mysql tailored to read only very large and very wide (lots of columns) tables (14G + ) in which queries never involved more than a small percentage of the available columns (i.e. < 5%) and often lacked WHERE clauses.
A couple years ago I argued against the file per field disk layout on the belief that when the # and size of these files were too large that head movement back and forth would become the dominant time consumer and efficiency would drop. In practice though we were used to seeing the file per field theory perform roughly 10 times faster than an equivalent SQL database. Recently I became aware that on our largest table, detailed natality, query times had gone up radically half a year ago and the individual in charge had failed to determine what had gone wrong. I now believe that the performance was wrecked when the hard drive was defragmented. Originally the file per field files were written out en masse by a java program, but that would have left the resultant files fragmented, but fragmented in such a way that the head movement was minimized during a query. When the hard drive was defragged suddenly jumping back and forth between the different column files forced excessive head movement and performance went south. I still believe this technology has a lot to offer, but now am proposing that the columns be written as a single file and with a pseudo internal fragmentation. For col a, b and c, .. a1, a2, a3... aN, b1, b2, b3 ... bN, c1, c2, c3, ... CN aN+1, ..., bN+1, ... CN+1 ... and then we have the factor of 10 improvement over the usual sql table layout for the very particular query pattern that this is designed to solve. (note ... N needs to be determined after studying OS/controller/harddrive functions to minimize head seek) ANYWAY, .... wanted to make sure this hit the list before someone tried to experiment with a file per field approach. -- Heitzso <[EMAIL PROTECTED]> MetaMedia, Inc. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
