Possible Sesssion/MySQL overload

Contact the Development Working Group here: http://groups.google.com/group/joomla-dev-general

This forum is now closed and archived.
Locked
HolmesSPH
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Mon Feb 27, 2006 11:39 pm

Possible Sesssion/MySQL overload

Post by HolmesSPH » Sat Dec 02, 2006 3:38 pm

I have been running two dedicated servers for my website for aproximately 8 months now... One server for HTTP and one for MYSQL...  My website has 42,000+ members, and we get some pretty heavy traffic...  I truely feel sometimes that I push Joomla to a certain limit...

usually about twice a month I will receive this error message...

Code: Select all

DB function failed with error number 1034
Incorrect key file for table 'mos_session'; try to repair it SQL=SELECT session_id FROM mos_session WHERE session_id = MD5( '7e8312f6e57c106d600629d460bb2e88' )
SQL =

SELECT session_id
 FROM mos_session
 WHERE session_id = MD5( '7e8312f6e57c106d600629d460bb2e88' )
This of course is easy to understand why the sites not working however, the error message is ALWAYS the same with exception to the MD5 coding..

When I check the MySQL server, everything APPEARS fine, but in all actuallity, the server has went into MySQL-Safe mode, AND has locked everything single table dealing with my website...  I have to actually reboot the whole server to get it running normal again..

i have searched high and low to figure this out but I can't... If anyone has any ideas please lert me know...

SERVER SETUP
Dual Xeon's (dual core)
4gb RAM
160GB 7200 EIDE
Apache 2.0
Plesk 7.5.5
MySQL 4.1
PHP 5.0
JOOMLA 1.0.8

If there is any more information needed to figure out WHY MySQL seems to crumble like this (ALWAYS in the middle of the night when no one is on)  I'd like to know....

THanks!
Signature rules: Literal URLs only - http://forum.joomla.org/viewtopic.php?f=8&t=65

User avatar
Robin
Joomla! Master
Joomla! Master
Posts: 15753
Joined: Thu Aug 18, 2005 10:41 am

Re: Possible Sesssion/MySQL overload

Post by Robin » Sat Dec 02, 2006 7:27 pm

Hi,

I'll have to check for you, but from experience I know someone always changes the session table from MyISAM to InnoDB. Also for locking records, see http://forum.joomla.org/index.php/topic ... #msg234440

I'll try to get this InnoDB use confirmed, and ask if there are more reasons than just performance regarding the use of this InnoDB.

Regards Robin

User avatar
Robin
Joomla! Master
Joomla! Master
Posts: 15753
Joined: Thu Aug 18, 2005 10:41 am

Re: Possible Sesssion/MySQL overload

Post by Robin » Sat Dec 02, 2006 7:52 pm

Hi,

I just spoke with the person who changes some basic Joomla! tables. Turns out that he uses HEAP (Memory) for the Session table, and InnoDB for the jos_user table (login and timestamp updates, huge source of locks). He also mentioned to make sure the query cache has enough RAM. You seem to have a lot available with 4Gb, so thats something worth checking as well.

Edit; interesting read as well: http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
Last edited by Robin on Sat Dec 02, 2006 8:09 pm, edited 1 time in total.

HolmesSPH
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Mon Feb 27, 2006 11:39 pm

Re: Possible Sesssion/MySQL overload

Post by HolmesSPH » Sun Dec 03, 2006 4:15 pm

Thats VERY interesting.... Has any one using Joomla actually done this and documented the process?

I have just about every MySQL book made by the MySQL dev team, and it did talk alot about different tables, but If I change the mos_sessions table to a HEAP table, does it change the SQL I have to use?  Also, I would assume that a HEAP for the sessions would be a HUGE benefit, concidering I have close to 300 user online at one time, along with streaming media, etc, it might really cut down on page load times not having to wait for a hard disk session write...

As for the mos_users table, I am familiar with how to change it to InnoDB, but are there any draw backs or tips I need to know in order to comtinue accessing it the same way??

Thanks, I will definiately read thru those threads you sent me, specially the load balanacing article!
Signature rules: Literal URLs only - http://forum.joomla.org/viewtopic.php?f=8&t=65

User avatar
Robin
Joomla! Master
Joomla! Master
Posts: 15753
Joined: Thu Aug 18, 2005 10:41 am

Re: Possible Sesssion/MySQL overload

Post by Robin » Mon Dec 04, 2006 2:20 pm

