If you have a support question or comment, click the Post New Topic link below. Sytist Manual | Sytist Articles | Facebook Page.

Slow Query And Big Installation

 
Please log in or Create an account to post or reply to topics.
 
Yann Queniart
79 posts
Mon Feb 19, 18
2:56 AM
Hi, I have juste move from Amazon EC2 et RDS to self host and as when sytist was on Amazon some part of the website is slow.

First, Sytist is host by my NAS and the capacity is normally not a limitation.
His configuration :
Synology DS918+, 4 core 1,5Ghz and boost to 2,3Ghz
16Go of RAM, 4 Hard drive in RAID and 2 SSD NVMe in RAID 1 as cache (theses SSD can send 3,3Gbs of data)

I access from LAN by my loopback so the internet access is not a limitation.

Some Webpage are slow and other not. OK I have a very big installation, I work in team and produce a lot of pictures. Actually 662000 pictures online and more than 100 events.

After some analyze I am sure the problem come from the DB and some query how are slow. I have activated the slow query fonction of mariadb and retrieve log.

Some query take more than 5s (for the moment I check only slow query over 5s).
The slow query is always the same : SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1047' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1; (I have and space between < and =, if not, I can't post correctly)

Only bp_blog_preview change but always the same query.

Anything to do to have better performances ?

Thank you for your help and continu to do the best pictures selling software ;)

Please find below the log.

# Time: 180219 9:36:05
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4644 Schema: prod_sytist QC_hit: No
# Query_time: 5.494452 Lock_time: 0.000130 Rows_sent: 0 Rows_examined: 661959
use prod_sytist;
SET timestamp=1519029365;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1047' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:39:05
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4737 Schema: prod_sytist QC_hit: No
# Query_time: 6.426412 Lock_time: 0.000120 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029545;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1487' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:39:11
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4737 Schema: prod_sytist QC_hit: No
# Query_time: 6.235764 Lock_time: 0.000112 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029551;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1493' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:39:18
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4737 Schema: prod_sytist QC_hit: No
# Query_time: 6.293884 Lock_time: 0.000130 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029558;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1451' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:39:24
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4737 Schema: prod_sytist QC_hit: No
# Query_time: 6.291524 Lock_time: 0.000114 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029564;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1583' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:41:25
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4740 Schema: prod_sytist QC_hit: No
# Query_time: 6.385294 Lock_time: 0.000109 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029685;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1582' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:41:31
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4740 Schema: prod_sytist QC_hit: No
# Query_time: 6.233697 Lock_time: 0.000107 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029691;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1529' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:41:37
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4740 Schema: prod_sytist QC_hit: No
# Query_time: 6.217049 Lock_time: 0.000095 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029697;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1479' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:41:43
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4740 Schema: prod_sytist QC_hit: No
# Query_time: 6.237670 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029703;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1328' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
# Time: 180219 9:41:50
# User@Host: SASytist[SASytist] @ [10.0.1.243]
# Thread_id: 4740 Schema: prod_sytist QC_hit: No
# Query_time: 6.295632 Lock_time: 0.000107 Rows_sent: 0 Rows_examined: 661959
SET timestamp=1519029710;
SELECT * FROM ms_blog_photos LEFT JOIN ms_photos ON ms_blog_photos.bp_pic=ms_photos.pic_id WHERE bp_blog_preview='1302' AND bp_sub_preview< ='0' ORDER BY bp_order ASC LIMIT 1;
Edited Mon Feb 19, 18 3:16 AM by Yann Queniart
This reply was deleted.
This reply was deleted.
Yann Queniart
79 posts
Mon Feb 19, 18
3:25 AM
I have quickly check the database structure. The query send me not a lot of results. This query is executed when browse list of events or sub gallery of an event, but it use ms_blog_photos and ms_photos the 2 biggest table (an other big table is ms_photo_keywords_connect but not use).

I am not an SQL expert but perhaps it is possible to have a little table table how store only events and sub gallery of events how will be more little and easy to read.
Tim - PicturesPro.com
9863 posts
admin
Mon Feb 19, 18
7:36 AM
If you are using Sytist version 2.3.0 or higher, click Settings in the main menu. Then underneath the left menu, click “Set Gallery Previews”. This process helps speed things up if you have a lot of sub galleries. This will probably help.

