I looked on the XenForo forum today and Mike has said that none of their queries exceed 500 KB, so the MySQL default might be sufficient. However, I see a handful of threads of people using the 1M default and it not being sufficient for certain tasks/pages.
I know if you make a wiki page as long as the default setting 100,000 characters, you will probably hit the 1M default when you try to save it. This might be a fringe case though, I created a test page with 500,000 characters and it will never save under XenForo before PHP hits the time limit. Interestingly, the same test page does save under vBulletin.