Forums / General / eZ Performance, table locks and dedicated sql-server

eZ Performance, table locks and dedicated sql-server

Next topic

Author	Message
Eivind Marienborg	Thursday 15 September 2005 3:05:57 am Good day :) I'm running a 3.6.0 site on a Xeon 2.40GHz with 1GB RAM. When few users are using the site it runs ok, but at times the whole system hangs, with load averages usually from 20 to 40 (but at times up to 170(!)), especially with many users online. I've studied the process list during the 'hang periods', and what's strange is that although the load average is high, there's almost no CPU usage. There's also much swap memory available. Several httpd and mysql-processes go into uninteruptable sleep. My site is a community based one, with users being able to send messages to each other, post in forum etc - In other words, there are much content creation going on, escpecially in the 'hang periods'. This leads me to a theory: The reason the server hangs, is that mysql locks the ezcontentobject_attribute table when inserting new rows (messages, forum posts etc). This makes the other threads unable to read from the table, and thus they go into an uninteruptable sleep, awaiting access to the table. When this happens to enough threads, the server hangs. The reason I'm posting this here, is that I'm in no way experienced with mysql. So, I'm hoping for some enlightning answers to the following questions: 1. Is the theory plausable? Does mysql acutally lock tables when writing to them, and will this prevent other threads from accessing the table? 2. We're thinking about buying a dedicated sql-server, and thus hopefully reducing the load on each of the servers. Does anyone have experience running eZ on multpiple servers? How is it working out for you? And could this contribute to solving/reducing our problem? 3. Could switching to Postgresql be a sollution? Is PSQL faster/better in regards to writing/reading/table locking? Any answer appriciated.. :)
Gabriel Ambuehl	Thursday 15 September 2005 4:33:41 am In any case, you should use a MySQL table type that supports rowlevel locking, so it can't be the table locking bottleneck. I THINK InnoDB supports that, check on the site. In general, it's always a good idea if DB can be run out of RAM (i.e. the DB should fit into RAM, better twice the RAM ;). When the system has to page, you've lost. Also, make religious use of all sorts of caching and a PHP accelerator. Visit http://triligon.org
Eivind Marienborg	Thursday 15 September 2005 4:46:41 am Thanks for the input :) And eZ supports innoDB? And I also have to increase the amount of RAM? Hehe, my project manager (who controls our money) is gonna love this ;) We're installing a php acclerator as soon as we find time. We were hoping to get Zend, but the only price I'm able to find at their website is about $1200 pr year.. Can this be right? Or have I've been looking at the wrong product?
Gabriel Ambuehl	Thursday 15 September 2005 4:54:40 am The apps in general don't care one bit for the table type you use, it should be mostly transparent to them. Get eaccellerator. It should do the job and is free (some benchmark claims it's even a bit faster than Zend's product). But some more RAM might help, depending on memory usage, of course. Visit http://triligon.org
Łukasz Serwatka	Thursday 15 September 2005 4:56:41 am Yes, eZ publish supports InnoDB tables. Check OpenSource PHP accelerators: eAccelerator http://eaccelerator.net/HomeUk and APC http://pecl.php.net/package/APC Personal website -> http://serwatka.net Blog (about eZ Publish) -> http://serwatka.net/blog
Eivind Marienborg	Thursday 15 September 2005 5:01:41 am Hmm, I just have to make sure I've gotten this right. InnoDB is an alternative to mysql. An, as it seems, a better one. Why isn't InnoDB default in the eZ installation? (I can't even remember seeing it as a displayed option on installation..?)
Hans Melis	Thursday 15 September 2005 5:29:50 am InnoDB is a storage engine in MySQL, just like MyISAM (the default type) is. eZ publish 3.6.0 and above can use the InnoDB storage engine and its transaction capabilities. Use bin/php/ezconvertmysqltabletype.php to convert your existing MyISAM tables to InnoDB and then edit the DatabaseSettings in site.ini to enable transactions. If you make a clean 3.6 install and your MySQL supports InnoDB, eZ publish will use InnoDB by default. Hans http://blog.hansmelis.be
Eivind Marienborg	Thursday 15 September 2005 5:32:52 am Aha :) Is there any way to check which storage engine I'm currently using?
Hans Melis	Thursday 15 September 2005 5:40:56 am Certainly :) If you're just concerned about the tables used by eZ publish, simply run the ezconvertmysqltabletype.php script with the --list parameter. php bin/php/ezconvertmysqltabletype.php --list Hans http://blog.hansmelis.be
Eivind Marienborg	Thursday 15 September 2005 6:06:37 am Ah, great :) All tables are already InnoDB. Does this mean that table locking can't be an issue in my case? Are there any other ideas as to what may be causing my problems..?
Gabriel Ambuehl	Friday 16 September 2005 1:02:32 am It shouldn't be table locking then. I'd monitor the RAM usage on your machine. Generally load sky rockets when it starts to swap. Visit http://triligon.org
Eivind Marienborg	Monday 26 September 2005 9:02:12 am It does indeed seem to be a RAM issue. The amount of free RAM drops down to about 6000k whenever a user edits their profile, and shoots up to 100.000k+ when they're done. We have some custom made extensions in relation to the profile editing, so what I need to do is find out which part of it is eating the RAM. Is there any way of tracking this?
Gabriel Ambuehl	Monday 26 September 2005 2:21:15 pm www.xdebug.org MIGHT help. Visit http://triligon.org
Bruce Morrison	Monday 26 September 2005 4:47:45 pm Hi Eivind I've seen the behaviour you describe on some heavily loaded sites that I take care of. What I think is happening is that when a object is edited the cached items all the way up the tree to the top node are removed and need to be regenerated, placing strain on the system. I suspect but have not been able to confirm that on a heavily loaded site, multiple hits to a page result in eZ attempting to "create" the cached page multiple times, each instance taking up resources and bogging the system down. SmartCacheClear is disabled by default and according to the viewcache.ini file: # Controls the smart viewcache handler, use enabled or disabled. # When the handler is disabled the system will clear the viewcache # for the current object, its parent and children, related objects # and objects with same keywords (if any). # When enabled it will in addition try to follow the rules set in viewache.ini # for more advanced relations between objects. e.g. to clear a forum # listing when a new topic or reply is posted. SmartCacheClear=disabled Configuration of this subsystem can help in these instances. Also look at PreCache generation - in site.ini [ContentSettings] -> PreViewCache. Static cache generation may also be helpful. Cheers Bruce My Blog: http://www.stuffandcontent.com/ Follow me on twitter: http://twitter.com/brucemorrison Consolidated eZ Publish Feed : http://friendfeed.com/rooms/ez-publish