To post a new support question, click the Post New Topic button below.
Current Version: 4.8.5 | Sytist Manual | Common Issues | Feature Requests
Please log in or Create an account to post or reply to topics.
You will still receive notifications of replies to topics you are part of even if you do not subscribe to new topic emails.
Slow Query And Big Installation
Y
Yann Queniart
140 posts
Mon Feb 19, 18 2:56 AM CST
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;
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.
Y
Yann Queniart
140 posts
Mon Feb 19, 18 3:25 AM CST
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.
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
16,145 posts
(admin)
Mon Feb 19, 18 7:36 AM CST
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.
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
My Email Address: info@picturespro.com
Y
Yann Queniart
140 posts
Mon Feb 19, 18 7:55 AM CST
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.
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
16,145 posts
(admin)
Mon Feb 19, 18 9:04 AM CST
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.
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
My Email Address: info@picturespro.com
Arron Robles
188 posts
Mon Feb 19, 18 9:07 AM CST
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
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 | APVisions.com | Robles365.com
Y
Yann Queniart
140 posts
Mon Feb 19, 18 9:36 AM CST
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.
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.
Y
Yann Queniart
140 posts
Mon Feb 19, 18 12:03 PM CST
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.
The system work, I see all the slow query been processed.
Y
Yann Queniart
140 posts
Wed Feb 21, 18 8:30 AM CST
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
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
Please log in or Create an account to post or reply to topics.
Loading more pages