Hi,
I’m using OJS 3.1.1.2.
Site level search is taking too long.
If I search inside a journal it’s works instantly.
Can I do something do improve the perfomance?
Regards,
Tarcisio Pereira.
delete some journals! (kidding 
)
Can you give some numbers: how many journals, how many articles, how large are the search related tables? Using mysql?
I am asking because with 70 journals I am not seeing any problems.
Hi @ajnyga
Deleting some journals would make my life much easier… 
I can’t give some numbers using mysql because it’s a postgresql database.
But, some numbers:
revistas=> select enabled, count(*) from journals group by enabled;
enabled | count
---------±------
0 | 44
1 | 178
(2 rows)
revistas=> select count(*) from published_articles;
count
94855
(1 row)
revistas=> select count(*) from issues;
count
7475
(1 row)
revistas=> SELECT
relname as “Table”,
pg_size_pretty(pg_total_relation_size(relid)) As “Size”,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as “External Size”
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC limit 33;
Table | Size | External Size
--------------------------------±-------±--------------
article_search_object_keywords | 31 GB | 16 GB
metrics | 18 GB | 11 GB
referrals | 744 MB | 94 MB
article_search_keyword_list | 511 MB | 280 MB
article_settings | 458 MB | 138 MB
notifications | 434 MB | 260 MB
sessions | 290 MB | 167 MB
email_log | 229 MB | 54 MB
comments | 218 MB | 201 MB
signoffs | 172 MB | 99 MB
rt_searches | 163 MB | 17 MB
article_search_objects | 154 MB | 61 MB
author_settings | 123 MB | 48 MB
event_log_settings | 85 MB | 38 MB
event_log | 72 MB | 24 MB
article_files | 63 MB | 18 MB
citations | 57 MB | 20 MB
user_settings | 57 MB | 23 MB
articles | 52 MB | 29 MB
authors | 49 MB | 15 MB
article_comments | 44 MB | 20 MB
usage_stats_temporary_records | 37 MB | 48 kB
users | 31 MB | 12 MB
roles | 30 MB | 22 MB
review_form_responses | 26 MB | 9200 kB
article_supp_file_settings | 20 MB | 14 MB
journal_settings | 16 MB | 7272 kB
published_articles | 16 MB | 7720 kB
rt_contexts | 15 MB | 3072 kB
email_log_users | 15 MB | 6200 kB
article_galleys | 15 MB | 5720 kB
article_galley_settings | 12 MB | 7512 kB
custom_section_orders | 11 MB | 4224 kB
(33 rows)
Regards,
Tarcisio Pereira
that is one huge OJS installation. Probably the largest I have seen.
It could be that you are just hitting postgresql performance issues with that 16GB database table.
I have zero experience with postgresql so I will tag @asmecher here so that this does not go unnoticed.
But when trying to debug this, I would try to isolate the sql that the system does in a site level search and the do a explain query with that to see what takes so long. Maybe there is something that could be done with the indexing.
1 Like
At least for mysql the query that does the actual is:
SELECT o.submission_id, MAX(s.context_id) AS journal_id, MAX(i.date_published) AS i_pub, MAX(ps.date_published) AS s_pub, COUNT(*) AS count FROM submissions s, published_submissions ps, issues i, submission_search_objects o NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0 WHERE s.submission_id = o.submission_id AND s.status = 3 AND ps.submission_id = s.submission_id AND i.issue_id = ps.issue_id AND i.published = 1 AND k0.keyword_text = ‘SEARCHSTRINGHERE’ GROUP BY o.submission_id ORDER BY count DESC LIMIT 500;
Running that with EXPLAIN gives this:
id 1
select_type SIMPLE
table NULL
type NULL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows NULL
Extras Impossible WHERE noticed after reading const tables
Not sure if that explains the slow query, but it does not look good? Usually you would see the types of indexes being used etc.
Hi,
php tools/rebuildSearchIndex.php
After this, search working again, slowly but working.
Regards,
Tarcisio Pereira