Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Dave MacDonald Posted July 6, 2016 Posted July 6, 2016 Hi there, We've a reasonable sized IPB board (43000 members, 3.6 million posts) that we upgraded from v3.4.7 to v4.1.13 last night. The upgrade went fine, no problems, and the forum us running, just about. The background processes have been set to run by cron as per advice I was given by IPB support however my server is on it's knees with a load of 13 as I write this versus a previous load on v3.4.7 that seldom got higher than 2.0 even at busy times. Support have told me that my hosting is "weak" and that they can't help me resolve the problem. The background processes will take about 3 months to finish going by their current rate. I've looked into "tuning" mysql but I'll be honest and say I'm making changes and I've no idea what most of them are doing. Either way the server load remains ridiculously high, the background processes are running at a snails pace and my users are unhappy. The host is a DigitalOcean VM with 320Gb SSD storage, 12 cores, and 32Gb of RAM. I'd have thought it was more than capable of servicing a load of 1500 users which is what we have online at the moment. Here's my my.cnf at the moment; [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 64M max-heap-table-size = 64M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 16G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 Anyone got any advice? I'd be up for paying a DBA to have a look on the server if that helps. The IPB support team don't want to know.
Dave MacDonald Posted July 6, 2016 Author Posted July 6, 2016 21 minutes ago, RevengeFNF said: Sent you a PM. Thanks, have responded.
ProSkill Posted July 6, 2016 Posted July 6, 2016 2 hours ago, Dave MacDonald said: Hi there, We've a reasonable sized IPB board (43000 members, 3.6 million posts) that we upgraded from v3.4.7 to v4.1.13 last night. The upgrade went fine, no problems, and the forum us running, just about. The background processes have been set to run by cron as per advice I was given by IPB support however my server is on it's knees with a load of 13 as I write this versus a previous load on v3.4.7 that seldom got higher than 2.0 even at busy times. Support have told me that my hosting is "weak" and that they can't help me resolve the problem. The background processes will take about 3 months to finish going by their current rate. I've looked into "tuning" mysql but I'll be honest and say I'm making changes and I've no idea what most of them are doing. Either way the server load remains ridiculously high, the background processes are running at a snails pace and my users are unhappy. The host is a DigitalOcean VM with 320Gb SSD storage, 12 cores, and 32Gb of RAM. I'd have thought it was more than capable of servicing a load of 1500 users which is what we have online at the moment. Here's my my.cnf at the moment; [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 64M max-heap-table-size = 64M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 16G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 Anyone got any advice? I'd be up for paying a DBA to have a look on the server if that helps. The IPB support team don't want to know. It seems like IPB support has been getting progressively worse unfortunately. Instead of actually trying to help they blame it on the host. In any case are you 100% sure the problem is with MYSQL? I've run into a similar issue but it was with a poorly written application namely videobox. I'd start by disabling third-party plugins and applications, and see if that helps. You could also try running mySQLTuner yourself. I don't think you need a full fledged DBA just someone who knows a bit more about this stuff. I'd be happy to help if you get stuck.
Dave MacDonald Posted July 6, 2016 Author Posted July 6, 2016 Thanks mate, much appreciated. We are only runnign with one plugin which is TapaTalk for our App. MYSQL is definitely not very happy. It's running at 300% CPU utilisation most of the time today, sometimes higher. I've spoken with the support guys again today and also to RevengeFNF above. I've got a mix of INNODB and MyISAM tables in the database so apparently I need to try and get them all over to INNODB and I've also been advised that I should be running MariaDB rather than MySQL Community Edition. The background processes which I am sure is the cause of the whole thing are hardly moving which is really annoying. If I could at least see they were progressing and it would only be a day or so of slow performance I wouldn't be so bothered but at the current rate of progress it could be months before they finish! Support team were polite and all, and they have given me snippets of advice, but they really don't want to get involved.
Dave MacDonald Posted July 6, 2016 Author Posted July 6, 2016 Just now, superj707 said: Have you tried http://mysqltuner.com/ Yeah. I've tweaked my.cnf with recommendations from running that.
SJ77 Posted July 6, 2016 Posted July 6, 2016 Hi Dave can you post your output from top if you have it? I think that would help folks just to make sure your load is driven by mysql Also, I am not expert especially considering all of the problems I am having myself LOL but just some thoughts: Maybe your load will go down once the upgrade background tasks complete. mysqltuner needs more time than 24hours to make good recommendations Are you using memcached, redis or other cache system?
Mike Gholson Posted July 6, 2016 Posted July 6, 2016 37 minutes ago, superj707 said: Have you tried http://mysqltuner.com/ This works great! Thanks for sharing.
Dave MacDonald Posted July 7, 2016 Author Posted July 7, 2016 Ok guys, all our problems are now resolved. Our forum is running quicker than it ever has before, and I've been using IPB for over a decade. Here's what happened and what we changed, hopefully it might help someone else, as it was very painful to get here! My thanks to the IPB support staff and to RevengeFNF and to everyone above who offered help. My Community Has about 43000 members, around 3.6 million posts in the database (we culled 5 million posts before the upgrade) and is 13 years old. We've been with Invision right from day one so we've been through every upgrade since, we've moved server dozens of times, we've messed around with stuff, we've had crashes, we've had database corruption. You name it, in 13 years we've had it all. We use the basic forum with only one plugin which is Tapatalk for our branded mobile App that is very popular. We run adverts on the forum to generate revenue to pay for our hosting and we use Nexus now to allow members to upgrade their account to see the forum ad-free. So it's all pretty bog standard stuff but it's relatively busy I guess. My Hosting We host on a Digital Ocean VM. I love DO, and I'm prepared to pay a premium for their incredible support and reliability. We have a 12 core, 32Gb RAM, 320Gb SSD server and we run Centos 7.2 x64 on it with Plesk management console sitting on top. We use MariaDB 10.1 as our database server (not 5.5, that is important!) Our Problem Post Upgrade We ran through the upgrade from 3.4.7 to 4.1.13 without any problems, it took maybe an hour or so to finish, no errors at all.The board re-opened and we set the background processes to run via Cron. They were going at a snails pace but I kind of knew that was going to happen as we had done a lot of testing of the upgrade, albeit without any user load. As users arrived on the forum everything looked ok but as more and more arrived the board started to run incredibly slowly, and we were seeing loads of sometimes 50 and over whereas on the same hardware on 3.4.7 we were seldom above a load of 3 even at busy times. Apache started to crash, the App was completely unusable. After a lot of messing around with my.cnf and of me shouting at IPB support we weren't much further forward. Ithen posted this topic and spoke to a few people like RevengeFNF. Our Fix Immediately on seeing our my.cnf that we had published RevengeFNF pointed out we were optimising for MyISAM tables and not InnoDB. To be honest I had no idea what the differences were, we were running a configuration I got from the toolbox at Percona.com. At the same time the IPB team asked me if I was running MyISAM or InnoDB. Looking at the database it seemed we had a mixture. Some on MyISAM, a lot actually, and some on InnoDB. Both IPB and RevengFNF advised me to change all the tables to InnoDB. I did this quickly and easily in PHPMyAdmin just using the ALTER TABLE SQL query eg; ALTER TABLE `20050615_ibf_core_themes ` ENGINE = INNODB; I did this for all the tables that were still showing MyISAM. The Post table was one of those, it took a few mins to run through but it finished with no problems. Having done that we then ran through the Percona Toolbox again, this time knowing for sure we were wanting to optimise the database config for InnoDB. Based on our answers and our server spec this is the my.cnf file we installed; [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 26G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log Now I've no idea if that is efficient or not but what I can tell you is that with 1500 users online right now, and background processes still running via Cron, the site is flying and the load is never above 3. So, as I said, hopefully this will help someone somewhere. Sorry for the long post!
Recommended Posts
Archived
This topic is now archived and is closed to further replies.