1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fixed XFMG 1.0.2 CPU intensive queries, maybe add some indexes?

Discussion in 'Media Gallery Resolved Bugs' started by imthebest, Jan 17, 2015.

  1. imthebest

    imthebest Formerly Super120

    When viewing the album list for a member (around 40% CPU usage on a i7 2600):


    When viewing a media item (around 60% CPU usage on a i7 2600):


  2. Xon

    Xon Well-Known Member

    That is the table schema for xengallery? You can see it by running:
    show create table xengallery;
  3. imthebest

    imthebest Formerly Super120

    There isn't a "xengallery" table but there are the following:

    | xengallery_add_map  |
    | xengallery_album  |
    | xengallery_album_permission  |
    | xengallery_album_view  |
    | xengallery_album_watch  |
    | xengallery_category  |
    | xengallery_category_map  |
    | xengallery_category_watch  |
    | xengallery_comment  |
    | xengallery_content_tag  |
    | xengallery_content_tag_map  |
    | xengallery_exif  |
    | xengallery_exif_cache  |
    | xengallery_field  |
    | xengallery_field_category  |
    | xengallery_field_value  |
    | xengallery_media  |
    | xengallery_media_user_view  |
    | xengallery_media_view  |
    | xengallery_media_watch  |
    | xengallery_private_map  |
    | xengallery_rating  |
    | xengallery_shared_map  |
    | xengallery_user_tag  |
    | xengallery_watermark  
    show create table [table_name];

    @Xon please let me know what to put in [table_name].
  4. Xon

    Xon Well-Known Member

    Sorry, misread the screenshot.

    You will want to check xengallery_comment

    I don't have XFMG, so you'll need to check the zip for an install file which should have the xengallery_comment table schema in it. I'm suspecting the indexes where not applied after an upgrade.
  5. imthebest

    imthebest Formerly Super120

    Here you go:

    | xengallery_comment | CREATE TABLE `xengallery_comment` (
      `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `content_id` int(10) unsigned NOT NULL DEFAULT '0',
      `content_type` enum('media','album') NOT NULL DEFAULT 'media',
      `message` mediumtext NOT NULL,
      `user_id` int(10) unsigned NOT NULL DEFAULT '0',
      `username` varchar(50) NOT NULL DEFAULT '',
      `ip_id` int(10) unsigned NOT NULL DEFAULT '0',
      `comment_date` int(10) unsigned NOT NULL DEFAULT '0',
      `comment_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
      `rating_id` int(10) unsigned NOT NULL DEFAULT '0',
      `likes` int(10) unsigned NOT NULL DEFAULT '0',
      `like_users` blob,
      `warning_id` int(10) unsigned NOT NULL DEFAULT '0',
      `warning_message` varchar(255) NOT NULL DEFAULT '',
      PRIMARY KEY (`comment_id`)
    Do it looks fine?
  6. Chris D

    Chris D XenForo Developer Staff Member

    There is likely an index missing there, for some reason. I think we need an index on content_id and content_type, at least.

    I'll do some profiling of the xengallery_album table, that doesn't quite seem right.
    imthebest and SneakyDave like this.
  7. imthebest

    imthebest Formerly Super120

    You mean that on my XFMG install there is an index missing? If so, maybe because it's an import from vBulletin 3.8 albums?
  8. Chris D

    Chris D XenForo Developer Staff Member

    No, I think we haven't actually put an index on there, yet.
    SneakyDave likes this.
  9. SneakyDave

    SneakyDave Well-Known Member

    I think Chris is saying that the comment table needs some more indexes added as part of the add-on, not just particularly on your install. That would probably help it run queries better.

    My table just has the one index also. Nothing to do with an import.

    edit: ninja'd
  10. imthebest

    imthebest Formerly Super120

    Has this been addressed on XFMG 1.0.3?
    Last edited: Feb 20, 2015
  11. Chris D

    Chris D XenForo Developer Staff Member

    Not yet, no.

    I will move this to bugs so we can look at it for the next release.
  12. Chris D

    Chris D XenForo Developer Staff Member

    We've made various improvements here for version 1.0.4.
    Walter, Coop1979, 51463 and 3 others like this.
  13. imthebest

    imthebest Formerly Super120

    Why this thread doesn't have a "Fixed" prefix?

    Also, this one should have the "Duplicate" prefix.
  14. Chris D

    Chris D XenForo Developer Staff Member

    If it makes you happy.
    Alluidh, Arno Nühm, Amaury and 4 others like this.

Share This Page