Analogged Posted August 10, 2012 Share Posted August 10, 2012 Hello, Im using MySQL 5.5 but all my databases still use MyISAM as the default. Some tables are getting quite large such as my shoutbox table having over 1 millions rows. Its looking like MyISAM is lagging for such occasion as trying to view the shoutbox archive and and still being able to read and write new rows. So im guessing i should be able to just convert this table to use InnoDB in hopes to help optimize it, or are there any reasons why i shouldn't. Thanks 343i Link to comment Share on other sites More sharing options...
Brett L Posted August 10, 2012 Share Posted August 10, 2012 InnoDB would probably give you better performance, however, personally I would just prune some of the older shouts. Link to comment Share on other sites More sharing options...
Analogged Posted August 10, 2012 Author Share Posted August 10, 2012 Yeah pruning does seem like the logical choice but my community is really proud of the shout count so im trying to keep it as long as possible. Thanks for the info, im gonna give it a try. Link to comment Share on other sites More sharing options...
Analogged Posted August 11, 2012 Author Share Posted August 11, 2012 Just thought id update this and say that i have succesfully converted the shoutbox shouts table to InnoDB while leaving the rest to still use MyISAM. Everything went smoothly and the archiving is working again with the shoutbox quite nicely for 1030k Shouts. Link to comment Share on other sites More sharing options...
Brett L Posted August 11, 2012 Share Posted August 11, 2012 If the number is really all you care about I would highly suggest looking at the PHP code, finding the output of the counter, adding a million to the number and then prune a million shouts. I'm not fully familiar with that 3rd party addon however, just looking at it on your site I don't see any kind of archive feature so it's not like your members can access those old shouts anyway. That's personally how I would handle the situation. If InnoDB is giving you the performance that you want then by all means continue with what you're doing. Just listing it as an option if you are still having issues. Link to comment Share on other sites More sharing options...
Analogged Posted August 11, 2012 Author Share Posted August 11, 2012 Thanks for the suggestion, i didn't think of that but that is definitely the road i will take if any more stability errors pop up. Cause your right just the actual number for total shouts is needed and not the actual shouts that no one would ready anyways. As for the archive iv disabled access to that feature and currently am only allowing my staff access as before switching to InnoDB just making a search to bring up all the shouts in 1 day would lock the table up and lag the shoutbox but now with the testing me and my mods have done it seems to be able to bring up a months worth of shouts without causing any lagging or other issues. But just for my own satisfactory purposes i tried pulling up all 1000k within a single search and it ended with me killing the process on the server as i kind of expected since the server spiked a huge load. I may see if i can edit the application to only allow users to search through the archives only so far so no one by mistake could crash it but even then i might still only allow my staff to use the archive. Thanks for the idea 343i Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.