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

[imthebest]

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):



Thanks,
Super120

 

[Xon]

That is the table schema for xengallery? You can see it by running:

show create table xengallery;

 

[imthebest]

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

 

[Xon]

@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]

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`)
) ENGINE=InnoDB AUTO_INCREMENT=46205 DEFAULT CHARSET=utf8 |

Do it looks fine?

 

[Chris D]

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]

There is likely an index missing there, for some reason.

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?

 

[Chris D]

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

 

[SneakyDave]

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

 

[imthebest]

Has this been addressed on XFMG 1.0.3?

 

[Chris D]

Not yet, no.

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

 

[Chris D]

We've made various improvements here for version 1.0.4.

 

[imthebest]

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

Also, this one should have the "Duplicate" prefix.

 

[Chris D]

If it makes you happy.