[TYPO3-english] sys_refindex

[Douglas La Farge]

Hi!  

Dmitry,

Thank you very much for the response. It's very very helpful.

We've decided to not empty the table as we are concerned with the problems it might cause.

However, we are moving forward with changing that table (or set of tables?) to InnoDB.  This seems like the best initial solution so we're starting there.

We already had InnoDB enabled via the install script - it set the cache tables to that but apparently it didn't set the sys_refindex tables to that.  We allowed Typo3 to set up the tables we figured it (T3) thought best but it appears sys_refindex isn't changed in this process?

Following your advice we looked at the Install Tool -> Database Analyzer  to change the engine types but it appears we're missing something - we don't see any way to use  this tool to change the engine types.  Is this a recent addition to the install tool?  Our current version of T3 for the website in question is 4.4.*. However, I checked a 4.6 install and the Install Tool looks the same.  Thus I'm concluding it's not a T3 version feature change.

We're capable of changing the table type with an ALTER statement but to do so we'd like to know which tables should be changed (all of the sys_refindex* tables?).  Or, if we did missing something with the Install Tool -> Db Analyzer area would you kindly provide a little more instruction as to how to find and use this functionality?

Thank you again for your help.

Best regards,
Doug



On Nov 29, 2011, at 12:14 AM, Dmitry Dulepov wrote:

> Hi!
> 
> Douglas La Farge wrote:
>> 1) what's this table for?  looking at the processlist there are queries queued that when ran at the command line return no results.
> 
> It is too long to explain. Let's say, it useful for tracking relations between objects and warning you that you delete a record, which has connections from other records. It is not critical in the the sense that TYPO3 will not break if you clear the data. But it may cause data inconsistency. On a huge installation you can live without it and it will give you a performance boost.
> 
>> 2)  can the table be emptied?  is it possible those 2.2 million records should not be there?  what sort of bad things will happen if this table gets emptied?
> 
> Yes, it can. But you need to understand that you may end up in certain degradation of the database consistency.
> 
> Given your log:
> 
>> 973445	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=2
>> 973617	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='tx_dam' AND recuid=12991
>> 973721	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=2
>> 974503	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=2
>> 974576	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='tt_news' AND recuid=62743
>> 974757	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='tt_news' AND recuid=62735
>> 975084	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='tx_dam' AND recuid=13001
>> 975143	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=3214
>> 975162	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=4719
>> 975164	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=2
>> 975424	site1_cms_user	localhost	site1_cms	Query	8474	Locked	SELECT * FROM sys_refindex WHERE tablename='pages' AND recuid=3225
>> 982787	site1_cms_user	localhost	site1_cms	Query	8475	Locked	INSERT INTO sys_refindex
> 
> I suspect that your MySQL server does not have InnoDB enabled. This is a major issue. You must have InnoDB if you want TYPO3 to work fast. InnoDB allows insertion without locks. MyISAM (default database table engine) locks the table when somebody tries to insert data. Thus you get performance issues.
> 
> I would recommend to enable InnoDB and use the Database Analyzer in the Install tool to fix engine types for tables.
> 
> If you cannot do that, I have a dirty trick for you. Warning: this really dirty and if you do that, you understand everything I wrote above about data consistency.
> 
> The trick is to change the engine of sys_refindex to BLACKHOLE. BLACKHOLE discards all writes to the database. So it is super fast. Huge installations may greatly benefit from using BLACKHOLE for certain tables. But if you use that, you must be careful.
> 
> -- 
> Dmitry Dulepov
> TYPO3 core&security teams member
> Blog: http://dmitry-dulepov.com/
> Twitter: http://twitter.com/dmitryd
> 
> Simplicity will save the world.
> _______________________________________________
> TYPO3-english mailing list
> TYPO3-english at lists.typo3.org
> http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english