OC Upgrade: Retrieving Notes & Discrepancies. All free disk space used, Openclinica hangs.

We ’re in the process of upgrading OpenClinica 3.1.4 (Oracle ) to OpenClinica 3.9 (PostgreSQL)

To test the upgrade we used a dump of our Oracle production database.
After upgrade, tests were performed .
Following issue came up. If in the menu, the ‘Notes & Discrepancies’ menu item is clicked, OPenclinica hangs after a while. (The ‘test’ production database has approx 3000 subjects ,450 users, 9000 discrepancy notes.)

At the server where the postgres database is installed, our free disk space of 8G went down to 0G , just by retrieving the discrepancy notes: ( SQL statement found in the PostgreSQL server status window )
SELECT COUNT(DISTINCT (dn.*)) from discrepancy_note dn,study s , user_account ua , study_subject ss
where dn.study_id = s.study_id
and ( s.study_id =$1 or s.parent_study_id =$2)
and dn.parent_dn_id is null
In the .. ..\PostgreSQL\8.4\data\base\pgsql_tmp directory, temporary files are created. They consumes all free space.

if SELECT COUNT(DISTINCT (dn.*))… is replaced by SELECT * …

Free space stays 8G but we get an ‘out of memory’ error.

One observation: Are the tables user_account and study_subject necessary in this query?


I reproduced the problem with a clean install of OpenClinica 3.9 with 5000 subjects, 7 users, 25 discrepancies. This setup consumes 150MB space.

Has this something to do with the query or is the PostgreSQL database not setup properly?

Comments

  • aborisenkoaborisenko Posts: 5
    Hi there,

    Let's split your question into two parts: the query itself and PostgreSQL setup.

    The query probably collects data on each note by user & subject, which is why we see the extra tables being queried. However, if your database is properly indexed, you shouldn't see performance issues. A great way to help the development staff and dig deeper into the query would be to do the following:
    1. Run Explain Analyze on your query (this is done by opening a SQL window in pgAdmin or another client and typing EXPLAIN ANALYZE [your query here])
    2. Take the results and run them through a visualization tool like http://explain.depesz.com/
    3. Share the results on this thread. Developers should be able to see what's indexed and what's not.

    PostgreSQL setup: it typically runs fine with default settings and OpenClinica, but you might want to look at increasing the work_mem and shared_buffers settings, especially if the above analysis states that you are sorting something on disk instead of in-memory.

    Best, Aleksandr
  • pvromp0pvromp0 Posts: 9
    Hi,

    Thanks for your response.

    For me it's important to know whether my observations origins from the Oracle to Postgres migration or not.

    Following tests were performed (On a small VM 18G disk space, Mem:1G) :

    1.
    Configuration1: Oracle to Postgres migrated database + OpenClinica 3.8
    Observations: Click Menu 'Notes and discrepancies' for study XX. Result: 1000 records retrieved in 20 sec. As expected.
    2.
    Configuration2: Oracle to Postgres migrated database + OpenClinica 3.9
    Observations: Click Menu 'Notes and discrepancies' for study XX. Result: "out of diskspace"
    3.
    Configuration3: Fresh Postgres / OpenClinica 3.9 install
    10000 Subjects & 42 discrepancies in study YY, 28 user accounts.
    Observations: Click Menu 'Notes and discrepancies' for study YY. Result: 42 records retrieved in: 940 sec. Takes long time.


    In Configuration2, the explain analyses query ran also into trouble. The query works with arround 240 000 000 records to retrieve finally 1000 discrepancies.
    In Configuration3, the explain analyses query gave output. This can be found in the attachment. According to me, loading the database with more data will give the same problem. ( arround 13 000 000 records)

    I didn't perform the tests on a fresh postgres 3.8 install, as my migrated installation behaved well.

    Temporary conclusion: 3.8: OK . 3.9 behaves differently and is perhaps not related to the migration process.

    Best Regards,

    Peter Van Rompaey
  • toskriptoskrip Posts: 247 ✭✭
    I have reproduced the same problem when upgrading from 3.4 to 3.9. It does not look like migration related bug. There is a pull request on OC github that should resolve it but it was not merged with master yet. Hopefully we will soon have 3.9.1 release fixing it as it is rather critical bug.

    T
  • toskriptoskrip Posts: 247 ✭✭
    Fix was just merged with master branch today. Tested in my setup and it resolved issue with discrepancies view.

    T
  • kkrumliankkrumlian Posts: 25
    Hey guys,

    Thank you for discovering this issue. As Tomas mentioned we are aware of the issue. We just merged the fix to the master branch. QE will start testing the fix tomorrow and if all looks good we will decide wether we release 3.9.1 or push the fix in 3.10 (3.10 is scheduled to be released the 2nd week of March).

    @pvromp0 try increasing work_mem in postgres and see if postgres stops filling the disk space.

    I will provide an update in the next few days.
    Thank you all for your patience
    Krikor
  • kkrumliankkrumlian Posts: 25
    Hey guys,

    3.9.1 released on friday. This should solve the DN issue.

    Thank you
    Krikor

Sign In or Register to comment.