User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 2 of 2

Thread: Forum optimisation MySQL Innodb?

  1. #1
    Forum Admin
    Points: 7,277,671, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 99.8%
    Achievements:
    Three FriendsVeteranCreated Album picturesCreated Blog entryOverdriveTagger First ClassYour first GroupRecommendation First Class

    Forum optimisation MySQL Innodb?


    TransAmDan's Avatar
    Join Date
    Apr 1999
    Location
    Waterlooville, Hampshire
    Posts
    13,122
    Points
    7,277,671
    Level
    100
    Total Contributions For

    TransAmDan     £ 5.00
    Avg. Time Online
    2 Hours 32 Minutes 57 Seconds
    Blog Entries
    31
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    vBActivity - Stats
    Points
    108,135
    Level
    75
    vBActivity - Bars
    Lv. Percent
    71.22%

    Forum optimisation MySQL Innodb?

    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).
    Forum optimisation MySQL Innodb?-mysql_innodb.jpg

    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.
    Forum optimisation MySQL Innodb?-mysql_myisam_db_access_times.jpg

    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.
    Forum optimisation MySQL Innodb?-mysql_innodb_db_access_times.jpg


    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.
    Last edited by TransAmDan; 03-09-2015 at 11:55.

    2000 Trans-Am WS6 LS1 M6. (The Bat)
    Corsa Cat back, 3.5" dual tips. F.A.S.T LSX Intake / F.A.S.T Fuel rails, Dynatech LT Headers / Y-Pipe / No Cats, Wisper Lid, ported MAF, Racetronix 42lb/hr fuel injectors. Hypertech 160 stat. !AIR, !EGR. Walbro 255, SPEC Stage II Clutch / Flywheel. HPTuners 1BAR SD Tune. Wide Band gauge.
    389RWHP/380RWTQ ([477HP/460TQ]Flywheel) Dyno Dynamics

    More info and photos at:-
    http://www.solent-renegades.co.uk/co...-trans-am.html

  2. #2
    Premier Users
    Points: 108,511, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 100.0%
    Achievements:
    OverdriveTagger First ClassThree FriendsCreated Blog entryVeteran100000 Experience Points
    Awards:
    Frequent Poster

    Forum optimisation MySQL Innodb?


    AmericanThunder's Avatar
    Join Date
    Jan 2014
    Location
    Swindon, UK
    Posts
    9,230
    Points
    108,511
    Level
    100
    Total Contributions For

    AmericanThunder     £ 15.00
    Avg. Time Online
    1 Hour 17 Minutes 23 Seconds
    Blog Entries
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    vBActivity - Stats
    Points
    20,218
    Level
    39
    vBActivity - Bars
    Lv. Percent
    98.32%
    ah ha - and if you connect the doohickey to the whatsit, do you get more flugaloids per nanosecond throughput?

    Thanks for what you do though!

Similar Threads

  1. Forum Speed
    By TransAmDan in forum Recent Changes and Adjustments
    Replies: 45
    Last Post: 02-07-2015, 15:01
  2. New to forum
    By Carlatheimpala in forum New Member Check-in
    Replies: 22
    Last Post: 17-06-2015, 03:22
  3. New to forum
    By recoveryharryp in forum New Member Check-in
    Replies: 9
    Last Post: 06-06-2014, 05:31
  4. New to the forum
    By Saturn Simon in forum New Member Check-in
    Replies: 9
    Last Post: 20-05-2014, 22:47
  5. New forum
    By TransAmDan in forum Recent Changes and Adjustments
    Replies: 35
    Last Post: 03-11-2009, 15:40

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •