1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.1 Long running queries on update xf_data_registry

Discussion in 'Troubleshooting and Problems' started by Jim Boy, Dec 13, 2013.

  1. Jim Boy

    Jim Boy Well-Known Member

    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?
     
  2. Tracy Perry

    Tracy Perry Well-Known Member

    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.
     
  3. Mike

    Mike XenForo Developer Staff Member

    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).
     
  4. Jim Boy

    Jim Boy Well-Known Member

    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.
     
  5. xf_phantom

    xf_phantom Well-Known Member

    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
    xenforo would remove the additional records automatically while the addon deinstallation:p
     
  6. Jim Boy

    Jim Boy Well-Known Member

    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.
     

Share This Page