Other things to try:

Go to Stats -> Shopping Carts. Delete older abandoned shopping carts.

Delete old galleries that are not needed.

Go to Settings -> Backup Database and do the optimize database.

Click Stats in the main menu. This will compile old stats and reduce the rows in the stats table.
 
 
 
 
 
Tim Grissett, DIA - PicturesPro.com || My Email Address: info@picturespro.com
Yann Queniart
79 posts
Mon Feb 19, 18
7:55 AM
Hello,

I have the last version 2.7.2.

Shopping Carts cleaned, database optimized (but I think that don't have effect on InnoDB database), stats ok and compiled.

For the old gallery it is more difficult, I photography sports events and I keep on long time pictures (my customers buy some time 2 years old pictures).

I have make tuning on MariaDB configuration for use cache, it is better but the first time SQL request is use is long.
Tim - PicturesPro.com
9863 posts
admin
Mon Feb 19, 18
9:04 AM
Did you do this?

click Settings in the main menu. Then underneath the left menu, click “Set Gallery Previews”. This process helps speed things up if you have a lot of sub galleries.

That is the main thing to do and this is the reason why I added that option.
 
 
 
 
 
Tim Grissett, DIA - PicturesPro.com || My Email Address: info@picturespro.com
Arron Robles
129 posts
Mon Feb 19, 18
9:07 AM
I know this was deleted, however that synology server RAM needs to be updated to 2GB. I know because I have the same server.

Also, if you are writing syntax that long you need TEMP tables with the amount of data you are pulling base on the the RAM you have allocated.

Hope this was helpful.

Cheers,
Arron
 
 
 
 
 
Arron Robles, Solutions Designer | Reports Developer |A.P.Visions.com - arron@apvisions.com | PicturesPro Affiliate | Sytist V.2.9.2 | PhotoCart V.7.1.0
Yann Queniart
79 posts
Mon Feb 19, 18
9:36 AM
Thank you Tim, I will try this.

Arron, I think it is not the same model. My Synology is sell with 4Gb and I have upgrade it to 16Gb. Or perhaps you speak about a software limitation ?

Actually my Sytist DB take 450Mb, I have enough memory to put all the DB in memory, I need certainly to tune MariaDB configuration more aggressively. I have for the moment make little tuning.
Yann Queniart
79 posts
Mon Feb 19, 18
12:03 PM
Hi, I have use the Set Gallery Previews function and for the moment the web server is unavailable since 3h.

The system work, I see all the slow query been processed.
Yann Queniart
79 posts
Wed Feb 21, 18
8:30 AM
Hello,

I have make some tuning of MariaDB because default configuration is not very good. Hope this can help other Stylist users.

With this MariaDB tuning Sytist is very very very fast ;)

Warning : this configuration is for big installation, with the maximum of connection on the database (for me 150 connections) the memory need can grow up to 5Gb. So adjust values.

The most import to boost performance :
- query_cache_type : to use SQL query cache
- sort_buffer_size : sytist use a lot of ORDER BY, so this boost theses queries
- join_buffer_size : sytist use a lot of JOIN, so this boost theses queries

My configuration :

[mysqld]
slow_query_log = 1
long_query_time = 3
thread_cache_size = 16

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
table_open_cache = 3000
thread_cache_size = 4
max_heap_table_size = 192M
tmp_table_size = 192M
key_buffer_size = 16M

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

innodb_additional_mem_pool_size = 8M
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 = 8

performance_schema=ON

# Skip reverse DNS lookup of clients
skip-name-resolve
Loading more pages
Loading more pages

Sign up for email promotions.

Your information is safe with us and won't be shared.

Thank you for signing up!

 
©2003 - 2017 Grissett, LLC. All Rights Reserved.

By continuing to browse or by clicking Accept Cookies, you agree to the storing of cookies on your device necessary to provide you with the services available through our website.

    Accept   Privacy & Cookie Policy
Loading More Photos
Scroll To Top
Close Window
Loading
Close