Re: MyISAM table size vs actual data, and performance
- Original Message - From: Rick James rja...@yahoo-inc.com Hey Rick, Thanks for your thoughts. * Smells like some huge LONGTEXTs were INSERTed, then DELETEd. Perhaps just a single one of nearly 500M. I considered that, too; but I can see the on-disk size grow over a period of a few months - it's not a sudden bump. * Yes, there is an impact on full table scans -- it has to step over the empty spots. Or maybe not -- one big cow chip of 500MB would be easy to leap over. Hmm, that is a point. I keep expecting a full tablescan to still use the PK, but this isn't InnoDB. Still, it's peculiar then that it suddenly becomes slow - I would expect the odds to tip in favour of an index scan as table_free grows, not the other way around. * OPTIMIZE TABLE is the primary way to recover the space. It _may_ be that space on the _end_ is automatically recovered. If so, you might see the .MYD shrink even when OPTIMIZE is not run. Yes, that's what I do, of course; but the free space should really be reallocated to updates - escpecially because of the longtext, which means it's allowed to fragment. Are there any tools available to analyze MyISAM datafiles? It'd be interesting to see how the free space is really distributed. * LONGTEXT is almost never useful. Do you really think there are thingies that big? Consider changing it to MEDIUMTEXT -- that would truncate any biggies to 16MB. * Smells like a key-value (EAV) schema design. Such is destined to fail when trying to scale. Yeah, you are probably stuck with Drupal. Here are my comments and recommendations on EAV: http://mysql.rjweb.org/doc.php/eav Yeps, Drupal. I could probably truncate that field, yes; but that's just another workaround, not a fix. It's indeed a key/value scheme - every single page load that gets through the caches will select-star that entire table. It's crap, but I have precious little influence on the CMS decisionmaking. * Please try to find a way in your Email client to display STATUS without losing the spacing. Heh, sorry. I've always preferred the wide layout, and tend to forget \G for mails. * When you switched to InnoDB, I hope you had innodb_file_per_table turned on. That way, you can actually recoup the space when doing ALTER. Otherwise, you will be stuck with a bloated ibdata1 file that you cannot easily shrink. Default on all instances, of course. * In InnoDB, the LONGTEXT will usually be stored separately, thereby making a full table scan relatively efficient. For now, they seem to be behaving. We'll see. Thanks, Johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MyISAM table size vs actual data, and performance
* Smells like some huge LONGTEXTs were INSERTed, then DELETEd. Perhaps just a single one of nearly 500M. * Yes, there is an impact on full table scans -- it has to step over the empty spots. Or maybe not -- one big cow chip of 500MB would be easy to leap over. * OPTIMIZE TABLE is the primary way to recover the space. It _may_ be that space on the _end_ is automatically recovered. If so, you might see the .MYD shrink even when OPTIMIZE is not run. * LONGTEXT is almost never useful. Do you really think there are thingies that big? Consider changing it to MEDIUMTEXT -- that would truncate any biggies to 16MB. * Smells like a key-value (EAV) schema design. Such is destined to fail when trying to scale. Yeah, you are probably stuck with Drupal. Here are my comments and recommendations on EAV: http://mysql.rjweb.org/doc.php/eav * Please try to find a way in your Email client to display STATUS without losing the spacing. * When you switched to InnoDB, I hope you had innodb_file_per_table turned on. That way, you can actually recoup the space when doing ALTER. Otherwise, you will be stuck with a bloated ibdata1 file that you cannot easily shrink. * In InnoDB, the LONGTEXT will usually be stored separately, thereby making a full table scan relatively efficient. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Friday, February 15, 2013 4:21 AM To: mysql. Subject: MyISAM table size vs actual data, and performance Hey list, I've got another peculiar thing going on :-) Let me give you a quick summary of the situation first: we host a number of Drupal sites, each site and it's db on separate VMs for reasons that are not important to this scenario. MySQL is 5.0.51a-24+lenny4-log (Debian); I don't have the exact Drupal version here but it's likely to be a 5.x branch. The easy thing to say would of course be upgrade your versions, but that's not an option right now. I don't really care if that means I have no actual *fix* for the problem - I know how to work around it. I'm just looking for a cause, ideally maybe even a specific known bug. Strangely enough, I'm seeing this on three distinct installs; but others with the same versions and setup (but different sites) seem to not exhibit the issue. So, what I'm seeing is this: Drupal's variable table keeps growing, but there does not seem to be more data. I understand how record allocation and free space in datafiles works, but this is well beyond the normal behaviour. http://www.tuxera.be/filestore/heciexohhohj/df-year.png As you can see here (the lime green line of /data), growth occurs gradually (and the issue happened in september, as well), until it seems to reach a certain point. At some point, however, performance on that table (notably select * - it's a drupal thing) pretty much instantly plummets, and the query takes around half a minute to run - whereas now, after reclaiming the free space, it takes 0.03 seconds. I don't have the exact numbers as I wasn't on-site yesterday evening, but since the disk is 5GB, the reclaimed space yesterday must have been around 850MB - for a table that is now 30MB. No records were deleted from the table, the workaround is as simple as OPTIMIZE TABLE variable - simply rebuild the table. The logs make no mention of a crashed table, so it's very unlikely that this is a borked index. Even if it were, I wouldn't expect a scan of 30MB in 1202 rows to take half a minute, on a table that is accessed so often that it's relevant blocks are bound to be in the filesystem cache. The table's structure is fairly simple, too: CREATE TABLE `variable` ( `name` varchar(128) NOT NULL DEFAULT '', `value` longtext NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I currently have another system that's also growing that table, here's a bit of session: blockquote mysql show table status like 'variable'; +--++-++--++--- --+-+--+---+--- -+-+-+-+--- --+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | | Data_length | Max_data_length | Index_length | Data_free | | Auto_increment | Create_time | Update_time | Check_time | Collation | | Checksum | Create_options | Comment | +--++-++--++--- --+-+--+---+--- -+-+-+-+--- --+--++-+ | variable | MyISAM | 10 | Dynamic | 1188 | 795 | 493277732 | | 281474976710655 | 41984 | 492332716 | NULL | 2011-12-13 16:18:53 | | 2013-02-15 12:35:18 | 2012-10-17 15:45:11 | utf8_general_ci | NULL
MyISAM table size vs actual data, and performance
Hey list, I've got another peculiar thing going on :-) Let me give you a quick summary of the situation first: we host a number of Drupal sites, each site and it's db on separate VMs for reasons that are not important to this scenario. MySQL is 5.0.51a-24+lenny4-log (Debian); I don't have the exact Drupal version here but it's likely to be a 5.x branch. The easy thing to say would of course be upgrade your versions, but that's not an option right now. I don't really care if that means I have no actual *fix* for the problem - I know how to work around it. I'm just looking for a cause, ideally maybe even a specific known bug. Strangely enough, I'm seeing this on three distinct installs; but others with the same versions and setup (but different sites) seem to not exhibit the issue. So, what I'm seeing is this: Drupal's variable table keeps growing, but there does not seem to be more data. I understand how record allocation and free space in datafiles works, but this is well beyond the normal behaviour. http://www.tuxera.be/filestore/heciexohhohj/df-year.png As you can see here (the lime green line of /data), growth occurs gradually (and the issue happened in september, as well), until it seems to reach a certain point. At some point, however, performance on that table (notably select * - it's a drupal thing) pretty much instantly plummets, and the query takes around half a minute to run - whereas now, after reclaiming the free space, it takes 0.03 seconds. I don't have the exact numbers as I wasn't on-site yesterday evening, but since the disk is 5GB, the reclaimed space yesterday must have been around 850MB - for a table that is now 30MB. No records were deleted from the table, the workaround is as simple as OPTIMIZE TABLE variable - simply rebuild the table. The logs make no mention of a crashed table, so it's very unlikely that this is a borked index. Even if it were, I wouldn't expect a scan of 30MB in 1202 rows to take half a minute, on a table that is accessed so often that it's relevant blocks are bound to be in the filesystem cache. The table's structure is fairly simple, too: CREATE TABLE `variable` ( `name` varchar(128) NOT NULL DEFAULT '', `value` longtext NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I currently have another system that's also growing that table, here's a bit of session: blockquote mysql show table status like 'variable'; +--++-++--++-+-+--+---++-+-+-+-+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++--++-+-+--+---++-+-+-+-+--++-+ | variable | MyISAM | 10 | Dynamic | 1188 | 795 | 493277732 | 281474976710655 | 41984 | 492332716 | NULL | 2011-12-13 16:18:53 | 2013-02-15 12:35:18 | 2012-10-17 15:45:11 | utf8_general_ci | NULL | | | +--++-++--++-+-+--+---++-+-+-+-+--++-+ 12:36:55|root@xendbprod1-1:/data/mysql 0 # mysqlcheck --verbose DBNAME variable # Connecting to localhost... DBBAME.variable OK # Disconnecting from localhost... 12:37:07|root@xendbprod1-1:/data/mysql 0 # mysqlcheck --verbose -g DBNAME variable # Connecting to localhost... DBNAME.variable OK # Disconnecting from localhost... mysql show table status where name like variable; +--++-++--++-+-+--+---++-+-+-+-+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++--++-+-+--+---++-+-+-+-+--++-+ | variable | MyISAM | 10 | Dynamic | 1188 | 497 | 493277732 | 281474976710655 | 41984 | 492686616 | NULL | 2011-12-13
Re: Table size vs Memory requirements?
On Nov 22, 2009, at 8:54 AM, Ryan Chan wrote: Hello, Is it common heard from people that if you have large table (assume MyISAM in my case), you need large memory in order to have the key/index in memory for performance, otherwise, table scan on disk is slow. But how to estimate how much memory I need? Consider a simple case, a MyISAM table is 10GB in size, with 2GB index, how much memory I need? Thanks. It's not the size of the table, it's the size of the index that you need to watch. MyISAM keeps the table and index separate, so the memory requirements can be considerably less than the size of the table. What you have likely heard is in reference to InnoDB tables. Since InnoDB clusters the index with the data, the memory requirements can be much greater. You may notice a significant drop off in performance from InnoDB once the data size passes a certain level, which is based on your RAM and InnoDB settings. MyISAM performance is usually fairy steady as the size of the table increases. -- Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table size vs Memory requirements?
Hello, Is it common heard from people that if you have large table (assume MyISAM in my case), you need large memory in order to have the key/index in memory for performance, otherwise, table scan on disk is slow. But how to estimate how much memory I need? Consider a simple case, a MyISAM table is 10GB in size, with 2GB index, how much memory I need? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table size vs Memory requirements?
In the last episode (Nov 22), Ryan Chan said: Is it common heard from people that if you have large table (assume MyISAM in my case), you need large memory in order to have the key/index in memory for performance, otherwise, table scan on disk is slow. But how to estimate how much memory I need? Consider a simple case, a MyISAM table is 10GB in size, with 2GB index, how much memory I need? If by table scan you mean a full table scan with no index usage, your RAM is irrevelant unless you have at leat 10GB (enough to cache the entire table). Anything less than that and you will have to read the entire table every time, and in that case, your disks' sequential throughput is the only factor. If you're more interested in random record lookups: Ideal case is 12 GB (no disk reads needed ever). Next best would be 2GB (enough to cache the entire index, but not the data, so you need to do one disk seek per lookup). Next best would be enough to cache all but the leaves of the index (requiring one index and one table seek per lookup); this depends on your key size but 200MB should be enough. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: the limitaiton of table size
At 10:47 PM -0400 3/18/08, Sookhyun Yang wrote: Dear all, I have a question about the limitatin of table size. If I use the InnoDB engine, I don't have to worry that the total size of table is greater than the single filesize? You can create an InnoDB tablespace that spans multiple files, yes. InnoDB will use the aggregate file size as the tablespace size. But remember that multiple tables share the tablespace. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
the limitaiton of table size
Dear all, I have a question about the limitatin of table size. If I use the InnoDB engine, I don't have to worry that the total size of table is greater than the single filesize? Thanks a lot! Best, Sookhyun.
Re: Table Size
There's a bitmap of which columns in the table allow NULL. This contains as many bits as there are NULL-able columns in the table, rounded up to the nearest byte. When a column is NULL, the bit is set to 1. That said, I'm not sure whether the CHAR's storage space is still present in the row when the column is NULL. I think it is. Maybe someone else knows that offhand. Baron Josh wrote: Thanks for all of your help/information. One additional question... do NULL values take up any space? For example, if I have a column defined as: repAccess char(1) default null When a user should have access to run a particular report, repAccess will be set to 'T'. If not, it is left null. In this example, the rows with 'T' will occupy an additional 1 btye for storing the single character, however, for rows with null... does that take up space? I'm only asking to give me an idea of what sort of space NULL values take up. One of my tables has hundreds of thousands of rows and could potentially have many null values... I'm trying to get an idea of whether or not those null values are taking up much space. Thanks. - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Dan Nelson [EMAIL PROTECTED] Cc: Josh [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Sunday, October 28, 2007 9:25:11 AM Subject: Re: Table Size Dan Nelson wrote: In the last episode (Oct 27), Baron Schwartz said: InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size.. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well.. It's true they're in the same file, but the secondary indexes show up in the 'Index_length' column in SHOW TABLE STATUS. I was double-checking that the primary key contributes to the 'Data_length' column, not the 'Index_length' column. -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
Thanks for all of your help/information. One additional question... do NULL values take up any space? For example, if I have a column defined as: repAccess char(1) default null When a user should have access to run a particular report, repAccess will be set to 'T'. If not, it is left null. In this example, the rows with 'T' will occupy an additional 1 btye for storing the single character, however, for rows with null... does that take up space? I'm only asking to give me an idea of what sort of space NULL values take up. One of my tables has hundreds of thousands of rows and could potentially have many null values... I'm trying to get an idea of whether or not those null values are taking up much space. Thanks. - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Dan Nelson [EMAIL PROTECTED] Cc: Josh [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Sunday, October 28, 2007 9:25:11 AM Subject: Re: Table Size Dan Nelson wrote: In the last episode (Oct 27), Baron Schwartz said: InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size.. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well.. It's true they're in the same file, but the secondary indexes show up in the 'Index_length' column in SHOW TABLE STATUS. I was double-checking that the primary key contributes to the 'Data_length' column, not the 'Index_length' column.
Re: Table Size
Dan Nelson wrote: In the last episode (Oct 27), Baron Schwartz said: InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well. It's true they're in the same file, but the secondary indexes show up in the 'Index_length' column in SHOW TABLE STATUS. I was double-checking that the primary key contributes to the 'Data_length' column, not the 'Index_length' column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Size
Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? Thanks.
Re: Table Size
Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
mysql show create table UserReports\G *** 1. row *** Table: UserReports Create Table: CREATE TABLE `UserReports` ( `rolID` int(10) unsigned NOT NULL, `repID` int(10) unsigned NOT NULL, PRIMARY KEY (`rolID`,`repID`), KEY `repID` (`repID`), CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE, CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Josh [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron
Re: Table Size
Have you tried optimize table? On 10/27/07, Josh [EMAIL PROTECTED] wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. Josh wrote: mysql show create table UserReports\G *** 1. row *** Table: UserReports Create Table: CREATE TABLE `UserReports` ( `rolID` int(10) unsigned NOT NULL, `repID` int(10) unsigned NOT NULL, PRIMARY KEY (`rolID`,`repID`), KEY `repID` (`repID`), CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` (`rolID`) ON DELETE CASCADE, CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` (`repID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Josh [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Size
Forgot to send the rest of 'show table status' Name: UserReports Engine: InnoDB Version: 10 Row_format: Compact Rows: 10388 Avg_row_length: 104 Data_length: 1081344 Max_data_length: 0 Index_length: 212992 Data_free: 0 Auto_increment: NULL Create_time: 2007-05-19 21:17:58 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL - Original Message From: Baron Schwartz [EMAIL PROTECTED] To: Josh [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, October 27, 2007 10:17:32 AM Subject: Re: Table Size Josh wrote: Hello, I have a database that is growing at a rate of 4-5 MB per day (that number is getting larger as well). Not too bad but I'm trying to clean up the tables to minimize the amount of space they take up. I have one particular table that has 2 columns: rolID int(10) unsigned repID int(10) unsigned Both are foreign keys to other tables. A show table status shows that this table has a Data_length=1081344 bytes. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that integers take up 4 bytes of space. There are currently 10472 rows in the table. 10472 x 4 x 2 = 83776 What am I calculating wrong? Why is the Data_length value so much larger? It depends on the storage engine, indexes, and possibly other things. What's the output of SHOW CREATE TABLE for this table? If you can't tell us that, what's the storage engine? (yes, I know Index_length is separate, but humor me). Baron
Re: Table Size
In the last episode (Oct 27), Baron Schwartz said: InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well. Useful reading: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html Note the 5-byte header per record in all indexes, plus another 13 bytes per record in the primary key, and the fact that the columns in your primary key are not omitted from the record data. So repid is actually stored three times in the .ibd file; once in the primary key, once in the record, and once in the `repid` index. There's quite a lot of overhead in making a transaction-safe multiversioned table. What I've seen is that for small row lengths (under 50 bytes) an InnoDB table is about twice the size as the same data in MyISAM format (including indexes). For your particular table, you're probably seeing the effect of 16k page sizes. With only 10K rows, your leaf pages are mostly empty. Try putting 100K rows in and see how big the .ibd file is. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table size??
Ratheesh K J wrote: Helo all, Just wanted to know when should a Table be considered for partitioning ( or should it be archiving ). Almost all of our tables are of Innodb type. I am looking for an estimate rather than a Depends on situation kind of an answer. We have few of our table swhich are very huge ( in terms of number of rows ), 70 lac rows. Should this be a factor for table partitioning or should the actual data size be a factor. 1) In that case for a system with 80 GB reserved for MySQL when should we worry about Table sizes? Partitioning helps queries across sections of those tables, it doesn't change table size. If you have a table with a year column you could break it down into months, so when you query a particular month, the whole table isn't checked, only a particular partition. So mysql can go to the right partition (month), then use appropriate indexes to find the relevant records. Without partitioning, the whole table index has to be evaluated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table size??
Helo all, Just wanted to know when should a Table be considered for partitioning ( or should it be archiving ). Almost all of our tables are of Innodb type. I am looking for an estimate rather than a Depends on situation kind of an answer. We have few of our table swhich are very huge ( in terms of number of rows ), 70 lac rows. Should this be a factor for table partitioning or should the actual data size be a factor. 1) In that case for a system with 80 GB reserved for MySQL when should we worry about Table sizes? 2) We have 3 specific tables which are of size 5GB, 3GB and 1.7GB respectively. The first two of them have more than 70 lakh rows. As a preventive measure what could be the best way to optimize these tables? Thanks, Ratheesh Bhat K J
Table size, db size and hence disk space.
I am new to databases and mysql in particular. I need to provide the specifications for database size for a new design... Lets assume one of my tables has the following structure column1 int column2 int column3 varchar(200) column4 char(1) column5 int column6 text column7 datetime column8 datetime which gives an approximate value of 630 bytes for a row in this table and given that I can have 48000 such rows in this table taking the table size to 30MB. Note that I have not taken into account the indexes and foreign keys... How much space do these take? I have 6 such innodb tables more or less the same size. How can I reach to a number for database size and hence disk space? Thanks
Table Size
Hi, What command used to check table size and database size? thanks - Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
Re: Table Size
At 2:15 pm -0700 5/4/06, Ravi Kumar wrote: What command used to check table size and database size? For table size: http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html I imagine (though I don't know for sure) that you can get the same info from the information_schema database that was introduced in v5. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database/table size
Hi there,
As far as I know, MySQL does not have a CREATE TABLE command do that.
The only thing I can think of is if by size you mean number of
rows. (Which is directly proportional to the size in bytes if you do
not use variable-length fields, anyway.)
If so, you can have an autoincrement field, and write a trigger to
delete the first record once you get to the newest record.
For instance, if you want your table to always have 100 rows, the
trigger would have the algorithm:
(use this before the insert statement runs!)
if ( max(autoincrement_field) - min(autoincrement_field) 99)
then
{ INSERT }
else {
delete from table where autoincrement_field=min(autoincrement_field)
INSERT
}
Please note that I've never really looked at triggers, so my pseudo
code may or may not be possible.
-Sheeri
On 1/31/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hi, there!
I would like to know whether mysql has built-in capabilities/config options
in order to limid a database size or a table size.
I want a table to grow up to a limit and, when reached, for a new row to be
inserted the oldest one be deleted. Has mysql got this
functionality built-in? If not, what other approaches could I use, triggers
...?
Kinf regards.
Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.
--
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]
database/table size
Hi, there! I would like to know whether mysql has built-in capabilities/config options in order to limid a database size or a table size. I want a table to grow up to a limit and, when reached, for a new row to be inserted the oldest one be deleted. Has mysql got this functionality built-in? If not, what other approaches could I use, triggers ...? Kinf regards. Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 4.1.1 on Linux table size limit of 4GB and grant priv for Load
As it looks like no one else has replied. The default table size is 4GB - effectively if you do not modify the table create statement this is the size you get. To alter the table size you need to change the value of the MAX_ROWS parameter. The table size is governed by MAX_ROWS multiplied by average row length. SHOW TABLE STATUS will get you the average row length or 'properties' in mysqlcc. My advice is to rename you existing table and then do a create table definition MAX_ROWS=n as select columns) from renamed table. Alternatively use mysqldump to dump the table, drop the version on the database, edit the .txt to add the MAX_ROWS parameter to the create statement and then re-import. Changing the table using the ALTER TABLE command will take forever. Full details of the MAX_ROWS parameter can be found in the manual under the CREATE TABLE command. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: V. Agarwal [mailto:[EMAIL PROTECTED] Sent: 30 April 2005 14:46 To: mysql@lists.mysql.com Subject: Mysql 4.1.1 on Linux table size limit of 4GB and grant priv for Load Hi, I am using Mysql built-in defaults for starting the server. It chokes when the table size grows to 4GB in data directory. What parameter needs to be tweaked for allowing larger size tables ? === -rw-rw1 vagarwal dev 4294967284 Apr 29 22:33 crec.MYD -rw-rw1 vagarwal dev 950261760 Apr 29 23:22 crec.MYI I created a user and granted all priviledges on a given database but it still does not allow 'load data infile ...' command to that user. Any clues ? = __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1.1 on Linux table size limit of 4GB and grant priv for Load
Hi, I am using Mysql built-in defaults for starting the server. It chokes when the table size grows to 4GB in data directory. What parameter needs to be tweaked for allowing larger size tables ? === -rw-rw1 vagarwal dev 4294967284 Apr 29 22:33 crec.MYD -rw-rw1 vagarwal dev 950261760 Apr 29 23:22 crec.MYI I created a user and granted all priviledges on a given database but it still does not allow 'load data infile ...' command to that user. Any clues ? = __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To extend InnoDB table size
MyISAM tables files have MYI suffix. See: http://dev.mysql.com/doc/mysql/en/full-table.html Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen Naveen C Joshi [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To extend InnoDB table size
Many many thanks... I was in confusion. But how can I exten the table size. Regards Naveen - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Naveen C Joshi [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, March 15, 2005 7:52 PM Subject: Re: To extend InnoDB table size Naveen C Joshi wrote: Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. These are MyIsam, not InnoDB. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To extend InnoDB table size
Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen
Re: To extend InnoDB table size
Naveen C Joshi wrote: Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. These are MyIsam, not InnoDB. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about showing db and/or table size
I know there must be a command line query to show the size of the database or of specific tables. What is it? I've been unable to find anything in the online manual (of course that presupposes I've been looking in the right place). Thanks, Susan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: question about showing db and/or table size
[snip] I know there must be a command line query to show the size of the database or of specific tables. What is it? I've been unable to find anything in the online manual (of course that presupposes I've been looking in the right place). [/snip] SHOW TABLE STATUS [FROM `table`] http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
phpMyAdmin don't show table size
Hello, after restoring from a mysqldump I use phpMyAdmin to view my database but the size field is unknown ... how could I get a size display ? -- Dilipan Sebastiampillai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpMyAdmin don't show table size
Update phpMyAdmin. Santino At 12:33 + 12-11-2004, Dilipan Sebastiampillai wrote: Hello, after restoring from a mysqldump I use phpMyAdmin to view my database but the size field is unknown ... how could I get a size display ? -- Dilipan Sebastiampillai -- 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]
OT: table size WAS RE: optimizing database
Razor Fish mailto:[EMAIL PROTECTED] on Thursday, October 21, 2004 3:19 PM said: i need consulting help for optimizing a database with 1.2 million records to handle 3-4 million hits a day. this is going to be a 'duh' question on my part but i just had to ask anyway. my largest table probably has 700 records in it. what the heck kind of data is being stored where it reaches the millions (or more)? chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table size WAS RE: optimizing database
I have multiple databases running tables with thousands of records in them. Some of my tables have as many as 130 million records in them. Memberships and patient data can easily run from thousands to tens of thousands of records. If you are looking into things like DNA/Genome mapping, you can easily run into billions of records. J.R. -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 3:41 PM To: [EMAIL PROTECTED] Subject: OT: table size WAS RE: optimizing database Razor Fish mailto:[EMAIL PROTECTED] on Thursday, October 21, 2004 3:19 PM said: i need consulting help for optimizing a database with 1.2 million records to handle 3-4 million hits a day. this is going to be a 'duh' question on my part but i just had to ask anyway. my largest table probably has 700 records in it. what the heck kind of data is being stored where it reaches the millions (or more)? chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: memory issue: paging, disk activity, table size - was: optimizing a select statement
Karthik Viswanathan wrote: Thanks for the information. Before I try to further look into the query, I would like to know if there is some memory issue. Its strange since the speed for executing same query differs. Its a Mac G5 with just 1GB ram. I could see lot of pageouts in the top command. The activity monitor shows that there is little free memory (12 -14M) and ~650M of inactive memory and ~250M of active memory. Read on web that the less free memory is the inactive memory will be used. When I run a query (like the one we had discussed) there is only a slight difference in this memory status, where as the disk activity shows active 'data in', 'read in' . Does this means that its out of physical memory and uses swap file? I am not sure how mysql uses memory. Couple of tables I read in the query is more than 4GB but the query uses only two column of those tables and it will not be more than 1 G for sure. I am not sure if I understood correct or not and would like to hear your suggestion. Is there any relation between the table size (no of rows and file size) and the system memory needed to get better performance? i read on web that its better to have ram more than the largest table size. is this true even if the query uses only few columns of big tables? Up to the point that you can cache all the data your server as a whole will ever use, it is better to have more RAM. The question is how much better, and the answer depends on the data access patterns which are nearly impossible to track without experimentation. A common reason for large variations in the speed of the same query is the caching issue - when the data happens to be cached it goes a lot faster. And, the type of query that is likely to be affected by this is the one that would perform a table scan or read records in a non-sequential order. So my recommendation is, first, optimize your queryies, and then get more RAM once you cannot optimize them any more. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory issue: paging, disk activity, table size - was: optimizing a select statement
Thanks for the information. Before I try to further look into the query, I would like to know if there is some memory issue. Its strange since the speed for executing same query differs. Its a Mac G5 with just 1GB ram. I could see lot of pageouts in the top command. The activity monitor shows that there is little free memory (12 -14M) and ~650M of inactive memory and ~250M of active memory. Read on web that the less free memory is the inactive memory will be used. When I run a query (like the one we had discussed) there is only a slight difference in this memory status, where as the disk activity shows active 'data in', 'read in' . Does this means that its out of physical memory and uses swap file? I am not sure how mysql uses memory. Couple of tables I read in the query is more than 4GB but the query uses only two column of those tables and it will not be more than 1 G for sure. I am not sure if I understood correct or not and would like to hear your suggestion. Is there any relation between the table size (no of rows and file size) and the system memory needed to get better performance? i read on web that its better to have ram more than the largest table size. is this true even if the query uses only few columns of big tables? Thanks for your help Karthik. At 07:44 PM 2/29/2004, you wrote: So if this is your query and based on the explain this is what I would recommend. SELECT Distinct (a.id) From table1 a INNER JOIN table1 b USING ( p_id ) INNER JOIN table2 c USING ( p_id ) INNER JOIN table3 d USING ( out_id ) INNER JOIN table4 e ON ( d.name_id = e.name_id ) INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) WHERE (f.name_id =45 OR f.name_id =56) AND b.id =275 AND a.id != b.id For some reason you are getting the using temporary on table b, I assume based on the explain that you have an index called (id) that only has the id in it. Here's the first problem. You are limiting b, by id. And joining on p_id. So mysql is trying to use the index with the combination of both of them which is combine I assume. So removing distince really won't help with this one much, since your indexes will always have a little problems. INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) This is probably the second biggest problem, you will always get a range. And ranges will always be slower. Don't really have a solution without actually touching the data. If you really have more than 50 million records this is really bad. a.id != b.id I would recommend trying to rewrite your query and just focus on table a and f. If you can get rid of them returning the extra 2 and 3 rows, I think that would solve your problem. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Size in Bytes
How do I figure out the physical disk storage size of a table and of the containing database? I'm using InnoDB. When I look at the directory through the OS, I see the Ibdata1 file is about 1GB and a MyServer-bin.06 file that is about 900MB. There are also other -bin and various log files that are much smaller. However, I have two databases on this server and I'd like to know the physical size of each of them separately. I'd also like to know the physical size of the individual tables. Thanks. Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 Confidentiality Note: This message and any accompanying attachments contain information from the law firm Fragomen, Del Rey, Bernsen Loewy, P.C. which is confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this e-mail in error, please notify our offices immediately, by telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED]
newbie mysql table size?
Hi, i read the mysql manual and it's explained the maximum table size in many operating system, but i can't find the maximum size for MS window (win32), what's the maximum table size for win32? My office want to migrate the database server to mysql, and it's contains a millions record. Can mysql hold up tables with each have millions record? Thanks, Alfha K. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie mysql table size?
On Mon, 25 Aug 2003 15:17:25 +0700 Alf Koswara [EMAIL PROTECTED] wrote: Hi, i read the mysql manual and it's explained the maximum table size in many operating system, but i can't find the maximum size for MS window (win32), what's the maximum table size for win32? My office want to migrate the database server to mysql, and it's contains a millions record. Can mysql hold up tables with each have millions record? Thanks, Alfha K. You can have up to 4 billion rows in one MyISAM table. Table size is limited only by your filesystem (as you understand, limits of your filesystem is not the topic to be discussed on this list). In case of win32, I suppose, the best place to search info about filesystem limits would be microsoft.com. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: table size in mysql
Is it possible to MERGE innodb tables? Can't find docs on mysql.com In replication. I guess I can update the slave if the master is not responding, right? Shall I 'stop slave' before , in case the master is down? Also will this create problems when the master comes back and the slave copies updates from the master log file? application/web server || || master -- slave This way I would write master and read on slave as default. But I would like to use slave as mater automatically if the first server is down. Also I would use a few innodb tables. Thanks. |-Original Message- |From: Roger Baklund [mailto:[EMAIL PROTECTED] |Sent: Tuesday, August 05, 2003 6:25 PM |To: [EMAIL PROTECTED] |Cc: NEWMEDIAPLAN |Subject: Re: table size in mysql | | |* NEWMEDIAPLAN | how many records can i put in a mysql table. | |As many as you like, pretty much. The total file size could be delimited by |your OS, but this can be dealt with using MERGE tables (splitting a single |table in multiple files) or InnoDB tables (with multiple table spaces). | | i need a table with more than | 50,000,000 records (just 4 short fields, it's basically a log with | id,action,date,result). What do you think? | |Should not be a problem. | |-- |Roger | | |-- |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]
Re: table size in mysql
* nm Is it possible to MERGE innodb tables? No, MERGE is for MyIsam tables only. InnoDb tables are stored in tablespaces, the problem with file size does not apply. You simply use multiple tablespaces when the data outgrows the OS limits. Can't find docs on mysql.com hm... mysql.com seems to be down right now... try this: URL: http://darkstar.ist.utl.pt/mysql/doc/en/ URL: http://darkstar.ist.utl.pt/mysql/doc/en/Table_types.html URL: http://darkstar.ist.utl.pt/mysql/doc/en/MERGE.html URL: http://darkstar.ist.utl.pt/mysql/doc/en/InnoDB.html In replication. I guess I can update the slave if the master is not responding, right? Replication can be done in several ways, for instance, both your servers could be masters/slaves for eachother. Read about replication here: URL: http://darkstar.ist.utl.pt/mysql/doc/en/Replication.html Shall I 'stop slave' before , in case the master is down? I don't understand this question, sorry! :) Also will this create problems when the master comes back and the slave copies updates from the master log file? Hopefully not. I have never used replication in mysql, but I have seen some people with problems on this list. My impression is that there are rearly bugs in the replication code, but it is a bit complex to deal with the setup and recovery after crashes. You should read the manual carefully. application/web server || || master -- slave This way I would write master and read on slave as default. That is a common and sensible solution. But I would like to use slave as mater automatically if the first server is down. This would be implemented in your middle layer application/web application. This example is python code: try: wconn = connect(primary_write_host) except: admin_warning(primary_write_host is down!) try: wconn = connect(secondary_write_host) except: admin_alert(site is down!) fail(Sorry, database servers are down!) try: rconn = connect(primary_read_host) except: admin_warning(primary_read_host is down!) try: rconn = connect(secondary_read_host) except: admin_alert(site is down!) fail(Sorry, database servers are down!) (The admin_warning()/admin_alert() functions are flood-protected, so you won't get one email/SMS per user click... ;)) Also I would use a few innodb tables. You can combine MyIsam and InnoDb tables in the same database. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table size in mysql
* NEWMEDIAPLAN what variable values /mysql tuning you suggest for more than 2000 potential concurrent users and big tables. 2000 concurrent users is much, at least if you mean 2000 concurrent requests to the database, as opposed to 2000 concurrent users of a web site. It is hard to give you a usefull answer without knowing more about your application. The default setting for 'max_connections' is 100, but this can be raised to 500-1000 for Linux and Solaris, according to the manual: URL: http://www.mysql.com/doc/en/Too_many_connections.html If there are many tables in the database (highly normalized database), and each query requests multiple tables (joins), then you may run out of file handles, because each query need at least one filehandle for each table in the query, usually more. URL: http://www.mysql.com/doc/en/Table_cache.html This is probably a bigger problem with MyIsam tables compared to InnoDb tables, because InnoDb stores data and indexes for all tables in a single (or multiple) tablespace, and only the table format as a separate file, while MyIsam tables use three files for each table: the format file (*.frm), the data file (*.MYD) and the index file (*.MYI). In most cases there is a middle layer server (application server), and this server could use a pool of connections to serve the real clients, it could cache query results, it could cache and delay inserts, and/or it could use a round robin method of selecting from multiple replicated mysql server slaves. Your initial question suggested that you mostly wanted to write to this big table, as it was described as a log file. If you want 2000 people to simultaneously query and/or update the same table, it's a lot more heavy. I think you would need multiple redundant mysql servers and replication, or very heavy hardware, probably both. :) I still have doubts on mysql possibilities, and the correct setup. Need more info on your application. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table size in mysql
* NEWMEDIAPLAN how many records can i put in a mysql table. As many as you like, pretty much. The total file size could be delimited by your OS, but this can be dealt with using MERGE tables (splitting a single table in multiple files) or InnoDB tables (with multiple table spaces). i need a table with more than 50,000,000 records (just 4 short fields, it's basically a log with id,action,date,result). What do you think? Should not be a problem. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table size in mysql
hello. how many records can i put in a mysql table. i need a table with more than 50,000,000 records (just 4 short fields, it's basically a log with id,action,date,result). What do you think? thanks in advance!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maximum table size
Hello As i understand it mysql maintains a file for every table created. Does it mean that the filesystem restricts the maximum size of a given table or do i have the option to make table data spawn more than on file like in oracle? Thanks in advance Mark Vollmann Mit freundlichen Grüßen, Mark Vollmann accelsis technologies GmbH www.accelsis.de Boschetsrieder Str. 20 D - 81379 München Tel. +49 (0)89 1893599-0 Fax. +49 (0)89 1893599-99 [EMAIL PROTECTED] -- Die Information in dieser E-Mail ist vertraulich und kann dem Berufsgeheimnis unterliegen. Sie ist ausschließlich für den Adressaten bestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, ist Ihnen jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. In dieser E-Mail enthaltene Meinungen oder Empfehlungen unterliegen den Bedingungen des jeweiligen Mandatsverhältnisses mit dem Adressaten. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing accelsis technologies client -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum table size
And the manual says : . http://www.mysql.com/doc/en/Table_size.html Jerry - Original Message - From: Mark Vollmann [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 02, 2003 9:39 AM Subject: Maximum table size Hello As i understand it mysql maintains a file for every table created. Does it mean that the filesystem restricts the maximum size of a given table or do i have the option to make table data spawn more than on file like in oracle? Thanks in advance Mark Vollmann Mit freundlichen Grüßen, Mark Vollmann accelsis technologies GmbH www.accelsis.de Boschetsrieder Str. 20 D - 81379 München Tel. +49 (0)89 1893599-0 Fax. +49 (0)89 1893599-99 [EMAIL PROTECTED] -- Die Information in dieser E-Mail ist vertraulich und kann dem Berufsgeheimnis unterliegen. Sie ist ausschließlich für den Adressaten bestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, ist Ihnen jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. In dieser E-Mail enthaltene Meinungen oder Empfehlungen unterliegen den Bedingungen des jeweiligen Mandatsverhältnisses mit dem Adressaten. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing accelsis technologies client -- 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]
Re: Maximum table size
If your os permits, mysql can handle file sizes up to terabytes. You don't need to spawn your tables. But you have to be careful when creating your myisam tables. If you don't specify your table size limit with the max_rows option when creating table mysql will create a table with 4G file size limit. If you use innodb you can setup auto extend feature, so you don't need to control your table size, if it reach its maximum innodb will automatically extend its file size. [EMAIL PROTECTED] wrote: Hello As i understand it mysql maintains a file for every table created. Does it mean that the filesystem restricts the maximum size of a given table or do i have the option to make table data spawn more than on file like in oracle? Thanks in advance Mark Vollmann Mit freundlichen Grüßen, Mark Vollmann accelsis technologies GmbH www.accelsis.de Boschetsrieder Str. 20 D - 81379 München Tel. +49 (0)89 1893599-0 Fax. +49 (0)89 1893599-99 [EMAIL PROTECTED] -- Die Information in dieser E-Mail ist vertraulich und kann dem Berufsgeheimnis unterliegen. Sie ist ausschließlich für den Adressaten bestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, ist Ihnen jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. In dieser E-Mail enthaltene Meinungen oder Empfehlungen unterliegen den Bedingungen des jeweiligen Mandatsverhältnisses mit dem Adressaten. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing accelsis technologies client -- Veysel Harun Sahin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database/table size
Is it possible to limit the size of a dbase or table? --- Keith Schuster Schuster Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Maximum Table Size Reached
Hi, Would anyone be able to confirm that mysqld-max-nt.exe does not have a 4 GB limitation. I'd like the DB to go up to 40GB and 330 million rows. I am flexible, I'll use the biggest DB I can create. ISAM seems to be the obvious choice but would INODB perform better on inserts for a large DB, since I got two indices besides the primary key: It is a common situation in a database application that the primary key is a unique identifier and new rows are inserted in the ascending order of the primary key. Thus the insertions to the clustered index do not require random reads from a disk. On the other hand, secondary indexes are usually non-unique and insertions happen in a relatively random order into secondary indexes. This would cause a lot of random disk I/Os without a special mechanism used in InnoDB. ... The insert buffer is periodically merged to the secondary index trees in the database. Often we can merge several insertions on the same page in of the index tree, and hence save disk I/Os. It has been measured that the insert buffer can speed up insertions to a table up to 15 times. Thanks could you switch to the max version of MySQL. This would increase you database size to some 2**64. Just install mysqld-max-nt.exe as your service to enable the support for 64 bit pointers. The only manual work you will have to do is to copy record for record from the old file to the new file. Your application does not have to be changed. Erich Brian Seccombe wrote: Hi there, I hope I am doing this right. We have a table running on a Window NT server on a NTFS partition. We are using MySQL 3.23.52-nt. The name of the database is scan, and the table in question is called scan too. We are a transport company, and store in this database a scanned copy of all the days POD's. This has grown rather fast and we have now reached the 4 gig default for a table. I have read that you can modify it too go above this by altering max rows, etc. however it does not seem to make a difference. Is there an easy way to make a table that essentially has no limit except the size of the hard drive? Many Thanks, Brian - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Temporary Table Size / Big Queries
SHOW STATUS (after 24 hrs uptime): created_tmp_disk_tables = 597 created_tmp_tables = 8893. SHOW VARIABLES: tmp_table_size = 33554432. Does this mean that 597 queries have created a result set in excess of 33MB? How can I find these rogue queries and optimise them? I could increase the tmp_table_size (over 1GB RAM) but I am concerned that tables this big are being created! Can this be logged somehow? Thanks, John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[MySQL v3.23.53a] BLOB Data and Table Size
I have two tables containing a BLOB cloumn as; -- supposed to be able to handle up to about 16MB CRETAE TABLE gallery1 ( idINTEGER, titleVARCHAR(255), mediumimageMEDIUMBLOB ); -- supposed to be able to handle up to about 4GB CRETAE TABLE gallery2 ( idINTEGER, titleVARCHAR(255), largeimageLONGBLOB ); Both table show the size about 48KB initially. And I added a 16MB(about 7,863KB) image into the table, gallery1 and a 4GB(about 11,620KB) into the table, gallery2, respectively. The size of gallery1, i,e, gallery1.db, shows 8,232KB and the size of gallery2, i.e., gallery2.db, shows 11,712KB. Each table contains only one entry. And I deleted both entries from two tables. So both tables contain no entry. However, the size of tables remain same as after insertion made. Q: How come the size of table does not decrease after deletion? Thank you. And regards, Pae P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL v3.23.53a] BLOB Data and Table Size
On 26 Nov 2002, at 8:52, Pae Choi wrote: Q: How come the size of table does not decrease after deletion? MySQL will reuse that space when you insert new records. To recover the space, you need to optimize the table: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL v3.23.53a] BLOB Data and Table Size
First of all, thanks for your reply as well as a pointer to the Doc. I tried the OPTIMIZE command as follows: mysql optimize table gallery; mysql optimize table gallery2; Both does not contain any entry since I deleted the BLOB data. But the size of tables, gallery.db and gallery2.db does not decresed. It remains as it was. Any comments? Thanks. Pae P.S.: The table is created with type=bdb option as: CREATE TABLE ... TYPE=BDB; On 26 Nov 2002, at 8:52, Pae Choi wrote: Q: How come the size of table does not decrease after deletion? MySQL will reuse that space when you insert new records. To recover the space, you need to optimize the table: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: [MySQL v3.23.53a] BLOB Data and Table Size
Pae, Tuesday, November 26, 2002, 6:52:20 PM, you wrote: PC I have two tables containing a BLOB cloumn as; PC -- supposed to be able to handle up to about 16MB PC CRETAE TABLE gallery1 ( PC idINTEGER, PC titleVARCHAR(255), PC mediumimageMEDIUMBLOB PC ); PC -- supposed to be able to handle up to about 4GB PC CRETAE TABLE gallery2 ( PC idINTEGER, PC titleVARCHAR(255), PC largeimageLONGBLOB PC ); PC Both table show the size about 48KB initially. PC And I added a 16MB(about 7,863KB) image into the PC table, gallery1 and a 4GB(about 11,620KB) into PC the table, gallery2, respectively. PC The size of gallery1, i,e, gallery1.db, shows 8,232KB PC and the size of gallery2, i.e., gallery2.db, shows PC 11,712KB. PC Each table contains only one entry. And I deleted PC both entries from two tables. So both tables contain PC no entry. PC However, the size of tables remain same as after PC insertion made. PC Q: How come the size of table does not decrease PC after deletion? It's an expected behaviour. According to the MySQL manual: In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL v3.23.53a] BLOB Data and Table Size
no database manager that i know of automatically packs the database after every record deletion. it would be too costly. that is one of the reasons that most of us do not store blobs in databases, but store them externally. I have two tables containing a BLOB cloumn as; -- supposed to be able to handle up to about 16MB CRETAE TABLE gallery1 ( idINTEGER, titleVARCHAR(255), mediumimageMEDIUMBLOB ); -- supposed to be able to handle up to about 4GB CRETAE TABLE gallery2 ( idINTEGER, titleVARCHAR(255), largeimageLONGBLOB ); Both table show the size about 48KB initially. And I added a 16MB(about 7,863KB) image into the table, gallery1 and a 4GB(about 11,620KB) into the table, gallery2, respectively. The size of gallery1, i,e, gallery1.db, shows 8,232KB and the size of gallery2, i.e., gallery2.db, shows 11,712KB. Each table contains only one entry. And I deleted both entries from two tables. So both tables contain no entry. However, the size of tables remain same as after insertion made. Q: How come the size of table does not decrease after deletion? Thank you. And regards, Pae P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL v3.23.53a] BLOB Data and Table Size
At 10:36 -0800 11/26/02, Pae Choi wrote: First of all, thanks for your reply as well as a pointer to the Doc. I tried the OPTIMIZE command as follows: mysql optimize table gallery; mysql optimize table gallery2; Both does not contain any entry since I deleted the BLOB data. But the size of tables, gallery.db and gallery2.db does not decresed. It remains as it was. Any comments? Thanks. Pae P.S.: The table is created with type=bdb option as: CREATE TABLE ... TYPE=BDB; OPTIMIZE performs defragmentation only for MyISAM tables. You can shrink your tables by dumping and reloading them: mysqldump --opt db_name gallery gallery2 dump.sql mysql db_name dump.sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL v3.23.53a] BLOB Data and Table Size
Paul, It really did the trick. Thanks. Regards, Pae - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Pae Choi [EMAIL PROTECTED]; Keith C. Ivey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 10:59 AM Subject: Re: [MySQL v3.23.53a] BLOB Data and Table Size At 10:36 -0800 11/26/02, Pae Choi wrote: First of all, thanks for your reply as well as a pointer to the Doc. I tried the OPTIMIZE command as follows: mysql optimize table gallery; mysql optimize table gallery2; Both does not contain any entry since I deleted the BLOB data. But the size of tables, gallery.db and gallery2.db does not decresed. It remains as it was. Any comments? Thanks. Pae P.S.: The table is created with type=bdb option as: CREATE TABLE ... TYPE=BDB; OPTIMIZE performs defragmentation only for MyISAM tables. You can shrink your tables by dumping and reloading them: mysqldump --opt db_name gallery gallery2 dump.sql mysql db_name dump.sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
fs/table size ...
Hi ! This is my first posting to this group so: hello to everybody. I`m using snort (IDS) with logging to MySQL DB. Snort is little bit noisy and it inserts a lot of datas in DB - about 130MB/4h. I`m confused about: is it better to use fs with support more then 2GB files (ext3, ReiserFS (which is not so good for large files), or tables build with raid option (i have read in mysql faq that indexes are not divided ...so that is a problem also0 This db has to have good effitency ... ps. sorry for my poor english. greetz boka --- Tytus na Dzikim Zachodzie http://tytus.wp.pl/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
fs/table size ... cd
Hi ! i forgot to tell You (sorry !) that DB has to be run on linux. ps. sql,query - included for filter. greetz boka --- Tytus na Dzikim Zachodzie http://tytus.wp.pl/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Table size
Which Operating Systems limit MySQL to a 2GB table size? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Table size
AIX if you didnt set the Large File System on, and some Linux Kernels. However, they dont limit anything but any file in the system to that size. For instance, the same see the OS if you have a 2,5 GB (not allowed - too big) or a 2,5 db file (the same reason) thats why there are out there some work arounds, like the Innodb. I hope this will make you an idea. Leonardo Javier Beln. AFIP-AR. - Original Message - From: Scott Pippin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:17 PM Subject: MySQL Table size Which Operating Systems limit MySQL to a 2GB table size? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: MySQL Table size
AIX if you didnt set the Large File System on, and some Linux Kernels. However, they dont limit anything but any file in the system to that size. For instance, the same see the OS if you have a 2,5 GB (not allowed - too big) or a 2,5 db file (the same reason) thats why there are out there some work arounds, like the Innodb. I hope this will make you an idea. Leonardo Javier Beln. AFIP-AR. - Original Message - From: Scott Pippin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:17 PM Subject: MySQL Table size Which Operating Systems limit MySQL to a 2GB table size? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Table size
Scott Pippin wrote: Which Operating Systems limit MySQL to a 2GB table size? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php hello, Linux 2.2.x at one time, but that may have changed. If you want to use Linux, use the 2.4.x kernel. I would recommend RedHat 7.2 or 7.3 (Oracle has been certified on 7.2 which helps give that warm fuzzy feeling). RedHat's kernels are also built from Alan Cox's kernel tree which contains misc. performance patches. walt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Table size in AIX
I found out my AIX box's maximum file size is 2GB. What can I do if I will have MySQL tables that are possibly over 2GB? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Table size in AIX
On Tue, 27 Aug 2002, Scott Pippin wrote: I found out my AIX box's maximum file size is 2GB. What can I do if I will have MySQL tables that are possibly over 2GB? Hello, There are basically two things you should consider: 1) Switch to InnoDB tables and create several data files just under 2GB each 2) Use MyISAM RAID option This requires you to compile your MySQL with --with-raid configuration parameter. Then MySQL will create separate 00, 01, 02, 03 and so on directories with a piece of your table in each of them. See Manual section 6.5.3 (CREATE TABLE syntax) for detailed coverage. You will want RAID_TYPE=STRIPED configuration since that facilitates for the spreading of the .MYD files. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Table size in AIX
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 27 August 2002 20:05, Scott Pippin wrote: I found out my AIX box's maximum file size is 2GB. What can I do if I will have MySQL tables that are possibly over 2GB? You could either use MERGE tables: http://www.mysql.com/doc/en/MERGE.html Or you could use the RAID_TYPE option: http://www.mysql.com/doc/en/CREATE_TABLE.html Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9a87rSVDhKrJykfIRAv52AJ9Xm+4rStIcU4I3qNTbgLgmvSusswCdGvsT /xcpFFw4pc3gJ6dZhyU6nVc= =/xGx -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Temp Table size?
When I create a temp table, If I put to much in it, I get a Temp Table [Table name] is Full error. I changed my tmp_table_size to a ver large number. Still same error, Any Ideas? This is windows 2000 MySQL 4.01 Config Loaded Form my.ini in winnt dir. Show Variables for server shows new tmp_table_size set In ini file. mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Temp Table size?
Hi Mike, Possibly out of available memory. (Or disk space assuming that's what it's hitting) Mike Grover wrote: When I create a temp table, If I put to much in it, I get a Temp Table [Table name] is Full error. I changed my tmp_table_size to a ver large number. Still same error, Any Ideas? This is windows 2000 MySQL 4.01 Config Loaded Form my.ini in winnt dir. Show Variables for server shows new tmp_table_size set In ini file. mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Colin Faber (303) 736-5160 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Temp Table size?
Michael Grover wrote: This simple query: create temporary table mike select * from wy union all select * from ks; will cause the following error: [root@localhost:3306] ERROR 1114: The table '#sqla08_3_1' is full what's wierd is I only have a #sqla08_3_0 table in my c:\winnt\temp dir... The second table is being created in memory, If you increase max_heap_table_size enough to cover the total result size, there is no error. What Im worried about is that if you have alot of user's, Alot of memory will get used fast for unions? mike Colin Faber wrote: Um.. by default the temp table should spill into a scratch disk like swap system.. normally this is handled in /tmp possibly you're allocating all available real memory all swap and filling /tmp. Look into that. Mike Grover wrote: It's memory, A query with a union apparently uses memory to do a union Before it pushes the results into a temp table? Any way around this? mike -Original Message- From: Colin Faber [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 2:58 PM To: Mike Grover Cc: [EMAIL PROTECTED] Subject: Re: Temp Table size? Hi Mike, Possibly out of available memory. (Or disk space assuming that's what it's hitting) Mike Grover wrote: When I create a temp table, If I put to much in it, I get a Temp Table [Table name] is Full error. I changed my tmp_table_size to a ver large number. Still same error, Any Ideas? This is windows 2000 MySQL 4.01 Config Loaded Form my.ini in winnt dir. Show Variables for server shows new tmp_table_size set In ini file. mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Colin Faber (303) 736-5160 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Colin Faber (303) 736-5160 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.22.32: max table size
As show table status doesn't appear to be supported in this older version I'm using, how did you who used this version get max table size information? I haven't been to find anything from the list archives/manual about this. Brian sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 3.22.32: max table size
Quoted from http://www.mysql.com/doc/T/a/Table_size.html is: By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. See section 4.5.6 SHOW Syntax. Gurhan -Original Message- From: Brian Warn [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:28 AM To: 'MySQL List' Subject: 3.22.32: max table size As show table status doesn't appear to be supported in this older version I'm using, how did you who used this version get max table size information? I haven't been to find anything from the list archives/manual about this. Brian sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 3.22.32: max table size
I did see that reference, but found that myisamchk wasn't an option either. I did find isamchk is available and have been seeing what information it is able to provide. I'm wondering now whether summing the table.ISD, table.ISM, and table.frm filesizes that I get from a simple ls -l output will give me the total diskspace occupied by the table? Brian sql,query -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 8:58 AM To: Brian Warn; 'MySQL List' Subject: RE: 3.22.32: max table size Quoted from http://www.mysql.com/doc/T/a/Table_size.html is: By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. See section 4.5.6 SHOW Syntax. Gurhan -Original Message- From: Brian Warn [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:28 AM To: 'MySQL List' Subject: 3.22.32: max table size As show table status doesn't appear to be supported in this older version I'm using, how did you who used this version get max table size information? I haven't been to find anything from the list archives/manual about this. Brian sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 3.22.32: max table size
You, like me, are using 3.22 which does not have the myisam table type (or others for that matter). ISAM I believe, has a lower limit. They do support sizes less that 4GB (up to the OS file size limits). The three file extentions total do constitute the total table size, but I believe that each file must fall under the 4GB limit, not the total. isamchk is the equivalent to myisamchk for ISAM table types. On Fri, 22 Feb 2002, Brian Warn wrote: Date: Fri, 22 Feb 2002 09:11:07 -0800 From: Brian Warn [EMAIL PROTECTED] To: 'Gurhan Ozen' [EMAIL PROTECTED], 'MySQL List' [EMAIL PROTECTED] Subject: RE: 3.22.32: max table size I did see that reference, but found that myisamchk wasn't an option either. I did find isamchk is available and have been seeing what information it is able to provide. I'm wondering now whether summing the table.ISD, table.ISM, and table.frm filesizes that I get from a simple ls -l output will give me the total diskspace occupied by the table? Brian sql,query -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 8:58 AM To: Brian Warn; 'MySQL List' Subject: RE: 3.22.32: max table size Quoted from http://www.mysql.com/doc/T/a/Table_size.html is: By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. See section 4.5.6 SHOW Syntax. Gurhan -Original Message- From: Brian Warn [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:28 AM To: 'MySQL List' Subject: 3.22.32: max table size As show table status doesn't appear to be supported in this older version I'm using, how did you who used this version get max table size information? I haven't been to find anything from the list archives/manual about this. Brian sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 3.22.32: max table size
Fromt the mysql.com site: http://www.mysql.com/doc/I/S/ISAM.html for all the limits.. Remember that your files sizes may be smaller because of your OS. On Fri, 22 Feb 2002, Brian Warn wrote: Date: Fri, 22 Feb 2002 09:11:07 -0800 From: Brian Warn [EMAIL PROTECTED] To: 'Gurhan Ozen' [EMAIL PROTECTED], 'MySQL List' [EMAIL PROTECTED] Subject: RE: 3.22.32: max table size I did see that reference, but found that myisamchk wasn't an option either. I did find isamchk is available and have been seeing what information it is able to provide. I'm wondering now whether summing the table.ISD, table.ISM, and table.frm filesizes that I get from a simple ls -l output will give me the total diskspace occupied by the table? Brian sql,query -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 8:58 AM To: Brian Warn; 'MySQL List' Subject: RE: 3.22.32: max table size Quoted from http://www.mysql.com/doc/T/a/Table_size.html is: By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. See section 4.5.6 SHOW Syntax. Gurhan -Original Message- From: Brian Warn [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:28 AM To: 'MySQL List' Subject: 3.22.32: max table size As show table status doesn't appear to be supported in this older version I'm using, how did you who used this version get max table size information? I haven't been to find anything from the list archives/manual about this. Brian sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Checking the Table Size
Hi guys, I am not able to insert into the MySQL database anymore. I think it has reached the table size. How do i check the size to see if it has reached the table size? Also let me know how to increase the table size in case it is reached the limit. Thanks, Satish = The only person in the world who is enviable is one who does not envy others __ Satish Santhakumar Graduate Student IIT, Chicago and Computer Programmer University of Chicago. __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Checking the Table Size
Satish: You can see how much space is used (among other things) with a query like: show table status from database like 'Syslog'; Increasing the maximum table size is a little more complicated and depends on what OS and table type you're using. Eric -Original Message- From: Satish Santhakumar [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 2:21 PM To: [EMAIL PROTECTED] Subject: Checking the Table Size =20 =20 Hi guys, =20 I am not able to insert into the MySQL database anymore. I think it has reached the table size. How do i check the size to see if it has reached the table size? Also let me know how to increase the table size in case it is reached the limit. Thanks, Satish =20 =3D=3D=3D=3D=3D The only person in the world who is enviable is one who does=20 not envy others __ Satish Santhakumar Graduate Student IIT, Chicago and Computer Programmer University of Chicago. =20 __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com =20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) =20 To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail=20 [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php =20 =20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Checking the Table Size
Satish, Saturday, February 09, 2002, 12:21:06 AM, you wrote: SS Hi guys, SS I am not able to insert into the MySQL database SS anymore. I think it has reached the table size. How do SS i check the size to see if it has reached the table SS size? Also let me know how to increase the table size SS in case it is reached the limit. You can check table size using SHOW TABLE STATUS. Look at: http://www.mysql.com/doc/S/H/SHOW_TABLE_STATUS.html The maximum table size depends on your operation system. See at: http://www.mysql.com/doc/T/a/Table_size.html SS Thanks, SS Satish -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Checking the Table Size
Satish, Saturday, February 09, 2002, 12:21:06 AM, you wrote: SS Hi guys, SS I am not able to insert into the MySQL database SS anymore. I think it has reached the table size. How do SS i check the size to see if it has reached the table SS size? Also let me know how to increase the table size SS in case it is reached the limit. You can check table size using SHOW TABLE STATUS. Look at: http://www.mysql.com/doc/S/H/SHOW_TABLE_STATUS.html The maximum table size depends on your operation system. See at: http://www.mysql.com/doc/T/a/Table_size.html SS Thanks, SS Satish -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Checking the Table Size
Hi guys, I am not able to insert into the MySQL database anymore. I think it has reached the table size. How do i check the size to see if it has reached the table size? Also let me know how to increase the table size in case it is reached the limit. Thanks, Satish = The only person in the world who is enviable is one who does not envy others __ Satish Santhakumar Graduate Student IIT, Chicago and Computer Programmer University of Chicago. __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Checking the Table Size
Satish: You can see how much space is used (among other things) with a query like: show table status from database like 'Syslog'; Increasing the maximum table size is a little more complicated and depends on what OS and table type you're using. Eric -Original Message- From: Satish Santhakumar [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 2:21 PM To: [EMAIL PROTECTED] Subject: Checking the Table Size Hi guys, I am not able to insert into the MySQL database anymore. I think it has reached the table size. How do i check the size to see if it has reached the table size? Also let me know how to increase the table size in case it is reached the limit. Thanks, Satish = The only person in the world who is enviable is one who does not envy others __ Satish Santhakumar Graduate Student IIT, Chicago and Computer Programmer University of Chicago. __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HEAP table size
Hello, I am using HEAP tables and would like to see how much memory they use. I found this snipped of instruction in the MySQL-documentation: -- The memory needed for one row in a HEAP table is: SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2) + ALIGN(length_of_row+1, sizeof(char*)) sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines. -- But frankly, I don't get it. Could someone please explain this to me? Lets say I have a heap-table that looks like this: CREATE TABLE get_mem( idINT NOT NULL, name CHAR(15) NOT NULL, nrMEDIUMINT NOT NULL, PRIMARY KEY(id) ) TYPE = HEAP; How would I calculate the memory used for 1 row? Thank you, Mathias ___ Spara filer på nätet. Lagra upp till 500 Mb på Passagen http://webbdrive.passagen.se - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redhat 7.2 Linux Maximum Database/Table Size
Hi, Jeremy Zawodny wrote: On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. that implies that MySQL itself (and all its tools) is able to handle files bigger than 2 GB? Is that the case with MySQL on Solaris8 also? Regards, Frank. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 8 days, processed 229,155,814 queries (294/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Dr. Frank Ullrich, Netzwerkadministration Verlag Heinz Heise GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redhat 7.2 Linux Maximum Database/Table Size
Yes, all programs which don't have built in limits can go smoewhere in the range of I think 8 TB but I have only tested it to 50 GB with MySQL. As for Solaris this is also true. Benjamin Arai [EMAIL PROTECTED] On Mon, 14 Jan 2002, Dr. Frank Ullrich wrote: Hi, Jeremy Zawodny wrote: On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. that implies that MySQL itself (and all its tools) is able to handle files bigger than 2 GB? Is that the case with MySQL on Solaris8 also? Regards, Frank. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 8 days, processed 229,155,814 queries (294/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- -- Benjamin Arai [EMAIL PROTECTED] -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redhat 7.2 Linux Maximum Database/Table Size
On Mon, Jan 14, 2002 at 09:55:16AM +0100, Dr. Frank Ullrich wrote: Hi, Jeremy Zawodny wrote: On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. that implies that MySQL itself (and all its tools) is able to handle files bigger than 2 GB? That's correct. File size limits, for the most part, are OS limitations, not limitations in MySQL. Is that the case with MySQL on Solaris8 also? Yes, it is. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 11 days, processed 263,542,340 queries (267/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Redhat 7.2 Linux Maximum Database/Table Size
On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. I understand that the RAID option can help break the 2GB/4GB barrier, esp. on Linux machines. But how do you surpass the Max_data_length restriction of 4294967295 bytes on a RAIDED table? do you increase max_rows on such a table? if so, would this affect the performance of a large table e.g. above 50GB? Without changing max_rows it seems that MySQL will still restrict the table size to 4GB, even with raid_chunks and raid_chunksize set to e.g. 50 and 256? Anyone out there tweaked these settings before and what was the outcome? Thanks. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Redhat 7.2 Linux Maximum Database/Table Size
You don't understand. You need to use a operating system which has a filesystem which lifts the 2 GB limit. By default from every Linux distrobtion I have used, if the OS has lifted the limit then they usually fix all the programs to uses the new file size capabilities. Raid doesn't help at all for the limit because the physical limit by the OS is a file size limit and not a partition or drive limit. Increase the max rows as you see appropriate but that is almost never the problem in terms of file size issues like you are having. Raids don't really help Table performance because in almost all cases the bottlneck is caused by the drives access time. raiding drives doesn't increase the access time therefore, you are most likely not going to see and poerformance increases using a raid system unless you are change to drives to ones with lower access times. Benjamin Arai [EMAIL PROTECTED] On Mon, 14 Jan 2002, Geoffrey Soh wrote: On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. I understand that the RAID option can help break the 2GB/4GB barrier, esp. on Linux machines. But how do you surpass the Max_data_length restriction of 4294967295 bytes on a RAIDED table? do you increase max_rows on such a table? if so, would this affect the performance of a large table e.g. above 50GB? Without changing max_rows it seems that MySQL will still restrict the table size to 4GB, even with raid_chunks and raid_chunksize set to e.g. 50 and 256? Anyone out there tweaked these settings before and what was the outcome? Thanks. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- -- Benjamin Arai [EMAIL PROTECTED] -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Redhat 7.2 Linux Maximum Database/Table Size
Hi, Sorry for being unclear :) I was talking about the --with-raid compilation option in MySQL that lets you create tables with the RAID_TYPE RAID_CHUNKS RAID_CHUNKSIZE options, allowing tables to span across multiple data files, each file having a size below the OS limit. Thanks for the response. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 1:52 AM To: Geoffrey Soh Cc: [EMAIL PROTECTED] Subject: RE: Redhat 7.2 Linux Maximum Database/Table Size You don't understand. You need to use a operating system which has a filesystem which lifts the 2 GB limit. By default from every Linux distrobtion I have used, if the OS has lifted the limit then they usually fix all the programs to uses the new file size capabilities. Raid doesn't help at all for the limit because the physical limit by the OS is a file size limit and not a partition or drive limit. Increase the max rows as you see appropriate but that is almost never the problem in terms of file size issues like you are having. Raids don't really help Table performance because in almost all cases the bottlneck is caused by the drives access time. raiding drives doesn't increase the access time therefore, you are most likely not going to see and poerformance increases using a raid system unless you are change to drives to ones with lower access times. Benjamin Arai [EMAIL PROTECTED] On Mon, 14 Jan 2002, Geoffrey Soh wrote: On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. I understand that the RAID option can help break the 2GB/4GB barrier, esp. on Linux machines. But how do you surpass the Max_data_length restriction of 4294967295 bytes on a RAIDED table? do you increase max_rows on such a table? if so, would this affect the performance of a large table e.g. above 50GB? Without changing max_rows it seems that MySQL will still restrict the table size to 4GB, even with raid_chunks and raid_chunksize set to e.g. 50 and 256? Anyone out there tweaked these settings before and what was the outcome? Thanks. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- -- Benjamin Arai [EMAIL PROTECTED] -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Redhat 7.2 Linux Maximum Database/Table Size
Oh, I don't know how well that would work. But I do know you will still run into limitations of 2 GB, so if you had 2 drives raided then you now have a 4 GB limit which doesn't help to much. I would think the best solution would be to re-compile your kernel to include large file support and tweak you MySQL for limit-less records. That's what I do when I run into older distro's. Benjamin Arai [EMAIL PROTECTED] On Mon, 14 Jan 2002, Geoffrey Soh wrote: Hi, Sorry for being unclear :) I was talking about the --with-raid compilation option in MySQL that lets you create tables with the RAID_TYPE RAID_CHUNKS RAID_CHUNKSIZE options, allowing tables to span across multiple data files, each file having a size below the OS limit. Thanks for the response. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 1:52 AM To: Geoffrey Soh Cc: [EMAIL PROTECTED] Subject: RE: Redhat 7.2 Linux Maximum Database/Table Size You don't understand. You need to use a operating system which has a filesystem which lifts the 2 GB limit. By default from every Linux distrobtion I have used, if the OS has lifted the limit then they usually fix all the programs to uses the new file size capabilities. Raid doesn't help at all for the limit because the physical limit by the OS is a file size limit and not a partition or drive limit. Increase the max rows as you see appropriate but that is almost never the problem in terms of file size issues like you are having. Raids don't really help Table performance because in almost all cases the bottlneck is caused by the drives access time. raiding drives doesn't increase the access time therefore, you are most likely not going to see and poerformance increases using a raid system unless you are change to drives to ones with lower access times. Benjamin Arai [EMAIL PROTECTED] On Mon, 14 Jan 2002, Geoffrey Soh wrote: On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. I understand that the RAID option can help break the 2GB/4GB barrier, esp. on Linux machines. But how do you surpass the Max_data_length restriction of 4294967295 bytes on a RAIDED table? do you increase max_rows on such a table? if so, would this affect the performance of a large table e.g. above 50GB? Without changing max_rows it seems that MySQL will still restrict the table size to 4GB, even with raid_chunks and raid_chunksize set to e.g. 50 and 256? Anyone out there tweaked these settings before and what was the outcome? Thanks. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- -- Benjamin Arai [EMAIL PROTECTED] -- -- -- Benjamin Arai [EMAIL PROTECTED] -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redhat 7.2 Linux Maximum Database/Table Size
Benjamin Arai [EMAIL PROTECTED] writes: Oh, I don't know how well that would work. But I do know you will still run into limitations of 2 GB, so if you had 2 drives raided then you now have a 4 GB limit which doesn't help to much. I would think the best solution would be to re-compile your kernel to include large file support and tweak you MySQL for limit-less records. That's what I do when I run into older distro's. If you're running a 2.4 kernel (or some 2.2 kernels, like the one in Red Hat Linux 6.2 enterprise), that's not an issue. You do need glibc 2.2, though - and in many cases, you need to add preprocessor flags as well. (note that glibc 2.1, which is used if you use the old compat compiler on Red Hat Linux 7.x, does not have full LFS support. I.e. compiling with egcs considered harmful) -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
max table size on Windows 2000
Hi, is it right, that the max. size of a table is 4 GB on WindowsOS ? I am using MySql from a Java - Program for a Log-Database and I get the Message: The table 'LOG4_FFE' is full. Is there another way to get around the Problem as using then to use the MERGE - utility? I am using My Sql 4.0.0-alpha and MyIsam Tables! Best Regards Peter ff-eCommerce softwareengineer Tel.: +49 69 95 63 47 57 http://www.ff-ecommerce.de Mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: max table size on Windows 2000
At 16:58 14/01/2002 +0100, Peter Arnold wrote: Hi! Hi, is it right, that the max. size of a table is 4 GB on WindowsOS ? I am using MySql from a Java - Program for a Log-Database and I get the Message: The table 'LOG4_FFE' is full. For this issue there are two options: 1- Use an OS of the family NT with NTFS file system. The size of the file is limited to the volume size. 2- Use table type InnoDB on FAT32 file system. This table uses the space table architecture. Regards, Miguel Is there another way to get around the Problem as using then to use the MERGE - utility? I am using My Sql 4.0.0-alpha and MyIsam Tables! Best Regards Peter ff-eCommerce softwareengineer Tel.: +49 69 95 63 47 57 http://www.ff-ecommerce.de Mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Redhat 7.2 Linux Maximum Database/Table Size
On Mon, 14 Jan 2002, Benjamin Arai wrote: Raids don't really help Table performance because in almost all cases the bottlneck is caused by the drives access time. raiding drives doesn't increase the access time therefore, you are most likely not going to see and poerformance increases using a raid system unless you are change to drives to ones with lower access times. While I'm sure it's possible to prove this statement with a contrived benchmark, it's not at all true in the general case. A striped RAID array will drastically increase performance in real-world tests, where many simultaneous queries are taking place over random sections of the database. Think about it: access time is a function of how far (on average) the head will have to move to access the appropriate cylinder, and how fast the drive is spinning (i.e., how long you have to wait for the appropriate sector to appear under the head). If you have only one head per cylinder and you're accessing random cylinders, access time will be at its worst. If you have a head per cylinder, access time will be at its best. In a striped array, you're increasing the number of heads per cylinder, so you're decreasing the chance any given head will have to move, so average access time drops. james montebello - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table size limitations: Error Handling and Raid 0
My question is two-fold, first regarding error handing of table size limitations, and secondly performance and other implications of raid striping. I'm building a system with an integrated MySQL database and there is a potential for filling up tables to the maximum file size for the OS I'm using (linux 2.2.x kernel, ext2 fs, 2gb max file size). I'd like to provide users with a graceful error-handling mechanism, essentially telling them: The database is full. You must remove stuff before you can add more. Along with that, a % used number would be nice. Is there a method (through a mysql query) of determining how much space the database is taking up? It seems like the alternative is looking directly at the file system's record. Secondly, to get past the 2gb limit I'm considering using Raid Striping on a single partition. I'm curious if people have done this and what the performance implications are (how much worse is 2, 3, 10, 20 files than one file), and does doing this successfully avoid the 2gb limit (or is there some other limiting factor?) What is the next limiting factor beyond the file system's 2gb limit given unlimited storage? [that one is just curiosity] Thanks, Eric Mayers Software Engineer Captus Networks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redhat 7.2 Linux Maximum Database/Table Size
On Fri, Jan 11, 2002 at 08:51:33AM +0100, Dr. Frank Ullrich wrote: Benjamin, can you also grow MyISAM tables to such sizes? You can. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 8 days, processed 229,155,814 queries (294/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Redhat 7.2 Linux Maximum Database/Table Size
I have been testing Redhat 7.2 Linux for about 3 month's. I few notes regarding the installation which might be helpful for MySQL Admins. - Maximum Table Size: Limited online by disk space. (I have tested tables upto 50 gigs) - Maximum Records Per Table: Limited only by available disk space. (I have tested tables upto 50 gigs) Note: Redhat 7.2 uses ext3 filesystem and must be modified after installation for best performance. ext3 must be set to minimal journaling or none at all. This doesn't effect max table size. From a stock install of Redhat 7.2 tables can reach 50+ gigs. Benjamin Arai [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Redhat 7.2 Linux Maximum Database/Table Size
All right How do you set ext3 to minimum journaling?? -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 10, 2002 9:22 PM To: [EMAIL PROTECTED] Subject: Redhat 7.2 Linux Maximum Database/Table Size I have been testing Redhat 7.2 Linux for about 3 month's. I few notes regarding the installation which might be helpful for MySQL Admins. - Maximum Table Size: Limited online by disk space. (I have tested tables upto 50 gigs) - Maximum Records Per Table: Limited only by available disk space. (I have tested tables upto 50 gigs) Note: Redhat 7.2 uses ext3 filesystem and must be modified after installation for best performance. ext3 must be set to minimal journaling or none at all. This doesn't effect max table size. From a stock install of Redhat 7.2 tables can reach 50+ gigs. Benjamin Arai [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Redhat 7.2 Linux Maximum Database/Table Size
On Thu, Jan 10, 2002 at 07:22:10PM -0800, Benjamin Arai wrote: I have been testing Redhat 7.2 Linux for about 3 month's. I few notes regarding the installation which might be helpful for MySQL Admins. - Maximum Table Size: Limited online by disk space. (I have tested tables upto 50 gigs) - Maximum Records Per Table: Limited only by available disk space. (I have tested tables upto 50 gigs) Note: Redhat 7.2 uses ext3 filesystem and must be modified after installation for best performance. ext3 must be set to minimal journaling or none at all. What's the point of ext3 if you're gonna disable journaling? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 8 days, processed 206,821,811 queries (290/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
