Re: Pushing mysql to the limits
Em Wed, 01 Nov 2006 00:00:23 -0800, Cabbar Duzayak escreveu: > We have huge amount of data, and we are planning to use logical > partitioning to divide it over multiple machines/mysql instances. This is a hard call. You will have to keep data consistent among servers, and MySQL does not support distributed transactions: it does have the XA interface, but it does not do its job properly. As far as I know, the only free SQL DBMS supporting distributed transactions is Ingres. > We are planning to use Intel based machines and will prefer ISAM since there > is not much updates but mostly selects. You are asking for trouble. Hear the voice of experience. > 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? Probably yes, but it will all depend on what you will do precisely with it. Anyway, you would be much better of with a more solid system, preferrably with proper XA distributed transactions (two-phase commit). > And, is it OK to put this much data in a single table, or should we divide it > over multiple tables? With a proper DBMS, you can partition the table in physical segments without complicating the logical model. Check Ingres or PostgreSQL, perhaps MySQL’s own MaxDB. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pushing mysql to the limits
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]
Re: Pushing mysql to the limits
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]
Pushing mysql to the limits
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]
