Loading...
 
Development

Development

Forum List Topic List

Forums » Development » MySQL - Optimize Table
 
Thread actions
PDF Print this page
  • Print all pages

    • MySQL - Optimize Table

    Posted by Mark Starmer posts: 16 ☆ ☆ ☆ United Kingdom

    Hello,

    I've found out that on a few tables (including tiki_page and tiki_session) the MySQL cache gets a little fragmented when doing a large number of updates. This can slow down MySQL quite a bit when displaying WIKI pages. Therefore a way of reducing this overhead is to use the OPTIMISE TABLE command (in conjunction with ANALYSE TABLE to check to see whether there is table fragmentation). This could be aded to the administration functions if not there already.

    I gained a noticable increase in speed after doing this.

    Best Regards

    Mark Starmer

    Reads: 2045
    Link
    Posted by Damian Parker posts: 2881 ☆ ☆ ☆ ☆ ☆ United Kingdom

    Hi Mark,

    Yes this is very useful. We should add this as an option on the new System Admin menu in 1.8 and above.

    I have to admit, I used it on my corpus database from my Squirrelmail installation and noticed a big difference there. Never thought about trying it on tiki!

    Damian!

    Link
    Posted by Thomas Vincent posts: 2 ☆ United States

    > Damian:
    > Hi Mark,
    >
    > Yes this is very useful. We should add this as an option on the new System Admin menu in 1.8 and above.
    >
    > I have to admit, I used it on my corpus database from my Squirrelmail installation and noticed a big difference there. Never thought about trying it on tiki!
    >
    > Damian!

    PHP-Nuke has something similiar in its administration panel.

    Link

    Posted by paulap01 posts: 112 ☆ ☆ ☆ Austria

    I am a newbee.

    Is there a feature within phpmyadmin to control these aspects?

    paulap

    Link

    Posted by Mark Starmer posts: 16 ☆ ☆ ☆ United Kingdom

    Yes, I believe you can access these functions from phpMyAdmin, after doing a little bit of research - it will only give you the option to optimise the table if it needs doing. perhaps it may be benificial to take a look at the code in phpMyAdmin and see if it can be ported directly over to tiki.

    Best Regards

    Mark Starmer

    Link
    Posted by paulap01 posts: 112 ☆ ☆ ☆ Austria

    > markstarmer:
    > Yes, I believe you can access these functions from phpMyAdmin, after doing a little bit of research - it will only give you the option to optimise the table if it needs doing. perhaps it may be benificial to take a look at the code in phpMyAdmin and see if it can be ported directly over to tiki.
    >
    > Best Regards
    >
    > Mark Starmer

    Thx Mark!

    I have tried it and it works easy:
    - choose a DB
    - set to structure tab
    - choose the tables to optimize
    - choose "Optimize Tables" from the drop-down-field at the right
    - Go!

    I use the phpMyAdmin 2.5.5-pl1. I think the latest prod release.

    paulap

    Link

    Posted by paulap01 posts: 112 ☆ ☆ ☆ Austria

    > markstarmer:
    > Yes, I believe you can access these functions from phpMyAdmin, after doing a little bit of research - it will only give you the option to optimise the table if it needs doing. perhaps it may be benificial to take a look at the code in phpMyAdmin and see if it can be ported directly over to tiki.
    >
    > Best Regards
    >
    > Mark Starmer

    Thx Mark!

    I have tried it and it works easy:
    - choose a DB
    - set to structure tab
    - choose the tables to optimize
    - choose "Optimize Tables" from the drop-down-field at the right
    - Go!

    I use the phpMyAdmin 2.5.5-pl1. I think the latest prod release.

    paulap

    Link

    Posted by Thomas Vincent posts: 2 ☆ United States

    > Damian:
    > Hi Mark,
    >
    > Yes this is very useful. We should add this as an option on the new System Admin menu in 1.8 and above.
    >
    > I have to admit, I used it on my corpus database from my Squirrelmail installation and noticed a big difference there. Never thought about trying it on tiki!
    >
    > Damian!

    PHP-Nuke has something similiar in its administration panel.

    Link

    Posted by MehMehMeh posts: 10 ☆ ☆ ☆

    A well designed system wold not need to use OPTIMIZE TABLE all over the place to handle variable btye field fragmanetation.

    If you had a forum system for instance, it would be an idea to make one table for the basic post info, and make it a fixed with including the subject string. You would then have a realted table for posts. you can use JOIN's to put content back together. With proper indexing, things like the view of forum indexes (which are demanding and have lots of hits), would be fast as it would not have to work on fragmented tables. Admittedly you might want to optimize the table with post data, however the system would be faster overall and would not need to have OPTIMIZE TABLE like Tiki does.

    Why should you rely on MySQL's cache ? That's just bad, because you are just relying on a cache because you can't be bothered as a development team to make your code clean.

    If Tiki had to have it's database put on a separate server, I'd hate to see it work, as with 200 queries in a page, you would make so much network traffic and put so much data contention on the network, not to mention it would be so slow. It's bad enough on this server, which obviously runs MySQL on the same system as the webserver (and no doubt this is a Linux system where IPC communication is fast)

    Link