Convert your tables to InnoDB

Started by [Unknown], September 20, 2005, 04:31:53 PM

Previous topic - Next topic

!Hachi!

my forum mysql is around 60 mb and we are on stable vps.should i convert to innodb.
Total Posts: 75397
Total Topics: 6236
Happy Ramadan

Ben_S

Entirely upto you, you should do the search ones at the very least though.
Liverpool FC Forum with 14 million+ posts.

!Hachi!

well googled it and didn't get any EASY answer.
      actually on my forum user online a tone time are more than 30 now.and spiders are always on site.and we hade trouble in openeing who's online list.every page including stats come up in 0.04 seconds "approx." but when we open it goes to 20+ seconds every time at everytime.asked host he said may be tables are corrputed restart the mysql service etc.
    errors like
Database Error:Can't create/write to file '/tmp/#sql_54f4_0.MYI' (Errcode: 122)
                  comes so i am thinking if innodb is solution i will like to do.let me know sorry i am not sql guru.
Happy Ramadan

Ben_S

root@shankly [~]# perror 122
System error: 122 = Disk quota exceeded

You need to take that up with your host.
Liverpool FC Forum with 14 million+ posts.

!Hachi!

i have shell access.Ben will you explain it bit more please.
Happy Ramadan

Ben_S

Database Error:Can't create/write to file '/tmp/#sql_54f4_0.MYI' (Errcode: 122) simply means that mysql couldnt write to the file because the user was over their disk space limit.
Liverpool FC Forum with 14 million+ posts.

!Hachi!

#66
ok so should i give more space to /tmp folder or change some settings in mysql?i have vps so i am littel hesitate to ask host as i have full controll over server.
                  and what about dead who's online list?
Happy Ramadan

Ben_S

Start be resolving your quota issues and then you can work on the rest.
Liverpool FC Forum with 14 million+ posts.

qtime

What is the smf_log_topics ??
It's 838 Mb with over 42.500.000 records.
I convert them to innoDB, after 24 hours it was ready, but the forum don't run after it, so I recovered the backup file.

Can I expect real speed improvement? And what about innoDB settings? Because the log_topics was 3,2 gig big after converting.

Any help will be much appreciated.

Ben_S

log_topics keeps track of what topics a user has read and upto what posts to control the "new" indicator.

How large is your forum, 42.500.000 records sounds like quite a lot, although if you have a lot of posts and members then it probably isn't really
Liverpool FC Forum with 14 million+ posts.

qtime

 494834 Posts in 89291 Topics by 61881 Members.

Is it possible to "clean" this one to speed up, or is the large table not important for the speed?

Ben_S

Mines ~12,000,000 records and I've noticed no real difference in speed from a fresh install, although you do have a fair few more than me, will bring it up with the devs though, could be handy to prune it a bit every now and then.
Liverpool FC Forum with 14 million+ posts.

ivo2296

Any news about latest SMF 1.1.1 ?
I mean - should be edited the first post?

qtime


Joshua Dickerson

Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Simplemachines Cowboy

I presume he wants to prune a db table because of what Ben_S said a few posts before:

Quote from: Ben_S on January 03, 2007, 11:24:56 AM
Mines ~12,000,000 records and I've noticed no real difference in speed from a fresh install, although you do have a fair few more than me, will bring it up with the devs though, could be handy to prune it a bit every now and then.
My SMF forum: The Open Range

qtime

Correctly Cowboy, but how to do that? Because it's 1 gig and I want to convert it to innodb, this will cost 3 days.

Ben_S

It would have to be done with a script that only deletes older data, at this stage such a script doesn't exist. I've suggested it to the devs so it may appear at some stage in a future version.
Liverpool FC Forum with 14 million+ posts.

qtime

What will happen if I replaced the table with an empty one from a fresh install?


Advertisement: