Working with PostgreSQL database is unusably slow

[schniepp]

JabRef version 4.31 on Ubuntu 16.04

• [X ] I have tested the latest development version from http://builds.jabref.org/master/ and the problem persists

Steps to reproduce the behavior:

1. Connect to PostgreSQL db with ≈3,400 entries.
2. See that it takes over 8 minutes the database has still not loaded completely.

This is on a very fast server. The Internet connection from here is only about 1 MB/s download. However, loading the same BibTeX file off a shared drive from the same server takes only a few seconds.
Saving my 3,400 entries to the remote database also took hours and hours, and JabRef consumed something like 8 GB of memory while doing this.

Would it be worth trying a MySQL db instead?

Log File

Paste an excerpt of your log file here

[bruot]

I also have long processing times with a smaller database of about 600 entries. About half an hour to paste 600 entries to the remote PostgreSQL DB when creating the tables. Then, it takes about 2 min to open it.

This occurs on both versions 4.3.1 and 3.8.2.

[abepolk]

I am interested in working on this issue. To recreate the problem, I could set up a test PostgreSQL database on Heroku (a PaaS I've used before), and fill it with BibTeX entries with a script. Then I could try to load the entries as described. Is there anything I should know before getting started?

[tobiasdiez]

We have some database related tests, they might be a good starting point https://github.com/JabRef/jabref/tree/master/src/test/java/org/jabref/logic/shared

However, I would wait until #4520 is merged. This PR already improves the DB performance and maybe this fixes this issue as well.

[abepolk]

Any other issues I can work on? I wanted to take a break from JavaFX, but if there's not a strong need, I can work on the importer dialog. Feel free to move this conversation to Gitter.

[bruot]

I think there may be two issues related to the DB requests:

• Single requests can be quite slow and make the user wait. Solution: speed can be improved, or it could be an idea to let the user continue using Jabref before requests end.
• Some operations seem to generate many DB requests: They could be grouped to be sent as a single request.

Regarding the second point, these operations seem to be concerned (from what I have tested):

• Opening the remote DB.
• Pasting or deleting multiple entries at once.
• Pasting a BibTex source that contains several fields.

[stefan-kolb]

Unfortunately this issue is known. The synch is done way too many times I think. There should be one or more duplicate issues for this already.

[schniepp]

I played around with python (using the psycopg2 library) to access my JabRef (PostGreSQL) database remotely. I have ≈4,000 entries, but I was surprised how blazingly fast this went via python. Getting all keys, or all filenames of attached PDFs, or all group information for all entries — it all happened within very few seconds or even fractions of a second. This compares to minutes that many JabRef operations take. This definitely gives me an idea that there is a lot of room for improvement.

My requests pulled many entries from the DB at once, rather than doing many individual reads.

Not sure if it's of any help here, and I really don't have much experience with databases at all. But I thought I'd mention it.

[AliZDev-v0]

@schniepp Is it problem still exist?

[stefan-kolb]

Issue should still persist.

[AliZDev-v0]

@NorwayMaple Do you want to work on it?

[abepolk]

@Ali96kz I'm looking into this. How do I find the keystore I need? Do you recommend I use something along the lines of keytool -genkey?

[tobiasdiez]

@schniepp @stefan-kolb are you sure the issue still exists in the latest development version? If I'm not mistaken @Ali96kz recently implemented some huge improvements to the performance.

It would be nice if you could try out the latest development version before @NorwayMaple invests time in trying to fix it. Thanks!

[bruot]

For me, tests on the latest Windows built with a remote PostgreSQL server show the problem described in the issue is solved, but there are still many actions that remain slow:

• Opening a DB: now fast (a few seconds for a few hundreds of entries)
• Editing a field: still slow as I type the first character (~5 s), a bit faster for the second character, then it gets faster and good as I continue typing.
• Pasting, in the BibTeX tab, an entry containing several fields: still very slow (1-2 min).
• Switching tabs: takes a few seconds.