Facebook  Twitter 

SMFHacks.com

+-

SMFHacks.com

+- User Information

Welcome, Guest.
Please login or register.
 
 
 
Forgot your password?

+- Forum Stats

Members
Total Members: 4255
Latest: andreios
New This Month: 3
New This Week: 1
New Today: 0
Stats
Total Posts: 43259
Total Topics: 7518
Most Online Today: 202
Most Online Ever: 2482
(April 09, 2011, 07:02:45 pm)
Users Online
Members: 0
Guests: 203
Total: 203

Author Topic: Gallery Pro extremely slow to load  (Read 7909 times)

0 Members and 1 Guest are viewing this topic.

Offline TeraS

  • Member
  • *
  • Posts: 39
    • View Profile
Gallery Pro extremely slow to load
« on: March 04, 2018, 07:35:57 pm »
We've been using Gallery Pro since version 4.0 and we've moved to a new server.

Real life has meant I wasn't able to update to 7.0.2, which is the last version I'd had before the updating stopped, again because of real life these past few years.

We updated from 5.0.3a to 7.0.2 and the loading time for the Gallery takes 5 seconds or longer. Many times the server times out with:

Query execution was interrupted (max_statement_time exceeded)
File: /usr/share/nginx/html/smf_forum/Sources/Gallery2.php
Line: 10754

Is there a way to fix this issue? Would moving further up the versions get us to a solution? I know we have a lot of files in the Gallery as well.

Thoughts would be welcome...

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16436
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #1 on: March 04, 2018, 07:45:59 pm »
It probably won't. I would need to see the query on line 10754 that is being run. If you can post that here that would help.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline lovetohax

  • Member
  • *
  • Posts: 15
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #2 on: March 04, 2018, 07:49:05 pm »
To add onto this (co-admin), we've been able to narrow it down to when users either search or enter the gallery.

This query pops up in the SQL process list as being the culprit:

| 666 | ***   | localhost | *** | Query   |  203 | Sending data | SELECT p.id_picture, p.id_cat, p.commenttotal, p.keywords, p.filesize, p.thumbfilename, p.approve |    0.000 |

It locks the tables and instantly takes down the whole forum. I had to set the query time out to 10 seconds to avoid the whole site crashing, with PHP timing out.

We've tried doing a brief update to the gallery to test it out, and it seems to not have any impact.

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16436
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #3 on: March 04, 2018, 07:56:04 pm »
Can see the whole query? I would liike to see it then do an explain on the query and a show create table on smf_gallery_pic I want to see the indexes that are setup.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline lovetohax

  • Member
  • *
  • Posts: 15
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #4 on: March 04, 2018, 07:58:09 pm »
The whole query is:

