We (Centers for Disease Control and Prevention) want to mount relatively large read only tables that rarely change on modest ($10K ??) hardware and get < 10 second response time. For instance, 10 years of detailed natality records for the United States in which each record has some 200 fields and, in mysql myisam table, is 14G in size. Queries will typically hit 6 to 10 fields from the table and while some queries will have a WHERE clause that can take advantage of a YEAR or GEOGRAPHY index, most won't.
Hardware side even a 320 LVD SCSI raid sitting on a 64/66 PCI bus is limited to some 300 MB/sec and a more common 160 SCSI raid is limited to 32/33 PCI bus speed of approx 100+ MB/sec. And the problem becomes one of moving a whole table over the bus in X time. Assuming 300 MB/sec and a 10 second query we're limited to a 3G table. Allowing the query response time to bump up to 30 seconds and we hit a 9G table limit. But our detailed natality table is 14G. Some design options: - do the typical data mining thing and preaggregate or subset the main table into a thousand or more subtables and write an intelligent filter that sits in front of these tables to decide which to reroute the query to ... - use what Census calls a transverse server design and write out each field in the table to its own file - use mysql compression on myisam table -- I've tried this and the process becomes horribly CPU bound when scanning through an entire 14G uncompressed, 2.2G compressed, table with little if any (and typically a loss) advantage time wise While the file per field layout is not optimal for the general case, in our situation it dramatically decreases the physical bus limitation because it is rare for more than a small hand full of fields to be involved in a SELECT request. ... and our data is strictly read only and is updated once a year. The actual numbers work out to: 10 fields max in a query is 1/20th the overall record size 1/20 * 14G = approx 700M 700M can be scanned in 7 seconds at 100MB/s or < 3 sec @ 300MB/s If we do a light weight compression to avoid being CPU bound but to keep the bottleneck on the IO side the figures improve even more. Because mySQL already has several back ends I'm assuming that writing this backend for mySQL is possible. The one key is knowing at the beginning of the rip through the table what fields are needed by the query engine, then only opening up those files (to avoid hitting the 200 files open problem). Thoughts? Suggestions? Anyone else out there willing to work with us on this one? Of course the back end would be contributed back, etc. [[ other notes, data is denormalized, but typical field is one or two bytes of some code, i.e. '0', '1' for gender, so normalizing and joining back doesn't make sense at all ]] -- 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
