• 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?

imthebest

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

1.png

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

2.png

Thanks,
Super120
 

imthebest

Formerly Super120
#3
There isn't a "xengallery" table but there are the following:

Code:
| 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].
 

Xon

Well-known member
#4
@Xon please let me know what to put in [table_name].
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.
 

imthebest

Formerly Super120
#5
Here you go:

Code:
| 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`)
) ENGINE=InnoDB AUTO_INCREMENT=46205 DEFAULT CHARSET=utf8 |
Do it looks fine?
 

Chris D

XenForo developer
Staff member
#6
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.
 

SneakyDave

Well-known member
#9
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