jaymc Posted April 19, 2008 Share Posted April 19, 2008 I recently converted nearly all my tables from MYISAM to INNODB I was expecting mysql load/locking to go down significantly, but.. it just hasn't I keep getting spirals of locking, 30 - 70 queries in the que I captured a query that was in the que for the longest at 12 seconds, I ran the same query after the load had gone and it took 0.08 seconds, so its not a bad query and since it was in the que for the longest, its not as if it was waiting for another query to finish. Should it even? I thought innodb meant no table locking.. Anyway, I have been unable to convert 3 tables to INNODB, they are the ones which use fulltext. Dont quote me on this but it does appear to be these tables that are causing the problem. At a guess I would say these are the source of whats kicking things off However, before the convert, I never had a problem with them.. so? Perhap because I'm now using INNODB, which has meant changes in the my.cnf to assign memory to the pool, is that some how over powering any table that uses MYISAM. For instance, where as MYISAM could feely use 768MB of the key_buffer, can now only use 128MB because INNODB tables are eating what memory I have assigned to that Hope someone can give some suggestions. I dont want to convert everything back to MYISAM without investigating. Is there a way to get FULLTEXT field tables running on INNODB. using BLOB or something? I was thinking of normalising tables with FULLTEXT and have the FULLTEXT field in a redundant table for that, but that would still be MYISAM and probably problimatic Any ideas/suggestions/things to try/look out for Thanks! Quote Link to comment Share on other sites More sharing options...
fenway Posted April 19, 2008 Share Posted April 19, 2008 You are correct, FULLTEXT is for myisam only. Quote Link to comment Share on other sites More sharing options...
jaymc Posted April 19, 2008 Author Share Posted April 19, 2008 You are correct, FULLTEXT is for myisam only. What are my options then? Am I stuck with myisam if I want to store a lot of characters? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 20, 2008 Share Posted April 20, 2008 You are correct, FULLTEXT is for myisam only. What are my options then? Am I stuck with myisam if I want to store a lot of characters? Store? Don't you mean search? Quote Link to comment Share on other sites More sharing options...
jaymc Posted April 20, 2008 Author Share Posted April 20, 2008 No, I mean storage field The text in the fulltext field is never used for search It is simply printed out Quote Link to comment Share on other sites More sharing options...
fenway Posted April 21, 2008 Share Posted April 21, 2008 No, I mean storage field The text in the fulltext field is never used for search It is simply printed out Now I'm confused... do you mean TEXT? FULLTEXT is an index type, not a column type. Quote Link to comment Share on other sites More sharing options...
jaymc Posted April 21, 2008 Author Share Posted April 21, 2008 can text type be used with innodb I just need a field that can store 10,000 chars and work with innodb Quote Link to comment Share on other sites More sharing options...
fenway Posted April 21, 2008 Share Posted April 21, 2008 can text type be used with innodb I just need a field that can store 10,000 chars and work with innodb If you're using v5+, VARCHAR can store up to 65K.. to my knowledge, only the GIS stuff isn't compatible with MyISAM. Quote Link to comment Share on other sites More sharing options...
jaymc Posted April 21, 2008 Author Share Posted April 21, 2008 Ah yeh I am I didnt realise TEXT field worked with INNODB Whats best to use then, TEXT or VARCHAR for 65k Quote Link to comment Share on other sites More sharing options...
fenway Posted April 21, 2008 Share Posted April 21, 2008 Ah yeh I am I didnt realise TEXT field worked with INNODB Whats best to use then, TEXT or VARCHAR for 65k Definitely VARCHAR... using TEXT fields causes all sorts of issues for GROUP BY, temporary tables, etc... if 65K is enough go with it. Only reason to use TEXT before v5 was that varchar was limited to 255 characters ;-( Quote Link to comment Share on other sites More sharing options...
jaymc Posted April 22, 2008 Author Share Posted April 22, 2008 Your the boss Thanks for the advice Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.