Fragmentation problem with MYD/MYI files
Hi, I have a large MyISAM database with 22 million rows. The MYD file is now 4 GB in size, and the MYI file is 2 GB. I am constantly adding rows to this database and rarely deleting any. Thus the database size grows constantly. MySQL seems to increase the database files (MYD/MYI) in very small increments. This has led to massive fragmentation of the files. Just recently my MYD file was in over 1 fragments! I am running MySQL 4.0.14b on Windows XP Pro. I have a 40 GB NTFS disk with 20 GB free space. I thought that NTFS could handle a situation like this without fragmenting files, but apparently that is not the case! Is it possible to configure MySQL so that it would allocate a large amount of extra space for the MYD/MYI files, and then use this space as necessary? This way the files would need to be grown only very rarely. It would be ideal if I could configure the increment in which MySQL increases the file sizes (for example, 100 MB, or a percentage of current file size). If this is not possible, I'd suggest to add such a feature to MySQL. I believe it would give a tremendous performance-boost for application where the databases keep growing. -- Mikko Noromaa ([EMAIL PROTECTED]) - tel. +358 40 7348034 Noromaa Solutions - see http://www.nm-sol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Repairing a large table takes weeks!
Hi, > > About two weeks ago I received "The table Worklist is full" > > error. > > what type of table? MyISAM? > how big is 'full'? Sorry I forgot to mention. The table is MyISAM with 32-bit file pointers. This makes its maximum size 4 GB. This is the limit I hit. Windows XP with NTFS has no file-size limits at 4 GB (they are somewhere in the TB range I believe). -- Mikko Noromaa ([EMAIL PROTECTED]) - tel. +358 40 7348034 Noromaa Solutions - see http://www.nm-sol.com/ > -Original Message- > From: Sebastian Tobias Mendel genannt Mendelsohn > [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 16, 2003 1:52 PM > To: [EMAIL PROTECTED] > Subject: Re: Repairing a large table takes weeks! > > > > About two weeks ago I received "The table Worklist is full" > error. Since > > what type of table? MyISAM? > how big is 'full'? > > "If you don't specify any of the above options, the maximum > size for a > table will be 4G (or 2G if your operating systems only > supports 2G tables)." > > i dont know how big files can be on NTFS/WinXP. > > > -- > Sebastian Mendel > www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repairing a large table takes weeks!
Hi, About two weeks ago I received "The table Worklist is full" error. Since then I have been struggling to update the table indexes beyond 32 bits as suggested by the MySQL documentation. I have tried the following methods (I have two identical MySQL databases on two identical computers): - ALTER TABLE Worklist MAX_ROWS=10. This created a new database file (#sql-50c_2829.*) in about 1.5 days. After this, the file sizes remained constant and nothing seemed to be happening. I let it run for 7 days, after which I killed the MySQL process. - Created a new table and did INSERT INTO Worklist_Temp SELECT * FROM Worklist. This has now been running for about 7 days on one of my servers, and it shows no signs of finishing (MySQL 4.0.14b). - As above, but with ALTER TABLE Worklist_Temp DISABLE KEYS. The INSERT phase went through quickly (a few hours). After that I ran ALTER TABLE Worklist_Temp ENABLE KEYS which is now rebuilding the database. It has now been running for 3 days (MySQL 4.0.12). - SHOW PROCESSLIST shows that during the slow periods, the MySQL server is doing "Repair with keycache". As suggested by the documentation, I tried setting myisam_sort_buffer_size to 180 MB (I have 256 MB of memory in the computer). This didn't change anything so I think the indexes were too large to be repaired by sorting. - I have defragmented the disks on both of the computers. - CHECK TABLE Worklist on the original table returns no errors. During this process, I have been using the following MySQL versions: 3.23.49, 4.0.12 and 4.0.14b. The computers are running Windows XP and using NTFS volumes. Both computers are workstation-grade with 1.0 GHz processor, 256 MB RAM and 40 GB HD. The harddrives have about 20 GB free space. If anyone has any ideas how to get this update done, I'd appreciate them a lot! The servers were in production use, and production has been halted for the past 2 weeks because of these problems... How long has MySQL taken for others when upgrading 4 GB databases after the "The table Worklist is full" error? -- Mikko Noromaa ([EMAIL PROTECTED]) - tel. +358 40 7348034 Noromaa Solutions - see http://www.nm-sol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: "select * from table where field=hex" doesnt work
Hi, Your solution converts the binary field to a string and then compares two strings. You should get rid of any conversion problems by comparing the values as binary data: SELECT * FROM table1 WHERE field1 = BINARY 0x6100620063006400; -- Mikko Noromaa ([EMAIL PROTECTED]) - SQL in Excel, check out ExcelSQL! - see http://www.excelsql.com - > -Original Message- > From: Koh Swee Meng [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 6:07 AM > To: [EMAIL PROTECTED] > Subject: Re: "select * from table where field=hex" doesnt work > > > oh, i found the solution for this, > by doing "SELECT * FROM table1 WHERE STRCMP(field1, > 0x6100620063006400)=0" will work as expected, so no need > to worry that > character %(ASCII 25) will cause any problem. > > > Regards, > Koh Swee Meng > > > - Original Message - > From: "Koh Swee Meng" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 10:55 AM > Subject: "select * from table where field=hex" doesnt work > > > > i inserted a new record with this SQL > > "INSERT INTO table1(field1) VALUES(0x6100620063006400)" > > type of table1.field1 is tinyblob. > > > > to retrieve the record, i tried SQL belows, > > 1) SELECT * FROM table1 WHERE field1 = 0x6100620063006400; > > 2) SELECT * FROM table1 WHERE field1 LIKE 0x6100620063006400; > > 1st SQL found nothing, but the 2nd SQL found the exact record. > > > > Is this a bug? I am using mysql 3.23.41 in Linux. > > > > > > Regards, > > Koh Swee Meng > > > > > > -- > > 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]
INSERT performance when indexes are used
Hi, I have a table with about 5 million records, and an index on that table's VARCHAR(255) field, as well as a couple of integer indexes. The VARCHAR-index is essential to my application as I frequently need to search through the whole table by using the VARCHAR field. When I do INSERTs into this table, they sometimes take a considerably long time to complete. For example, inserting 1000 rows sometimes takes 700 ms, sometimes 7 ms. I assume this is because the indexes need to be modified. Is it possible to leave extra space in the index tree leaves so that individual INSERTs could work without modifications to the whole index tree? I know that I'll have about 1-2 INSERTs per day, and I'd like each of them to work as fast as possible (preferably less than 1 ms). Since the table grows anyway, I don't mind the index files containing some unused space. The table is a MyISAM table, with a 1 GB MYD-file and a 400 MB MYI-file. I know there are workarounds to this problem (doing inserts in a single batch instead of several, loading data from a text file, etc.), but for now I'd just like to know how well the indexes can be customized. I am running MySQL 3.23.49 on Windows NT with MyISAM tables. -- Mikko Noromaa (mikkon@nm-sol) - tel. +358 40 7348034 Noromaa Solutions - see http://www.nm-sol.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
INSERT performance when indexes are used
Hi, I have a table with about 5 million records, and an index on that table's VARCHAR(255) field, as well as a couple of integer indexes. The VARCHAR-index is essential to my application as I frequently need to search through the whole table by using the VARCHAR field. When I do INSERTs into this table, they sometimes take a considerably long time to complete. For example, inserting 1000 rows sometimes takes 700 ms, sometimes 7 ms. I assume this is because the indexes need to be modified. Is it possible to leave extra space in the index tree leaves so that individual INSERTs could work without modifications to the whole index tree? I know that I'll have about 1-2 INSERTs per day, and I'd like each of them to work as fast as possible (preferably less than 1 ms). Since the table grows anyway, I don't mind the index files containing some unused space. The table is a MyISAM table, with a 1 GB MYD-file and a 400 MB MYI-file. I know there are workarounds to this problem (doing inserts in a single batch instead of several, loading data from a text file, etc.), but for now I'd just like to know how well the indexes can be customized. I am running MySQL 3.23.49 on Windows NT with MyISAM tables. -- Mikko Noromaa (mikkon@nm-sol) - tel. +358 40 7348034 Noromaa Solutions - see http://www.nm-sol.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
