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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
