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. > | | > | > > > --------------------------------------------------------------------- > 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
