My website has a lot of nodes ~1 milion. Our hosting service is warning us because the site crashes regularely because of bad SQL queries. They sent us the SQL slow log repport.
# Query 1: 0.00 QPS, 0.01x concurrency, ID 0x3F0DF81FB404BF18873F31CFC39886A9 at byte 1954904002
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.63
# Time range: 2024-08-08 04:50:36 to 14:05:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 51 35
# Exec time 45 342s 6s 19s 10s 19s 4s 8s
# Lock time 99 25s 388ms 1s 714ms 945ms 184ms 740ms
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Rows affecte 3 35 1 1 1 1 0 1
# Bytes sent 0 385 11 11 11 11 0 11
# Query size 99 2.07G 33.28M 87.03M 60.59M 83.55M 15.64M 59.38M
# String:
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ #################################
INSERT INTO "key_value" ("name", "collection", "value") VALUES ('queue_stashed_results', 'simple_sitemap', 'a:4:{s:7:\"variant\";s:7:\"default\";s:7:\"results\";a:0:{}s:17:\"processed_results\";a:1:{i:2000;a:8:{s:7:\"lastmod\";s:25:\"2024-08-07T06:26:02+02:00\";s:8:\"priority\";s:3:\"0.5\";s:10:\"changefreq\";s:7:\"monthly\";s:6:\"images\";a:0:{}s:4:\"meta\";a:2:{s:4:\"path\";s:14:\"product/996876\";s:11:\"entity_info\";a:2:{s:11:\"entity_type\";s:16:\"commerce_product\";s:2:\..........
As we can see, the stash query can become very large, is there any way we can optimize this behavior ?
Active
4.2
Code
@damienmo The following may help you:
If that doesn't help, try to find out what the largest data is.