XF 1.4 vB to xenForo problems

[PASS]

Ever since I moved my two vB sites over to XF, I've been having server problema. It is the first time I had to open a support ticket with the host in over a year.

The CPU load has been through the roof and MySQL connections have been getting reached at 550 since I install XF. Happens about every other day around the same time give or take an hour. It brings my sites/server to a crawl. Have to re-boot the server to fix it. This is was support guy said.

I checked the slow query log on your server and there are many queries listed in there, right before your server was rebooted. I will list the 2 queries with the longest execution time below:

INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES ('b4b11398c7045ffe24ad3ef5a55b6d5b', 'a:6:{s:12:\"sessionStart\";i:1427679500;s:2:\"ip\";s:4:\"BùKR\";s:11:\"sessionCsrf\";s:16:\"lkAiVkcoDmFe7wi-\";s:9:\"userAgent\";s:72:\"Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)\";s:7:\"robotId\";s:6:\"google\";s:16:\"previousActivity\";i:0;}', '1427683100');


UPDATE vsavilxh_guests SET dateline = '1427679505' WHERE userip = '66.249.89.45';

I hope this information helps. You might want to check with a DBA as to what might be causing this.

Any idea what I can do to stop this? I never had issues like this until I moved from vB.

my.cnf

[mysqld]
log-slow-queries=/var/lib/mysql/log-slow-queries.log
open_files_limit=10896
innodb_file_per_table=1
# safe-show-database
skip-networking
thread_cache_size=4
tmpdir="/home/mysql-tmp"
default-storage-engine=MyISAM

max_connections=550
max_allowed_packet=268435456
innodb_buffer_pool_size=8M

I'm on a cloud VPS server with these specs..
0.7 core
3GB memory
Apache v2.2.29
PHP 5.4.39
MySQL 5.5.42

Two XF sites. (same traffic when they ran on vB)
Site 1 Averages: 125 members and 200 guests (30 mins) 250 member daily
Site 2 Averages: 25 members and 75 guests (30 mins) 45 members daily

 

[Mike]

First, the xf_session query should be very fast as it's just a basic insert. The second query you showed is not related to XF (or is related to an add-on) so I couldn't comment on it at all. I don't know if that is a slow query.

Your my.cnf is totally untuned though. You're actually only allowing InnoDB to even use 8MB of memory. This should be way higher: http://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/

 

[PASS]

Thanks Mike, I will check out how to tune my.cnf. Hopefully that will fix things. Any suggestions for my.cnf that I can try?