MyISAM, MyISAM - or SolidDB?

[W13]

I've heard good things about SolidDB. There was an article on Database Journal about it setting new records. Has anybody seen a significant increase in performance from switching db engines? Currently, we're using MyISAM on 'MYSQL (4.1.18-standard-log)' (and PHP 4.4.2 (cgi-fcgi)).

We run a pretty high traffic forum... so anything we can do to cut down on the query time helps. I'm interested to find out what others have done to optimize mySQL for IP.Board. Thanks.

[Coastie]

innodb will be nice once it supports full text indexing.

[bfarber]

I've no experience with SolidDB - if you use it, let me know how it performs.

MyISAM is generally recommended for the majority of IPB tables. sessions table can be set to HEAP, and I usually set topic_markers table to InnoDB (row-level locking, and no real searching done on the table).

[tomturd]

sessions table can be set to HEAP, and I usually set topic_markers table to InnoDB (row-level locking, and no real searching done on the table).

Will this make much difference? We have a pretty big IPB and since upgrading to 2.2 our dedicated server is having troubles. We've upgraded to MySQL 5.1 but it hasn't made any difference.

Are there any other changes to the tables we can make to speed things up?

EDIT: I've set ibf_sessions to 'MEMORY' (apparently this is the same as HEAP in MySQL 5) - cant see any options to set topic_markers to InnoDB? (can only change to MyISAM, MEMORY, ARCHIVE or MRG MYISAM) :s

EDIT 2: Ok, just looked at the SQL System Vars and it says 'have_innodb DISABLED' - would enabling it to allow topic_markers use InnoDB make *that* much difference?

[bfarber]

It would only be worthwhile if you see the topic_markers table locking a lot. If you don't notice it locking, you likely won't notice much difference.

I've got some other resource improvements ready for 2.2.2 that will help tremendously. Feel free to submit a ticket if you wish to test them.

Other than that, it's also good to go through the httpd.cnf and my.cnf files to ensure you've set appropriate values for the traffic your server gets and the amount of data it's processing.

[W13]

Our biggest problem is the ibfposts table. When it grows past 1 million rows, it just slows everything down. Any suggestions specifically for that?

[bfarber]

One of the resource improvements I have ready is for the search functionality, which is generally where the slow-downs occur.

Other than ensuring fulltext searching is enabled (and the indexes created) there's little more you can do to optimize it.

I've been hearing some interesting things about MySQL 5.1 and it's ability to partition large tables - I could see this helping a lot, but I've not tested it or heard any reports from others who have tested it yet.

[athlonkmf]

the frequently updated and accessed tables should be in innodb when you have an active board. This will prevent table lockings when someone post something and others have to wait.

These tables are topics, posts, topic_trackers, session.

Putting session into HEAP is good for mid-traffic board, but the memory mysql can use for operations will be lessened, since the space is occupied for this in-memory table.
For high-traffic board, this is not even an option, because HEAP-tables have table-locks too.

The search function is just way too slow and such a performance hog that big boards should just disable it or chose another solution like a seperate server just for searches or another type of search engine.

[bfarber]

I disagree. I don't believe you should set either topics OR posts to innodb. Additionally, sessions can run in HEAP engine, which keeps the table in Memory (and since this table is hit on every page load, memory is the best place for it really). I've had it running in memory on some very large boards (>600 users online at a time) with some adjustments to my.cnf.

MySQL does not support fulltext indexes for innodb engine, so you cannot create fulltext indexes on the topics and posts table if you run it in innodb engine. Innodb uses on average 3x more diskspace for a table than MyISAM - so your already large posts table will now triple in size (this is a rough estimate, YMMV). Also, InnoDB is much much much slower to search through than Myisam - given that the topics and posts table are really the ONLY two tables searched, I'd not recommend setting them to InnoDB for this reason alone.

Regarding the search - the problem I see is that presently, no limit is applied to the results. I based my changes (not yet released) on a site with approx 3 million posts (give or take). A search was done for a rather specific phrase, and there were over 25,000 results returned. Without the limit, IPB pulls all 25,000, puts these into an array. Then it does a foreach loop on the array, checks some permissions, and then stops when it hits 1000 results.

End result? You get a max of 1000 results (and a notice on the search results page that results were limited to 1000).

So, I restructured the query to do the permission checking directly in the query, and added a 1000 results limit.

End result? Same as before. HOWEVER

Original search from mysql commandline (not even counting the looping php has to do twice and the permission checks done on each result): 7.9 seconds

New search results with 1000 limit: 0.12 seconds

