Professional Web Applications Themes

in linux, how to limit quota size per database - MySQL

Hi, The mysql 5 is running in Linux Enterprise. let's say linux user mysql has quota limitation to a folder /home/db. I'm wondering, if there are 3 database, dbA, dbB, dbC, dbA and dbB are configured in my.cnf innodb_data_file_path = /home/db/dbA:100M:autoextend how will mysql control this quota limitation? we know in linux, the file which size hit the limit will get cut to shorten, if same in the way mysql controls database, all tablse will be damaged? also, can mysql be configured to, if all dbA and dbB are going to hit the max size of /home/db, move files into ...

  1. #1

    Default in linux, how to limit quota size per database

    Hi,

    The mysql 5 is running in Linux Enterprise.

    let's say linux user mysql has quota limitation to a folder /home/db.

    I'm wondering, if there are 3 database, dbA, dbB, dbC, dbA and dbB are
    configured in my.cnf

    innodb_data_file_path = /home/db/dbA:100M:autoextend

    how will mysql control this quota limitation? we know in linux, the
    file which size hit the limit will get cut to shorten, if same in the
    way mysql controls database, all tablse will be damaged?

    also, can mysql be configured to, if all dbA and dbB are going to hit
    the max size of /home/db, move files into another folder?

    ---
    Thanks
    John
    Toronto

    john_woo@canada.com Guest

  2. #2

    Default Re: in linux, how to limit quota size per database

    [email]john_woocanada.com[/email] wrote:
    > innodb_data_file_path = /home/db/dbA:100M:autoextend
    >
    > how will mysql control this quota limitation?
    The line you show above doesn't specify a quota limitation, it says that
    the file is initially created at 100MB, and may grow larger indefinitely.

    Also, you can't specify an absolute path with that configuration
    parameter; all data file paths are relative to innodb_data_home_dir.
    You can modify this only through symbolic links.

    You seem to have some misunderstanding of about how InnoDB files and
    paths are specified. You should read the following doc pages carefully:

    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: in linux, how to limit quota size per database


    Bill Karwin wrote:
    > [email]john_woocanada.com[/email] wrote:
    > > innodb_data_file_path = /home/db/dbA:100M:autoextend
    > >
    > > how will mysql control this quota limitation?
    >
    > The line you show above doesn't specify a quota limitation, it says that
    > the file is initially created at 100MB, and may grow larger indefinitely.
    >
    > Also, you can't specify an absolute path with that configuration
    > parameter; all data file paths are relative to innodb_data_home_dir.
    > You can modify this only through symbolic links.
    >
    > You seem to have some misunderstanding of about how InnoDB files and
    > paths are specified. You should read the following doc pages carefully:
    >
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html[/url]
    > [url]http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html[/url]
    > [url]http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html[/url]
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]
    >
    > Regards,
    > Bill K.
    Thanks lots, Bill.

    Can you tell,
    1. what's the syntax to setup per database?
    2. once file size hit the limitation, and a couple transactions
    continue insert or update tables, how mysql deal with it? ex. rollback
    all in same transaction?

    John

    john_woo@canada.com Guest

  4. #4

    Default Re: in linux, how to limit quota size per database

    [email]john_woocanada.com[/email] wrote:
    > 1. what's the syntax to setup per database?
    There is no syntax to configure different tablespace files per database.
    There are only two options with InnoDB:
    1. one or more tablespace files, in which multiple databases share the
    space;
    2. one file per table.
    > 2. once file size hit the limitation, and a couple transactions
    > continue insert or update tables, how mysql deal with it? ex. rollback
    > all in same transaction?
    You'll get an error with a message that "the table is full".
    See [url]http://dev.mysql.com/doc/refman/5.0/en/full-table.html[/url].

    This shouldn't roll back the transaction, it only cancels the INSERT or
    UPDATE statement that spawned the error. Uncommitted changes made by
    other statements in the same transaction can still be committed.

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: in linux, how to limit quota size per database


    Bill Karwin wrote:
    > [email]john_woocanada.com[/email] wrote:
    > > 1. what's the syntax to setup per database?
    >
    > There is no syntax to configure different tablespace files per database.
    > There are only two options with InnoDB:
    > 1. one or more tablespace files, in which multiple databases share the
    > space;
    > 2. one file per table.
    >
    > > 2. once file size hit the limitation, and a couple transactions
    > > continue insert or update tables, how mysql deal with it? ex. rollback
    > > all in same transaction?
    >
    > You'll get an error with a message that "the table is full".
    > See [url]http://dev.mysql.com/doc/refman/5.0/en/full-table.html[/url].
    >
    > This shouldn't roll back the transaction, it only cancels the INSERT or
    > UPDATE statement that spawned the error. Uncommitted changes made by
    > other statements in the same transaction can still be committed.
    >
    Thanks lots again, Bill.

    One more question, is it posssible, using mysql-built-in function, to
    tell how much quota already been used, over all InnoDB databases?

    I know, by using unix repquota command, I can see mysql's usage.

    John
    Toronto

    john_woo@canada.com Guest

Similar Threads

  1. sharedobject size limit ?
    By fanno in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: August 16th, 02:58 PM
  2. About the polygon limit size
    By Newt99 in forum Macromedia Director 3D
    Replies: 0
    Last Post: April 14th, 12:46 PM
  3. CF7 PDF Size Limit
    By CFLett in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 7th, 06:43 PM
  4. 255 limit of field size
    By raj chahal in forum ASP
    Replies: 2
    Last Post: September 13th, 06:27 PM
  5. OT: Limit on maximum # of threads in Linux?
    By Holger Rauch in forum Debian
    Replies: 2
    Last Post: July 4th, 02:30 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