Convert your tables to InnoDB

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

Previous topic - Next topic

pcigre

Quote from: Motoko-chan on October 26, 2007, 01:50:50 AM
Little secret. There is work being done to get Sphinx support working in SMF (probably as a mod). A few places are using it already for testing.

That would be great... We all have big problems with search.

Douglas

#121
Quote from: Motoko-chan on October 26, 2007, 01:50:50 AMLittle secret. There is work being done to get Sphinx support working in SMF (probably as a mod). A few places are using it already for testing.

Quote from: Ben_S on October 26, 2007, 04:28:27 AMAnd it works very well :)
AMEN TO THAT!

Quote from: Ben_S on October 27, 2007, 01:42:36 PMI've not seen any issues whatsoever with my messages table, no evidence of any locking going on,
I have, and I am, unfortunately.  Havin' to change the Ville's _messages DB over to InnoDB.  ::grumbles::
Doug Hazard
* Full Stack (Web) Developer for The Catholic Diocese of Richmond
(20+ Diocesan sites, 130+ Church sites & 24 School sites)
* Sports Photographer and Media Personality
* CFB Historian
* Tech Admin for one 1M+ post, one 2M+ post and one 10M+ post sites (last two are powered by multiple servers)
* WordPress Developer (Junkie / Guru / Maven / whatever)

pcigre

Any1 to shere seacret about making Spinix to work with others?

IchBin™

IchBin™        TinyPortal

Something like that

I ran into a bug with converting smf_boards and smf_categories to InnoDB where the main forum page doesn't show boards and categories in the right order. The bug and a fix is here: http://www.simplemachines.org/community/index.php?topic=212877.0

Something like that

I've since converted all my tables to InnoDB (except the MEMORY ones).

One thing I haven't seen mentioned that really helps performance is to increase InnoDB's buffers. You can do this by editing my.cnf and changing innodb_buffer_pool_size.

On a pure InnoDB server (not doing web or anything else), people recommend setting it to as much as 80% of available RAM. As I'm using a mixed server with 1 GB or RAM, I set it to 256 MB: innodb_buffer_pool_size=256M . If you are on a 1 GB machine, you might want to set it a little lower if you're using Apache, say 128M, if you find Apache processes are making your system swap.

I also recommend setting innodb_flush_method=O_DIRECT on Linux systems. This prevents Linux from caching the database files itself. It's pointless for Linux to cache them as MySQL already has them cached if you have increased innodb_buffer_pool_size.

After changing these settings, SHOW STATUS shows that MySQL almost never has to touch the disks when doing reads for SMF. Over 99.95% of my reads come straight from RAM!

Stüldt Håjt

Quote from: Phalloidium on October 02, 2007, 10:44:43 PM
If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

Can you give specific instructions how to do this.

Something like that

Quote from: Stüldt Håjt on January 15, 2008, 06:16:50 PM
Quote from: Phalloidium on October 02, 2007, 10:44:43 PM
If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

Can you give specific instructions how to do this.

I did it with phpMyAdmin myself. It's easier to not screw up that way.

Webrunner

I use sphinx and it works very well :)
There is a difference between knowing the path and walking the path.

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

zukdj

My forum is very slow, I'm on a shared server on PowWeb but I know very little about MySQL. Is there an easy way to tune up my forum to make it fast? I looked in the MySQL database and I'm completely lost.

青山 素子

I used to manage a forum running on PowWeb. There isn't much you can do, their service has gone down in quality significantly since they were bought out. (They've been packing their clusters much heavier since that time.)

About the only thing you can try to do is make sure hostname lookups are off and you've tried the tips in the stickies. Beyond that, I encourage leaving PowWeb.
Motoko-chan
Director, Simple Machines

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


slackerpunk

hi not sure if this is the right place to ask this question but how do you repair InnoDB tables

Ben_S

Same way as MyISAM tables,

REPAIR TABLE table_name
Liverpool FC Forum with 14 million+ posts.

Chriss Cohn

So to have a summarizing (im missing a sticky about that), which tables should converted to innoDB and what additional "tuning" for innoDB should we set into our my.cnf ???

Regards, Christian

青山 素子

It all depends on your board access patterns and your traffic. Heavy posting needs different optimization than heavy viewing.

In general, INNODB works best for tables that have a lot of insertions, MyISAM is faster for tables that get a lot of SELECTs. Also, once you get heavier traffic, you need to increase the buffers for INNODB or you'll lose the advantage it gives.
Motoko-chan
Director, Simple Machines

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


Chriss Cohn

What i mean is that the first post is outdated and i doubt that all the tables have the same name as in the 1.0.x branch.....
So something summarizing for the 1.1.x version would be great (the tables you shoud AT LEAST convert)....

Regards, Christian

Something like that

Quote from: Chriss Cohn on February 02, 2008, 12:51:48 PM
What i mean is that the first post is outdated and i doubt that all the tables have the same name as in the 1.0.x branch.....
So something summarizing for the 1.1.x version would be great (the tables you shoud AT LEAST convert)....

Regards, Christian


You can safely convert all of them, except smf_boards. SMF relies on smf_boards being kept sorted on boardOrder by MyISAM, but InnoDB will not keep the sorted order.

You'll also want to increase innodb_buffer_pool_size in my.cnf. If you have 1 GB or less, I'd set it to quarter of your RAM, and to half if you have more (this is for machines that are also doing webserving, etc). It'd also set the option innodb_flush_method=O_DIRECT if you increase innodb_buffer_pool_size.

Chriss Cohn

All except of one?  :o
No i mean, thats just YOUR oppinion.... i want to hear some others first....
also the most important:
What is at a upgrade to SMF 2.0 ??? DO i then first need to make all tables back to MyIsam first?

Regards, Christian

AleXit

Hello,
I converted all my tables to InnoDB... everything goes ok except one bug:
The order of my board are completely reverted, and I can NOT change it anymore.
Moreover, some subforum of some boards are not visibile in the board index:
hxxp:www.studenti.fi.it/forum/index.php?action=forum [nonactive]
Look, in the category "corsi di laurea" forums are not in alphabetical order.

Changes from admin panel does not have effects :(

I think some tables are corrupted or similar.. (smf_boards ?)

What should I do?  :-\

Overseer

you shouldnt convert all.. didnt you read the post?

Advertisement: