[TYPO3-english] sys_refindex

[Dmitry Dulepov]

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.