Hi,

I have no experience with this topic myself, so the info provided is all I can do. I hope it will help you out though.

User avatar
Jinx
Joomla! Champion
Joomla! Champion
Posts: 6569
Joined: Fri Aug 12, 2005 12:47 am
Contact:

Re: Possible Sesssion/MySQL overload

Post by Jinx » Tue Dec 05, 2006 10:26 pm

As far as I know there are no other changes needed to those tables. If your MySQL server supports InnoDB and HEAP u should be able to just change the tables to uses these. Nothing else should be changed.
Johan Janssens - Joomla Co-Founder, Lead Developer of Joomla 1.5

http://www.joomlatools.com - Joomla extensions that just work

User avatar
eyezberg
Joomla! Hero
Joomla! Hero
Posts: 2860
Joined: Thu Aug 25, 2005 5:48 pm
Location: Geneva mostly
Contact:

Re: Possible Sesssion/MySQL overload

Post by eyezberg » Thu Dec 07, 2006 10:51 am

Is there a "Running Joomla! off a dedicated server: recommended advanced settings" documentation this could be added to somewhere? Maybe a blog post..?

Not that I'd have a clue what those InnoDB, HEAP and MyISAM beast are and what difference they make myself, as I'm never gonna be using a dedicated server, but others might be quite interested to get better performance out of their server..! ;)

HolmesSPH, if you make these changes as suggested, maybe you could report if this does help and the error disappears?
Sometimes one pays most for the things one gets for nothing.
The important thing is not to stop questioning. Curiosity has its own reason for existing. AE
http://joomla15.[URL banned].com for J! 1.5 screenshots
http://www.eyezberg.com

HolmesSPH
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Mon Feb 27, 2006 11:39 pm

Re: Possible Sesssion/MySQL overload (solved.... I think)

Post by HolmesSPH » Thu Dec 07, 2006 7:51 pm

Well.... the server locked up again yesterday.... and I had them hard reboot the server like always, and when they restarted the server they said they noticed some hard disk errors, and they said that it might run for forever but with errors, and I have a feeling that the errors locking up has been from the hard drive beiing bad... they replaced the hard drive and everything seems fine...

I WILL attempt to switch the sessions table to a heap table which would be a HUGE advantage, and might free up alot of I/O interactivity...

I am hoping also that by using a HEAP table instead of MyISAM, I can get much greater speeds while search engines are on the website... because the search engines make my "guest(s)" count go out of the roof, and unfortunately the search engines arn't logging in, so there's a ton of multiple sessions, and ALL of them are writting and reading from the hard disk...

I will DEFINITELY post my results and what I did and if it helped or not with stats..
Thanks!
Signature rules: Literal URLs only - http://forum.joomla.org/viewtopic.php?f=8&t=65

HolmesSPH
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Mon Feb 27, 2006 11:39 pm

Re: Possible Sesssion/MySQL overload

Post by HolmesSPH » Sat Dec 09, 2006 3:05 am

Well, its been two days running with HEAP instead of MyISAM for the sessions table and there have been no errors...
unfortunately I have not really seen any new trends in server loads... Unfortunately, my server load averages are as shifty as a middle-aged pregnant women who is bi-polar....

I have had server loads of over 150, but I usually stay between 5-15 or eve 25 on a heavy night... but thats ONLY on the MySQL server...

Before I made some optimizations to components such as UddeIM and CB it was much worse... but it's helped doing some simple things..

I am going to attempt to change some of the most commonly used tables to InnoDB next and see what results I get.... but the real issue is that I simple need a nice brand new server for the DB lol... my one 160GB EIDE 7200 RPM drive isn't enough to handle the MySQL challanges... I really a nice RAID with 15K drives :-)
Signature rules: Literal URLs only - http://forum.joomla.org/viewtopic.php?f=8&t=65

HolmesSPH
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Mon Feb 27, 2006 11:39 pm

Re: Possible Sesssion/MySQL overload

Post by HolmesSPH » Mon Jan 08, 2007 5:53 pm

Well, the HEAP tables have been running great... I also finally changed mos_comprofiler InnioDB (using a serverly hacked version of CB).... I pretty much have like 10% code left from the original CB lol

I also changed the mos_uddeim table to Innodb.... Unfortunately with the a-gillion messages sent every day, MyISAM is a horrible option for a messaging system.  Remember that InnoDB allows row locking VS table locking, so if you have A LOT of writes, InnoDB is much better in most circumstances.  But remember, InnoDB can actually SLOW down your database if your site doesn't have alot of traffic.

