Skip to content

sqlitebrowser freezes with 22Gb database file. #584

Closed

Description

@eitijupaenoithoowohd

sqlitebrowser freezes with 22Gb database file. I can open database, edit the pragmas, view structure, but sqlitebrowser freezes when I try to click on "Browse Data" tab and eats 100% CPU.
sqlite3 CLI client works with that DB file without any problem.

Database structure is:
CREATE TABLE DATA (
time TIMESTAMP,
one_get DOUBLE,
two_get DOUBLE,
three_get DOUBLE,
four_get DOUBLE
);
CREATE UNIQUE INDEX unique_time on DATA(time)

Ubuntu 14.04
sqlitebrowser 3.8.99
QT version 4.8.6
SQLite version 3.8.2

Activity

  1. justinclift commented on May 5, 2016

    @justinclift
    Member

    Hmmm, that's probably the largest SQLite database file I've heard of someone using with this (so far).

    Looks like we need to put further effort into handling large databases. 😉

    Out of curiosity, how much ram do you have in that computer?

  2. eitijupaenoithoowohd commented on May 5, 2016

    @eitijupaenoithoowohd
    Author

    ram do you have in that computer

    8Gb. By the way, ram leaks are not observed at the moment of freeze. There is CPU loading only.

    largest SQLite database file I've heard of someone using with this

    We are using even bigger DB files from program written in c. It works like a charm.

  3. MKleusberg commented on May 5, 2016

    @MKleusberg
    Member

    I see you're using the nightly build? I actually think this might be caused by a bug we introduced since the latest release but that should be fixed now. So can you maybe try tomorrow's nightly build? 😃 Would be interested to know if that's making things a little better 😃

  4. justinclift commented on May 5, 2016

    @justinclift
    Member

    @MKleusberg That's a good point.

    @eitijupaenoithoowohd With the version you're using, is that the standard package on Ubuntu, or did you compile it yourself? If it's one you've compiled, then @MKleusberg just (today) added a patch which might help. So, compiling it again would be worth testing. 😉

  5. eitijupaenoithoowohd commented on May 6, 2016

    @eitijupaenoithoowohd
    Author

    Thank you so much for your answers.

    I have cloned and built yesterday's git master, because previously I had tried to use very old Ubuntu's version built from launchpad.net Seems, that it was the version 3.8.0. Result was similar for any versions - sqlitebrowser freezes.

    Today morning I have built sqlitebrowser up to commit 484206b60c5f57ea8259031af67b3c0f28944e5 Fri May 6 01:23:15 2016 +0200

    Unfortunately it did not fix my problem - sqlitebrowser still freezes immediately when "Browse Data" tab pressed.

  6. justinclift commented on May 6, 2016

    @justinclift
    Member

    @MKleusberg Maybe we should add some way to trigger output of useful diagnostics / progress / etc? Maybe a command line argument?

  7. eitijupaenoithoowohd commented on Jul 4, 2016

    @eitijupaenoithoowohd
    Author

    Now I know what had happened. Sqlitebrowser does not freeze. Every time when I clicked on "Browse Data" tab It had been waiting when following SQL request would finish execution:
    SELECT COUNT(*) FROM (SELECT _rowid_, FROM EURJPY ORDER BY _rowid_ ASC);

    Perhaps you have reasons to make this request so strange.

    Of course it is not solve the issue, but I have made some experiments to get duration of similar requests to that big DB:

    $ sqlite3 ./DATA.db
    SQLite version 3.8.2 2013-12-06 14:53:30
    sqlite> .timer ON
    SELECT COUNT() FROM (SELECT _rowid_, FROM EURJPY ORDER BY _rowid_ ASC);
    296000329
    Run Time: real 340.013 user 136.504500 sys 18.499167

    sqlite> SELECT COUNT(_rowid_) FROM DATA;
    296000329
    Run Time: real 267.229 user 51.368474 sys 13.184472

    sqlite> SELECT COUNT(*) FROM DATA;
    296000329
    Run Time: real 298.312 user 6.519477 sys 17.093569

    sqlite> SELECT COUNT(ROWID) FROM DATA;
    296000329
    Run Time: real 262.883 user 52.967703 sys 14.101523
    sqlite>

  8. justinclift commented on Jul 29, 2016

    @justinclift
    Member

    @eitijupaenoithoowohd Thanks, that's useful info. 😄

  9. RelativelyRandom commented on Dec 8, 2017

    @RelativelyRandom

    Sorry to jump into an old topic like this, but I just tried browsing a 10 GB, 80 million row table. CPU and memory usage are fine, but the app was using 100% of my hard drive for quite a few minutes until I killed it.

    I assume it's happening because the pagination controls below the data grid also show the total number of rows? In a table like this, it's not a realistic thing to do. Maybe the count query should include a limit?

    But I'm not one of the developers so I may be completely wrong.

  10. chrisjlocke commented on Dec 8, 2017

    @chrisjlocke
    Member

    If you click 'View' and 'Preferences', then the 'Database' tab, you should have a 'Prefetch Block Size' entry.

    image

    Mines set to a low number, but I think its about 50,000 by default. This should be the number of records it tries to read in any one lump. Can you confirm what your value is?

  11. RelativelyRandom commented on Dec 8, 2017

    @RelativelyRandom

    Nope, didn't help.

    I don't believe prefetching the actual values is the issue, but the fact that it's trying to do a count of all the rows, no matter how many it's ignoring when prefetching.

    I believe this little number here is the culprit, when the table gets really big:

    sqlite_browser

  12. justinclift commented on Dec 8, 2017

    @justinclift
    Member

    Ahhh yeah. When DB4S first goes to the Browse Data tab, one of the first queries it does is along the lines of SELECT count(*) FROM tablename. For really large tables, that can be a terribly slow operation.

    I don't remember off hand for SQLite, but with some database systems having an index on the table will make it pretty fast. Are you able to try adding indexes to the larger database tables?

  13. justinclift commented on Dec 8, 2017

    @justinclift
    Member

    Oh, it's probably also a good idea to grab use any of our recent nightly builds instead of our 3.10.1 release. We've updated our initial data loading code to be multi-threaded, which should also help a bit for things after that initial row count. 😄

  14. 33 remaining items

  15. wmertens commented on Dec 14, 2018

    @wmertens
    • shouldn't the csv export load all rows and not rely on 50000?
    • I can't save the preload setting, it stays 50000
    • the biggest problem is the count query which needs to load all the rows to get the answer
  16. sky5walk commented on Dec 14, 2018

    @sky5walk
    • shouldn't the csv export load all rows and not rely on 50000?
      My desire, but is not the case to date.
    • I can't save the preload setting, it stays 50000
      The preference settings work on Windows 10?
    • the biggest problem is the count query which needs to load all the rows to get the answer
      I made a suggestion somewhere to not attempt loading large files directly.
      Instead use ATTACH and run the Count(rowid) in a thread.
      Then DETACH and load the DB per the prefetch limits.
  17. mgrojo commented on Dec 14, 2018

    @mgrojo
    Member

    @sky5walk

    I would caution making defaults based on edge case, very big databases.
    If you starve the grid of all available rows, and a user selects 'export all' to csv, the export code should exhaust the source database, not the grid. Currently, the user is unaware it is a partial export.

    I'm aware of the problem with the copy to clipboard, which uses the data loaded in the table view, but is it happening too in the csv export? Is there an issue for that?

    @wmertens

    • I can't save the preload setting, it stays 50000
      The preference settings work on Windows 10?

    If this is confirmed, we'd like a new issue will all the details that you could provide. It's rather critical if it's in the beta.

  18. sky5walk commented on Dec 15, 2018

    @sky5walk

    @sky5walk

    I would caution making defaults based on edge case, very big databases.
    If you starve the grid of all available rows, and a user selects 'export all' to csv, the export code should exhaust the source database, not the grid. Currently, the user is unaware it is a partial export.

    I'm aware of the problem with the copy to clipboard, which uses the data loaded in the table view, but is it happening too in the csv export? Is there an issue for that?

    Nice, 'file-export-table-csv' works regardless of prefetch.
    And I just got [Ctrl+c] to work also.
    But, [Ctrl+Shift+c] only loads to the prefetch amount with the balance of rows = 'loading...'?
    Why the different algo just to prepend the headers?
    If that gets fixed, I can drop my prefetch to 10000 or less?

  19. mgrojo commented on Dec 15, 2018

    @mgrojo
    Member

    And I just got [Ctrl+c] to work also.
    But, [Ctrl+Shift+c] only loads to the prefetch amount with the balance of rows = 'loading...'?
    Why the different algo just to prepend the headers?

    That's strange, because they use the same function and get the data in the same way. I guess the [Ctrl+Shift+C] was done after the table was fully loaded for some previous action.

  20. sky5walk commented on Dec 15, 2018

    @sky5walk

    I tried it twice to confirm because I did not expect that.

  21. mgrojo commented on Dec 15, 2018

    @mgrojo
    Member

    I just tried and I see the same behaviour.

    This is my version data:

    • Version 3.11.99 (Dec 13 2018)
    • Qt Version 5.9.5
    • SQLite Version 3.15.2

    And these, the steps:

    1. Set the Prefecht value to 500.
    2. Open a DB and select a table with ~1500 rows.
    3. Click in the upper-left corner to select all
    4. Press Ctrl+Shift+C or use the contextual menu
    5. Paste in Thunderbird (HTML) and Emacs (text). Both shows loading... in the lower 2/3 part of the table.
    6. Do the same with Ctrl+C in DB4S.
    7. Paste again in Thunderbird and Emacs. The result is the same except for the headers.
    8. Now scroll through the table in DB4S until the end, letting it load all the data.
    9. Repeat steps from 4 to 7. The results are tables with all the data in all cases.

    I have a fix for this ( #1373) but my tests were catastrophic with big databases. But I think the problem is simply that I cannot hold some much data in memory for loading and copying to clipboard such a big database, even if the cells only contain 'loading...'. Then came the release and I left that change apart. I'll take a look now to whether it's fit enough for pushing it, but I'll have to test with smaller databases now.

  22. sky5walk commented on Dec 15, 2018

    @sky5walk

    Yes, I was going to mention the behavior depends whether the user scrolls to the bottom of the grid.
    That is not intuitive either. :(
    I realize big data will stress the clipboard and it is better to inform the user of partial data capture.
    Then, the user can decide how to proceed, instead of waiting for infinite hourglass or worse, getting less than the expected data.
    Thanks for clearing up this issue.

  23. justinclift commented on Dec 16, 2018

    @justinclift
    Member

    Yeah, it's a definite bug that "Select all" isn't really selecting everything in the database table.

    It sounds like we'll need to somehow be aware of large tables though, and have some kind of special treatment for them.

    Not sure what the options are.

    Hmmm, is there a way to dynamically figure out how much memory (say) 5 rows will take up, so then we'd be able to (roughly) estimate the required memory for the full table being copied into a buffer? If there's a way to determine how much memory is available to us (without hitting swap), that would at leave give us some figures to start working out an approach with.

  24. justinclift commented on Dec 16, 2018

    @justinclift
    Member

    Ahhh. Hadn't seen #1373. 😉

  25. MKleusberg commented on Feb 13, 2020

    @MKleusberg
    Member

    Can we close this issue? I guess most or even all of the problems mentioned here are solved by now 😄

  26. MKleusberg commented on Jun 3, 2020

    @MKleusberg
    Member

    Closing this because it looks like this issue is resolved and we haven't heard back anything for a long time. If there's any remaining problem with this, please just re-open this issue any time 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions