[TYPO3-english] Saving pages in BE locks complete database

Dmitry Dulepov dmitry.dulepov at gmail.com
Tue Apr 20 09:36:56 CEST 2010


Hi!

Christopher Lörken wrote:
> Sadly, the Typo3 locking mode was not the problem. We've chosen
> "disabled" but it didn't show any difference to the standard locking
> method. (Except of some duplicate key errors when the cache was cleared
> and some pages where generated in parallel).

If you can change what server runs, install sysvsem PHP module and use
semaphores. They work much better than any other locking method.

> Based on what Dmitry explained about InnoDB I investigated the
> fragmentation a bit more and have changed our configuration to
> innodb_file_per_table (i.e. that the InnoDB tables are in one file per
> database table).

This helps a lot if you want to decrease the same of the database, make
seeks faster and reduce fragmentation.

> Sadly that didn't have an effect either.

So it was not that bad yet :)

> Interestingly I managed to deadlock the database by issueing a "ALTER
> TABLE pages ENGINE=InnoDB" statement when I wanted to rebuild the table
> after a couple of days. Something what I wouldn't expect at all to make
> problems since we only have 151 rows in that table and a total ibd
> filesize of 320kb. Should not even take a second to recreate that file...

This may happens when somebody actively reads and writes to the database.
That "alter table" requires an exclusive lock on the table. It is better to
stop the web server for 2–3 minutes when doing "alter table".

> Since (as I've stated) some of our queries are shown to be in state
> "end" without really ending I tried to find any problems connected to
> that and came across this MySQL bug report:
> http://bugs.mysql.com/bug.php?id=39091
> 
> It has been closed after the guy who had the problem upgraded MySQL so I
> too upgraded our database from the most recent 5.0 to the most recent
> 5.1 but guess what... no effect...
> (We are using the default InnoDB engine, not the Oracle plugin... Is
> that stable enough for production? Is it faster/better?)

I have 5.1.36 but no problem.

> The only thing I can say is that queries (especially inserts) on all our
> InnoDB tables sometimes take up 3-4 seconds to complete. This makes
> working with the Typo3 cache really problematic for us.

It may depend a lot on your MySQL settings. By default it is not optimized
for heavy load at all.

-- 
Dmitry Dulepov
TYPO3 expert / TYPO3 security team member
Read more @ http://dmitry-dulepov.com/


More information about the TYPO3-english mailing list