Re: HEAP tables vs MYISAM on ramdisk
Mark Maunder wrote: The table I'm using is non-critical data, so it's not really an issue for me. But I was browsing through the mysql manual looking for a way to rebuild an MYI file from the .frm and MYD file (is there a way?) when I came across this: There is: REPAIR TABLE t1 USE_FRM -- 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]
Re: HEAP tables vs MYISAM on ramdisk
Hi Mark, - Original Message - From: "Mark Maunder" Sent: Monday, February 23, 2004 4:17 PM Subject: Re: HEAP tables vs MYISAM on ramdisk > 411 is packed with features I'm dying to have on my production server, > but I had it on my dev box, and I got some table corruption which, > admittedly, I was too lazy to try to reproduce. So I've downgraded to > production 4 again. I have a heavily updated fulltext index which may be > the root of the evil. Yeah, there are a few bugs with full-text indexes in 4.1.1! Hopefully 4.1.2 (which should be out soon I think) will be ready to rock in production. :-) Here's the 4.1.1 full-text bugs I know of: http://bugs.mysql.com/1977 http://bugs.mysql.com/2190 http://bugs.mysql.com/2417 And with multi-byte character sets: http://bugs.mysql.com/2033 http://bugs.mysql.com/2065 Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HEAP tables vs MYISAM on ramdisk
411 is packed with features I'm dying to have on my production server, but I had it on my dev box, and I got some table corruption which, admittedly, I was too lazy to try to reproduce. So I've downgraded to production 4 again. I have a heavily updated fulltext index which may be the root of the evil. The last 3 paragraphs of this doc give me the impression that key caching helps with updates too: http://www.mysql.com/doc/en/MyISAM_key_cache.html The strange thing is that I have a 128 meg key_buffer on my server, but I still get a huge speed increase by moving the MYI file of my heavily utilized fulltext index table to a ramdisk. I suppose a cache by definition can't be as efficient as if the entire index were being accessed directly from memory. But the MYI file is only 14 megs, so the entire thing should be cacheable. On Mon, 2004-02-23 at 19:47, Eric B. wrote: > Index caches are new to 4.1.x, but key caches have been around for a while. > Definitely in 4.0, can't remember about 3.x. Either way though, I don't see > either helping with inserts or updates. Only with queries. > > MySQL dsadoes suggest using a seperate key cache for temporary tables though: > http://www.mysql.com/doc/en/Multiple_key_caches.html > > I haven't tried 4.11 yet (I'm just about to d/l it), but would expect it to > be pretty stable. > > Eric > > > "Mark Maunder" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > The table I'm using is non-critical data, so it's not really an issue > > for me. But I was browsing through the mysql manual looking for a way to > > rebuild an MYI file from the .frm and MYD file (is there a way?) when I > > came across this: > > > > http://www.mysql.com/doc/en/CACHE_INDEX.html > > > > Index caches are only available in MySQL 4.11 unfortunately, so perhaps > > my ramdisk idea is a workaround for index caching until 411 is stable? > > > > On Mon, 2004-02-23 at 15:34, Eric B. wrote: > > > How are you ensuring syncronization between the ram disk and the HD? Is > > > there a writeback / writethrough mechanism for ram disks? Are you not > > > risking major data loss if ever you have a power failure or PC failure? > > > > > > Thanks for the info! > > > > > > Eric > > > > > > "Mark Maunder" <[EMAIL PROTECTED]> wrote in message > > > news:[EMAIL PROTECTED] > > > > Since HEAP tables don't support fulltext indexes, is moving MYISAM > > > > tables to ramdisk an acceptable workaround? > > > > > > > > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote: > > > > > I've noticed a 4 times insert speed improvement by moving the MYI > index > > > > > file of a myisam table to a ramdisk. The MYD file is still on a > physical > > > > > disk, and I benchmarked the difference between moving just the index > > > > > file, or moving both, and it was only a 10% difference in speed. The > > > > > table has a large fulltext index. > > > > > > > > > > Has anyone else played with moving MYI files to ramdisk for > performance? > > > > > Any caveats that you know of, besides running out of ramdisk space? > > > > > > > > > > > > > > > > > > -- > > 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: HEAP tables vs MYISAM on ramdisk
Index caches are new to 4.1.x, but key caches have been around for a while. Definitely in 4.0, can't remember about 3.x. Either way though, I don't see either helping with inserts or updates. Only with queries. MySQL does suggest using a seperate key cache for temporary tables though: http://www.mysql.com/doc/en/Multiple_key_caches.html I haven't tried 4.11 yet (I'm just about to d/l it), but would expect it to be pretty stable. Eric "Mark Maunder" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The table I'm using is non-critical data, so it's not really an issue > for me. But I was browsing through the mysql manual looking for a way to > rebuild an MYI file from the .frm and MYD file (is there a way?) when I > came across this: > > http://www.mysql.com/doc/en/CACHE_INDEX.html > > Index caches are only available in MySQL 4.11 unfortunately, so perhaps > my ramdisk idea is a workaround for index caching until 411 is stable? > > On Mon, 2004-02-23 at 15:34, Eric B. wrote: > > How are you ensuring syncronization between the ram disk and the HD? Is > > there a writeback / writethrough mechanism for ram disks? Are you not > > risking major data loss if ever you have a power failure or PC failure? > > > > Thanks for the info! > > > > Eric > > > > "Mark Maunder" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > > > Since HEAP tables don't support fulltext indexes, is moving MYISAM > > > tables to ramdisk an acceptable workaround? > > > > > > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote: > > > > I've noticed a 4 times insert speed improvement by moving the MYI index > > > > file of a myisam table to a ramdisk. The MYD file is still on a physical > > > > disk, and I benchmarked the difference between moving just the index > > > > file, or moving both, and it was only a 10% difference in speed. The > > > > table has a large fulltext index. > > > > > > > > Has anyone else played with moving MYI files to ramdisk for performance? > > > > Any caveats that you know of, besides running out of ramdisk space? > > > > > > > > > > > > -- > 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: HEAP tables vs MYISAM on ramdisk
The table I'm using is non-critical data, so it's not really an issue for me. But I was browsing through the mysql manual looking for a way to rebuild an MYI file from the .frm and MYD file (is there a way?) when I came across this: http://www.mysql.com/doc/en/CACHE_INDEX.html Index caches are only available in MySQL 4.11 unfortunately, so perhaps my ramdisk idea is a workaround for index caching until 411 is stable? On Mon, 2004-02-23 at 15:34, Eric B. wrote: > How are you ensuring syncronization between the ram disk and the HD? Is > there a writeback / writethrough mechanism for ram disks? Are you not > risking major data loss if ever you have a power failure or PC failure? > > Thanks for the info! > > Eric > > "Mark Maunder" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Since HEAP tables don't support fulltext indexes, is moving MYISAM > > tables to ramdisk an acceptable workaround? > > > > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote: > > > I've noticed a 4 times insert speed improvement by moving the MYI index > > > file of a myisam table to a ramdisk. The MYD file is still on a physical > > > disk, and I benchmarked the difference between moving just the index > > > file, or moving both, and it was only a 10% difference in speed. The > > > table has a large fulltext index. > > > > > > Has anyone else played with moving MYI files to ramdisk for performance? > > > Any caveats that you know of, besides running out of ramdisk space? > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HEAP tables vs MYISAM on ramdisk
How are you ensuring syncronization between the ram disk and the HD? Is there a writeback / writethrough mechanism for ram disks? Are you not risking major data loss if ever you have a power failure or PC failure? Thanks for the info! Eric "Mark Maunder" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Since HEAP tables don't support fulltext indexes, is moving MYISAM > tables to ramdisk an acceptable workaround? > > On Sat, 2004-02-21 at 18:35, Mark Maunder wrote: > > I've noticed a 4 times insert speed improvement by moving the MYI index > > file of a myisam table to a ramdisk. The MYD file is still on a physical > > disk, and I benchmarked the difference between moving just the index > > file, or moving both, and it was only a 10% difference in speed. The > > table has a large fulltext index. > > > > Has anyone else played with moving MYI files to ramdisk for performance? > > Any caveats that you know of, besides running out of ramdisk space? > > > > > -- > Mark Maunder <[EMAIL PROTECTED]> > ZipTree.com > > > -- > 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]
