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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
