Thanks, Rick.
As always, you are extremely, helpful. much appreciated.
The random access of the large file has been a point of conjecture. Having an outside viewpoint may break the stalemate...
The commit option- never sure about this one. Our setting is:
innodb_flush_log_at_trx_commit=0
Not sure if that is reasonable.
We have between 100 and 400 queries per second on the database.
I will check out the link you provided today.
RAID is h/w.
your last comment about the 'summary' tables is one i will pursue with the developer.
thanks, again.
A
Rick James Wrote:
-------------------------------------------------------
> 13/18 -- fine.
> Other ram things -- not much.
>
> 40GB table -- if randomly accessed, you will be
> effectively I/O bound in spite of your big RAM.
>
> file-per-table -- desirable.
>
> innodb_flush_method = O_DIRECT -- desirable,
> especially if you have good buffering in the Disk
> controller.
>
> innodb_flush_log_at_trx_commit -- this can make
> the biggest diff in performance.
> More info:
>
http://forums.mysql.com/read.php?24,213851,214241
>
> Check the details of your RAID.
> * Controller or software? (hdw is better)
> * Onboard write cache? (very desirable)
> * Write caching on drives, themselves? (probably
> should turn off)
>
> For many queries, RAID-5 is as fast as RAID-10.
>
> Your overnight stuff could probably be turned into
> summary tables that can be maintained "up to the
> minute" without noticeable performance hit. But
> that is another discussion.