The schema in question needs a redesign (dynamic row format, contains two BLOB columns) but I was wondering if anyone has written up some guidelines for general data warehouse configuration of MySQL 4.0 -- Google has not turned up anything useful.
An example table has 2.1M rows and is 365MB in size. Queries against the table are generally full table scans as efforts to index the table yield little gain (the indexes don't seem to be selective enough). Also, joins on this table are miserable since the BLOB columns make MySQL use tmp disk for sorting instead of keeping everything in memory. There's 10 GB RAM on the box, we're using 64-bit build of MySQL on Solaris 8, and tmp_table_size = 2G, sort_buffer_size = 2G, max_heap_table_size = 2G. I've got read_buffer_size and read_rnd_buffer_size cranked up to 64M, which I'm hoping will make these full table scans do better -- I could be wrong, though. Storage is not local disk but on HBA-attached SAN. As I said, obvious steps to take are to rework the schema and introduce composite/conjoint tables where possible and to move those BLOB columns out of the main fact table ... but even then, there should be "optimal" settings for a DB that generally does full table scans on 2M rows ... a lot of the data can be kept in that 10 GB of memory, if I could only force MySQL to use it: those BLOB columns are probably killing me. Any tuning advice would be much appreciated. Thanks. -- Dossy -- Dossy Shiobara mail: [EMAIL PROTECTED] Panoptic Computer Network web: http://www.panoptic.com/ "He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on." (p. 70) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
