(01-16-2010, 06:30 PM)Omniscient Wrote: [ -> ]Recently I was having severe mysql problems with my biggest forum. I would have constant crashes and I tried just about everything.
Eventually I tried to alter tables from myisam to innodb and with a few minor adjustments my 1.8gb database was actually consuming 1/10th of it's previous resources. Where I was nearly about to get a new server now I have room to double or even triple in database size without a problem.
The secret why innodb was so much more efficient was how it handles table locks over mysiam. The method used by myisam to lock tables is to lock the entire table on every read/write. Innodb does row-level locking which means only the effected row is locked.
For tables that are constantly accessed that get locked very often Innodb is incredibly efficient. One important note is that innodb does take more overheard and ram but for large databases the offset could be a huge difference to your server resources.
Here is a blog post I made about my adjustments: Innodb Vs MyISAM
Thanks for the information. Looks this will sort out my problem also. Hope to get some more advise from you.
I have a site
www.bigbrotherafrica.com with over 29,000 members hosted on my own dedicated server with Intel Xeon 3450 (Quad Core ), and 8 GB RAM ( on this server, I also have some other sites, but with little traffic ). The site has 81,169 posts in 6,331 threads and growing . It is a fan site for big brother africa show ( a reality show in Africa ). Every year , it has 91 days show on the tv 24x7. So these 91 days are the busy days for my site. During these 3 month, normal from Monday to Friday, Africa time 9:00 am - 4:00 pm , my site has very heavy traffic. Especially on every Monday, it can get 4000-5000 visitors online same time . But other time is not very busy, it can get less than 1000 visitors online same time every 15 minutes.
For last 2 weeks, I started getting too much connection errors. My server support team told me that
my server is processing a significant amount of traffic, driving the load average well over 100 (under 1 is preferred):
It is generating a very large number of "defunct" php processes, which are adding to this load.
Database traffic is a major component of the problem:
the numerous queries that are starting with "UPDATE mybb_users SET lastactive=" have locked each other for table access.
at any given time, there are 150+ database interactions occurring:
When I checked the server load from for admin cp page, it can be over 200.00. Most time was around 100.
Currently I have around 2000 new posts daily ( at the moment, my site is always crash , it can crash 10 times a day. I have to keep restart mysql ) . Everyday, around 6-8 hours, the site has heavy traffic, other time is not much.
Do you think my type of site can use your way to release the load? Will your way suit for my site?
Am not a technical people. Actually I even dont know much about the server. Hope to get some advise from you.
Thanks.
