List:General Discussion« Previous MessageNext Message »
From:Dossy Date:June 3 2004 3:20pm
Subject:Tuning MySQL 4.0.20 for large full table scans
View as plain text  
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: dossy@stripped 
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)
Thread
Tuning MySQL 4.0.20 for large full table scansDossy3 Jun
  • Re: Tuning MySQL 4.0.20 for large full table scansDan Nelson3 Jun
    • mysqldump WHERE clauseMcKeever Chris3 Jun
      • Re: mysqldump WHERE clauseMcKeever Chris3 Jun
      • Re: mysqldump WHERE clausegerald_clark3 Jun
        • Re: mysqldump WHERE clauseMcKeever Chris3 Jun
          • Re: mysqldump WHERE clauseMcKeever Chris3 Jun
      • Re: mysqldump WHERE clauseMichael Stassen3 Jun
        • SOLVED Re: mysqldump WHERE clauseMcKeever Chris3 Jun
    • Re: Tuning MySQL 4.0.20 for large full table scansDossy3 Jun
      • Re: Tuning MySQL 4.0.20 for large full table scansDan Nelson3 Jun
  • Re: Tuning MySQL 4.0.20 for large full table scansMatt W3 Jun