List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 3 2004 3:47pm
Subject:Re: Tuning MySQL 4.0.20 for large full table scans
View as plain text  
In the last episode (Jun 03), Dossy said:
> 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).

Consider multicolumn indexes; if you always "SELECT field1 from table
where field2=blah", creating an index on (field2,field1) will let mysql
bypass table lookups completely.

> Also, joins on this table are miserable since the BLOB columns make
> MySQL use tmp disk for sorting instead of keeping everything in
> memory.

Unless you're selecting those blob fields, I don't think mysql will
keep them during the join operation.  I could be wrong though.
 
> 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.

What's your I/O throughput (run "iostat -xcnz 2") during one of these
full table scans?  If you can get 100MB/sec, a scan of a 365MB file
should take under 5 seconds.
 
> 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.

With 10GB of RAM, Solaris should be caching your entire table in
memory.  You will still have the overhead of mysql reading the data
from the OS cache but you should still get good performance.  Make
soure you're not mounting your filesystem with the forcedirectio
option, which will disable the OS cache.
 
-- 
	Dan Nelson
	dnelson@stripped
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