Forum optimisation MySQL Innodb?

[TransAmDan]

I'm always looking at making the forum perform better. Its been a long journey, usually upgrading the spec of the server.
I'm now running one forum on a dedicated server Xeon E3-1230V2@3.3Ghz (8 processors show up in 'Top') with 8Gb of RAM. Running Xcache. Yes its been running splendid, but you know you get that urge, I wonder if I can make improvements...

Page speed info at the bottom is usual.
Page generated in 0.69243 seconds with 84 queries. Memory Usage: 19.73 MB
can be a little higher some time, but usually under 1 second.

The answers tend to be looking at the MySQL server for speed increases. So i have logged into that(same server) and looks at what it suggests to improve things. One was pointing out table locks and convert some tables from MyISam to Innodb, as Innodb does Row locking instead of table locking, so can avoid the queuing of MySQL statements.

So i done a search and came across Part 2 vB4mance: vBulletin4 Optimization - Basic Guide to InnoDB Conversion - vBulletin Community Forum

Completed the following tables in Innodb by running the MySQL commands:-

alter table attachment engine=InnoDB;
alter table datastore engine=InnoDB;
alter table deletionlog engine=InnoDB;
alter table forum engine=InnoDB;
alter table pm engine=InnoDB;
alter table pmreceipt engine=InnoDB;
alter table pmtext engine=InnoDB;
alter table post engine=InnoDB;
alter table postparsed engine=InnoDB;
alter table searchcore engine=InnoDB;
alter table searchgroup engine=InnoDB;
alter table searchlog engine=InnoDB;
alter table sigparsed engine=InnoDB;
alter table subscribethread engine=InnoDB;
alter table thread engine=InnoDB;
alter table threadviews engine=InnoDB;
alter table user engine=InnoDB;
alter table usertextfield engine=InnoDB;

I done this around 9pm Monday night, the forum runs fine. Processor usage at the time looked normal or a little higher, however this was probably because we has a rush of visitors and also the daily back up was about to start, so not a good time to look at averaged stats.

Anyway I got a few email alerts in the night saying the performance of the pages has dropped (New Relic monitoring).

As you can see the MySQL part of the data fetching for the page has greatly increased since the switch to InnoDB type tables in the database.

I have a comparison before and after the Innodb change. listed in the order of what takes the most in 'ms' at the top.

Worse case in average in an hours worth of monitoring the MySQL is taking 1.5ms on one request, and less on other MySQL accesses.
This makes its around 10ms on average per page request.

Now with Innodb on the tables shown above.


MySQL is taking 9 ms on some requests, there could be many of these requests on a page.
So checking the total per page, Its now taking 50ms-100ms per page on just MySQL alone.

So far its looking like going Innodb takes 10 times longer on just the MySQL.

So with that test I have switched back to MyISam tables in the database, it would seem the table lock is very few times per hour, to avoid this lock you can use a different form of table which takes 10 times longer in the database fetching the data.

 

[AmericanThunder]

ah ha - and if you connect the doohickey to the whatsit, do you get more flugaloids per nanosecond throughput? :eyebrows:

Thanks for what you do though! :high5: