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

[Christopher Lörken]

Hi Joachim,

the cache tables are not too large I think. They all have less than 
40,000 rows and cache_pages is with 1.5GB the (by far) largest of them.
I've also tried saving in the backend after manually truncating 
cache_pages and cache_hash but it did not show a positive effect.

@InnoDB:
Dmitry pointed out that InnoDB allows row-level locking so since I think 
that in my case the pages table is the problem, I've chaned it's table 
type to InnoDB. It looked good at first allowing me to save in the 
backend as usual.
Today, however, the DB locked again with an update query on pages 
changing the last changed timestamp... (186 rows, < 200kb)

I did a "mysqladmin debug" and got some output about locks... 
Unfortunately I'm not to sure how to read that. The following types are 
listed:

Lock Used by delayed insert
Highest priority write lock
Low priority read lock

The pages table itself is shown with:
typo3.pages  Locked - write  Highest priority write lock
So I would assume, that the update thread has the lock and should 
actually perform the write. Nevertheless, that query does not come back 
even after 10 minutes...

Does anyone have an idea?

Regards,
Christopher

Am 05.01.2010 17:00, schrieb joachim rinck:
> hmm. sorry for not beeing very helpful but i wonder: how large are the
> cache-tables? is it possible to test if clearing the entire cache
> solves the problem temporarily (i'm not sure if it is a good idea to
> test this while the system is under heavy load, but probably it can be
> testet during no-load times using something like ab to generate load
> artificially) - background: i'm upon the lookout for occasional BE
> slowness when cache tables are large. it doesn't lock up the FE though
> in this case and also it's the other caching (framwork).
>
> 2010/1/4 Christopher Lörken<lists at bytro.com>:
>> Hi Dmitry and thanks for your answer. I'm well aware of InnoDB and your
>> performance tweak blog post ;)
>>
>> InnoDB _is_ enabled on our server.
>> The following tables are InnoDB:
>>
>> cache_hash
>> cache_imagesizes
>> cache_md5params
>> cache_pages
>> cache_pagesection
>> cache_typo3temp_log
>> fe_sessions
>> fe_session_data
>> sys_log
>>
>> What confuses me a bit know is that table cache_extensions is _not_ InnoDB.
>> I am pretty sure though that we did not change any defaults.
>>
>> Can cache_extensions be the problem?
>>
>> If no, what else?
>>
>> Am 04.01.2010 18:57, schrieb Dmitry Dulepov:
>>>
>>> Hi!
>>>
>>> On 04/01/2010 17:52, Christopher Lörken wrote:
>>>>
>>>> we have the problem that saving anything like pages or page content in
>>>> the backend oftentimes results in complete database deadlocks. This
>>>> happens very reproducible when the page is under a load like 40-50
>>>> requests per second.
>>>
>>> That's why some tables are defined as InnoDB :) Enable InnoDB on your
>>> MySQL host! It is not there just for fun, it is for a reason!
>>>
>>
>> _______________________________________________
>> TYPO3-english mailing list
>> TYPO3-english at lists.typo3.org
>> http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english
>>