Advertisement:

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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #80 on: February 10, 2007, 07:48:53 AM »
What will happen if I replaced the table with an empty one from a fresh install?

All topics will be marked as unread.
Liverpool FC Forum with 14 million+ posts.

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #81 on: February 10, 2007, 11:13:12 AM »
ok, that's what I was hoping.
Is there a command to empty the table in php myadmin?

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #82 on: February 10, 2007, 09:31:52 PM »
TRUNCATE TABLE tablenamehere;
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #83 on: February 10, 2007, 09:34:16 PM »
ok, I will try, thanks for answering.
« Last Edit: February 10, 2007, 09:47:26 PM by qtime »

Offline Saku

  • Jr. Member
  • **
  • Posts: 305
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #84 on: February 22, 2007, 08:41:22 AM »
I just changed these to Innodb:

log_search_messages
log_search_results
log_search_subjects
log_search_topics
log_search_words

log_search_words at 475MB took 2 hours! Anyway, all those tables still show as MYISAM even though the query claimed to be successful. I changed flood_control to HEAP and it shows up correctly.

Now what??

I have the same problem, If I remove skip-innodb from my.cnf Mysql wont restrat
here is my.cnf file : 

Code: [Select]
[mysqld]
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
log-bin
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Any one know how to enable INNODB support in MySQL 4.1.21 ?

Offline emrys01

  • Semi-Newbie
  • *
  • Posts: 33
Re: Convert your tables to InnoDB
« Reply #85 on: March 10, 2007, 12:55:10 AM »
Why do you want to prune a table?

you can prune a table, but you can't prune a fish.

(oh, i'm so sorry.  honestly.)

Offline etdwh

  • Semi-Newbie
  • *
  • Posts: 36
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #86 on: March 10, 2007, 05:18:07 AM »
HI all,

I will be migrating our forum from phpBB to SMF soon.
We have about 160,000++ posts, and 2000+ members.

Should i convert the tables to InnoDB BEFORE the migration or AFTER the migration?
Any pro's/con's of both ???

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #87 on: March 10, 2007, 05:21:44 AM »
I think after, because it will be set back to MyISAM. But I am not a smf developer :).

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #88 on: March 10, 2007, 11:18:16 AM »
You can set the destination SMF tables to innodb if you need, but you don't need to touch the phpBB db since the converters don't change the source database at all.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #89 on: August 08, 2007, 07:07:23 PM »
What should be faster
setting to heap or InnoDB for the table log online?
Now I have setup to heap.
Is it possible for mysql to read more fields at once in heap mode or can it be locked?

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #90 on: August 08, 2007, 07:20:18 PM »
As per the suggestion, heap since it's all in ram.
Liverpool FC Forum with 14 million+ posts.

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Re: Convert your tables to InnoDB
« Reply #91 on: August 08, 2007, 07:25:46 PM »
ok, thanks for the fast replay, I assume that, but I did not know for sure if there could be a locking problem.

Offline FTL_error

  • Semi-Newbie
  • *
  • Posts: 12
  • Gender: Male
    • Free Talk Live
Re: Convert your tables to InnoDB
« Reply #92 on: August 24, 2007, 02:30:51 AM »
This is absolutely powerful stuff. My forum [nofollow] is now running a whole lot faster, with 265840 Posts in 14664 Topics by 1679 Members. Maybe SMF should use InnoDB for these tables by default, or at least offer InnoDB as an installation option.
Free Talk Live [nofollow] - Talk radio where you are in control!

Free Talk Live BBS [nofollow] running SMF!

Offline heavyccasey

  • Jr. Member
  • **
  • Posts: 212
Re: Convert your tables to InnoDB
« Reply #93 on: September 01, 2007, 01:26:44 AM »
It's supposedly really slow and unnecessary for small forums.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #94 on: September 01, 2007, 11:53:41 AM »
It isn't that InnoDB is slow, but just that it has some disadvantages. Once you get big enough to need it, then the advantages outweigh the disadvantages.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline tmdg

  • Semi-Newbie
  • *
  • Posts: 31
Re: Convert your tables to InnoDB
« Reply #95 on: September 01, 2007, 12:19:46 PM »
It's supposedly really slow and unnecessary for small forums.

But how big is a small forum.     I'm new to administering smf and investigating what can be done  to improve performance.    I've not seen any guide lines (Havn't been looking long though.) as to what size things have to et to before they teh forum is deemed to be large enough to gain advantage from using InnoDB.

t.
Tom.

Offline SleePy

  • Let there be light!
  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,498
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: Convert your tables to InnoDB
« Reply #96 on: September 02, 2007, 12:47:27 AM »
Did you read some of the stickies at the top of the board?
I believe some of them explain about InnoDB.

How large is your forum?
How many online in a time period?
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline tmdg

  • Semi-Newbie
  • *
  • Posts: 31
Re: Convert your tables to InnoDB
« Reply #97 on: September 02, 2007, 04:52:16 PM »
Did you read some of the stickies at the top of the board?
I believe some of them explain about InnoDB.
Well I read the starter post in
http://www.simplemachines.org/community/index.php?topic=50217.0  wich is of course this thread.

But not every post in the thread.

How large is your forum?

Well I don't think our forum is that big.   
Uncompressed the backup of the database is less than 10 Meg.
Just under 100,000 posts and about 7,000 topics.
in July we have 200,000 page views and 150,000 in August give or take.
How many on-line in a time period?
Typically there are not more then say 30 people on lone at once  in a 15 minute period.
Though I think it it typically much less than that.

I'm not expecting a real answer to the question.    I was really trying to point out  the problem of knowing. what is big and what is small.  It's a bit like asking how tall do you have to be to be tall.

I guess it's really all  down to transaction rate really. 

Perhaps the real quetion to ask here is.

Does converting the tables indicated have the same effect as optimizing?
I haven't seen a post the says "I tried the optimization but that made no difference, So I converted to InnoDB and now it goes like the wind".  Or words to that effect.

I did read teh post teh say something like "I only have a small DB but after converting to InnoDB it goes like you wouldn't belive".

Regatrds.
Tom.

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #98 on: September 03, 2007, 04:31:02 AM »
You should first do a optimize and repair before you transfer them. ;)

And yes, innodb takes up more space, but the performance gain ism worth it (that is, if you are having locking problems)
There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #99 on: September 28, 2007, 01:44:23 AM »
It's supposedly really slow and unnecessary for small forums.

But how big is a small forum.     I'm new to administering smf and investigating what can be done  to improve performance.    I've not seen any guide lines (Havn't been looking long though.) as to what size things have to et to before they teh forum is deemed to be large enough to gain advantage from using InnoDB.

t.
Tom.

That will depend on your server hardware. On my forums, I found the severe slowness started at around 100 simultaneous users. I'd recommend the changes above if you have 50+ simultaneous users.
« Last Edit: September 28, 2007, 02:56:18 AM by Phalloidium »