Hi, What been said below is true, I also think you should probably use Merge table on subsequent table.
While 2G won't be enough for holding the full index for sure, splitting the table could allow you to have much smaller index and be able to hold that one in memory. Also if you use dynamic size table, the limit by default of the size is 4G, if you need to go above that size you need to run an alter table (if you didn't create it with the parameters) and that actually convert the 32bits pointers to bigger one which would slow down thing a bit on a 32bits architecture :) Also note that you probably want to use MyISAM table format and not the old ISAM one :) But with that database size and that amount of ram, you'll have intensive IO unless it's almost always the same data that is accessed... (Which doesn't make much sense if you keep that much!) Just my 2cent... -- Mathieu Bruneau aka ROunofF Dan Buettner a écrit : > Cabbar - > > That much data is possible in MySQL; I've seen references to terabyte > databases in the past. 700-800 GB in a single table appears to be > possible but I wouldn't really recommend it. A couple of suggestions, > based on my own reading (I've not worked with such large dbs in MySQL > myself, only in Sybase): > > - The MyISAM format can be used with MERGE tables, which would allow > you to break up your data across multiple smaller tables but still > treat it as one logical table for SELECT, UPDATE, and INSERT purposes. > The advantage would be potentially easier backups, faster > checks/repairs if you experienced corruption, possibly faster purges > if you purge data and have your tables arranged by date order (or > however you might split things up), and the ability to compress static > tables to reduce disk consumption and possibly speed up load time off > disk. > http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html > > - 2 GB RAM may well not allow you to keep the indices in memory, which > could slow things down significantly. With as much data as you have > and three columns being indexed, you may not be able to put enough RAM > in the machine to keep it in memory, however. You might consider > whether indexing a prefix (first few characters) instead of an entire > column would be worthwhile - you'd have to find a balance between > index size and search speed. > > The limit for a single table is quite large (65536 terabytes now), but > for such large tables you do need to either specify size up front or > run an alter table later to bring it up to spec. The filesystem is > one potential limiter as well. > http://dev.mysql.com/doc/refman/5.0/en/table-size.html > http://dev.mysql.com/doc/refman/5.0/en/create-table.html > > HTH, > Dan > > > On 11/1/06, Cabbar Duzayak <[EMAIL PROTECTED]> wrote: >> Hi, >> >> We have huge amount of data, and we are planning to use logical >> partitioning to divide it over multiple machines/mysql instances. We >> are planning to use Intel based machines and will prefer ISAM since >> there is not much updates but mostly selects. The main table that >> constitutes this much of data has about 5 columns, and rows are about >> 50 bytes in size, and 3 columns in this table need to be indexed. >> >> So, what I wanted to learn is how much can we push it to the limits on >> a single machine with about 2 gig rams? Do you think MYSQL can handle >> ~ 700-800 gigabyte on a single machine? And, is it OK to put this much >> data in a single table, or should we divide it over multiple tables? >> If that is the case, what would be the limit for a single table? >> >> Any help/input on this is greatly appreciated. >> >> Thanks. >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> >> > === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