Code: [Select]
$smcFunc['db_query']('', "INSERT INTO {db_prefix}gallery_pic
(ID_CAT, filesize,thumbfilename,filename, height, width,
keywords, title, description,ID_MEMBER,date,approved,allowcomments,
sendemail,mediumfilename,mature)
VALUES ($ID_CAT, " . $row['filesize']  . ",'" . $extrafolder .  $thumbname . "', '" . $extrafolder . $filename . "',
$sizes[1], $sizes[0], '" . $row['keywords']  . "','" . $row['title']  . "', '" . $row['description']  . "', " . $row['ID_MEMBER']  . ",
$t," . $row['approved']  . ", " . $row['allowcomments']  . "," . $row['sendemail']  . ",'" . $extrafolder .  $mediumimage . "'," . $row['mature']  . ")");
$gallery_pic_id = $smcFunc['db_insert_id']('{db_prefix}gallery_pic', 'id_picture');

Though the line is in the middle of the query and not the beginning (weird, is this the right query?).

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16436
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #5 on: March 04, 2018, 07:59:33 pm »
Is it the insert or the selecT? Inserts generally should be pretty quickly unless you a lot of transactions/a huge table.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline TeraS

  • Member
  • *
  • Posts: 39
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #6 on: March 04, 2018, 08:02:04 pm »
Total pictures in the gallery at this moment is: 39071

Offline lovetohax

  • Member
  • *
  • Posts: 15
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #7 on: March 04, 2018, 08:02:15 pm »
That's the line in the Gallery2.php, which I don't think is correct. I think the correct one is the query beforehand, I guess it's erroring out at the wrong line.

Here is the SHOW CREATE TABLE:

Code: [Select]
| smf_gallery_pic | CREATE TABLE `smf_gallery_pic` (
  `id_picture` int(11) NOT NULL AUTO_INCREMENT,
  `id_member` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `date` int(10) unsigned NOT NULL DEFAULT 0,
  `title` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `views` int(10) NOT NULL DEFAULT 0,
  `filesize` int(10) NOT NULL DEFAULT 0,
  `height` int(10) NOT NULL DEFAULT 0,
  `width` int(10) NOT NULL DEFAULT 0,
  `filename` tinytext DEFAULT NULL,
  `thumbfilename` tinytext DEFAULT NULL,
  `commenttotal` int(10) NOT NULL DEFAULT 0,
  `id_cat` int(10) NOT NULL DEFAULT 0,
  `user_id_cat` int(10) NOT NULL DEFAULT 0,
  `approved` tinyint(4) NOT NULL DEFAULT 0,
  `allowcomments` tinyint(4) NOT NULL DEFAULT 0,
  `totalratings` int(10) NOT NULL DEFAULT 0,
  `rating` int(10) NOT NULL DEFAULT 0,
  `type` tinyint(4) NOT NULL DEFAULT 0,
  `sendemail` tinyint(4) NOT NULL DEFAULT 0,
  `id_topic` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `keywords` varchar(100) DEFAULT NULL,
  `mature` tinyint(4) NOT NULL DEFAULT 0,
  `mediumfilename` tinytext DEFAULT NULL,
  `videofile` tinytext DEFAULT NULL,
  `additionalcats` tinytext DEFAULT NULL,
  `featured` tinyint(4) NOT NULL DEFAULT 0,
  `allowratings` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id_picture`),
  KEY `id_cat` (`id_cat`),
  KEY `user_id_cat` (`user_id_cat`),
  KEY `id_member` (`id_member`),
  KEY `rating` (`rating`),
  KEY `views` (`views`),
  KEY `commenttotal` (`commenttotal`),
  KEY `totalratings` (`totalratings`),
  KEY `id_cat_2` (`id_cat`),
  KEY `user_id_cat_2` (`user_id_cat`),
  KEY `id_member_2` (`id_member`),
  KEY `id_cat_3` (`id_cat`),
  KEY `user_id_cat_3` (`user_id_cat`),
  KEY `id_member_3` (`id_member`),
  KEY `id_cat_4` (`id_cat`),
  KEY `user_id_cat_4` (`user_id_cat`),
  KEY `id_member_4` (`id_member`)
) ENGINE=Aria AUTO_INCREMENT=44171 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 |

Offline lovetohax

  • Member
  • *
  • Posts: 15
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #8 on: March 04, 2018, 08:06:34 pm »
Oh, and sorry, but I can't get the full query, this is just what I could find when scrolling up in my SSH terminal.

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16436
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #9 on: March 04, 2018, 08:08:07 pm »
That query if you can get the whole query that would help.
My guess it is is from the searching of the gallery.

Also drop these extra indexes on the smf_gallery_pic table should not be for
Code: [Select]
  KEY `id_cat_2` (`id_cat`),
  KEY `user_id_cat_2` (`user_id_cat`),
  KEY `id_member_2` (`id_member`),
  KEY `id_cat_3` (`id_cat`),
  KEY `user_id_cat_3` (`user_id_cat`),
  KEY `id_member_3` (`id_member`),
  KEY `id_cat_4` (`id_cat`),
  KEY `user_id_cat_4` (`user_id_cat`),
  KEY `id_member_4` (`id_member`)
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16436
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #10 on: March 04, 2018, 08:14:54 pm »
I am not really familiar with MariaDB so I am not sure if Aria engine type is the best for it if that is like InnoDB or not.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline TeraS

  • Member
  • *
  • Posts: 39
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #11 on: March 04, 2018, 08:17:55 pm »
This issue was also present on mySQL on the prior server as well. if that helps at all.

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16436
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #12 on: March 04, 2018, 08:19:43 pm »
It's related to searching but I need to see how the query that locks up.
You can do test searches and see if it locks up.If so post that query here.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline TeraS

  • Member
  • *
  • Posts: 39
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #13 on: March 04, 2018, 08:22:48 pm »
There's also an issue where the unviewed page for many, comes back with a blank page. It's not every user, and there seems to be no clear reason for it either.

Offline TeraS

  • Member
  • *
  • Posts: 39
    • View Profile
Re: Gallery Pro extremely slow to load
« Reply #14 on: March 04, 2018, 08:25:09 pm »
Would passing you access to the database and server help? Honestly I'd love to have this sorted out if we can...

 

Related Topics

  Subject / Started by Replies Last post
4 Replies
6815 Views
Last post March 31, 2007, 01:37:52 pm
by SMFHacks
5 Replies
7711 Views
Last post June 24, 2007, 02:09:53 pm
by wkheathjr
9 Replies
7820 Views
Last post November 18, 2009, 08:45:31 am
by RollingRock
10 Replies
3949 Views
Last post August 02, 2018, 10:07:47 pm
by shuban
4 Replies
7133 Views
Last post August 08, 2023, 05:31:29 pm
by tank_fv101

+- Recent Topics

No thumbnails on new uploads by SMFHacks
March 27, 2024, 02:10:41 pm

Display the Contact Page for guests by SMFHacks
March 27, 2024, 10:55:43 am

is it possible to add support for odysee.com by fvlog19
March 21, 2024, 08:47:51 am

Request for admin notification by davejo
March 10, 2024, 01:31:59 am

I need help with torrent upload by Ineedsmfhelp
March 09, 2024, 10:01:13 pm

an idea for new mod (( content type with different display )) by SMFHacks
February 27, 2024, 01:36:27 pm

[Mod] RSS Feed Poster by SMFHacks
February 27, 2024, 11:57:18 am

find duplicate pictures by fvlog19
February 14, 2024, 02:22:40 pm

Error uploading video. by SMFHacks
February 08, 2024, 02:04:16 pm

Gallery icon as last added image by fvlog19
February 01, 2024, 01:04:56 pm

Powered by EzPortal