Re: Bug related to large tables and it's indexes on Win2k
- Original Message - From: Keith C. Ivey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Jared Richardson [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 5:24 PM Subject: Re: Bug related to large tables and it's indexes on Win2k | On 4 Jun 2002, at 15:43, Jared Richardson wrote: | | | AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; | | | | Why do you use AVG_ROW_LENGTH=4096? It seems to me the max record | | length is 528...? | | | According to the MySql docs, the max table size is AVG_ROW_LENGTH * | MAX_ROWS | | We were trying to ensure the maximum amount of growth... this table | can grow very very large | | That's fine, but if you make the max size bigger than what you really | need, you bloat your index and slow things down for no benefit. As I | understand it, with the default max table size of 4 GB (for dynamic- | record MyISAM tables), the index will have 4-byte pointers to the | records. The next step would be 5-byte pointers, which would allow a | max table size of 1024 GB (or 1 TB). But you're asking for an even | higher max table size, forcing MySL to use 6-byte pointers in the | index, which allows a max table size of 256 TB. | | I don't think there's any difference between setting AVG_ROW_LENGTH | to 4096 and setting it to 528, since either will put you in the same | size range when combined with MAX_ROWS=4294967295. | That makes sense. | Think about how many rows you're actually going to have and what the | real average row length is. 528 may be your maximum row length, but | I think it's unlikely that your VARCHARs average anywhere near 255. | Most likely the numbers are low enough that the 1 TB table size is | sufficient, so your indexes will only need 5-byte pointers and thus | be smaller. | This table is part of a product that contains publicly available (and always expanding) publicly avilable biological data in addition to large companies internal data. A one terrabyte cap very well could come back to haunt us one day! (sadly enough!) We'll give it a shot with more reasonable numbers and see if we can work around this problem. Thank you very much for this input! | [Filter fodder: SQL] | | -- | Keith C. Ivey [EMAIL PROTECTED] | Tobacco Documents Online | http://tobaccodocuments.org - 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: Bug related to large tables and it's indexes on Win2k
On 5 Jun 2002, at 7:50, Jared Richardson wrote: This table is part of a product that contains publicly available (and always expanding) publicly avilable biological data in addition to large companies internal data. A one terrabyte cap very well could come back to haunt us one day! (sadly enough!) I fear that you'll run into problems with more than just MySQL if you have files that large, but I don't have any direct experience with them. Others on the list may have more experience with really huge files. Is there any reasonable way of breaking up your data into sets (by date, by company, by species?) and having many separate tables that you can access through a merge table when necessary? That could be especially useful if older data gets archived, if some searches don't need all the data, or if some of the separate tables won't ever change and can thus be compressed. Also, another way of making your indexes smaller would be to use PACK_KEYS=1 when creating your table, since your BIGINT IDs will probably compress well. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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
Bug related to large tables and it's indexes on Win2k
Hi all, When large tables are being addressed, we seem to have encountered a bug related to having large indexes on the table. We have several tables in our system that have reached 4 gigs in size. We altered the table definition to allow it to get larger... this is our current table creation statement (for the table giving us trouble, not the entire system) CREATE TABLE IcAlias( IcAliasID BIGINT NOT NULL PRIMARY KEY, mID VARCHAR(255) NOT NULL, IcEntityID BIGINT NOT NULL, IcTypeID SMALLINT NOT NULL, IcDupSortID VARCHAR(255) NOT NULL, INDEX mIDIdx (mID), INDEX IcTypeIDIdx (IcTypeID), INDEX IcEntityIDIdx (IcEntityID), INDEX IcDupSortIDIdx (IcDupSortID)) AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; Before we added the AVG_ROW_LENGTH and MAX_ROWS settings, we generated a table that was 4 gigs in size and an index that was over 6 gigs. Now the table fails when the index file approaches 4 gigs (actually 3.5 something)! We are getting this message when trying to insert data: Error: Can't change size of indexfile, error: 22, when using table: icalias We originally saw this in 3.23.36 and have reproduced it in the latest 4.0 alpha download. The platform is a Windows 2000 box running on dual Athlons (1900+ I think) with 4 gigs of ram and a 135 gig raid. I've spent a lot of time looking in Deja and on the MySql site and have not found a solution to this problem. Given that it takes 12 hours or so to build this table, it takes a while to test any attempts at a fix. Any help would be ~greatly~ appreciated! I really don't know what to try next! - 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: Bug related to large tables and it's indexes on Win2k
Win2k (at least the version that we are on) does indeed support files much larger than 2 gigs. Another db we use has index files that are 6.8 gigs and 4.7 gigs. - Original Message - From: miguel solorzano [EMAIL PROTECTED] To: Jared Richardson [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Jared Richardson [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 9:40 AM Subject: Re: Bug related to large tables and it's indexes on Win2k At 08:17 4/6/2002 -0400, Jared Richardson wrote: Hi, When large tables are being addressed, we seem to have encountered a bug related to having large indexes on the table. We have several tables in our system that have reached 4 gigs in size. Did you had tested if actually your OS supports size files greater than 4 GB ?. I use Win2k Server and its default format doesn't permits files with size greater than 2 GB (even formatted with NTFS). Regards, -- 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: Bug related to large tables and it's indexes on Win2k
The table type is the default, MYISAM - Original Message - From: Schneck Walter [EMAIL PROTECTED] To: 'Jared Richardson ' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 10:11 AM Subject: AW: Bug related to large tables and it's indexes on Win2k | Hi, | | what table-typ do you use ? | | rg Walter | | -Originalnachricht- | Von: Jared Richardson | An: [EMAIL PROTECTED]; [EMAIL PROTECTED] | Cc: Jared Richardson | Gesendet: 04.06.02 14:17 | Betreff: Bug related to large tables and it's indexes on Win2k | | Hi all, | | When large tables are being addressed, we seem to have encountered a bug | related to having large indexes on the table. | | We have several tables in our system that have reached 4 gigs in size. | We | altered the table definition to allow it to get larger... this is our | current table creation statement (for the table giving us trouble, not | the | entire system) | | CREATE TABLE IcAlias( |IcAliasID BIGINT NOT NULL PRIMARY KEY, |mID VARCHAR(255) NOT NULL, |IcEntityID BIGINT NOT NULL, |IcTypeID SMALLINT NOT NULL, |IcDupSortID VARCHAR(255) NOT NULL, |INDEX mIDIdx (mID), |INDEX IcTypeIDIdx (IcTypeID), |INDEX IcEntityIDIdx (IcEntityID), |INDEX IcDupSortIDIdx (IcDupSortID)) |AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; | | Before we added the AVG_ROW_LENGTH and MAX_ROWS settings, we generated | a | table that was 4 gigs in size and an index that was over 6 gigs. Now | the | table fails when the index file approaches 4 gigs (actually 3.5 | something)! | We are getting this message when trying to insert data: | Error: Can't change size of indexfile, error: 22, when using table: | icalias | | We originally saw this in 3.23.36 and have reproduced it in the latest | 4.0 | alpha download. The platform is a Windows 2000 box running on dual | Athlons | (1900+ I think) with 4 gigs of ram and a 135 gig raid. | | I've spent a lot of time looking in Deja and on the MySql site and have | not | found a solution to this problem. Given that it takes 12 hours or so to | build this table, it takes a while to test any attempts at a fix. | | Any help would be ~greatly~ appreciated! I really don't know what to try | next! | | | - | 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
Re: Bug related to large tables and it's indexes on Win2k
Thanks Walter since their is no reason (that I am aware of) for the system not to handle the current problem, I want to try to solve it if possible. I have been using MySql for a number of years... if this is an actual bug, it will be the first one I've ever encountered! :) Also, we've tested InnoDB with our data (it's all meta data so it's a very large number of very small bits of data) and found it to be slower than the ISAM tables for our application. - Original Message - From: Schneck Walter [EMAIL PROTECTED] To: 'Jared Richardson ' [EMAIL PROTECTED]; Schneck Walter [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 10:20 AM Subject: AW: Bug related to large tables and it's indexes on Win2k | Well, | | im not an expert in MYSQL tabletypes, | but what if seen yet InnoDB is the most | preferred tabletyp for real appis. | if possible try out InnoDB. | | rg Walter | | -Originalnachricht- | Von: Jared Richardson | An: Schneck Walter; [EMAIL PROTECTED]; [EMAIL PROTECTED] | Gesendet: 04.06.02 16:15 | Betreff: Re: Bug related to large tables and it's indexes on Win2k | | The table type is the default, MYISAM | | - Original Message - | From: Schneck Walter [EMAIL PROTECTED] | To: 'Jared Richardson ' [EMAIL PROTECTED]; | [EMAIL PROTECTED]; [EMAIL PROTECTED] | Sent: Tuesday, June 04, 2002 10:11 AM | Subject: AW: Bug related to large tables and it's indexes on Win2k | | | | Hi, | | | | what table-typ do you use ? | | | | rg Walter | | | | -Originalnachricht- | | Von: Jared Richardson | | An: [EMAIL PROTECTED]; [EMAIL PROTECTED] | | Cc: Jared Richardson | | Gesendet: 04.06.02 14:17 | | Betreff: Bug related to large tables and it's indexes on Win2k | | | | Hi all, | | | | When large tables are being addressed, we seem to have encountered a | bug | | related to having large indexes on the table. | | | | We have several tables in our system that have reached 4 gigs in size. | | We | | altered the table definition to allow it to get larger... this is our | | current table creation statement (for the table giving us trouble, not | | the | | entire system) | | | | CREATE TABLE IcAlias( | |IcAliasID BIGINT NOT NULL PRIMARY KEY, | |mID VARCHAR(255) NOT NULL, | |IcEntityID BIGINT NOT NULL, | |IcTypeID SMALLINT NOT NULL, | |IcDupSortID VARCHAR(255) NOT NULL, | |INDEX mIDIdx (mID), | |INDEX IcTypeIDIdx (IcTypeID), | |INDEX IcEntityIDIdx (IcEntityID), | |INDEX IcDupSortIDIdx (IcDupSortID)) | |AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; | | | | Before we added the AVG_ROW_LENGTH and MAX_ROWS settings, we | generated | | a | | table that was 4 gigs in size and an index that was over 6 gigs. Now | | the | | table fails when the index file approaches 4 gigs (actually 3.5 | | something)! | | We are getting this message when trying to insert data: | | Error: Can't change size of indexfile, error: 22, when using table: | | icalias | | | | We originally saw this in 3.23.36 and have reproduced it in the latest | | 4.0 | | alpha download. The platform is a Windows 2000 box running on dual | | Athlons | | (1900+ I think) with 4 gigs of ram and a 135 gig raid. | | | | I've spent a lot of time looking in Deja and on the MySql site and | have | | not | | found a solution to this problem. Given that it takes 12 hours or so | to | | build this table, it takes a while to test any attempts at a fix. | | | | Any help would be ~greatly~ appreciated! I really don't know what to | try | | next! | | | | | | - | | 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
Re: Bug related to large tables and it's indexes on Win2k
Hi Jared and Walter I don't suggest changing to InnoDB just to try solve a unknown problem! MyISAM tables are usually less problematic, being the default table type, and most tested in MySQL. And it seems Jared has benchmarked it as being more suited to his situation. InnoDb tables are usually better where you need row locking and transactions, and MyISAM usually for tables where the vast majority of queries are selects (such as websites). Jared, I can't help solve your problem, but I'd be very interested if you got an answer! Two suggestions though that may be of use: 1) Make sure your indexes are healthy 2) Try using a MERGE table regards, ian gilfillan - Original Message - From: Jared Richardson [EMAIL PROTECTED] To: Schneck Walter [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 4:24 PM Subject: Re: Bug related to large tables and it's indexes on Win2k Thanks Walter since their is no reason (that I am aware of) for the system not to handle the current problem, I want to try to solve it if possible. I have been using MySql for a number of years... if this is an actual bug, it will be the first one I've ever encountered! :) Also, we've tested InnoDB with our data (it's all meta data so it's a very large number of very small bits of data) and found it to be slower than the ISAM tables for our application. - Original Message - From: Schneck Walter [EMAIL PROTECTED] To: 'Jared Richardson ' [EMAIL PROTECTED]; Schneck Walter [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 10:20 AM Subject: AW: Bug related to large tables and it's indexes on Win2k | Well, | | im not an expert in MYSQL tabletypes, | but what if seen yet InnoDB is the most | preferred tabletyp for real appis. | if possible try out InnoDB. | | rg Walter | | -Originalnachricht- | Von: Jared Richardson | An: Schneck Walter; [EMAIL PROTECTED]; [EMAIL PROTECTED] | Gesendet: 04.06.02 16:15 | Betreff: Re: Bug related to large tables and it's indexes on Win2k | | The table type is the default, MYISAM | | - Original Message - | From: Schneck Walter [EMAIL PROTECTED] | To: 'Jared Richardson ' [EMAIL PROTECTED]; | [EMAIL PROTECTED]; [EMAIL PROTECTED] | Sent: Tuesday, June 04, 2002 10:11 AM | Subject: AW: Bug related to large tables and it's indexes on Win2k | | | | Hi, | | | | what table-typ do you use ? | | | | rg Walter | | | | -Originalnachricht- | | Von: Jared Richardson | | An: [EMAIL PROTECTED]; [EMAIL PROTECTED] | | Cc: Jared Richardson | | Gesendet: 04.06.02 14:17 | | Betreff: Bug related to large tables and it's indexes on Win2k | | | | Hi all, | | | | When large tables are being addressed, we seem to have encountered a | bug | | related to having large indexes on the table. | | | | We have several tables in our system that have reached 4 gigs in size. | | We | | altered the table definition to allow it to get larger... this is our | | current table creation statement (for the table giving us trouble, not | | the | | entire system) | | | | CREATE TABLE IcAlias( | |IcAliasID BIGINT NOT NULL PRIMARY KEY, | |mID VARCHAR(255) NOT NULL, | |IcEntityID BIGINT NOT NULL, | |IcTypeID SMALLINT NOT NULL, | |IcDupSortID VARCHAR(255) NOT NULL, | |INDEX mIDIdx (mID), | |INDEX IcTypeIDIdx (IcTypeID), | |INDEX IcEntityIDIdx (IcEntityID), | |INDEX IcDupSortIDIdx (IcDupSortID)) | |AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; | | | | Before we added the AVG_ROW_LENGTH and MAX_ROWS settings, we | generated | | a | | table that was 4 gigs in size and an index that was over 6 gigs. Now | | the | | table fails when the index file approaches 4 gigs (actually 3.5 | | something)! | | We are getting this message when trying to insert data: | | Error: Can't change size of indexfile, error: 22, when using table: | | icalias | | | | We originally saw this in 3.23.36 and have reproduced it in the latest | | 4.0 | | alpha download. The platform is a Windows 2000 box running on dual | | Athlons | | (1900+ I think) with 4 gigs of ram and a 135 gig raid. | | | | I've spent a lot of time looking in Deja and on the MySql site and | have | | not | | found a solution to this problem. Given that it takes 12 hours or so | to | | build this table, it takes a while to test any attempts at a fix. | | | | Any help would be ~greatly~ appreciated! I really don't know what to | try | | next! | | | | | | - | | 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. | | |
Re: Bug related to large tables and it's indexes on Win2k
* Jared Richardson [...] CREATE TABLE IcAlias( IcAliasID BIGINT NOT NULL PRIMARY KEY, mID VARCHAR(255) NOT NULL, IcEntityID BIGINT NOT NULL, IcTypeID SMALLINT NOT NULL, IcDupSortID VARCHAR(255) NOT NULL, INDEX mIDIdx (mID), INDEX IcTypeIDIdx (IcTypeID), INDEX IcEntityIDIdx (IcEntityID), INDEX IcDupSortIDIdx (IcDupSortID)) AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; Why do you use AVG_ROW_LENGTH=4096? It seems to me the max record length is 528...? Also, you do normally not want an index on 255 characters of a column... have you considered using INDEX mIDIdx (mID(10)) and INDEX IcDupSortIDIdx (IcDupSortID(10)) or similar? -- Roger 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: Bug related to large tables and it's indexes on Win2k
I replied below - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 3:24 PM Subject: Re: Bug related to large tables and it's indexes on Win2k | * Jared Richardson | [...] | CREATE TABLE IcAlias( | IcAliasID BIGINT NOT NULL PRIMARY KEY, | mID VARCHAR(255) NOT NULL, | IcEntityID BIGINT NOT NULL, | IcTypeID SMALLINT NOT NULL, | IcDupSortID VARCHAR(255) NOT NULL, | INDEX mIDIdx (mID), | INDEX IcTypeIDIdx (IcTypeID), | INDEX IcEntityIDIdx (IcEntityID), | INDEX IcDupSortIDIdx (IcDupSortID)) | AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; | | Why do you use AVG_ROW_LENGTH=4096? It seems to me the max record | length is 528...? | According to the MySql docs, the max table size is AVG_ROW_LENGTH * MAX_ROWS We were trying to ensure the maximum amount of growth... this table can grow very very large We'll try an import with only 528 AVG_ROW_LENGTH and see what happens. | Also, you do normally not want an index on 255 characters of a | column... have you considered using INDEX mIDIdx (mID(10)) and | INDEX IcDupSortIDIdx (IcDupSortID(10)) or similar? | Got a build going with that right now! :) Thanks - 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
