Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Question Unanswered: Spanning DB over many Disks, Not Just files.

    How do I Span a Database over many differnt disks? also, How do I setup a table to span accross differnt files, the docs on the mysql site seem unclear and vauge..

    Thanks for any input..

    Justin.

  2. #2
    Join Date
    Jul 2002
    Posts
    55
    AFAIK you can't with MyISAM. You need to use InnoDB tables. With InnoDB, each tablespace is a single file that can be mounted on its own volume. Check out the InnoDB documents on mysql.com

  3. #3
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    MySQL has a built in software implementation of RAID for MyISAM tables. It allows you to specify several files in the CREATE TABLE command. You simply need to compile with --with-raid.

    A few points to note:
    (1) It doesn't apply to indexes.
    (2) The feature is really to get around the 2GB/4GB file limits and not performance.

    Read more at http://www.mysql.com/doc/en/CREATE_TABLE.html

    To span the entire database over several disks, you can simply use symlinks.

  4. #4
    Join Date
    Oct 2002
    Posts
    6

    Question

    Thats actually what my post was about.. Im not sure of two things:
    1. how can I find out it my Mysql server was compiled with RAID and how to specify file size and location, the manual does not help much, and the two books I have say nothing about it.

  5. #5
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Originally posted by siteproject
    Thats actually what my post was about.. Im not sure of two things:
    1. how can I find out it my Mysql server was compiled with RAID and how to specify file size and location, the manual does not help much, and the two books I have say nothing about it.
    You can see if MySQL was compiled with RAID by executing the following:
    Code:
    mysqladmin variables
    If have_raid is set to 'yes' then all is well.

    To create a table, use the following sql:
    Code:
    create table tablename (col1 varchar(64),
                            col2 varchar(64))
      RAID_TYPE=STRIPED
      RAID_CHUNKS=5
      RAID_CHUNKSIZE=10000;
    This tells it to create the table with 5 datafiles. Each file will be written to in 10M chunks in a round-robin fashion. The datafiles file be located in 5 directories named 01-05 under the main data directory.

    If you run the above code on a database that wasn't compiled with --with-raid, it will simply be created as a normal table.

  6. #6
    Join Date
    Oct 2002
    Posts
    6
    I Think I know what your response will be, but if you have a version of MYSQL not compiled with this, is there any way to change it after the fact??


    Originally posted by Paul
    You can see if MySQL was compiled with RAID by executing the following:
    Code:
    mysqladmin variables
    If have_raid is set to 'yes' then all is well.

    To create a table, use the following sql:
    Code:
    create table tablename (col1 varchar(64),
                            col2 varchar(64))
      RAID_TYPE=STRIPED
      RAID_CHUNKS=5
      RAID_CHUNKSIZE=10000;
    This tells it to create the table with 5 datafiles. Each file will be written to in 10M chunks in a round-robin fashion. The datafiles file be located in 5 directories named 01-05 under the main data directory.

    If you run the above code on a database that wasn't compiled with --with-raid, it will simply be created as a normal table.

  7. #7
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Sorry, as far as I'm aware, there is no way to add this functionality except during compile time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •