Bug 4737 - Role data slow to load for users with several roles in a journal with many active submissions
Role data slow to load for users with several roles in a journal with many ac...
Status: RESOLVED FIXED
Product: OJS
Classification: Unclassified
Component: User Interface
2.3.3
All All
: P5 normal
Assigned To: Alec Smecher
: 5114 (view as bug list)
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2009-09-09 12:10 PDT by Jen Whitney
Modified: 2010-11-12 05:31 PST (History)
10 users (show)

See Also:
Version Reported In: 2.1.2
Also Affects:


Attachments
MySQL query log (61.89 KB, text/plain)
2009-09-25 08:50 PDT, Jen Whitney
Details
Patch against OJS 2.3.1 (7.60 KB, patch)
2010-02-01 14:55 PST, Alec Smecher
Details | Diff
Patch against OJS 2.3.1 (19.74 KB, patch)
2010-02-01 16:13 PST, Alec Smecher
Details | Diff
Patch against OJS 2.3.1 (8.01 KB, patch)
2010-02-03 10:46 PST, Alec Smecher
Details | Diff
Patch against OCS 2.3 (23.62 KB, patch)
2010-02-03 14:57 PST, Alec Smecher
Details | Diff
Patch against OJS 2.3.1 (557 bytes, patch)
2010-02-16 11:29 PST, Alec Smecher
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Jen Whitney 2009-09-09 12:10:33 PDT
Some of our OJS users have several roles with a number of journals -- when they try to access their site-level user home page, it loads very slowly, particularly when each journal has a large number of active submissions (as is the case for our development instance).

Underlying queries to fetch data for a role (e.g., in EditorSubmissionDAO::getUnfilteredEditorSubmissions) can take a full minute or longer to run when executed directly against the database -- multiplied by several roles for each of several journals means a long load time.
Comment 1 Jen Whitney 2009-09-09 12:25:52 PDT
Sorry, not just the site-level user home -- anywhere UserHandler::getRoleDataForJournals is chugging through many roles with many active submissions (site-level merely the worst case).
Comment 2 Michael Nason 2009-09-10 06:37:02 PDT
I'm going to send Alec another note about this specifically, but we're getting this error due to a large number of articles queued as "Unassigned".  We have these unassigned issues because our batch uploading process at UNB often requires a re-upload if information is incorrect (as it is more expediant than re-upping each piece of the issue manually).  Since there's no overwrite or permanent delete function for these aborted issues, and since all the articles for these deleted issues are recycled back into the "unassigned" category.  

While I understand that this is specifically an issue for UNB, I think it's entirely possible that a popular journal accepting large numbers of submissions could still experience slow-down on par with what we're seeing.  

I just thought I'd clarify why we're having this problem so it could be approached properly.
Comment 3 Michael Nason 2009-09-22 08:30:04 PDT
Update:

Even after the removal of "cruft" unassigned articles and a more accurate production level database, each press of "user home" at the system administrator level is accompanied with a 13 to 14 second wait for load. I don't think the content we're hosting is unreasonable.  This is a rough estimate for about 12-13 journals with only 3-4 utilizing the review process (roughly 80 articles in total).  

Since "User Home" now maps site-wide that means navigating through these journals, be it for demonstration of OJS to editors for prototypes, or regular daily system administrator duties, now takes much longer than it used to.
Comment 4 Alec Smecher 2009-09-22 13:12:11 PDT
Thanks, Mike -- I've been doing some optimizations that might've affected this page (bug #4772). Could you check if it's still behaving so slowly? If so, could you configure a MySQL slow query log with a threshold of 1 second to determine which queries are taking so long to execute?
Comment 5 Jen Whitney 2009-09-25 08:50:02 PDT
Created attachment 2479 [details]
MySQL query log

Here's a brief query log captured while Mike was loading site- and journal-level user home pages.  Mike, can you comment?
Comment 6 Michael Nason 2009-09-25 09:12:32 PDT
I certainly would...

So with User Home after the patch there's still a 12-13 second wait on site-wide user home display.  Individual journal user homes vary from super quick to 2-4 seconds depending on how populated their editorial queue is.

That said, Archives loading (not for ToCs and general issues but within editorial) is taking something like 4-5 minutes.

Examples: 

- Journal with ~1400 items in archives (and a clean editorial process), requesting 25 for display (4-5 minutes).
- Journal with ~900 items in arhives (and a handful of items in editorial process), requesting 25 for display (4 minutes).
- Journal with 10 items in archives (a few seconds)

That said, 800-1200 items will definitely be pretty standard for journals with published backissues.
Comment 7 jerico 2009-10-08 10:53:55 PDT
I had similar performance problems when I installed OJS 2.3 on my windows machine for the first time (7sec load time). I did some profiling and found out that the "culprit" was file access. In other words in my case the database was not the problem.

Bottlenecks were:
- include statements (file access + parsing)
- config access
- locale access
- smarty template cache access

I got about 25% improvement by replacing PHP 5.2.11 with PHP 5.3. I achieved about another 25% enabling eAccelerator on Windows. But this was still not satisfying. As I know that NTFS is a very slow filesystem (especially with encryption installed on my dev machine) I tested OJS 2.3 on Linux and now the pages load in less than 1 sec. So that was it.
Comment 8 Alec Smecher 2009-10-08 11:21:25 PDT
The big changes that'll result in more file loads for OJS 2.3 (compared to previous versions are:
- Locale files split; each page load will typically load several small locale file caches rather than one enormous one. Should result in both speed and memory usage for the large majority of requests (i.e. non-authenticated users)
- Template files split (and we'll proceed in this direction further in subsequent releases)

I typically code assuming that filesystem performance will be high compared to any other mechanism, though I wasn't aware that NTFS was so much slower. It's not our most typical deployment environment, so I don't mind if it takes a bit of a performance hit compared to other filesystems -- but it might be worth some side-by-side testing to see if we can improve things all around.
Comment 9 Alec Smecher 2009-10-08 11:22:39 PDT
Incidentally, this original entry got lost in the noise as it wasn't scheduled against OJS 2.3. We'll probably need to work on this pretty soon. Scheduling against 2.3.1.
Comment 10 Alec Smecher 2009-12-12 21:11:23 PST
Deferring to 2.3.2 -- but if this is a problem for someone now, I can work on a patch shortly.
Comment 11 Jason Nugent 2009-12-15 05:44:12 PST
Mike asked me to comment on this, mainly to address the possibility that our file system may be the culprit, if it is slow.  Our dev server uses 15K RPM Ultra320 SCSI drives for local disk access, and our main NAS is attached via fibre channel, and is a Sun StorEdge disk array composed of the same drive type.  We use journaled EXT3 filesystems, but I doubt that this is the cause of the problem.  We have sites that serve more dynamic content in a higher request environment without issue.
Comment 12 jerico 2009-12-18 05:16:08 PST
Hi Jason, the file system problems I reported were due to (encrypted) NTFS. On ext3 the performance problems disappeared. This doesn't mean that you cannot have another FS problem but I also doubt that this is the reason in your case.
Comment 13 James MacGregor 2010-01-30 15:11:44 PST
One possible workaround for this (and possibly Bug 5114, which may be a dupe -- Colin is investigating). What if we add a check to see if the user is a Site Admin; and if so, only load the role links and not the extended dashboard information (# of assignments in queues, etc.)? If I'm understanding correctly, the slow load time is due to the sheer number of queries happening when a Site Admin, who is enrolled in many journals with many roles, logs in. Site Admins are likely the only users who would be enrolled in, say >5 journals in an install; and likely don't need to see how many unassigned articles they have as Editor of Journal x. Thoughts?

Mike, Jen or Colin -- could you verify whether you see slow load times when logging in as another user, who is for example only enrolled in one or two journals?
Comment 14 jerico 2010-01-31 02:46:17 PST
Another idea: Is it possible to implement a "mass loader" interface in the DAO for those cases in which a list of articles needs to be loaded? It would probably be faster to query the whole list of articles with its dependencies at once rather than loading every single article and then loading it's dependencies. Well, probably you've already thought about that...
Comment 15 Jen Whitney 2010-02-01 06:41:44 PST
That would be an acceptable workaround for site admins, but it won't address the slow load times experienced by journal editors who try to view journal archives.

We have a number of journals affected by this: trying to view the archives of journals with back runs of 1500+ articles takes 4-5 min.
Comment 16 Colin Prince 2010-02-01 08:10:14 PST
*** Bug 5114 has been marked as a duplicate of this bug. ***
Comment 17 Colin Prince 2010-02-01 11:04:11 PST
(In reply to comment #13)

Yes, another user here who is manager and editor for 4 journals logged into my test site and it took 89 seconds to log in. She is not an site admin.
Comment 18 Alec Smecher 2010-02-01 11:10:41 PST
Colin, I'll take a look at this in the afternoon and hopefully have a pretty quick solution. The bottleneck is fetching and looping through articles in PHP rather than doing it in the DB.
Comment 19 Alec Smecher 2010-02-01 14:55:45 PST
Created attachment 2972 [details]
Patch against OJS 2.3.1

Optimized count getter for section editor and editor roles. Colin, can you test this a) to make sure that the counts don't change when you apply this and b) that the performance is indeed improved?

If both check out, I'll roll out this kind of improvement to a few other places (other roles, also the list fetches themselves and not just the counts). Should result in a very considerable performance increase.
Comment 20 Alec Smecher 2010-02-01 16:13:05 PST
Created attachment 2973 [details]
Patch against OJS 2.3.1

Attachment #2972 [details] will optimize the submission counts (e.g. the number of "In Review" or "In Editing" etc. in the User Home and on the sidebar) for Editors and Section Editors.

Attachment #2973 [details] will optimize the lists themselves, i.e. the Section Editor's "In Review" list.
Comment 21 Alec Smecher 2010-02-02 13:53:08 PST
Process (documented for porting):

SQL magic to get a single max-ish row using left joins:

LEFT JOIN edit_assignments ea ON (a.article_id = ea.article_id)
LEFT JOIN edit_assignments ea2 ON (a.article_id = ea2.article_id AND ea.edit_id < ea2.edit_id)
LEFT JOIN edit_decisions edec ON (a.article_id = edec.article_id)
LEFT JOIN edit_decisions edec2 ON (a.article_id = edec2.article_id AND edec.date_decided < edec2.date_decided)
WHERE edec2.date_decided IS NULL AND ea2.edit_id IS NULL

- Rewrite get...SubmissionsCount to use discrete SQL queries rather than calling the getUnfiltered...
- Change getUnfiltered... to _getUnfiltered... (should be marked internal only)
- Change $status param to _getUnfiltered... to be an optional additional string for "WHERE" clause statements
- Change callers to _getUnfiltered... as follows:
   - Return by reference
   - Add WHERE clause stuff for joins at beginning of this comment to new WHERE clause string
   - Add $rangeInfo to calls to _getUnfiltered...
   - Change returners to use DAOResultFactory instead of VirtualArrayIterator
Comment 22 Colin Prince 2010-02-02 14:00:38 PST
Sorry away till just now

I downloaded version 1.78 from CVS since I can't use the patch on 2.3.1.2.

It's fantastic, superfast! Less than one second. Great work.
Comment 23 Alec Smecher 2010-02-02 14:04:55 PST
Thanks, Colin; can you verify that the counts don't change?
Comment 24 Colin Prince 2010-02-02 14:19:18 PST
Sorry they don't match. I sent you the screenshots by email since there's journal private info.
Comment 25 Alec Smecher 2010-02-03 10:46:19 PST
Created attachment 2977 [details]
Patch against OJS 2.3.1

Correct some discrepancies in list behavior due to the above patches
Comment 26 Alec Smecher 2010-02-03 14:57:49 PST
Created attachment 2978 [details]
Patch against OCS 2.3
Comment 27 Jeff Liske 2010-02-10 16:45:11 PST
Hello,

I have a journal who has 3000+ articles in the editor archives, and clicking the editor archives link takes a long time to load, then I get an error.

Will patches 2972, 2973, and 2977 work with OJS version 2.3.1-2?  Or will we have to wait until 2.3.2 is released?

Thanks,

Jeff
Comment 28 Alec Smecher 2010-02-10 18:31:45 PST
Jeff, yes, those patches should work against OJS 2.3.1. Apply them in sequence. If you have any trouble, back them out again in the reverse sequence, then let me know.
Comment 29 Jeff Liske 2010-02-12 13:12:37 PST
(In reply to comment #28)
> Jeff, yes, those patches should work against OJS 2.3.1. Apply them in sequence.
> If you have any trouble, back them out again in the reverse sequence, then let
> me know.

Hi Alec,

I attempted to apply the patch #2972 and it wasn't working.  In the file SectionEditorSubmissionDAO.inc.php, line 632 in version 2.3.1-2 is this:

for($i = 0; $i < 4; $i++) {

and line 645 and 646 didn't have an empty line between them, I think that's why the patch was failing.  However, I managed to modify the file to get the patch working and the other two patches #2973 and #2977 worked fine.  So after applying the patches, I go to click on editor archives and it displays an empty list with no articles, but we should have many in there.

Jeff
Comment 30 Alec Smecher 2010-02-12 13:57:53 PST
Jeff, are you sure there isn't a related error message in your PHP error log? Check there, and if you're still in doubt, try using the most recent versions of the modified files from CVSWeb (e.g. <http://pkp.sfu.ca/cvs/cvsweb.cgi/ojs2/classes/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php>).
Comment 31 Jason Nugent 2010-02-16 10:08:23 PST
(In reply to comment #30)
> Jeff, are you sure there isn't a related error message in your PHP error log?
> Check there, and if you're still in doubt, try using the most recent versions
> of the modified files from CVSWeb (e.g.
> <http://pkp.sfu.ca/cvs/cvsweb.cgi/ojs2/classes/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php>).

Alec, I don't know if this qualifies as a related error message, but we've just upgraded our dev instance from 2.3.0 to 2.3.1-2 (the latest, I believe), using the patch from the download site.  I've applied the three patches on this bug report in order and Mike is experiencing what I believe is missing content.  1400 items, down to 3.

This is showing in my error log when I access the Site Administrators "My Journals" page:

ojs2 has produced an error\n  Message: WARNING: Cannot use a scalar value as an array\n  In file: /etcdata/etcdev_www/ojs2.3-devel/lib/pkp/classes/core/DataObject.inc.php\n  At line: 76\n  Stacktrace: \n  Server info:\n   OS: Linux\n   PHP Version: 5.2.5\n   Apache Version: Apache/2.2.3 (Linux/SUSE)\n   DB Driver: mysql\n   DB server version: 5.0.26


Jason
Comment 32 Alec Smecher 2010-02-16 10:19:49 PST
Jason, this is probably unrelated. It's probably improperly localized data in a ..._settings table. See if you can find out what key is getting fetched in DataObject.inc.php and check to see if it's supposed to be localized, i.e. if its entry in the ..._settings table should have the "locale" column set.
Comment 33 Jason Nugent 2010-02-16 10:49:04 PST
Hey Alec,

Just to be thorough, I've pulled the specific files that the patches touch out of CVSWeb.  I believe the behaviour is the same, although perhaps Mike may comment.

And you're right -- those other errors were unrelated. There were several locale settings in the user_settings table for our administrator account that had an empty locale field.

Jason
Comment 34 Michael Nason 2010-02-16 11:14:08 PST
Before our update and patches, the "editorial archives" (which were painfully slow to load) contained quite a number of files.  For a few of our journals it would have been something like 1200 to 1400 articles.  

After the patches there is definitely a performance increase (I can certainly see it at the User Home level) but it's a lot more difficult to tell in the "editorial archives" because where I once saw the individual articles for the whole run of the journal, now I only see three or four items.  

This content hasn't been blown away, though.  I can still access all of our articles and their Review/Summary/History information by browsing "back issues" as an editor.  It's just that this content isn't being served up the way it was before in the "archives" section.
Comment 35 Alec Smecher 2010-02-16 11:25:48 PST
Mike, these patches weren't supposed to change the list behavior at all (besides making everything way faster), so it sounds like some join condition has changed.

I've just had a look at the current CVS versions and it looks like they aren't the most current -- we're migrating from CVS to git and perhaps there's some versioning oddness because of that. I'll check in and get back to you shortly.
Comment 36 Alec Smecher 2010-02-16 11:29:35 PST
Created attachment 3019 [details]
Patch against OJS 2.3.1

Mystery resolved -- try applying this most recent patch to restore the Archives list to its former glory.
Comment 37 Michael Nason 2010-02-16 12:52:04 PST
The archives certainly did return, but so did the 4+ minute wait to load extensive archives.

Performance still enhanced generally, but the editorial archives are still quite sluggish.
Comment 38 Alec Smecher 2010-02-16 13:09:16 PST
Mike, have you set your "items per page" so that the entire archive set appears in a single load? If not, it might be worth looking into your DB to see if you're missing an index or something; it really shouldn't be that heavy, even for large lists.
Comment 39 Michael Nason 2010-02-16 14:07:30 PST
It's only loading (or should only be loading) 25 items as per my "items per page" setting on Step 5 of the setup.
Comment 40 Jason Nugent 2010-02-16 14:41:05 PST
Tomorrow, I will turn on query logging and do some tests to ensure that our indexes were created correctly during the upgrade process.
Comment 41 Jason Nugent 2010-02-17 06:50:31 PST
Alrighty.  I have done quite a bit of profiling this morning and have an idea of what's going on.  The query is probably as optimized as it can be, but it does create a very large temporary table.

Enabling MySQL query caching with a reasonably large cache size of at least 64 or 128M improves the situation dramatically, once the page is loaded once.  The problem is that the cache naturally flushes if any of the tables involved in the query are modified.

If I get a hit from the cache, the Archives page loads instantly. 

I suppose the next thing I can try is using a tmpfs partition for temporary tables, and also increasing the size of HEAP tables to minimize writes out to disk. This may be moving beyond what other OJS instances are capable of doing, though -- especially those on purchased hosting solutions.
Comment 42 Alec Smecher 2010-02-17 08:45:39 PST
Jason, what platform are you hosting on? I suspect your MySQL version will affect performance quite a bit; we've also had reports that NTFS's relatively slow file access plays a role elsewhere.
Comment 43 Jason Nugent 2010-02-17 09:22:55 PST
Our machines are running SuSE Linux Enterprise Server 10, Service Pack 3.  The version of MySQL in this distro is currently 5.0.26.  Running on 2 64-bit Dual Core Opteron 280 processors, with 4 Gb of memory.
Comment 44 Alec Smecher 2010-02-17 09:42:19 PST
Jason, that sounds like a pretty similar configuration to what we're running... Did this page start performing poorly with the OJS 2.3 update, or was it slow before?
Comment 45 Jason Nugent 2010-02-17 09:44:27 PST
Alec -- we run our live OJS installation on the same server, with identical data. It's just a different virtualhost on the same hardware, running OJS 2.2.3.0.  There are no performance issues there.
Comment 46 Alec Smecher 2010-02-17 09:55:36 PST
The strange thing is that the Archives query between 2.2.x and 2.3.x has not changed much and should actually be faster (e.g. no longer using VirtualArrayIterator to fetch results). Can you confirm that it's the big query that is causing the slowdown e.g. by comparing the performance of that query directly in MySQL with with its equivalent from 2.2.x? There are several related query changes when fetching article objects (e.g. the lookups for signoffs now using the new signoffs structure) that might confuse things if you're testing from within PHP.
Comment 47 Jason Nugent 2010-02-17 10:03:27 PST
Yeah, if I hit the Archives page, I can open up a MySQL shell and have a look at the output of show processlist.  While I'm waiting for the page to come back, my mysqld process is pegged at 100% cpu usage, and the show processlist is listing the SELECT query for that page as a "copying to tmp table" for 40 or 50 seconds.  It's not four minutes+ like Mike suggested (sorry Mike!) but easily one or two.

I've also enabled query logging and can confirm that the query on that page is the culprit, because I can run it from the mysql shell and wait for it to come back.

Here's the query, in all of its glory:

SELECT DISTINCT
                                a.*,
                                scf.date_completed as copyedit_completed,
                                spr.date_completed as proofread_completed,
                                sle.date_completed as layout_completed,
                                COALESCE(atl.setting_value, atpl.setting_value) AS submission_title,
                                aap.last_name AS author_name,
                                COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
                                COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev
                        FROM
                                articles a
                                INNER JOIN article_authors aa ON (aa.article_id = a.article_id)
                                LEFT JOIN article_authors aap ON (aap.article_id = a.article_id AND aap.primary_contact = 1)
                                LEFT JOIN sections s ON (s.section_id = a.section_id)
                                LEFT JOIN edit_assignments e ON (e.article_id = a.article_id)
                                LEFT JOIN users ed ON (e.editor_id = ed.user_id)
                                LEFT JOIN signoffs scf ON (a.article_id = scf.assoc_id AND scf.assoc_type = 257 AND scf.symbolic = 'SIGNOFF_COPYEDITING_FINAL')
                                LEFT JOIN users ce ON (scf.user_id = ce.user_id)
                                LEFT JOIN signoffs spr ON (a.article_id = spr.assoc_id AND scf.assoc_type = 257 AND spr.symbolic = 'SIGNOFF_PROOFREADING_PROOFREADER')
                                LEFT JOIN users pe ON (pe.user_id = spr.user_id)
                                LEFT JOIN signoffs sle ON (a.article_id = sle.assoc_id AND scf.assoc_type = 257 AND sle.symbolic = 'SIGNOFF_LAYOUT')
                                LEFT JOIN users le ON (le.user_id = sle.user_id)
                                LEFT JOIN review_assignments r ON (r.article_id = a.article_id)
                                LEFT JOIN users re ON (re.user_id = r.reviewer_id AND cancelled = 0)
                                LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = 'title' AND stpl.locale = 'en_US')
                                LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = 'title' AND stl.locale = 'en_US')
                                LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = 'abbrev' AND sapl.locale = 'en_US')
                                LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = 'abbrev' AND sal.locale = 'en_US')
                                LEFT JOIN article_settings atpl ON (a.article_id = atpl.article_id AND atpl.setting_name = 'cleanTitle' AND atpl.locale = 'en_US')
                                LEFT JOIN article_settings atl ON (a.article_id = atl.article_id AND atl.setting_name = 'cleanTitle' AND atl.locale = 'en_US')
                                LEFT JOIN edit_assignments ea ON (a.article_id = ea.article_id)
                                LEFT JOIN edit_assignments ea2 ON (a.article_id = ea2.article_id AND ea.edit_id < ea2.edit_id)
                                LEFT JOIN edit_decisions edec ON (a.article_id = edec.article_id)
                                LEFT JOIN edit_decisions edec2 ON (a.article_id = edec2.article_id AND edec.edit_decision_id < edec2.edit_decision_id)
                        WHERE   edec2.edit_decision_id IS NULL
                                AND ea2.edit_id IS NULL
                                AND a.journal_id = '2'
                                AND a.submission_progress = 0 AND (a.status <> 1);

Running from a mysql shell gives me:

595 rows in set (48.24 sec)

Now, looking at that, I am wondering why I am getting so many records back. Mike, I thought you said that you had paging limited to 25, or at most 100 in some cases?
Comment 48 Alec Smecher 2010-02-17 10:05:49 PST
Jason, the SELECT DISTINCT is likely to blame. I suspect converting this over to a plain old SELECT would help a lot -- but this isn't something that changed between 2.2.x and 2.3.x, which is what I'm confused about.
Comment 49 Jason Nugent 2010-02-17 10:25:09 PST
Hey Alec -- removing DISTINCT didn't result in a noticeable improvement.   I may start picking the queries apart to see if there's one in particular.  

Here's a thought -- temporary tables aren't necessarily slow, unless you're copying a huge amount of data from a large field.  Are there fields that can be removed from that a.* glob to streamline it?
Comment 50 Alec Smecher 2010-02-17 10:31:54 PST
There are only two columns that might get big: comments_to_ed and citations. You could try using this instead of a.* to avoid fetching those two:

a.article_id, a.user_id, a.journal_id, a.section_id, a.language, '' AS comments_to_ed, '' AS citations, date_submitted, last_modified, date_status_modified, status, submission_progress, current_round, submission_file_id, revised_file_id, review_file_id, editor_file_id, pages, fast_tracked, hide_author, comments_status

A few of those columns are probably not used for the archives listing, but they are small (numeric or date). However, aside from the "citations" column getting added, nothing really changed here between releases.
Comment 51 Alec Smecher 2010-02-17 15:08:08 PST
Committed last fix for archives listing.
Comment 52 Jacob Sanford 2010-03-10 08:02:36 PST
Looks like an index solves this problem - the WHERE clause on TABLE signoffs needed a multi-column index to speed things along.

Fix:

CREATE INDEX assoc_id_assoc_type_symbolic ON signoffs (assoc_id,assoc_type,symbolic)

Cheers,

Jake
Comment 53 Alec Smecher 2010-03-10 08:15:15 PST
Hmm, there's already an entry for this index in the lib/pkp/xml/schema/signoff.xml descriptor:

                <index name="signoff_symbolic">
                        <col>symbolic</col>
                        <col>assoc_type</col>
                        <col>assoc_id</col>
                        <UNIQUE />
Comment 54 Jason Nugent 2010-03-10 08:42:10 PST
Alec,

Not quite -- the two indexes have different cardinalities because the column orders in the index declaration are different.   I believe that MySQL is using sub indexes more effectively in this case.

To confirm, I dropped your index and replaced it with Jake's version, on our test installation.  Page loads are now nearly instantaneous.

Jason
Comment 55 Jacob Sanford 2010-03-10 08:47:11 PST
Hi Alec, I do see that, however I believe that keeping the symbolic column as the last 'stop' in the key sequence is the most efficient method.
Comment 56 Alec Smecher 2010-03-10 08:53:30 PST
Huh, I humbly admit that I had no idea that the order of column specification affected index creation. I'll commit the new ordering.
Comment 57 bdgregg 2010-04-28 09:11:34 PDT
(In reply to comment #56)
> Huh, I humbly admit that I had no idea that the order of column specification
> affected index creation. I'll commit the new ordering.

Alec,
Since we are running into this same "slowness" issue in version 2.3.1-2 on a production system, can you provide a final patch or the steps to incorporate the changes that will be put in place to the database so that we're in line with the next update? We're literally waiting 2-3 minutes accessing the "User Home" page and the "Editor" page which is making it painfully slow in the updating.

Much appreciated,
Brian Gregg
University of Pittsburgh.
Comment 58 Jacob Sanford 2010-04-28 09:21:49 PDT
Hi Brian,

Comment #52 has a quick-fix MySQL syntax that should correct an 'in-use' legacy database that's experiencing these symptoms. 

Cheers,

Jake


(In reply to comment #57)
> (In reply to comment #56)
> > Huh, I humbly admit that I had no idea that the order of column specification
> > affected index creation. I'll commit the new ordering.
> 
> Alec,
> Since we are running into this same "slowness" issue in version 2.3.1-2 on a
> production system, can you provide a final patch or the steps to incorporate
> the changes that will be put in place to the database so that we're in line
> with the next update? We're literally waiting 2-3 minutes accessing the "User
> Home" page and the "Editor" page which is making it painfully slow in the
> updating.
> 
> Much appreciated,
> Brian Gregg
> University of Pittsburgh.
Comment 59 Alec Smecher 2010-04-28 09:23:19 PDT
Brian, just to add to Jake's comment -- try running the following statements. (Just creating an index as per comment #54 means you'll have a largely duplicated index.)

ALTER TABLE signoffs DROP INDEX signoff_symbolic;
CREATE INDEX assoc_id_assoc_type_symbolic ON signoffs (assoc_id,assoc_type,symbolic);

If you're still seeing poor performance, we can look at some of these patches.
Comment 60 bdgregg 2010-04-28 09:26:00 PDT
(In reply to comment #59)
> Brian, just to add to Jake's comment -- try running the following statements.
> (Just creating an index as per comment #54 means you'll have a largely
> duplicated index.)
> 
> ALTER TABLE signoffs DROP INDEX signoff_symbolic;
> CREATE INDEX assoc_id_assoc_type_symbolic ON signoffs
> (assoc_id,assoc_type,symbolic);
> 
> If you're still seeing poor performance, we can look at some of these patches.

Alec, Jake,
Thanks for the quick response.  I'll give this a try in about an hour or so.  I'll report back on what we find.

Thanks again.
Brian
Comment 61 Jason Nugent 2010-04-28 09:27:05 PDT
(In reply to comment #60)
> (In reply to comment #59)
> > Brian, just to add to Jake's comment -- try running the following statements.
> > (Just creating an index as per comment #54 means you'll have a largely
> > duplicated index.)
> > 
> > ALTER TABLE signoffs DROP INDEX signoff_symbolic;
> > CREATE INDEX assoc_id_assoc_type_symbolic ON signoffs
> > (assoc_id,assoc_type,symbolic);
> > 
> > If you're still seeing poor performance, we can look at some of these patches.
> 
> Alec, Jake,
> Thanks for the quick response.  I'll give this a try in about an hour or so. 
> I'll report back on what we find.
> 
> Thanks again.
> Brian

Just to add to the pile on :)

You should probably use CREATE UNIQUE INDEX instead of CREATE INDEX, since the index is well, unique.  This results in further optimizations by MySQL.
Comment 62 bdgregg 2010-04-28 09:46:22 PDT
(In reply to comment #61)
> (In reply to comment #60)
> > (In reply to comment #59)
> > > Brian, just to add to Jake's comment -- try running the following statements.
> > > (Just creating an index as per comment #54 means you'll have a largely
> > > duplicated index.)
> > > 
> > > ALTER TABLE signoffs DROP INDEX signoff_symbolic;
> > > CREATE INDEX assoc_id_assoc_type_symbolic ON signoffs
> > > (assoc_id,assoc_type,symbolic);
> > > 
> > > If you're still seeing poor performance, we can look at some of these patches.
> > 
> > Alec, Jake,
> > Thanks for the quick response.  I'll give this a try in about an hour or so. 
> > I'll report back on what we find.
> > 
> > Thanks again.
> > Brian
> 
> Just to add to the pile on :)
> 
> You should probably use CREATE UNIQUE INDEX instead of CREATE INDEX, since the
> index is well, unique.  This results in further optimizations by MySQL.

Jason,

I will perform the following then:

ALTER TABLE signoffs DROP INDEX signoff_symbolic;
CREATE UNIQUE INDEX assoc_id_assoc_type_symbolic ON signoffs
(assoc_id,assoc_type,symbolic);

I'll let you know the outcome.

Thanks,
Brian
Comment 63 bdgregg 2010-04-28 10:50:22 PDT
(In reply to comment #62)
> (In reply to comment #61)
> > (In reply to comment #60)
> > > (In reply to comment #59)
> > > > Brian, just to add to Jake's comment -- try running the following statements.
> > > > (Just creating an index as per comment #54 means you'll have a largely
> > > > duplicated index.)
> > > > 
> > > > ALTER TABLE signoffs DROP INDEX signoff_symbolic;
> > > > CREATE INDEX assoc_id_assoc_type_symbolic ON signoffs
> > > > (assoc_id,assoc_type,symbolic);
> > > > 
> > > > If you're still seeing poor performance, we can look at some of these patches.
> > > 
> > > Alec, Jake,
> > > Thanks for the quick response.  I'll give this a try in about an hour or so. 
> > > I'll report back on what we find.
> > > 
> > > Thanks again.
> > > Brian
> > 
> > Just to add to the pile on :)
> > 
> > You should probably use CREATE UNIQUE INDEX instead of CREATE INDEX, since the
> > index is well, unique.  This results in further optimizations by MySQL.
> 
> Jason,
> 
> I will perform the following then:
> 
> ALTER TABLE signoffs DROP INDEX signoff_symbolic;
> CREATE UNIQUE INDEX assoc_id_assoc_type_symbolic ON signoffs
> (assoc_id,assoc_type,symbolic);
> 
> I'll let you know the outcome.
> 
> Thanks,
> Brian

All - this is just amazing, we are now essentially down to almost an immediate response from the pages where we used to have 2-5 minute wait times.  Exactly what we needed.  Just the above database fix was applied, no other patches listed above were needed.  This definately solved our slowness issue.

Thank you!
Brian.
Comment 64 ushasharma84 2010-11-11 02:00:33 PST
hi
I am using OJS-2.3.1-2 and we are facing the same issue of slow performance in Editor's Role. Now when I am applying patch 2972 with dry-run it is giving an error Hunk #1 failed at 630. Dont know what is the reason? Please provide some details for this.
Comment 65 Jacob Sanford 2010-11-12 05:31:49 PST
(In reply to comment #64)
> hi
> I am using OJS-2.3.1-2 and we are facing the same issue of slow performance in
> Editor's Role. Now when I am applying patch 2972 with dry-run it is giving an
> error Hunk #1 failed at 630. Dont know what is the reason? Please provide some
> details for this.

Although most of the above patches tidy up some of the queries, the consensus now seems to be that you would see the most improvement by creating a new table index in 'signoffs'.

bdgregg has listed an SQL-CLI snippit in comment #62 that should be appropriate to run if you are experiencing the same problems as we were.