Like you said, 1 solution is to throw hardware at it..
This idea is based on an assumption that it's an I/O bottleneck.. not a
cpu one.. if this is incorrect let us know..
Few options: depending on how many channels your controller(s)
have.. reconfigure existing disks for more i/o
Drop in 2+ raid arrays( could be 1 or 0+1) arrays of disks each..
Run mysql with --raid option, rebuild your existing database on a
mysql sw raid emulation setup.. symlink the virtual stripe files to each
of the new raid arrays..
All depends on what kind of disks you have now, open disk
Outsource a developer to update the code for you ;)
On Thu, 24 Apr 2003, Ryan R. Tharp wrote:
> Running MySQL 4.0.12 on a dual Xeon 2.8ghz with 2gb memory running FreeBSD 4.8 on a
> Hardware RAID 0+1 SCSI HDDs
> Have this table that I'm trying to optimize for. It has 1.1million rows with 29
> fields averaging 506 Bytes per
row. We've considered splitting the table but we don't have enough developer time
available to convert all the
code that uses the table. So we'd like just to throw hardware/memory at it for the time
> Some software tricks I've tried are playing with:
> Dynamic sized rows and static sized rows
> InnoDB and MyISAM
> Indexes and No Indexes
> Primary Keys and No Primary Key (Just an Index for the auto-increment)
> I wrote a benchmark that tests most of these scenarios and then I began to adjust the
> various mysql buffers
but to my amazement the benchmarks ran best when I didn't set any buffers in the my.cnf at
> Also the fastest benchmark numbers aren't that fast. Is this table too big? Any other
> optimization tips to
try? Any one have any ideas or suggestions?