XF 1.1 Long running queries on update xf_data_registry

[Jim Boy]

Howdy we are getting queries run for 60 seconds or more which should be fast you would think on updating data registry.

For example a truncated :

INSERT INTO xf_data_registry^M\n\t\t\t\t(data_key, data_value)^M\n\t\t\tVALUES^M\n\t\t\t\t('simpleCache', 'a:5:{s:11:\\"activeCount\\";i:6;s:16:\\"lastModifiedDate\\";i:1381194975;s:8:\\"natCache\\";a:3:{s:13:\\"checkTabPerms\\";b:0;s:8:\\"nodeTabs\\";a:2:{i:0;a:26:{s:7:\\"node_id\\";i:3;s:15:\\"nat_display_tab\\";i:1;s:20:\\"nat_display_tabperms\\";i:0;s:12:\\"nat_tabtitle\\";s:5:\\"Clubs\\";s:17:\\"nat_display_order\\";i:1;s:12:\\"nat_position\\";s:6:\\"middle\\";s:14:\\"nat_childlinks\\";i:0;s:19:\\"nat_childlinksperms\\";i:0;s:12:\\"nat_markread\\";i:0;s:17:\\"nat_linkstemplate\\";s:23:\\"nat_teams_linkstemplate\\";s:9:\\"nat_popup\\";i:1;s:9:\\"nat_tabid\\";s:0:\\"\\";s:14:\\"nat_childnodes\\";s:255:\\"15,226,242,18,172,252,156,326,25,264,157,164,306,7,168,249,14,404,231,247,232,23,233,292,26,224,225,94,246,307,336,318,397,19,355,163,202,204,299,27,85,240,244,13,203,139,28,146,241,338,16,406,237,293,22,295,323,29,347,243,20,349,129,118,17,206,248,32,329\\";s:19:\\"nat_firstchildnodes\\";s:6774:\\"a:19:{i:0;a:13:{s:7:\\"node_id\\";i:15;s:5:\\"title\\";s:8:\\"Adelaide\\";s:11:\\"description\\";s:34:\\"The Team for ALL South Australians\\";s:9:\\"node_name\\";N;s:12:\\"node_type_id\\";s:5:\\"Forum\\";s:14:\\"parent_node_id\\";i:3;s:13:\\"display_order\\";i:5;s:15:\\"display_in_list\\";i:1;s:3:\\"lft\\";i:136;s:3:\\"rgt\\";i:141;s:5:\\"depth\\";i:1;s:8:\\"style_id\\";i:0;s:18:\\"effective_style_id\\";i:0;}i:1;a:13:{s:7:\\"node_id\\";i:18;s:5:\\"title\\";s:14:\\"Brisbane Lions\\";s:11:\\"description\\";s:46:\\"Team of the millennium... 3 flags and counting\\";s:9:\\"node_name\\";N;s:12:\\"node_type_id\\";s:5:\\"Forum\\";s:14:\\"parent_node_id\\";i:3;s:13:\\"display_order\\";i:10;s:15:\\"display_in_list\\";i:1;s:3:\\"lft\\";i:142;s:3:\\"rgt\\";i:151;s:5:\\"depth\\";i:1;s:8:\\"style_id\\";i:0;s:18:\\"effective_style_id\\";i:0;}i:2;a:13:{s:7:\\"node_id\\";i:25;s:5:\\"title\\";s:7:\\"Carlton\\";s:11:\\"description\\";s:44:\\"Proud Sponsors of Matthew Kreuzer since 2008\\";s:9:\\"node_name\\";N;s:12:\\"node_type_id\\";s:5:\\"Forum\\";s:14:\\"parent_node_id\\";i:3;s:13:\\"display_order\\";i:15;s:15:\\"display_in_list\\";i:1;s:3:\\"lft\\";i:152;s:3:\\"rgt\\";i:161;s:5:\\"depth\\";i:1;s:8:\\"style_id\\";i:0;s:18:\\"effective_style_id\\";i:0;}i:3;a:13:{s:7:\\"node_id\\";i:7;s:5:\\"title\\";s:11:\\"Collingwood\\";s:11:\\"description\\";s:35:\.....[lots more - the statement is about 50K in size]....6:\\"active\\";i:0;}}}')^M\n\t\t\tON DUPLICATE KEY UPDATE^M\n\t\t\t\tdata_value = VALUES(data_value)

This is causing issues in a number of areas such infracting, uploading avatars etc. Our installation - bigfooty.com is a large installation, 30,000,000 posts. We are now running on AWS using RDS. Neither web servers nor database are under duress

Any ideas?

 

[TPerry]

Howdy we are getting queries run for 60 seconds or more which should be fast you would think on updating data registry.

For example a truncated :