I believe the changes I've prepared will drastically improve the searching resource usage - they're being tested on two of the largest sites we host right now for 2.2.2, with very promising outcomes so far. No bugs found as of yet. As I've stated earlier in this thread, if anyone else (with a large site who can notice the search differences) wishes to test the code changes submit a ticket and I'll be happy to get you setup.

[athlonkmf]

I disagree. I don't believe you should set either topics OR posts to innodb. Additionally, sessions can run in HEAP engine, which keeps the table in Memory (and since this table is hit on every page load, memory is the best place for it really). I've had it running in memory on some very large boards (>600 users online at a time) with some adjustments to my.cnf.

MySQL does not support fulltext indexes for innodb engine, so you cannot create fulltext indexes on the topics and posts table if you run it in innodb engine. Innodb uses on average 3x more diskspace for a table than MyISAM - so your already large posts table will now triple in size (this is a rough estimate, YMMV). Also, InnoDB is much much much slower to search through than Myisam - given that the topics and posts table are really the ONLY two tables searched, I'd not recommend setting them to InnoDB for this reason alone.

Regarding the search - the problem I see is that presently, no limit is applied to the results. I based my changes (not yet released) on a site with approx 3 million posts (give or take). A search was done for a rather specific phrase, and there were over 25,000 results returned. Without the limit, IPB pulls all 25,000, puts these into an array. Then it does a foreach loop on the array, checks some permissions, and then stops when it hits 1000 results.

End result? You get a max of 1000 results (and a notice on the search results page that results were limited to 1000).

So, I restructured the query to do the permission checking directly in the query, and added a 1000 results limit.

End result? Same as before. HOWEVER

Original search from mysql commandline (not even counting the looping php has to do twice and the permission checks done on each result): 7.9 seconds

New search results with 1000 limit: 0.12 seconds

I believe the changes I've prepared will drastically improve the searching resource usage - they're being tested on two of the largest sites we host right now for 2.2.2, with very promising outcomes so far. No bugs found as of yet. As I've stated earlier in this thread, if anyone else (with a large site who can notice the search differences) wishes to test the code changes submit a ticket and I'll be happy to get you setup.

And I'm based on experience with a very large board (mine) with over 1000 users on at a time and 4000 posts daily, yet always tight on budget when it comes to hardware. While monitoring the queries issued on mysql I've found that myisam causes table locks too often and if you do not have the appropiate oomph as your servers (and i'm talking about dual cpu, xeons opterons here) mysql won't be able to handle the query fast enough to unlock the table. Especially the session could be a pain, especially since it needs to updated so often...

If your only reason for using myisam is because you want fulltext indexes for searches, i rather disable it and use google sitesearch. Or... if you got the skills, use a cheap low profile server just for searches.

That is what I did years ago. The main forum browsing and posting with innodb tables, and searches went to a different server.
This is how I managed to live up with the bare minimum of hardware (a P4 2.8ghz server with 1 GB of RAM as main server and a AMD duron as file/searchserver).
Granted, I had to change the count(*)-queries manually, because innodb isn't good with them.

As of now, I don't even care much for the search, because I'm using xapian as backend for searches, which gives me far better search results (probablistic, weighted, stemming, and phrase/proximity) with in an amazing time (0.05 seconds or less) searching through 2.6 million posts. (i still haven't had time to integrate this optimally though)

But to conclude. People have to make compromises when they can't have everything and I think you should at least let the users know the consequences of the available options.
For me, overall browsing experience is far more important than searches

[Will L.]

bfarber patched my forums and wow do I notice a HUGE speed of searching difference

wow

thanks Brandon for finding this fix

[tomturd]

Other than that, it's also good to go through the httpd.cnf and my.cnf files to ensure you've set appropriate values for the traffic your server gets and the amount of data it's processing.

Thanks, needed some inspiration to do it :P I've gone through both files and adjusted a few things. Seems to have helped a lot!

Found a handy little script here - http://www.day32.com/MySQL/ called tuning-primer.sh. It analyses your previous mysql usage and makes suggestions on what to change. Seems to have worked a treat, but I guess I shall find out tomorrow when everyones back on. Fingers crossed..

[Jaggi]

this all sounds good, maybe someone can do an official thread over at ipsbeyond on how to optimize the db for larger boards, i guess bfarber is most experianced in this so you could start it :P.

[bfarber]

Lol, really I'm not that experienced (only a little). Blake is a genius when it comes to the server config stuff - I leave it to him usually lol.

But, mysql DOES provide in a base package a my.cnf, my-huge.cnf, my-small.cnf and my-large.cnf. The last three are just suggested mysql config files based on the size of your database. It would be worthwhile to compare the my.cnf you have in use to the default my-large.cnf and my-huge.cnf to see if there's anything worth changing.

.Will - you're welcome. :)

@athlonkmf, yes, your server hardware does also help determine what you can and can't do in your setup. If you find innodb works best for you, you're free to use it. :)

Oh, and Josh saw this thread and pointed out - we have another site that constantly has nearly 5000-6000 users online at any given time, using HEAP engine for sessions. They do, however, have multiple dedicated servers.

[Jaggi]

i'm on a forum which has 28 member groups, i'm going to remove alot of them as a quick fix to solve a issue with the acp manage groups link. I have a ticket in for it but basically the last query on that page takes over 18 seconds to execute. Maybe some optimization needed there too?

[blair]

I have a site with over 800K posts and 200K members. I've seen searches for popular terms take over 20 seconds. Any improvement would be welcome. I noticed this Sphinx search engine mod. Anyone tried it? http://mods.invisionize.com/db/index.php/f/7203

Also, I have the same problem with usergroup searches. Click on a usergroup, go get a cup of coffee and come back. VERY slow.

P.S. The biggest improvements to my db performance have come from tweaking the key_buffer size. Add up the size of all your MYI files, and make key_buffer slightly larger (memory permitting). Also, increasing record_buffer and sort_buffer to 6M helps. It would be helpful to have a thread where we could compare my.cnf configs. Of course, it varies greatly depending on hardware (RAM) and database size.

[VelvetElvis]

Two things that have helped me a lot:

midpoint insertion for key cache (key_cache_division_limit = 40)

and

low_priority_updates = 1

[bfarber]

Guys, I'd love it if those of you experienced with tweaking your my.cnf file and who have had various experiences could start up an article in the Ineo system on IPS Beyond. This information is wonderful for users with larger sites looking to squeeze the most out of their setup - but without a centralized collection (and without people reporting their experiences) it's hard for users to find this.

@blair

I have a site with over 800K posts and 200K members. I've seen searches for popular terms take over 20 seconds. Any improvement would be welcome.

Submit a ticket and ask for me specifically, noting this thread. I can put my search changes up on your site, and you can let me know how they fare. It's entirely reversable, and I've had some drastic performance increases I think you'd benefit from. :)

[backdream]

Good topic:)

