Advertisement:

Author Topic: Convert your tables to InnoDB  (Read 224389 times)

Offline !Hachi!

  • SMF Hero
  • ******
  • Posts: 1,666
  • Gender: Male
    • pakurdu.net
Re: Convert your tables to InnoDB
« Reply #60 on: August 01, 2006, 12:40:28 AM »
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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #61 on: August 01, 2006, 05:57:38 AM »
Entirely upto you, you should do the search ones at the very least though.
Liverpool FC Forum with 14 million+ posts.

Offline !Hachi!

  • SMF Hero
  • ******
  • Posts: 1,666
  • Gender: Male
    • pakurdu.net
Re: Convert your tables to InnoDB
« Reply #62 on: August 01, 2006, 06:48:02 AM »
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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #63 on: August 01, 2006, 07:48:18 AM »
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.

Offline !Hachi!

  • SMF Hero
  • ******
  • Posts: 1,666
  • Gender: Male
    • pakurdu.net
Re: Convert your tables to InnoDB
« Reply #64 on: August 01, 2006, 08:02:14 AM »
i have shell access.Ben will you explain it bit more please.
Happy Ramadan

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #65 on: August 01, 2006, 08:54:11 AM »
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.

Offline !Hachi!

  • SMF Hero
  • ******
  • Posts: 1,666
  • Gender: Male
    • pakurdu.net
Re: Convert your tables to InnoDB
« Reply #66 on: August 01, 2006, 08:56:24 AM »
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?
« Last Edit: August 01, 2006, 09:12:47 AM by hassan »
Happy Ramadan

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #67 on: August 01, 2006, 02:23:28 PM »
Start be resolving your quota issues and then you can work on the rest.
Liverpool FC Forum with 14 million+ posts.

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #68 on: January 02, 2007, 02:09:36 PM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #69 on: January 02, 2007, 06:49:07 PM »
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.

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #70 on: January 03, 2007, 02:42:12 AM »
 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?

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #71 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.
Liverpool FC Forum with 14 million+ posts.

Offline ivo2296

  • Full Member
  • ***
  • Posts: 514
  • Gender: Male
  • SMF Bulgarian Translator
Re: Convert your tables to InnoDB
« Reply #72 on: February 08, 2007, 08:00:15 AM »
Any news about latest SMF 1.1.1 ?
I mean - should be edited the first post?

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #73 on: February 08, 2007, 12:13:40 PM »
How do I prune a table?

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,778
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #74 on: February 08, 2007, 11:36:28 PM »
Why do you want to prune a table?
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?

Offline Simplemachines Cowboy

  • Jr. Member
  • **
  • Posts: 354
  • KA-CLICK
    • The Open Range
Re: Convert your tables to InnoDB
« Reply #75 on: February 09, 2007, 12:36:58 AM »
I presume he wants to prune a db table because of what Ben_S said a few posts before:

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

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #76 on: February 09, 2007, 01:40:01 AM »
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.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #77 on: February 09, 2007, 12:25:20 PM »
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.

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #78 on: February 09, 2007, 04:52:17 PM »
What will happen if I replaced the table with an empty one from a fresh install?

Offline webvision

  • Semi-Newbie
  • *
  • Posts: 63
Re: Convert your tables to InnoDB
« Reply #79 on: February 10, 2007, 05:36:53 AM »
thanks for sharing nice info