8000 Photos In Stock Section: Slow Page Loading

Hi Tim,

I have a stock section with 8000 photos. > https://beeldbank.otterspeer.com/bndestem/
The page and included photo-pages load slowly, approx. 10-15 seconds per page view. Is there a way to speed it up?
When I check my hosting's resource usage, then CPU and RAM usage are low (25% max), but SQL I/O usage exceed the hosting's limits with approx. 600%. (10MB/sec allowed, with stock search query it peaks at 90-110MB/sec)

My hosting is quite fast I think; 4 cpu cores, 2gb RAM, 500GB SSD storage, set php limits high and so on.. maybe you have some tips for me to speed stock-pages up? Is there a way to index sql databases to make it faster? Or will it be better with next updates?

Edited by

H Tim,

Do you have any ideas on this?

My hostingprovider gave some tips to speed things up, but it didn't make much difference.
I've changed all sql tables to InnoDB structure (was MyISAM)
Added some indexes to tables.
I've used a lot of keywords..may that be the problem?

Thanks in advance!

Edited by

Hi Tim

Do you have an update on this topic? I'm planning a huge collection of stock photography, and I like the way you set up the stock sections. But I tested with 11.000 pictures, but the stock became unusable because of page loading times of 30-40 seconds, where all other sections on my website (including large galleries) are loading in milliseconds to 2 seconds or so.

Maybe the stock sections are not designed for such large collections. In my hosting control panel I see a very high database load when browsing that large stock collection, and I think it has to do with the thousands of keywords in the sql keyword table.

As an alternative I can set up a category with sub-categories and client/event galleries? I've tested a gallery with the same 11.000 photo's, and that's working fast, and it's searchable on keywords and captions.

What do you think? Should I invest some time in that last option, or are there updates or fixes planned for the stock sections?

Thanks!!

Edited by

Hi,

I have me too a lot of pictures online (I am a big equestrian photographer, I have actually 900.000) I have solved all my problem by add index.

The problem is there is some joint SQL query without index, the result is the query is slow and eat 100% mysql process. If you know a little mysql you can log query with no index and add index. I can explain you how do this If you want.

I have browse your website, very beautiful site. Personally I use Koken for website site and sytist for only shop, but koken have no more support so I will perhaps use only sytist. Do you have make a lot change on default them ?

Edited by

I've asked my hosting if I can change mysql / mariaDB cache settings myself. May be that helps too. Hosting has 2GB of memory, mysql query cache is set to 32M. Any suggestions for this? Is it possible to change those cache settings without editing de my.cnf file (can't access that)

Edited by

Query cache can be a solution but not a long time solution.

Personally my MariaDB is a Docker image and have 5Go of RAM with this configuration.
[mysqld]
max_connections = 50
#long_query_time = 10
slow_query_log = OFF
log_queries_not_using_indexes = OFF
slow_query_log_file = /var/lib/mysql/mariadb-slow.log

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 32M
query_cache_min_res_unit = 2048
table_open_cache = 3000
max_heap_table_size = 256M
tmp_table_size = 256M
key_buffer_size = 8M

max_allowed_packet = 64M

binlog_cache_size = 512K
read_buffer_size = 1M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 16M

innodb_buffer_pool_size = 2G
innodb_thread_concurrency = 8
innodb_log_file_size = 256M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_buffer_pool_instances = 2

performance_schema=ON

# Skip reverse DNS lookup of clients
skip-name-resolve

You can boost performance of mysql server but joint on no indexes will always be a problem. You will need to finally add index.

Edited by

You can find slow query and index problem like this.
Enable slow query log, if you can't do with my.cnf you can do it with SQL query.

SET GLOBAL slow_query_log_file = '/var/lib/mysql/mariadb-slow.log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_queries_not_using_indexes = 'ON';

Theses 3 commands will say where store slow query log, enable slow query and enable not using indexes (not using indexes is store in slow query log, no separate log). But you need to have access of path logs.

After some time you can see in your log SQL query who have problem. Generally index are missing on WHERE or ORDER BY clauses. It is perhaps more complexe with JOINT or AS if you don't know SQL.

For add indexes just use this query : ALTER TABLE `ms_product_subs` ADD INDEX(`sub_main_prod`);
In this sample I add index for sub_main_prod in the table ms_product_subs.

For me one index have solved a lot of speed problem.

Don't forget when you have finish your debug to disable slow query log if you don't need it anymore.

Edited Tue Mar 12, 19 8:06 AM by Yann Queniart

Wow, thanks Yann! That will help me!
My ms_photo_keywords_connect table is by far the largest table. Do you have any suggestion on which columns to index in that one?

In the meanwhile I will figure out the slow query log ;-)

Edited by

My 3 biggest tables are : ms_blog_photos, ms_photo_keywords_connect and ms_photos

Please find screenshots of index.

I have make other fine tuning on more little table like products table (you will find them with slow query log). In a normal situation all queries need to be make on indexes, I reality it is not always easy to have a perfect indexed table but the biggest tables and the most complicated queries need to be make on indexes.

Edited by