ANYWAYS, Since i've switch those two tables (most writes go to these) I've noticed better performance... I also noticed that for the past 8 months, the MySQL machine was only using half the amount of RAM, and that the CPU's had quit a bit of idle time. AND the wait parameter for the disks really wasn't even enough to register.... So I was confuzzled as to why my server load was so high...  So I downloaded a new my.cnf file made for machines with 4GB of ram (My server(s) each have 4GB, and Dual XEON's... THIS was a HUGE help as well... Now at PEAK PEAK times, the server's using more RAM to do it's work, and the Server load has went down.

Unfortanately, because the motherboard on my server wont register more then 4gb, it's worthless to get an upgrade... BUT, perhaps, I could get a different database server, with a RAID for once, and one that's capable of more then 4GB, and then I can open up my settings in my.cnf even more....  For a while I was concidering just doing replication with another server, but as it turns out, I htink my issue is I just need more RAM, because im not using more then 50% of my CPU power, and my disks are handling things great


Sorry this was sooo long... Hope this helps any one else.... Im no expert, but I enjoy learning  :P
Signature rules: Literal URLs only - http://forum.joomla.org/viewtopic.php?f=8&t=65

User avatar
Robin
Joomla! Master
Joomla! Master
Posts: 15753
Joined: Thu Aug 18, 2005 10:41 am

Re: Possible Sesssion/MySQL overload

Post by Robin » Sat Jan 13, 2007 10:54 pm

It sure is interesting to read. If you have more info, please keep posting  ;)

HolmesSPH
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Mon Feb 27, 2006 11:39 pm

Re: Possible Sesssion/MySQL overload

Post by HolmesSPH » Thu May 31, 2007 11:17 am

Since the last post, I have yet made some more improvements to the MySQL & HTTP dedicated servers which I'd like to share.

I had both upgraded to FC6 so that I could use some new plugins for Apache that were FC5 or above, and I also had a RAID 1+0 installed on the HTTP server which doubles has a file server too for right now.  IT WAS A HUGE improvement just doing that.

However, the BIGGEST improvement was from the utlization of the old mm_turke caching extension which is NOW called e-accelerator.  I chose it because I looked at SEVERAL bench marks against zend, APC, and others and it seemed to be the best in terms of speed and low over heard.

It took my average sever load from around 10-20 to 1-2.  However there are great caching components for Joomla now, however I would still suggest something like e-Accelerator before using an extension, there will be less over head and mess in the long wrong.


Secondly, for the MYSQL server, I wound up doing some MAJOR query optimizations that I missed my first time thru.  Alot of you are using CB and some ond CBE, and I noticed unfortunately that CB and CBE had terrible table indexes so I corrected table indexes and BLAMO! HUGE drop in MySQL server load, which you would think I would have looked at this area already but I hadn't lol.

Remember when people complain about Joomla being slow its a LIE! Joomla is NOT slow, the scripts people write for Joomla are open source and sometimes are written with little or no experience, their written with hard work and a love of OS scripts.  Unfortunately some of these components, modules, and bots are a terrible source of trouble because table schema's are bad, indenxes don't exist, and queries are badly formed lol.

If you wan't to sit and say Joomla is a terrible component, then you better dang well know the code behind it, because I think it's a great system! lol  OH YEAH, and I have not gotten ONE SINGLE sessions table crash since I originally implemented HEAP as the table type! ;-)
Signature rules: Literal URLs only - http://forum.joomla.org/viewtopic.php?f=8&t=65

User avatar
Jinx
Joomla! Champion
Joomla! Champion
Posts: 6569
Joined: Fri Aug 12, 2005 12:47 am
Contact:

Re: Possible Sesssion/MySQL overload

Post by Jinx » Thu May 31, 2007 12:33 pm

HolmesSPH wrote: Remember when people complain about Joomla being slow its a LIE! Joomla is NOT slow, the scripts people write for Joomla are open source and sometimes are written with little or no experience, their written with hard work and a love of OS scripts.  Unfortunately some of these components, modules, and bots are a terrible source of trouble because table schema's are bad, indenxes don't exist, and queries are badly formed lol.
Amen to that.
Johan Janssens - Joomla Co-Founder, Lead Developer of Joomla 1.5

http://www.joomlatools.com - Joomla extensions that just work


Locked

Return to “Development”