Advertisement:

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

Offline pcigre

  • Jr. Member
  • **
  • Posts: 212
  • Gender: Male
    • Game community
Re: Convert your tables to InnoDB
« Reply #120 on: November 24, 2007, 11:40:20 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.

Offline Douglas

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 3,105
  • Gender: Male
  • Non sibi sed patriae
    • @BearlyDoug on Twitter
    • TheFan.net
Re: Convert your tables to InnoDB
« Reply #121 on: November 29, 2007, 11:15:56 PM »
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.

And it works very well :)
AMEN TO THAT!

I'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::
« Last Edit: November 29, 2007, 11:17:59 PM by Douglas »
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)

Offline pcigre

  • Jr. Member
  • **
  • Posts: 212
  • Gender: Male
    • Game community
Re: Convert your tables to InnoDB
« Reply #122 on: November 30, 2007, 05:23:29 AM »
Any1 to shere seacret about making Spinix to work with others?

Offline IchBin™

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,115
  • Gender: Male
  • I don't speak German.
Re: Convert your tables to InnoDB
« Reply #123 on: December 15, 2007, 12:02:19 AM »
No secrets at this point. :)
IchBin™        TinyPortal
Coding Guidelines       

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #124 on: December 24, 2007, 07:36:29 PM »
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

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #125 on: January 15, 2008, 04:46:20 PM »
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!

Offline Stüldt Håjt

  • Jr. Member
  • **
  • Posts: 326
Re: Convert your tables to InnoDB
« Reply #126 on: January 15, 2008, 06:16:50 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.

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #127 on: January 15, 2008, 06:52:41 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.

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #128 on: January 22, 2008, 04:29:37 PM »
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

Offline zukdj

  • Semi-Newbie
  • *
  • Posts: 11
Re: Convert your tables to InnoDB
« Reply #129 on: January 22, 2008, 08:09:28 PM »
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.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #130 on: January 22, 2008, 11:58:00 PM »
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.


Offline slackerpunk

  • Jr. Member
  • **
  • Posts: 193
Re: Convert your tables to InnoDB
« Reply #131 on: January 23, 2008, 05:04:38 AM »
hi not sure if this is the right place to ask this question but how do you repair InnoDB tables

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #132 on: January 23, 2008, 05:07:23 AM »
Same way as MyISAM tables,

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

Offline Chriss Cohn

  • Full Member
  • ***
  • Posts: 495
  • Gender: Male
  • who does not go with the time, goes with the time
Re: Convert your tables to InnoDB
« Reply #133 on: January 26, 2008, 06:27:15 PM »
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

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #134 on: January 26, 2008, 06:57:42 PM »
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.


Offline Chriss Cohn

  • Full Member
  • ***
  • Posts: 495
  • Gender: Male
  • who does not go with the time, goes with the time
Re: Convert your tables to InnoDB
« Reply #135 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

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #136 on: February 03, 2008, 05:12:13 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.

Offline Chriss Cohn

  • Full Member
  • ***
  • Posts: 495
  • Gender: Male
  • who does not go with the time, goes with the time
Re: Convert your tables to InnoDB
« Reply #137 on: February 03, 2008, 07:04:19 PM »
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

Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #138 on: April 17, 2008, 03:22:19 PM »
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?  :-\

Offline Overseer

  • Sr. Member
  • ****
  • Posts: 746
  • Gender: Male
    • dubcc.com - West Coast Connection Forum
Re: Convert your tables to InnoDB
« Reply #139 on: April 17, 2008, 04:38:27 PM »
you shouldnt convert all.. didnt you read the post?