XF 1.2 Server hangs in new XF install

[Wild Onion]

Just switched over my forum fro VB 3.7 to XF. Everything seems to be working fairly well, but the server seems to intermittently get bogged down and sometimes stops.

I'm a novice at SQL, but think I've tracked it down to this in my WHM SQL process list:

894707xxx_xxxxxlocalhostxxxx_xxxxxPrepare11LockedSELECT thread.* , user.*, IF(user.username IS NULL, thread.username, user.username) AS use

I'm getting 10-20 of these on the process list, which I think is the reason it hangs. Any ideas?

 

[Paul B]

Do you have any add-ons installed?

If so, disable them all and re-enable them one at a time to determine which one is causing it.

 

[Wild Onion]

I disabled them all and I'm still have the same issue

 

[Paul B]

I am unable to find that query in the core code.
It appears to be from an add-on.

Was your server previously tuned for vB?
If so, you may need to change some settings to suit XF.
Also, are all of your table types correct?

 

[Wild Onion]

After seeing a different SQL error, I disabled exim, and that seemed to fix it. I'm not using email on this domain, so I guess that's OK for now.

 

[Wild Onion]

I still haven't been able to fix this issue. Things I've tried:

• Disabled all add-ons.
• Enabled innoDB (which I had missed on the import).
• Increased InnoDB settings in my.cnf.
• Disabled all mail

I'm completely stumped. The site is almost unusable.

 

[Paul B]

Your previous post stated you had resolved it.

I'm confused as to what the actual problem is now.

 

[Wild Onion]

It's the same problem, it just seems to come and go. I've tracked down that there is a problem with users writing to the database.

This process is currently at 369 % CPU:

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.hawkeyelounge.com.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock

 

[Paul B]

I suspect this is related to your other thread: http://xenforo.com/community/threads/delayed-sql-processes-new-vb-3-7-import.66146/#post-695289

You may need to do some investigation and server tuning.

 

[Mike]

Can you run this query as the root MySQL user?

SHOW FULL PROCESSLIST;

We need to see it when the load is high.

 

[Wild Onion]

Sorry about the formatting.

This is the full text of the "info":

SELECT thread.* , user.*, IF(user.username IS NULL, thread.username, user.username) AS use

 

[Mike]

If you're running this via phpMyAdmin, you need to select the option to display the full text -- I need to see the whole query.

However, this does potentially look like a situation where MySQL simply needs to be tuned (mostly around buffer sizes: http://www.mysqlperformanceblog.com/2013/09/20/innodb-performance-optimization-basics-updated/).