With my largest forum, hackforums.net, I have had some great difficulty with MySQL lately. To give you an overview I had a 1.8gb database with 4 million records total. Multiple times per day mysql tables would lock until the server would reach max connections and crash. I have been battling this for months and it’s been getting worse week after week. This week was terrible with dozens of crashes in one day alone. So something had to be done.
The server is a dedicated mysql server with 8gb ram and an Intel quad-core processor. It should be more than enough to handle the size of this DB. The HTTP server has no problems at all. On the sql server the load does hit about 5 during peak moments but it’s normally at 2 which is about 50% CPU usage and that’s fine.
During problem times the sql process spikes to 300% wcpu usage (75%+ of cpu). The table locks are ridiculous and I watch in horror as nothing unlocks them including repairs and flush. I was forced to restart mysql every time it locked up.
So I have been looking at solutions, talking with experts, and speaking with mybb team. I have the busiest mybb forum of them all. Some may have larger databases but mine has the most queries with about 250 per second.
It was suggested that my pipe from the http to the mysql server may be the problem. It was a possibility but imho unlikely.
Eventually I decided to try innodb tables for the 2 main tables that have locked up. They are threads table and the users table. All my tables previously were myisam with the exception of sessions. I alter my sessions table to be heap/memory for increased performance.
In order to change to innodb I had to remove fulltext search in admincp. Then alter the tables in phpmyadmin. I had to actually remove the fulltext index from the threads table as it’s used to index the subject field. I changed it from a full-text index to an ordinary b-tree index. The users table required no such alterations and was easy to convert to innodb.
The reason I wanted innodb is because it supports row level locking. Essentially in myisam every time the table is accessed it locks the entire table. You can imagine with 250 queries per second how locks can be a problem causing delays. With innodb only the actual data row is locked.
Lastly I decided to remove the full-text index from the messages column inside the posts table. It was about 350mb alone and since I won’t be using full-text search I don’t believe I’ll need it. I had not converted the posts table to innodb as it’s not actually updated as often as threads or users.
The threads table is updated with every view with this query:
UPDATE mybb_threads SET views = views+1 WHERE tid=xxx;
The users table is updated constantly from the timeonline function of mybb inside class_sessions. I had altered this function but it had little effect at the time. I’ll most likely undo my changes.
Now onto the results of my innodb and full-text alterations.
Previously mysqld was using regularly 200% wcpu.
After changes mysqld is using 25% wcpu.
Previously server load was 2+
Currently after changes load is .2
This is a 1000% increase in performance and I am hoping resolves my problems. I hope my information has served to help others with active MyBB and performance problems.
