Professional Web Applications Themes

Space problems with MEMORY tables - MySQL

I created the first tables using InnoDB. I successfully loaded the data without a problem. Now, to make intermediate processing faster for the batch of data loaded on a given day, I want to try to use MEMORY tables. That has worked to a point, but with one problem. Only half of the data I used with the InnoDB tables gets loaded into the MEMORY tables when the load aborts with an error about the table being full. It can't be a question of the amount of memory available since the machine has 2 GB RAM, and each data file ...

  1. #1

    Default Space problems with MEMORY tables

    I created the first tables using InnoDB. I successfully loaded the
    data without a problem. Now, to make intermediate processing faster
    for the batch of data loaded on a given day, I want to try to use
    MEMORY tables. That has worked to a point, but with one problem. Only
    half of the data I used with the InnoDB tables gets loaded into the
    MEMORY tables when the load aborts with an error about the table being
    full. It can't be a question of the amount of memory available since
    the machine has 2 GB RAM, and each data file is only about 32 MB. At
    the rate we're importing data, it would take a century or more to fill
    a disk that comes with a new entry level machine! That makes it all
    the harder to understand wy I would encounter problems with tables
    being full.

    My first guess is that this is a configuration issue, but I haven't
    fund the relevant doentation for how the maximum table size is
    determined. I'd assumed that the tables could be filled until the
    available disk space has been consumed, except for memory tables which
    would be determined by the available memory. I guess I was mistaken,
    but if so, what is the truth?

    What part of the doentation talks about issues related to tables
    being full?

    What is the best way to address this issue?

    Thanks,

    Ted

    Ted Guest

  2. #2

    Default Re: Space problems with MEMORY tables

    "Ted" <r.ted.byersrogers.com> wrote:
    > I created the first tables using InnoDB. I successfully loaded the
    > data without a problem. Now, to make intermediate processing faster
    > for the batch of data loaded on a given day, I want to try to use
    > MEMORY tables.
    I guess this won't help much (but this depends on what you mean with
    "intermediate processing"). If you want to "just load" data into tables
    you should load directly. There is absolutely no benefit from loading
    data into intermediate MEMORY tables and copy it into the real INNODB
    tables later. The manual contains lots of tips on howto load data fast:
    RTFM here: [url]http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html[/url]

    Since you are using InnoDB: make sure AUTOCOMMIT is turned off for
    loading data into InnoDB tables.
    > That has worked to a point, but with one problem.
    > ... the load aborts with an error about the table being
    > full.
    MEMORY tables have a (configurable) size limit. RTFM here:
    <http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html>
    > My first guess is that this is a configuration issue, but I haven't
    > fund the relevant doentation for how the maximum table size is
    > determined. I'd assumed that the tables could be filled until the
    > available disk space has been consumed, except for memory tables which
    > would be determined by the available memory. I guess I was mistaken,
    > but if so, what is the truth?
    You guessed correctly for on-disk-tables - with few exceptions, read
    below. However in most cases it is not a good idea to allow unlimited
    use of memory by the database server. So all allocation of memory in
    MySQL is limited by respective configuration settings. RTFM here:
    <http://dev.mysql.com/doc/refman/5.0/en/memory-use.html>

    For most on-disk-tables there is no explicit size limit. Your tables
    can grow as long as the corresponding files can grow on your disk.
    There are few exceptions though:

    1. for efficiency MYISAM data files will be addressed using 32 bit
    pointers by default. That means you have a soft limit of 4GB data
    per MYISAM table. To overcome this limit you have to explicitly
    specify number of rows and avg. row size in ALTER/CREATE TABLE.
    If the product of both numbers exceeds 4GB, this MYISAM table will
    use 48 bit pointers.

    2. InnoDB stores all data/indexes together in one or more tablespaces.
    Tablespaces can have a fixed size, in that case there is a size
    limit for all database objects. Usually one has the "autoextend"
    option in place, that allows a tablespace to grow.

    There are some more limits, most of them specific to the storage engine
    used. RTFM here:
    <http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html>


    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. Datalist Layout spacing problems, row space
    By NancyASAP in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: April 30th, 01:16 PM
  2. Massive white space inbetween tables when viewed withOpera and Firefox
    By Lingobeginner in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: January 19th, 07:10 PM
  3. Disk Space Problems With MX7
    By ssri-stew in forum Coldfusion Server Administration
    Replies: 6
    Last Post: June 20th, 06:32 AM
  4. Trim White Space problems
    By Andy Humm in forum Macromedia Director Basics
    Replies: 2
    Last Post: February 10th, 03:22 PM
  5. Replies: 0
    Last Post: July 21st, 11:21 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