List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 18 2003 5:57pm
Subject:Re: Spreading Tables Across Disks
View as plain text  
At 13:49 -0400 4/18/03, colbey@stripped wrote:
>I'm assuming you are doing this to improve I/O... typically I eliminate
>the problem by dropping in a nice hardware raid config (raid 0+1 or
>something similar)..
>If you are dealing with just normal disks.. you may may want to look at
>compiling mysql with --raid option..
>Then when you create database/tables it will create them based on a raid
>0 (striping) type setup..
>Once the table is created..  I can't remember exactly.. but there are like
>multiple directories with parts of the tables files in each..  shutdown
>mysql.. move/symlink those files to each of the disks.. restart mysql..
>load in the data and test it out..

This only applies to the data file for MyISAM tables.  The index file
remains in a single file.

>I think you'll probably get some good performance especially if you are
>using scsi disks, or IDE's with 1 disk on each channel..
>good luck..
>On Thu, 17 Apr 2003, mazur wrote:
>>  I am experimenting with spreading tables/indexes across multiple
>>  disks.  My goal is to get the best performance from the db, and
>>  therefore app.  I have a few theory questions, though, that I'm not
>>  sure of.
>>  Let say I have a single, dedicated MySQL server(v4.1 on RedHat7.3)
>>  with dual-processors and three hard disks (disk A, B and C).  Until
>>  now I had two databases on disk A, both with about:
>>  40 tables
>>  tables range from 100 rows --> 75,000 rows
>>  each database directory about 500MB
>>  It runs great...all responses are instant.  I am about to start
>>  utilizing a 33-million-row table of addresses, doing selects only.  It
>>   is about 3.5GB of data.  I plan to have 3 indexes off this table.
>>  I *think* logic would say to put this new 33-mil-row table on disk B,
>>  and the indexes on disk C.  What I am not sure of is, let's assume a
>>  flood of inserts/select/deletes come in for data on disk A, and a
>>  bunch of selects for data on disk B, all simultaneously.  Will MySQL
>>  utilize all three disks simultaneously, thus I get the benefit of
>>  spreading the tables/indexes out?
>>  If the answer is "yes", then:
>>  1) Who is choosing this simultaneous access?  The OS has nothing to
>>  say about it, right? (other than the OS has to support dual-procs)
>>  2) Is it because MySQL will spawn at least three threads (one for
>>  activity on each disk) for just one processor to execute?
>>  3) Or is it the fact the server has 2 processors for MySQL to use, and
>>   MySQL is smart enough to use both?
>>  4) What would happen if the server had only one processor and three
>>  disks?  Would MySQL have to run its statements one at a time, and
>>  therefore there would be little benefit of tables/indexes on multiple
>>  disks (other than the disk heads would more often be in the "right
>>  place" if it used tables on three disks)?
>>  Thanks for any help in understanding this.  Of course if there is an
>>  FAQ for this sort of thing, please point me at it!  :-)
>  > Rob Mazur

Paul DuBois
sql, query
Spreading Tables Across Disksmazur17 Apr
  • Re: Spreading Tables Across Diskscolbey18 Apr
    • Re: Spreading Tables Across DisksPaul DuBois18 Apr
Re: Spreading Tables Across Disksmazur18 Apr
  • Re: Spreading Tables Across DisksBlair Colbey18 Apr
  • Re: Spreading Tables Across DisksPaul DuBois18 Apr
  • Re: Spreading Tables Across DisksDavid Griffiths18 Apr
  • Re: Spreading Tables Across DisksBrian McCain19 Apr
Re: Spreading Tables Across Disksmazur18 Apr