I will try Xapian and Sphinx on my large forum. But seems Sphinx is more simple.

[Fast Lane]

Any chance you can post the changes online here so we can do them ourselves? I understand they are beta changes so of course we would take that into consideration...

[bfarber]

The problem is, there's a LOT of changes, to two files - more than I care to post. ;)

[ErwinB]

Just one question : I know that HEAP type for *_sessions table can improve performance, but what about mutualised hosting ? Couldn't it take more RAM memory of this kind of servers ? And if the HEAP type is selected, if think the indexes of *_sessions are useless...

Edit : we don't have any problems with that, just to learn a bit more :)

[Vitaly]

I have a site with over 800K posts and 200K members. I've seen searches for popular terms take over 20 seconds. Any improvement would be welcome. I noticed this Sphinx search engine mod. Anyone tried it?

http://mods.invisionize.com/db/index.php/f/7203

I have the same mod at my site, and very happy with speedup. It's much more effective than fulltext
- faster search (you'll never get such speed with myisam fulltext indexes)
- faster index build
- almost no memory consumption for search index (fulltext index takes a lot of memory).
- morfology support (almost impossible with fulltext indexes)

I think, IPB must have native support for sphinx search. I can't imagine better solution for forums with >1 000 000 posts.

All tables are innodb (no needs for fulltext indexes with sphinx). Memory pool for innodb is "locked" to protect it from swapping. I prefer innodb because it's robust (myisam isn't) and it's more easy to allocate required memory (myisam allocates memory only for key buffers, and data is cached with system cache).

In my case, I see no reasons to play with different database engines, because php takes much more resources. The only place, you can get significant speedup in SQL, is search. IMHO, the next optimisation should be done on webserver site. Here is a breaf: http://www.ipsbeyond.com/forums/index.php?...showarticle=199

Regards

[bfarber]

Vitaly,

Thank you for taking the time to start the article. Your experience is always a benefit. :D

Others who have the experience with server optimizations, recommendations, and so on should definitely contribute to the article. It would be great to start a massive suggestion list with tried-and-tested server optimizations.

[Vitaly]

By the way, note, that even your optimized fulltext queries will be slower than sphinx search. 10 times, for example. I think, fulltext indexes are ineffective & "memory expencive". And default mysql config doesn't allow to search 2-letter words.

Of cause, we can accept some limitations, we can disable search for guests, we can use faster hardware, but that's a bad way, IMHO.