Professional Web Applications Themes

How do you partition tables in mysql 5? - MySQL

Hi... According to the MySQL 5.0 doentation, for setting up advanced innodb, the following is stated: An advanced my.cnf example. Suppose that you have a Linux computer with 2GB RAM and three 60GB hard disks at directory paths /, /dr2 and /dr3. The following example shows possible configuration parameters in my.cnf for InnoDB. [mysqld] # You can write your other MySQL server options here # ... innodb_data_home_dir = # # Data files must be able to hold your data and indexes innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend OK....let's assume that I have 2-tables, X and Y, and I want to create them, BUT ...

  1. #1

    Default How do you partition tables in mysql 5?

    Hi...

    According to the MySQL 5.0 doentation, for setting up advanced
    innodb, the following is stated:

    An advanced my.cnf example. Suppose that you have a Linux computer with
    2GB RAM and three 60GB hard disks at directory paths /, /dr2 and /dr3.
    The following example shows possible configuration parameters in my.cnf
    for InnoDB.

    [mysqld]
    # You can write your other MySQL server options here
    # ...
    innodb_data_home_dir =
    #
    # Data files must be able to hold your data and indexes
    innodb_data_file_path =
    /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend

    OK....let's assume that I have 2-tables, X and Y, and I want to create
    them, BUT have X point to the ibdata1 path and Y to ibdata2. According
    to the CREATE TABLE doc, the DATA DIRECTORY option only works for
    MyISAM.

    Anybody know how to do this using innodb?

    Thanks,
    Joe

    JoeT Guest

  2. #2

    Default Re: How do you partition tables in mysql 5?

    "JoeT" <trubisz> wrote:
    > ... let's assume that I have 2-tables, X and Y, and I want to create
    > them, BUT have X point to the ibdata1 path and Y to ibdata2. According
    > to the CREATE TABLE doc, the DATA DIRECTORY option only works for
    > MyISAM.
    >
    > Anybody know how to do this using innodb?
    You cannot do that. InnoDB decides how it distributes data over
    tablespaces. OTOH you can use --innodb-file-per-table to have
    InnoDB create a separate tablespace for each table. IMHO there
    is no particularly good reason for doing that anyway. YMMV.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  3. #3

    Default Re: How do you partition tables in mysql 5?

    Axel Schwenke wrote:
    > you can use --innodb-file-per-table to have
    > InnoDB create a separate tablespace for each table. IMHO there
    > is no particularly good reason for doing that anyway. YMMV.
    IMHO the best reason for using innodb-file-per-table is that when you
    drop a table, the filesystem space is released.

    If you put all your InnoDB tables in one filespace file, the only way to
    get the file to shrink is to dump all your other InnoDB tables, remove
    the filespace file, and re-import the tables.

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: How do you partition tables in mysql 5?

    Actually, having done really huge systems (we are talking about very
    high speed/high volume OLTP systems), I can utilize the advantages of
    the hardware as well. So, it becomes a major performance advantage.

    Joe

    JoeT Guest

  5. #5

    Default Re: How do you partition tables in mysql 5?

    "JoeT" <trubisz> wrote:
    > Actually, having done really huge systems (we are talking about very
    > high speed/high volume OLTP systems), I can utilize the advantages of
    > the hardware as well. So, it becomes a major performance advantage.
    Ahh. I guessed that already.

    My experience as well as the experience of MySQL customers shows,
    that multiple disks are best used as RAID. The most appropriate
    RAID level is RAID-10 (a stripe of mirrors). If you need the last
    performance kick, you might go for RAID-0. However you should
    avoid RAID-5 because it has very bad write performance.

    Compared to hand-tuned distribution of data on multiple disks,
    RAID has many advantages

    - it's simple
    - it's self-adjusting - no need to change the disk layout
    if your data distribution or access pattern changes
    - it's easily extensible

    There is a nice bluprint from SUN concerning that issue:
    <http://www.sun.com/blueprints/1000/layout.pdf>

    I know the old Oracle rule-of-thumb "separate data and indizes, put
    in different tablespaces, put each tablespace onto it's own disk" -
    but IMNSHO this rule is pretty outdated in the time of massively
    parallelized storage systems.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. mysql dbi list of tables
    By neo ancient in forum PERL Modules
    Replies: 3
    Last Post: October 7th, 03:56 AM
  2. backing up partition tables
    By Florian von Savigny in forum Linux / Unix Administration
    Replies: 5
    Last Post: July 18th, 12:34 AM
  3. checking mysql tables
    By Rusty Wright in forum PHP Development
    Replies: 2
    Last Post: September 20th, 05:09 AM
  4. MySql temporary tables
    By Andu in forum PHP Development
    Replies: 1
    Last Post: September 16th, 03:31 AM
  5. Fun with Partition Tables and Zero's!!!
    By jayjwa in forum Linux Setup, Configuration & Administration
    Replies: 12
    Last Post: July 20th, 08:18 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139