We have a joomla-3.9.14 site with at least ten ####_finder_links_terms tables, some of which are larger than 2GB each, and the main ####__finder_terms.ibd file larger than 7GB.
The site has more than 60,000 articles.
There are quite a few entries in the mariadb-slow.log log file similar to this:
Code: Select all
SET timestamp=1577289525;
SELECT l.link_id,l.start_date AS ordering
FROM `####_finder_links` AS l
INNER JOIN `####_finder_links_terms2` AS m ON m.link_id = l.link_id
WHERE l.access IN (1,1,2,3,6) AND l.state = 1 AND l.published = 1 AND (l.publish_start_date = '0000-00-00 00:00:00' OR l.publish_start_date <= '2019-12-25 15:55:00') AND (l.publish_end_date = '0000-00-00 00:00:00' OR l.publish_end_date >= '2019-12-25 15:55:00') AND m.term_id IN (282,648,462524,678538,5224053,8188900,1166,3279853,5980969,546,21891,3389343)
ORDER BY l.start_date DESC LIMIT 17000, 1000;
# Time: 191225 9:58:47
# User@Host: finder[finder] @ darwin-db.vpn.example.com [10.255.57.114]
# Thread_id: 3646471 Schema: lmv5 QC_hit: No
# Query_time: 1.420889 Lock_time: 0.000127 Rows_sent: 1000 Rows_examined: 2426093
# Rows_affected: 0 Bytes_sent: 31153
#
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
# explain: 1 SIMPLE l range PRIMARY,idx_published_list,idx_published_sale idx_published_list 16 NULL 37010 18827.00 100.00 100.00 Using index condition; Using where; Using filesort
# explain: 1 SIMPLE m ref PRIMARY,idx_term_weight,idx_link_term_weight PRIMARY 4 lmv5.l.link_id 40 124.14 100.00 0.77 Using where; Using indexIf we're searching 2.4M tables every time there's a query, of course it's going to be slow.
Is there anything that can be done to speed up the search and keep it from producing timeout errors? I don't believe hardware is the problem - the database is running on a server separate from the web server itself, connected on a gigabit local network, with E5-2620 and 128GB of memory and Micron_5200_MTFDDAK480TDC SSDs.
Are there performance recommendations available for joomla specifically? I've run and implemented the recommendations from MySQLTuner, including optimizing the finder tables.
Does anyone want to invest some time and help determine the bottleneck here?
