[sqlite] ATTACH DATABASE statement speed
It really seems something strange happens at filesystem level. This is a simple copy of slightly less than 1gb. It needs 9 seconds including sync. % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date Tue Aug 18 19:22:23 JST 2015 sending incremental file list italy-latest.osm.pbf 946,976,283 100% 123.88MB/s0:00:07 (xfr#1, to-chk=0/1) Tue Aug 18 19:22:32 JST 2015 However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks normal for a while. (I hope gmail don't mess up with the formatting...) 60, 90, 80 MB/s is kinda expected: 08/18/2015 07:27:38 PM Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util encplate 0.00 0.00 820.00 13.0062.11 0.26 153.34 1.872.271.14 73.46 1.20 99.80 0.00 0.00 1214.500.0094.58 0.00 159.49 0.960.780.780.00 0.78 95.20 0.00 0.00 1008.50 22.0078.09 0.41 155.99 1.501.460.96 24.16 0.93 95.80 but after some seconds it drops terribly to less than 10MB/s 08/18/2015 07:29:04 PM Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util encplate 0.00 0.00 124.003.50 9.88 0.12 160.72 1.67 12.99 11.21 76.14 7.65 97.50 0.00 0.00 69.00 18.00 5.68 0.29 140.55 1.81 20.92 14.15 46.86 11.38 99.00 0.00 0.00 86.000.00 7.05 0.00 167.91 1.04 12.03 12.030.00 11.24 96.70 And so, going to 10MB per second it can easily require few hours... I am out of ideas, but thanks for all the support. On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin wrote: > > On 18 Aug 2015, at 7:30am, Paolo Bolzoni > wrote: > >> Any other idea of what can I try? Perhaps my filesystem is misconfigured? > > The long time you quote is not standard for SQLite and I don't think anyone > can help you solve it by knowing picky details of SQLite. I'm even surprised > that it changed with your -O0 compilation since this suggests features of > your compiler I didn't know about. > > It's possible one of the developer team can help but they're reading this and > can pitch in if they think so. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] pragma temp_store_directory is deprecated, what is the alternative?
Another issue to consider is security. Some programs, such as mine, needs to carefully control temporary files. Unless the user selects a specific directory for temporary files, the files are put in a subdirectory of the program directory, created at run-time, then is security erased when done. Letting the operating system handle where to put the temporary files is a security issue for certain types of programs. My opinion is it is best to simply ask the user at install where to put temporary files and save that in a config file or in a table field, or simply create a temporary directory on the programs directory. Scott Doctor scott at scottdoctor.com --
[sqlite] ATTACH DATABASE statement speed
It seems my system CPUs overheats regularly and so the system throttle the CPU speed. It is definitely not good, I need to disassemble my laptop, clean everything, and probably replace the conductive cpu paste. However, this does not explain anything. sqlite3 appears IO bound, not CPU bound: in fact the programs seems to use 5% of the CPU tops. Any other idea of what can I try? Perhaps my filesystem is misconfigured? On Tue, Aug 18, 2015 at 3:02 PM, Paolo Bolzoni wrote: > I think the ATTACH slowness was a misunderstanding caused by the > optimization as it does not happen anymore now I compiled with -O0. > The long time was actually deleting the table from the input db, this > explains also why sqlite was making the journal file for the > operation. > > I am aware it sounds implausible, but I really have not idea why the > standard error message was not appearing before. > > Yes, I am using Linux > > % uname -a > Linux slyrogue 4.1.4-1-ARCH #1 SMP PREEMPT Mon Aug 3 21:30:37 UTC 2015 > x86_64 GNU/Linux > > > About hardware failures I do read some: > mce: [Hardware Error]: Machine check events logged > lines in dmesg. I try to investigate more. > > On Tue, Aug 18, 2015 at 12:28 PM, Simon Slavin > wrote: >> >> On 18 Aug 2015, at 4:14am, Paolo Bolzoni >> wrote: >> >>> In the input and output database I had a table of the same name and using: >>> >>> DROP TABLE IF EXISTS WaysNodes; >>> >>> sqlite3 was actually deleting the table of the input db, this was >>> unexpected as I thought that >>> without any prefix it deleted from the main database. >> >> So did I. I'm glad you have a working solution but there is still something >> wrong. Not only is your text above correct but it shouldn't take 13 hours >> to do an integrity check on a 13 Gig database with simple indexes. >> >> I'm still suspecting some kind of hardware problem. I'll be interested to >> know what happens if you ATTACH your copy of the input database, on the >> other disk, rather than the original. Does it still take a very long time ? >> >> Hmm. What OS are you using again ? Oh, you used iostat. Linux. So not >> the Windows pre-read caching bug. Okay. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
I think the ATTACH slowness was a misunderstanding caused by the optimization as it does not happen anymore now I compiled with -O0. The long time was actually deleting the table from the input db, this explains also why sqlite was making the journal file for the operation. I am aware it sounds implausible, but I really have not idea why the standard error message was not appearing before. Yes, I am using Linux % uname -a Linux slyrogue 4.1.4-1-ARCH #1 SMP PREEMPT Mon Aug 3 21:30:37 UTC 2015 x86_64 GNU/Linux About hardware failures I do read some: mce: [Hardware Error]: Machine check events logged lines in dmesg. I try to investigate more. On Tue, Aug 18, 2015 at 12:28 PM, Simon Slavin wrote: > > On 18 Aug 2015, at 4:14am, Paolo Bolzoni > wrote: > >> In the input and output database I had a table of the same name and using: >> >> DROP TABLE IF EXISTS WaysNodes; >> >> sqlite3 was actually deleting the table of the input db, this was >> unexpected as I thought that >> without any prefix it deleted from the main database. > > So did I. I'm glad you have a working solution but there is still something > wrong. Not only is your text above correct but it shouldn't take 13 hours to > do an integrity check on a 13 Gig database with simple indexes. > > I'm still suspecting some kind of hardware problem. I'll be interested to > know what happens if you ATTACH your copy of the input database, on the other > disk, rather than the original. Does it still take a very long time ? > > Hmm. What OS are you using again ? Oh, you used iostat. Linux. So not the > Windows pre-read caching bug. Okay. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
Just another guess: Have you tried to increase the page chache drastically ? I can remeber that "PRAGMA quick_check" is pretty slow for bigger DBs without an increased page cache. Maybe something like: PRAGMA cache_size=50; PRAGMA quick_check; Marcus Am 18.08.2015 um 12:38 schrieb Paolo Bolzoni: > It really seems something strange happens at filesystem level. > > This is a simple copy of slightly less than 1gb. It needs 9 seconds > including sync. > % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date > Tue Aug 18 19:22:23 JST 2015 > sending incremental file list > italy-latest.osm.pbf > 946,976,283 100% 123.88MB/s0:00:07 (xfr#1, to-chk=0/1) > Tue Aug 18 19:22:32 JST 2015 > > > However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks > normal for a while. > (I hope gmail don't mess up with the formatting...) > > 60, 90, 80 MB/s is kinda expected: > 08/18/2015 07:27:38 PM > Device: rrqm/s wrqm/s r/s w/srMB/swMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate >0.00 0.00 820.00 13.0062.11 0.26 > 153.34 1.872.271.14 73.46 1.20 99.80 >0.00 0.00 1214.500.0094.58 0.00 > 159.49 0.960.780.780.00 0.78 95.20 >0.00 0.00 1008.50 22.0078.09 0.41 > 155.99 1.501.460.96 24.16 0.93 95.80 > > but after some seconds it drops terribly to less than 10MB/s > 08/18/2015 07:29:04 PM > Device: rrqm/s wrqm/s r/s w/srMB/swMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate >0.00 0.00 124.003.50 9.88 0.12 > 160.72 1.67 12.99 11.21 76.14 7.65 97.50 >0.00 0.00 69.00 18.00 5.68 0.29 > 140.55 1.81 20.92 14.15 46.86 11.38 99.00 >0.00 0.00 86.000.00 7.05 0.00 > 167.91 1.04 12.03 12.030.00 11.24 96.70 > > And so, going to 10MB per second it can easily require few hours... > > > I am out of ideas, but thanks for all the support. > > > > On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin wrote: >> >> On 18 Aug 2015, at 7:30am, Paolo Bolzoni >> wrote: >> >>> Any other idea of what can I try? Perhaps my filesystem is misconfigured? >> >> The long time you quote is not standard for SQLite and I don't think anyone >> can help you solve it by knowing picky details of SQLite. I'm even >> surprised that it changed with your -O0 compilation since this suggests >> features of your compiler I didn't know about. >> >> It's possible one of the developer team can help but they're reading this >> and can pitch in if they think so. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] ATTACH DATABASE statement speed
Hello ! Do you have foreign keys on your tables ? And if so do you have indexes on then ? A database with foreign keys and no indexes can run very slow for mas insert/update/delete ? Cheers ! ? > Tue Aug 18 2015 12:38:51 CEST from "Paolo Bolzoni" > Subject: Re: [sqlite] ATTACH DATABASE >statement speed > > It really seems something strange happens at filesystem level. > > This is a simple copy of slightly less than 1gb. It needs 9 seconds > including sync. > % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date > Tue Aug 18 19:22:23 JST 2015 > sending incremental file list > italy-latest.osm.pbf > 946,976,283 100% 123.88MB/s 0:00:07 (xfr#1, to-chk=0/1) > Tue Aug 18 19:22:32 JST 2015 > > > However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks > normal for a while. > (I hope gmail don't mess up with the formatting...) > > 60, 90, 80 MB/s is kinda expected: > 08/18/2015 07:27:38 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 820.00 13.00 62.11 0.26 > 153.34 1.87 2.27 1.14 73.46 1.20 99.80 > 0.00 0.00 1214.50 0.00 94.58 0.00 > 159.49 0.96 0.78 0.78 0.00 0.78 95.20 > 0.00 0.00 1008.50 22.00 78.09 0.41 > 155.99 1.50 1.46 0.96 24.16 0.93 95.80 > > but after some seconds it drops terribly to less than 10MB/s > 08/18/2015 07:29:04 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 124.00 3.50 9.88 0.12 > 160.72 1.67 12.99 11.21 76.14 7.65 97.50 > 0.00 0.00 69.00 18.00 5.68 0.29 > 140.55 1.81 20.92 14.15 46.86 11.38 99.00 > 0.00 0.00 86.00 0.00 7.05 0.00 > 167.91 1.04 12.03 12.03 0.00 11.24 96.70 > > And so, going to 10MB per second it can easily require few hours... > > > I am out of ideas, but thanks for all the support. > > > > On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin wrote: > > >>On 18 Aug 2015, at 7:30am, Paolo Bolzoni >>wrote: >> >> >>>Any other idea of what can I try? Perhaps my filesystem is misconfigured? >>> >> The long time you quote is not standard for SQLite and I don't think >>anyone can help you solve it by knowing picky details of SQLite. I'm even >>surprised that it changed with your -O0 compilation since this suggests >>features of your compiler I didn't know about. >> >> It's possible one of the developer team can help but they're reading this >>and can pitch in if they think so. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
The docs say you have to close the DB handle to clean them up. I'm concerned that if a process is SIGKILLed or just exits abruptly that the temporary DBs will accumulate on disk. What mechanism is used to create the temporary files? If the file is unlinked after open, then process exit is sufficient, but I haven't been able to trigger data overflow onto disk in my quick testing. Thanks, Sam
[sqlite] ATTACH DATABASE statement speed
I think I got it. As often when something is really weird, it is was not what I expected. In the input and output database I had a table of the same name and using: DROP TABLE IF EXISTS WaysNodes; sqlite3 was actually deleting the table of the input db, this was unexpected as I thought that without any prefix it deleted from the main database. Besides, I think the compiler optimization changed a bit the order I was seeing the standard error messages as it appeared the time needed was in the ATTACH, not in the cleaning and preparing the output db. However, when I applied the Simon suggestion of vacuum after deleting everything, but before re-create, and attached to the input db after the problem apparently disappeared. I also compiled without optimizations, it does not really matter in a program comprised mainly by sql statements anyway. About PRAGMA integrity_check, it returned OK for the db, but after about 13 hours. I also tried to copy the db in another disk, copy back and compare with cmp. All fine. Cheers, Paolo On Mon, Aug 17, 2015 at 9:26 PM, Simon Slavin wrote: > > On 17 Aug 2015, at 9:22am, Paolo Bolzoni > wrote: > >> The pragma integrity_check is still running... Maybe my disk sucks for >> some reason? > > I wonder whether the hard disk is faulty or the file is on a bad sector. > > If the other tests show nothing, can you duplicate the input database file ? > Do something that forces the OS to read the whole thing. If you have a good > idea of how long reading and writing 13GB should take (ten minutes ?), this > may take an obviously unreasonable time. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
On 18 Aug 2015, at 11:38am, Paolo Bolzoni wrote: > but after some seconds it drops terribly to less than 10MB/s This, along with some information from your previous posts, all goes to suggest you have a hardware problem of some kind. My guess is that your hard disk is becoming faulty, either in general or with failures on certain sectors. Whatever it is it's not related to SQLite. Do you have an external drive available ? Could you move the database to that drive and do the same thing and see if you get a similar slowdown ? Simon.
[sqlite] ATTACH DATABASE statement speed
On 18 Aug 2015, at 7:30am, Paolo Bolzoni wrote: > Any other idea of what can I try? Perhaps my filesystem is misconfigured? The long time you quote is not standard for SQLite and I don't think anyone can help you solve it by knowing picky details of SQLite. I'm even surprised that it changed with your -O0 compilation since this suggests features of your compiler I didn't know about. It's possible one of the developer team can help but they're reading this and can pitch in if they think so. Simon.
[sqlite] pragma temp_store_directory is deprecated, what is the alternative?
On Sat, 15 Aug 2015 01:17:28 +0100 Simon Slavin wrote: > > BTW, Posix is almost silent on the question. It says TMPDIR will > > define the location of a temporary store, but not how. > > I'm okay if the documentation simply says something like ... > > For Darwin (Mac), it's always /tmp/ > For Linux, see the TMPDIR environment variable > For Windows see [whatever it is] Agreed, although IIUC it could be simpler than that, see next. > However I suspect things may be more complicated than that. For > instance, does .NET respect the OS's choice of temporary directory no > matter which OS it's running under ? I have no idea. What I'm suggesting is that there is no "OS's choice", really. There are a few functions in the C standard library, e.g. tmpfile(3), that may consult the environment. The variable's name varies by implementation. Some implementations, notably GNU's (if the documentation is correct), do not consult the environment. I would guess .NET is written atop the Win32 API and uses GetTempFileName or somesuch. That uses GetTempPath, whose return value is affected by TMP and TEMP. (https://msdn.microsoft.com/en-us/library/windows/desktop/aa364992 (v=vs.85).aspx). GetTempPath and tmpnam(3) on Windows both honor TMP, but the fallback policies differ. So it's not really a question of what the OS's choice is, because the *OS* offers no "temporary file" function. It's really a question of which library function is called, and how that function is implemented. But none of that matters unless those functions are used. An application -- or library, as in SQLite's case -- need not use them, which in any case aren't all that helpful. AIUI SQLite does *not* use those functions, but rather has its own way to determine where temporary files go. In that case the rule could be quite simple and OS-independent. For instance, 1. Use "SQLITE_TMPDIR" if defined 2. Use current working directory otherwise where the value is set by sqlite3_initialize and cannot be changed thereafter. --jkl
[sqlite] order by not working in combination with random()
Simon Slavin wrote: > On 18 Aug 2015, at 1:32am, Simon Davies > wrote: > >> sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; >> -6629212185178073901 >> -5293473521544706766 >> 2649466971390864878 >> -6185422953036640443 >> 1855956853707028764 > > Eek. Sorry, I should have tried it before posting. That's what this > whole thread is about, isn't it. And I suppose it's another sign that > I should be using WITH instead. > > Thanks for the heads-up, Simon. ... and then, at some wonderful moment, sqlite devs will implement query flattening for CTE (like they did for subquery above), and you'll be in square one. (Or, maybe, they will finally implement "common subexpression elimination", and original query will suddenly work again). It is not good idea to depend on undocumented behavior.
[sqlite] ATTACH DATABASE statement speed
On 18 Aug 2015, at 4:14am, Paolo Bolzoni wrote: > In the input and output database I had a table of the same name and using: > > DROP TABLE IF EXISTS WaysNodes; > > sqlite3 was actually deleting the table of the input db, this was > unexpected as I thought that > without any prefix it deleted from the main database. So did I. I'm glad you have a working solution but there is still something wrong. Not only is your text above correct but it shouldn't take 13 hours to do an integrity check on a 13 Gig database with simple indexes. I'm still suspecting some kind of hardware problem. I'll be interested to know what happens if you ATTACH your copy of the input database, on the other disk, rather than the original. Does it still take a very long time ? Hmm. What OS are you using again ? Oh, you used iostat. Linux. So not the Windows pre-read caching bug. Okay. Simon.
[sqlite] order by not working in combination with random()
On 18 Aug 2015, at 1:32am, Simon Davies wrote: > sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; > -6629212185178073901 > -5293473521544706766 > 2649466971390864878 > -6185422953036640443 > 1855956853707028764 Eek. Sorry, I should have tried it before posting. That's what this whole thread is about, isn't it. And I suppose it's another sign that I should be using WITH instead. Thanks for the heads-up, Simon. Simon.
[sqlite] order by not working in combination with random()
On 17 August 2015 at 21:50, Simon Slavin wrote: > > On 17 Aug 2015, at 9:46pm, Jeffrey Mattox wrote: > >> Could the random() be made part of an expression (that doesn't change the >> result) to fool the optimizer into only doing the random() once, like this: >> >> SELECT ( random() * col_thats_always_one ) AS x FROM table ORDER BY x > > Use a sub-select: > > SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC LIMIT 20 So: SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> create table myTable( id integer ); sqlite> insert into myTable values(1),(2),(3),(4),(5); sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; -6629212185178073901 -5293473521544706766 2649466971390864878 -6185422953036640443 1855956853707028764 sqlite> > Alternatively I think you could use WITH (CTE format) but I would like > someone more familiar with its syntax to figure it out. > > Simon. Regards, Simon
