Convert your tables to InnoDB

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

Previous topic - Next topic

Ben_S

Quote from: qtime on February 09, 2007, 04:52:17 PM
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.

qtime

ok, that's what I was hoping.
Is there a command to empty the table in php myadmin?

青山 素子

Motoko-chan
Director, Simple Machines

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


qtime

#83
ok, I will try, thanks for answering.

Saku

Quote from: Triangle on May 19, 2006, 02:05:08 PM
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 : 

[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 ?

emrys01

Quote from: groundup on February 08, 2007, 11:36:28 PM
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.)

etdwh

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 ???

qtime

I think after, because it will be set back to MyISAM. But I am not a smf developer :).

青山 素子

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.


qtime

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?

Ben_S

As per the suggestion, heap since it's all in ram.
Liverpool FC Forum with 14 million+ posts.

qtime

ok, thanks for the fast replay, I assume that, but I did not know for sure if there could be a locking problem.

FTL_error

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!

heavyccasey

It's supposedly really slow and unnecessary for small forums.

青山 素子

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.


tmdg

Quote from: heavyccasey on September 01, 2007, 01:26:44 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.

SleePy

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 ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

tmdg

Quote from: SleePy 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.
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.

Quote from: SleePy on September 02, 2007, 12:47:27 AM
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.
Quote from: SleePy on September 02, 2007, 12:47:27 AM
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.

Webrunner

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

Something like that

#99
Quote from: tmdg link=topic=quote author=tmdg link=topic=50217.msg1224445#msg1224445 date=1188663586]
Quote from: heavyccasey on September 01, 2007, 01:26:44 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.

Advertisement: