Search of large finder tables times out Topic is solved

Discussion regarding Joomla! 3.x Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
ggossamer
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 117
Joined: Wed Sep 29, 2010 4:50 pm

Search of large finder tables times out

Post by ggossamer » Wed Dec 25, 2019 4:33 pm

Hi,
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 index
It seems to indicate the query time of this specific query only took 1.42 seconds, but the real-time was much longer than that - it was more than a minute. Increasing the gateway timeout is not a solution.

If 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?

User avatar
Nidzo2203
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 195
Joined: Sat Nov 21, 2009 4:52 pm
Contact:

Re: Search of large finder tables times out

Post by Nidzo2203 » Thu Dec 26, 2019 12:57 pm

The finder tables are used by Smart Search which is not recommended for large sites like yours (more than 60.000 articles).
I would disable Smart search and empty finder tables.

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 11755
Joined: Thu Feb 15, 2007 5:48 am
Location: Suzhou, China

Re: Search of large finder tables times out

Post by toivo » Thu Dec 26, 2019 1:20 pm

One solution worth investigating in your particular case is Apache Solr, running on the Apache Tomcat server, and developing a Joomla search plugin using the Solarium PHP Solr Client, available from http://www.solarium-project.org/.
Toivo Talikka, Global Moderator

ggossamer
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 117
Joined: Wed Sep 29, 2010 4:50 pm

Re: Search of large finder tables times out

Post by ggossamer » Thu Dec 26, 2019 4:36 pm

Nidzo2203 wrote:
Thu Dec 26, 2019 12:57 pm
The finder tables are used by Smart Search which is not recommended for large sites like yours (more than 60.000 articles).
I would disable Smart search and empty finder tables.
Great solution, and the preferred one for me, thanks.


Post Reply

Return to “Performance - Joomla! 3.x”