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@lists.typo3.org > http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english _______________________________________________ TYPO3-english mailing list TYPO3-english@lists.typo3.org http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english