INSERT INTO xf_data_registry^M\n\t\t\t\t(data_key, data_value)^M\n\t\t\tVALUES^M\n\t\t\t\t('simpleCache', 'a:5:{s:11:\\"activeCount\\";i:6;s:16:\\"lastModifiedDate\\";i:1381194975;s:8:\\"natCache\\";a:3:{s:13:\\"checkTabPerms\\";b:0;s:8:\\"nodeTabs\\";a:2:{i:0;a:26:{s:7:\\"node_id\\";i:3;s:15:\\"nat_display_tab\\";i:1;s:20:\\"nat_display_tabperms\\";i:0;s:12:\\"nat_tabtitle\\";s:5:\\"Clubs\\";s:17:\\"nat_display_order\\";i:1;s:12:\\"nat_position\\";s:6:\\"middle\\";s:14:\\"nat_childlinks\\";i:0;s:19:\\"nat_childlinksperms\\";i:0;s:12:\\"nat_markread\\";i:0;s:17:\\"nat_linkstemplate\\";s:23:\\"nat_teams_linkstemplate\\";s:9:\\"nat_popup\\";i:1;s:9:\\"nat_tabid\\";s:0:\\"\\";s:14:\\"nat_childnodes\\";s:255:\\"15,226,242,18,172,252,156,326,25,264,157,164,306,7,168,249,14,404,231,247,232,23,233,292,26,224,225,94,246,307,336,318,397,19,355,163,202,204,299,27,85,240,244,13,203,139,28,146,241,338,16,406,237,293,22,295,323,29,347,243,20,349,129,118,17,206,248,32,329\\";s:19:\\"nat_firstchildnodes\\";s:6774:\\"a:19:{i:0;a:13:{s:7:\\"node_id\\";i:15;s:5:\\"title\\";s:8:\\"Adelaide\\";s:11:\\"description\\";s:34:\\"The Team for ALL South Australians\\";s:9:\\"node_name\\";N;s:12:\\"node_type_id\\";s:5:\\"Forum\\";s:14:\\"parent_node_id\\";i:3;s:13:\\"display_order\\";i:5;s:15:\\"display_in_list\\";i:1;s:3:\\"lft\\";i:136;s:3:\\"rgt\\";i:141;s:5:\\"depth\\";i:1;s:8:\\"style_id\\";i:0;s:18:\\"effective_style_id\\";i:0;}i:1;a:13:{s:7:\\"node_id\\";i:18;s:5:\\"title\\";s:14:\\"Brisbane Lions\\";s:11:\\"description\\";s:46:\\"Team of the millennium... 3 flags and counting\\";s:9:\\"node_name\\";N;s:12:\\"node_type_id\\";s:5:\\"Forum\\";s:14:\\"parent_node_id\\";i:3;s:13:\\"display_order\\";i:10;s:15:\\"display_in_list\\";i:1;s:3:\\"lft\\";i:142;s:3:\\"rgt\\";i:151;s:5:\\"depth\\";i:1;s:8:\\"style_id\\";i:0;s:18:\\"effective_style_id\\";i:0;}i:2;a:13:{s:7:\\"node_id\\";i:25;s:5:\\"title\\";s:7:\\"Carlton\\";s:11:\\"description\\";s:44:\\"Proud Sponsors of Matthew Kreuzer since 2008\\";s:9:\\"node_name\\";N;s:12:\\"node_type_id\\";s:5:\\"Forum\\";s:14:\\"parent_node_id\\";i:3;s:13:\\"display_order\\";i:15;s:15:\\"display_in_list\\";i:1;s:3:\\"lft\\";i:152;s:3:\\"rgt\\";i:161;s:5:\\"depth\\";i:1;s:8:\\"style_id\\";i:0;s:18:\\"effective_style_id\\";i:0;}i:3;a:13:{s:7:\\"node_id\\";i:7;s:5:\\"title\\";s:11:\\"Collingwood\\";s:11:\\"description\\";s:35:\.....[lots more - the statement is about 50K in size]....6:\\"active\\";i:0;}}}')^M\n\t\t\tON DUPLICATE KEY UPDATE^M\n\t\t\t\tdata_value = VALUES(data_value)

This is causing issues in a number of areas such infracting, uploading avatars etc. Our installation - bigfooty.com is a large installation, 30,000,000 posts. We are now running on AWS using RDS. Neither web servers nor database are under duress

Any ideas?

Disable your add-ons via config.php and see if it still happens. If not, re-enable via config.php then go into the ACP and disable them all and start re-enabling them one at a time until it happens again (if it does). Will eliminate a problematic add-on.

 

[Mike]

It's a primary key update, so it shouldn't be taking that long in theory. This may be a symptom rather than the root problem.

However, add-ons are putting their own data in there. Whether or not this is a good thing or correct is really down to the add-on's usage. It's likely that the only way to remove the data is to uninstall add-ons that have put data there, though I suspect add-ons may not necessarily clean up that data as desired (in which case, it would have to be manually removed; the add-on author should be able to help).

 

[Jim Boy]

Thanks for the suggestions. Add-ons are certainly an issue in general and I have had to hack aay a few of them to get them to behave.

However i this case it was a little different. It came down the blob value for 'SimpleCache; being fixed or locked somehow. The updates were taking a long time because the data value would not change. I couldn't delete the field and I couldn't even drop the table. Eventually I discovered I could change the value of the primary key. The row was quickly recreated by another process and everything ran smoothly. Also at this time I could delete the original row.

It is starting to sound more like a MySQL issue, although I am not 100% convinved. Possibly a corrupted table, however I was able to pull the contents out of table and do a ysqldump.

We will be shifting to 1.2 shortly and hopefully the improvements there will add in or setup, which is very decentralised.

 

[xf_phantom]

It seems that it's a problem with the nodes as tabs addon ( How many nodes do you have?)

As mike said, disabling the addon won't help here
That's why i made this suggestion
In combination with http://xenforo.com/community/threads/xenforo-1-3.63389/page-3#post-683279

addon system improvementsxenforo should handle much more addon specific data & tasks automatically (e.g. create sql installcode, content type & content type field handling, or just think about a standardized installer.. ATM each addon have to implement the same logic to create/alter/delete the tables and for the cleanup of the orphan data like attachments, modqueue, newsfeed,... IMO this could be much easier and 3rd party dev friendlier

xenforo would remove the additional records automatically while the addon deinstallation

 

[Jim Boy]

Interesting, I'd already noted NodesAsTabs as an add on of interest as I had noticed that it triggered that particular cache. We have 300+ nodes, but dodn't really utilise them so much as tabs, only 20 get listed as tabs.

Certainly in an ideal world add-ons would be firewalled with entry into data only though some abstraction layer, but that is probably not a realistic solution.