You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
sqlitebrowser freezes with 22Gb database file. #584
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
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 😃
@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. 😉
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.
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>
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.
If you click 'View' and 'Preferences', then the 'Database' tab, you should have a 'Prefetch Block Size' entry.
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?
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:
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?
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. 😄
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.
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?
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?
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.
Paste in Thunderbird (HTML) and Emacs (text). Both shows loading... in the lower 2/3 part of the table.
Do the same with Ctrl+C in DB4S.
Paste again in Thunderbird and Emacs. The result is the same except for the headers.
Now scroll through the table in DB4S until the end, letting it load all the data.
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.
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.
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.
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 😄
Activity
justinclift commentedon May 5, 2016
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?
eitijupaenoithoowohd commentedon May 5, 2016
8Gb. By the way, ram leaks are not observed at the moment of freeze. There is CPU loading only.
We are using even bigger DB files from program written in c. It works like a charm.
MKleusberg commentedon May 5, 2016
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 😃
justinclift commentedon May 5, 2016
@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. 😉
eitijupaenoithoowohd commentedon May 6, 2016
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.
justinclift commentedon May 6, 2016
@MKleusberg Maybe we should add some way to trigger output of useful diagnostics / progress / etc? Maybe a command line argument?
eitijupaenoithoowohd commentedon Jul 4, 2016
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_, FROMEURJPYORDER 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_, FROMEURJPYORDER BY_rowid_ASC);296000329
Run Time: real 340.013 user 136.504500 sys 18.499167
sqlite> SELECT COUNT(
_rowid_) FROMDATA;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>
justinclift commentedon Jul 29, 2016
@eitijupaenoithoowohd Thanks, that's useful info. 😄
RelativelyRandom commentedon Dec 8, 2017
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.
chrisjlocke commentedon Dec 8, 2017
If you click 'View' and 'Preferences', then the 'Database' tab, you should have a 'Prefetch Block Size' entry.
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?
RelativelyRandom commentedon Dec 8, 2017
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:
justinclift commentedon Dec 8, 2017
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?
justinclift commentedon Dec 8, 2017
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. 😄
33 remaining items
wmertens commentedon Dec 14, 2018
sky5walk commentedon Dec 14, 2018
mgrojo commentedon Dec 14, 2018
@sky5walk
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
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.
sky5walk commentedon Dec 15, 2018
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?
mgrojo commentedon Dec 15, 2018
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.
sky5walk commentedon Dec 15, 2018
I tried it twice to confirm because I did not expect that.
mgrojo commentedon Dec 15, 2018
I just tried and I see the same behaviour.
This is my version data:
And these, the steps:
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.
sky5walk commentedon Dec 15, 2018
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.
justinclift commentedon Dec 16, 2018
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.
justinclift commentedon Dec 16, 2018
Ahhh. Hadn't seen #1373. 😉
MKleusberg commentedon Feb 13, 2020
Can we close this issue? I guess most or even all of the problems mentioned here are solved by now 😄
MKleusberg commentedon Jun 3, 2020
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 😄