#1330 Process hangs at 100% CPU usage in server mode

open
1
2013-12-31
2013-12-04
No

Hello,

We are using HSQLDB as the primary database in server mode for an open source project (a RSS feed reader, here https://github.com/sismics/reader\).

Sometimes, when no heavy task is performed on the database, the hsqldb-server process hangs and it takes 100% CPU. I've attached a jstack if it can helps you.

The database is quite big, around 500Mo. You can find the structure here : https://github.com/sismics/reader/blob/master/reader-core/src/main/resources/db/update/dbupdate-000-0.sql

Thank you for this great project :)

Discussion

  • Benjamin Gamard

    Benjamin Gamard - 2013-12-04
     
    Attachments
  • Fred Toussi

    Fred Toussi - 2013-12-04
    • assigned_to: nobody --> fredt
     
  • Fred Toussi

    Fred Toussi - 2013-12-04

    Looks like an unoptimised update statement that is taking too long. Please provide the dtabase schema (copied from the .script file) and the sql statement.

     
  • Benjamin Gamard

    Benjamin Gamard - 2013-12-04

    I've attached the .script file.

    I think the slow query is :

    update T_USER_ARTICLE ua set ua.USA_READDATE_D = :readDate where ua.USA_IDARTICLE_C in (
    select a.ART_ID_C
    from T_ARTICLE a
    where a.ART_ID_C = ua.USA_IDARTICLE_C and a.ART_DELETEDATE_D is null
    ) and ua.USA_IDUSER_C = :userId and ua.USA_DELETEDATE_D is null and ua.USA_READDATE_D is null

     
  • Benjamin Gamard

    Benjamin Gamard - 2013-12-04
     
    Attachments
  • Fred Toussi

    Fred Toussi - 2013-12-30
    • priority: 5 --> 1
    • status: open --> closed-invalid
     
  • Fred Toussi

    Fred Toussi - 2013-12-30

    No schema was provided,

     
  • Benjamin Gamard

    Benjamin Gamard - 2013-12-31

    I have attached the .script file as you asked, it's not enough ?

     
  • Fred Toussi

    Fred Toussi - 2013-12-31

    I didn't notice the script. Reopened.

     
  • Fred Toussi

    Fred Toussi - 2013-12-31
    • status: closed-invalid --> open
     
  • Fred Toussi

    Fred Toussi - 2013-12-31

    This looks like a update query for a user to indicate which articles he has read at which date.
    It seems you could implement it in a more simple manner. When an article is read or another event happens, you just update the relevant row (or insert it if it does not exist) using a MERGE.
    In general, a link table can be updated by selecting the id's of the two fk references to the other two tables. You need an index (with two columns) on the user id and article id to speed up selection of the link row..
    You can discuss this further in the help forum if you wish.

     
  • Benjamin Gamard

    Benjamin Gamard - 2013-12-31

    So we have just missed the index on (userid+articleid) ?

    In fact the subquery is sometimes more complex (the query is generated with some code), because we can mark a category or a subscription as read (on the query I've copied, all articles are marked as read).

     
  • Fred Toussi

    Fred Toussi - 2013-12-31

    Please discuss it in the help forum. There are other, faster ways to do what you want.

     
  • Benjamin Gamard

    Benjamin Gamard - 2014-01-03

    After some tests, I still believe there is an actual bug.

    I've copied the production database on my local computer, and opened it in embedded mode. Then I've executed my slow query.
    Indeed it's not very fast (3-4sec), but it's very far from the server mode behavior.

    As I've said before, in server mode, the server uses 100% CPU, doesn't accept any requests, and that for an unlimited amount of time (several days at least).

    Even if my query is slow and a full scan is done, it can't be that slow. Plus, I don't think that crashing the server with an update request should be a normal behavior from a database server.

     
  • Fred Toussi

    Fred Toussi - 2014-01-03

    The server mode is simply a newtork listener that accepts the JDBC requests and submits them to the database. It shouldn't take longer to process the UPDATE statement. There must be a separate issue on the server.

     
  • Benjamin Gamard

    Benjamin Gamard - 2014-01-03

    It's a simple HSQLDB 2.3.0 running with Oracle JVM 1.7.0_17, nothing fancy.
    Nothing else is running on this server, the CPU is idle most of the time.

     
  • Fred Toussi

    Fred Toussi - 2014-01-03

    First, your test proves there is no bug. The server runs the UPDATE in-process after it receives the request and returns the result to the client.

    The main thing to check on the server is memory allocation for JVM and total memory. If JVM allocated max memory is above available memory, then the memory is paged in and out of disk, which is very slow. Another thing that might be different is OS caching the file in memory for reads.

    In any case, optimise the UPDATE and any other queries to minimise disk acccess. I can help you with query optimisation but not with sys admin issues.

     
  • Benjamin Gamard

    Benjamin Gamard - 2014-01-03

    You say "The server runs the UPDATE
    in-process after it receives the request and returns the result to the
    client.", which is not true.
    The server never returns any result, even after days at 100% CPU.

    Anyway, I have tried an optimization on the query, and I hope the issue won't appear again. If it does, I will check the memory usage.

    Thank you for you advices.

     
  • Fred Toussi

    Fred Toussi - 2014-01-03

    What I said happens in general, after the execution completes. Obviously the UPDATE doesn't complete on the server. But if you run the server on your local machine, it will.

    Check the memory usage anyway using the Java monitoring tool. If it is showing over-utilization, reduce the JVM memory and if necessary, reduce the CACHE SIZE in HSQLDB.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks