Professional Web Applications Themes

Going for a LARGE Table: Any Tips? - MySQL

Hi there I'm developing a large web application. Part of this web application will be storing numerical chart data in a MySQL table - these numbers will be already calculated, and are just being stored for reference. In this particular table, the stored data will never be deleted or changed. The only actions performed will be SELECTs and INSERTs. There will never be any DELETEs or UPDATEs. The end-result charts themselves have many rows. Let's say about 40-100 rows per chart. I will be storing thousands of charts. I am definitely looking at the possibility of there being millions (upon ...

  1. #1

    Default Going for a LARGE Table: Any Tips?

    Hi there

    I'm developing a large web application. Part of this web application will
    be storing numerical chart data in a MySQL table - these numbers will be
    already calculated, and are just being stored for reference.

    In this particular table, the stored data will never be deleted or
    changed. The only actions performed will be SELECTs and INSERTs. There
    will never be any DELETEs or UPDATEs.

    The end-result charts themselves have many rows. Let's say about 40-100
    rows per chart. I will be storing thousands of charts. I am definitely
    looking at the possibility of there being millions (upon millions?) of
    rows at some time in the future. I am very aware of relational database
    concepts, and one large table truly makes the most sense in this
    particular situation. Believe me, there are foreign keys up the wahoo.

    A general question to the experts out there (or just people with
    experience ;))... what kind of things should I 'look out for', or
    prepare for, when designing/using a table that is so large? My previous
    MySQL tables in all my years of programming have never really had more
    than 40,000 rows. This table will of course be the largest table in a
    database that holds many more tables (about 20 or 30).

    Here are things I am concerned/wondering about. Any advice on them would
    be very much appreciated.

    1) Table type: all the tables in the database that depend on data being
    inserted/updated to other tables are all InnoDB using transactions. This
    table, as mentioned, will strictly be INSERTed to and SELECTed from.
    Should I use MyISAM or InnoDB? Just about all columns will be storing
    numbers, possibly a varchar column or two, and no blobs/text columns.
    However, these columns will need to be 'searched' sometimes.

    2) Backing up / Restoring: What should I look out for in terms of
    dumping/restoring the table? Anything other than the traditional
    mysqldump?

    3) If I do go with an InnoDB table... I imagine I will probably have to
    change that 'ibdata' setting, or at least get familiar/involved with it,
    since this table will be so large. I've seen the term 'multiple
    tablespaces' in reference to InnoDB tables. Is this something I should
    be investigating for a table of this size? What exactly does it mean?

    4) Do I need to think about tion of any sort? Or slave/master
    things? Or can I just use the database as is? There would never be more
    than 50 or so people using the database at once (if that matters).

    The system is a P4 (2.8), running Linux RedHat, MySQL 4.1.7 (though I'm
    pretty sure I will have it upgraded to MySQL 5 before putting this into
    production or even building it - or do I even need to), 1 GB of RAM....
    basically our own box being managed/hosted somewhere across the
    continent.

    I know this is a lot of advice to ask. I'm self-taught, and have been
    re-reading some MySQL books in preperation for this project, which will
    be my 'largest' to date, and will be quite complex overall (this is a
    small but still critical part of the overall project). The books teach
    well, but they don't offer to much advice to specific situations ;)

    Thanks.

    Good Man Guest

  2. #2

    Default Re: Going for a LARGE Table: Any Tips?

    Good Man wrote:
    > Hi there
    >
    > I'm developing a large web application. Part of this web application will
    > be storing numerical chart data in a MySQL table - these numbers will be
    > already calculated, and are just being stored for reference.
    >
    > In this particular table, the stored data will never be deleted or
    > changed. The only actions performed will be SELECTs and INSERTs. There
    > will never be any DELETEs or UPDATEs.
    >
    > The end-result charts themselves have many rows. Let's say about 40-100
    > rows per chart. I will be storing thousands of charts. I am definitely
    > looking at the possibility of there being millions (upon millions?) of
    > rows at some time in the future. I am very aware of relational database
    > concepts, and one large table truly makes the most sense in this
    > particular situation. Believe me, there are foreign keys up the wahoo.
    >
    > A general question to the experts out there (or just people with
    > experience ;))... what kind of things should I 'look out for', or
    > prepare for, when designing/using a table that is so large? My previous
    > MySQL tables in all my years of programming have never really had more
    > than 40,000 rows. This table will of course be the largest table in a
    > database that holds many more tables (about 20 or 30).
    >
    > Here are things I am concerned/wondering about. Any advice on them would
    > be very much appreciated.
    >
    > 1) Table type: all the tables in the database that depend on data being
    > inserted/updated to other tables are all InnoDB using transactions. This
    > table, as mentioned, will strictly be INSERTed to and SELECTed from.
    > Should I use MyISAM or InnoDB? Just about all columns will be storing
    > numbers, possibly a varchar column or two, and no blobs/text columns.
    > However, these columns will need to be 'searched' sometimes.
    >
    > 2) Backing up / Restoring: What should I look out for in terms of
    > dumping/restoring the table? Anything other than the traditional
    > mysqldump?
    >
    > 3) If I do go with an InnoDB table... I imagine I will probably have to
    > change that 'ibdata' setting, or at least get familiar/involved with it,
    > since this table will be so large. I've seen the term 'multiple
    > tablespaces' in reference to InnoDB tables. Is this something I should
    > be investigating for a table of this size? What exactly does it mean?
    >
    > 4) Do I need to think about tion of any sort? Or slave/master
    > things? Or can I just use the database as is? There would never be more
    > than 50 or so people using the database at once (if that matters).
    >
    > The system is a P4 (2.8), running Linux RedHat, MySQL 4.1.7 (though I'm
    > pretty sure I will have it upgraded to MySQL 5 before putting this into
    > production or even building it - or do I even need to), 1 GB of RAM....
    > basically our own box being managed/hosted somewhere across the
    > continent.
    >
    > I know this is a lot of advice to ask. I'm self-taught, and have been
    > re-reading some MySQL books in preperation for this project, which will
    > be my 'largest' to date, and will be quite complex overall (this is a
    > small but still critical part of the overall project). The books teach
    > well, but they don't offer to much advice to specific situations ;)
    >
    > Thanks.
    >
    Have a look at the ARCHIVE engine. It may be useful to you.
    [url]http://dev.mysql.com/tech-resources/articles/storage-engine.html[/url]

    As for the advice you're asking.
    The dump can be long. If your data is really huge, you may think about
    copying the data directory somewhere (after stopping the server), and then
    storing it away.

    Think about tion only if :
    - you need to balance you access load;
    or
    - you need to perform a file-copy backup and can't stop the master for any reason.
    In this case you can just stop the slave, carry out the backup, and restart it,
    without affecting the master.

    But don't think that tion is a replacement for a backup. If you make a mistake
    in the master, the slave is automatically affected.

    About backup, have a look at this recent presentation:
    [url]http://lenz.homelinux.org/mysql/[/url]

    About data storage, there is some unusual ideas here:
    [url]http://datacharmer.blogspot.com/2005/11/data-on-demand-for-data-warehouses.html[/url]

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

Similar Threads

  1. Dreamweaver Slow with large table after installingContribute 4
    By ChrisAudano in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: December 8th, 09:19 PM
  2. Replies: 8
    Last Post: March 16th, 09:20 AM
  3. Replies: 2
    Last Post: March 2nd, 04:17 PM
  4. Large SQL table causes web page to hang
    By Michael Netherton in forum ASP Database
    Replies: 2
    Last Post: July 16th, 11:49 AM
  5. Large table creation
    By Yan-Hong Huang[MSFT] in forum ASP.NET General
    Replies: 0
    Last Post: June 25th, 07:02 AM

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