23 apr 2016, E.Pasma:
> Hello,
> I tried the scripts but..
>
> createBigTable.sh is beyond the capacity of my system. Instead I
> used SQL script like in
> www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html
>
> My point is that the definition of the table is a waste of
Hello,
I tried the scripts but..
createBigTable.sh is beyond the capacity of my system. Instead I used
SQL script like in
www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html
My point is that the definition of the table is a waste of capacity,
even though it serves on
On 22 April 2016 at 21:24, Adam Devita wrote:
>
> That said, why is the dropping of a table dependent on the size of
> the table? Does Sqlite have to mark every block of memory it used as
> dropped? (This is obvious for high security mode, but otherwise?)
In rollback journal mode, every
On 22 April 2016 at 16:00, Cecil Westerhof wrote:
> What I find very interesting is that the user time and the sys time does
> not increase significantly, but the real time does. Does this point to the
> problem, or is this to be expected?
>
It suggests the extra time is spent waiting for I/O
2016-04-22 14:06 GMT+02:00 E.Pasma :
>
> 22 apr 2016, Cecil Westerhof:
>
>>
>> ?With createBigTable.sh ...
>>
> Can you paste the svript in the message? Attachments are not sent.
>
?createBigTable.sh:
#/usr/bin/env bash
# An error should terminate the script
# An unset variable is also an error
22 apr 2016, Cecil Westerhof:
>
> ?With createBigTable.sh ...
Can you paste the svript in the message? Attachments are not sent.
Regards, E.Pasma
On 4/22/16, Rowan Worth wrote:
>
> I've written this under the presumption that sqlite touches every database
> page that was associated with a table during the delete/drop... I can think
> of some optimisations allowing much of the i/o to be skipped (at least
> least when secure_delete isn't
2016-04-22 10:12 GMT+02:00 Rowan Worth :
> On 22 April 2016 at 16:00, Cecil Westerhof wrote:
>
> > What I find very interesting is that the user time and the sys time does
> > not increase significantly, but the real time does. Does this point to
> the
> > problem, or is this to be expected?
> >
2016-04-21 8:16 GMT+02:00 Cecil Westerhof :
>
> 2016-04-21 7:50 GMT+02:00 Cecil Westerhof :
>
>> ?I think it is an edge case. On my real system I only got this when there
>> where 1E8 records. I am now testing on very old (8 year) hardware to and
>> from work.
>> The processor is:
>> Intel(R)
In general, CPUs got much faster than disk IO a long time ago, so it
is expected that a single thread, write through to disk program would
have lots of time where the is CPU waiting for disk IO to complete.
(BTW: A common error of novice db programmers is using a disk based db
to store variables
21 apr 2016, Cecil Westerhof:
>
> ?I think it is an edge case. On my real system I only got this when
> there
> where 1E8 records. I am now testing on very old (8 year) hardware to
> and
> from work.
Hello,
the answer to Cecils question is YES here. I tested on a computer with
just 512 Mb
2016-04-21 7:50 GMT+02:00 Cecil Westerhof :
> ?I think it is an edge case. On my real system I only got this when there
> where 1E8 records. I am now testing on very old (8 year) hardware to and
> from work.
> The processor is:
> Intel(R) Atom(TM) CPU N270 @ 1.60GHz
> with 1 GB of RAM. I am
2016-04-21 1:37 GMT+02:00 J Decker :
> If you upload a file to google drive and later try to replace it with
> a different version google drive often corrupts it. Always delete and
> upload a new version.
>
?That was not the problem. When I unzipped the file I uploaded, it was
different as the
2016-04-21 1:04 GMT+02:00 jungle Boogie :
> On 20 April 2016 at 14:55, Cecil Westerhof wrote:
> > This one I download, unpacked and tried. It worked. So it should be
> > correct now:
> > https://drive.google.com/file/d/0BzW5q7uL-6z0UjFhRWZWSENnZW8/
>
>
> Windows 8.1 4 gigs of RAM, dell
2016-04-20 22:35 GMT+02:00 Cecil Westerhof :
> 2016-04-20 22:21 GMT+02:00 Scott Robison :
>
>> On Wed, Apr 20, 2016 at 2:00 PM, Cecil Westerhof
>> wrote:
>>
>> > How stupid that I did not think about Google Drive. :'-( Here it is:
>> >
2016-04-20 22:21 GMT+02:00 Scott Robison :
> On Wed, Apr 20, 2016 at 2:00 PM, Cecil Westerhof
> wrote:
>
> > How stupid that I did not think about Google Drive. :'-( Here it is:
> > https://drive.google.com/file/d/0BzW5q7uL-6z0SDdya2REaFNFUVE
> >
> > ?I am very curious.
> >
>
> I downloaded
2016-04-20 18:11 GMT+02:00 R Smith :
>
>
> On 2016/04/20 5:56 PM, Cecil Westerhof wrote:
>
>> 2016-04-20 16:07 GMT+02:00 R Smith :
>>
>>
>>> On 2016/04/20 3:31 PM, Cecil Westerhof wrote:
>>>
>>> ?It is still 411 MB. When I am home I will try it on another system also
>>> to
>>> look if there the
For me, source_id = 2016-04-18 15:46:14 eba27d4d17a76884292667d570d542e580ee3e77
Windows 10 1511 Pro, i7-3632QM 2.4Ghz, 16 GB Ram, 1 TB 850 Pro SSD
with secure_delete=0
either
drop table testuniqueuuid;
or the sum of both
delete from testuniqueuuid;
drop table testuniqueuuid;
takes under 2
On 2016/04/20 5:56 PM, Cecil Westerhof wrote:
> 2016-04-20 16:07 GMT+02:00 R Smith :
>
>>
>> On 2016/04/20 3:31 PM, Cecil Westerhof wrote:
>>
>> ?It is still 411 MB. When I am home I will try it on another system also to
>> look if there the timing is differently. Is there an upload site you
>>
2016-04-20 16:07 GMT+02:00 R Smith :
>
>
> On 2016/04/20 3:31 PM, Cecil Westerhof wrote:
>
>> 2016-04-20 12:35 GMT+02:00 R Smith :
>>
>>
>>> On 2016/04/20 10:50 AM, Cecil Westerhof wrote:
>>>
>>> The Devs do read the list, and often post, and they will be very
>>> interested in what you have
If you upload a file to google drive and later try to replace it with
a different version google drive often corrupts it. Always delete and
upload a new version.
On Wed, Apr 20, 2016 at 4:04 PM, jungle Boogie
wrote:
> On 20 April 2016 at 14:55, Cecil Westerhof wrote:
>> This one I download,
On Wed, Apr 20, 2016 at 3:31 PM, Cecil Westerhof
wrote:
> >>> ?Do the developers read this list, or should I post a bug report?
>
They do, and it's actually the preferred way to discuss
issues/bugs/performances, etc...
Dr. Hipp even wrote not long ago "bugs" entered would be aggressively
On Wed, Apr 20, 2016 at 3:55 PM, Cecil Westerhof
wrote:
>
> ?This one I download, unpacked and tried. It worked. So it should be
> correct now:
> https://drive.google.com/file/d/0BzW5q7uL-6z0UjFhRWZWSENnZW8/
>
> ?Made with zip.
>
Windows 10 1511 with Feb 2016 updates, x64
i7-6700K 4.00GHz
On 2016/04/20 3:31 PM, Cecil Westerhof wrote:
> 2016-04-20 12:35 GMT+02:00 R Smith :
>
>>
>> On 2016/04/20 10:50 AM, Cecil Westerhof wrote:
>>
>> The Devs do read the list, and often post, and they will be very
>> interested in what you have discovered if it is not a system anomaly on
>> your
On 20 April 2016 at 14:55, Cecil Westerhof wrote:
> This one I download, unpacked and tried. It worked. So it should be
> correct now:
> https://drive.google.com/file/d/0BzW5q7uL-6z0UjFhRWZWSENnZW8/
Windows 8.1 4 gigs of RAM, dell latitude with i5 process from a few
years ago, lots of tabs
2016-04-20 12:35 GMT+02:00 R Smith :
>
>
> On 2016/04/20 10:50 AM, Cecil Westerhof wrote:
>
>> 2016-04-20 10:44 GMT+02:00 Dominique Devienne :
>>
>> On Wed, Apr 20, 2016 at 10:36 AM, Cecil Westerhof >> >
>>> wrote:
>>>
>>> I am baffled. Still DELETE before DROP is a lot more efficient. And it
On Wed, Apr 20, 2016 at 2:00 PM, Cecil Westerhof
wrote:
> How stupid that I did not think about Google Drive. :'-( Here it is:
> https://drive.google.com/file/d/0BzW5q7uL-6z0SDdya2REaFNFUVE
>
> ?I am very curious.
>
I downloaded this and opened it with sqlite3.exe shell version 3.11.0.
On 2016/04/20 10:50 AM, Cecil Westerhof wrote:
> 2016-04-20 10:44 GMT+02:00 Dominique Devienne :
>
>> On Wed, Apr 20, 2016 at 10:36 AM, Cecil Westerhof
>> wrote:
>>
>>> I am baffled. Still DELETE before DROP is a lot more efficient. And it
>>> looks that it is not bothered when other programs
2016-04-20 10:44 GMT+02:00 Dominique Devienne :
> On Wed, Apr 20, 2016 at 10:36 AM, Cecil Westerhof
> wrote:
>
> > I am baffled. Still DELETE before DROP is a lot more efficient. And it
> > looks that it is not bothered when other programs are running (most of
> > the time). I would think that a
On Wed, Apr 20, 2016 at 10:36 AM, Cecil Westerhof
wrote:
> I am baffled. Still DELETE before DROP is a lot more efficient. And it
> looks that it is not bothered when other programs are running (most of
> the time). I would think that a DROP should take the least time:
>
I agree. That's weird.
2016-04-19 15:27 GMT+02:00 Olivier Mascia :
> > Le 19 avr. 2016 ? 14:49, Cecil Westerhof a
> ?crit :
> >
> > SECURE_DELETE
>
> Are you aware of the net effect of SQLITE_SECURE_DELETE?
>
> https://www.sqlite.org/compile.html#secure_delete
>
> The documentation talks about a "small performance
2016-04-19 15:29 GMT+02:00 R Smith :
>
>
> On 2016/04/19 2:49 PM, Cecil Westerhof wrote:
>
>> 2016-04-18 12:47 GMT+02:00 Olivier Mascia :
>>
>> PRAGMA compile_options; (has SQLITE_SECURE_DELETE been set for compiling
>>> the library?)
>>>
>>> ?sqlite3 checkUUID.sqlite
>> SQLite version 3.8.7.1
2016-04-19 15:27 GMT+02:00 Olivier Mascia :
> > Le 19 avr. 2016 ? 14:49, Cecil Westerhof a
> ?crit :
> >
> > SECURE_DELETE
>
> Are you aware of the net effect of SQLITE_SECURE_DELETE?
>
> https://www.sqlite.org/compile.html#secure_delete
>
> The documentation talks about a "small performance
2016-04-19 15:12 GMT+02:00 Simon Slavin :
>
> On 19 Apr 2016, at 1:49pm, Cecil Westerhof wrote:
>
> > ?Is it not strange that first DELETE and then DROP is so more efficient
> as
> > just a DROP?
>
> Yes.
>
> Can you please try the same comparison in the SQLite shell tool ? You can
> use
>
>
On 2016/04/19 2:49 PM, Cecil Westerhof wrote:
> 2016-04-18 12:47 GMT+02:00 Olivier Mascia :
>
>> PRAGMA compile_options; (has SQLITE_SECURE_DELETE been set for compiling
>> the library?)
>>
> ?sqlite3 checkUUID.sqlite
> SQLite version 3.8.7.1 2014-10-29 13:59:56
> Enter ".help" for usage hints.
> Le 19 avr. 2016 ? 14:49, Cecil Westerhof a ?crit :
>
> SECURE_DELETE
Are you aware of the net effect of SQLITE_SECURE_DELETE?
https://www.sqlite.org/compile.html#secure_delete
The documentation talks about a "small performance penalty", yet on very large
tables, especially if overflowing
2016-04-19 4:43 GMT+02:00 Rowan Worth :
> On 19 April 2016 at 02:01, Cecil Westerhof wrote:
>
> > 2016-04-18 4:04 GMT+02:00 Rowan Worth :
> >
> > > On 18 April 2016 at 06:55, Cecil Westerhof
> > wrote:
> > >
> > > > ?I put a strace on it. This was what I got:
> > > > Process 26455 attached with
2016-04-18 12:47 GMT+02:00 Olivier Mascia :
> PRAGMA compile_options; (has SQLITE_SECURE_DELETE been set for compiling
> the library?)
>
?sqlite3 checkUUID.sqlite
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> PRAGMA compile_options;
ENABLE_COLUMN_METADATA
On 19 Apr 2016, at 1:49pm, Cecil Westerhof wrote:
> ?Is it not strange that first DELETE and then DROP is so more efficient as
> just a DROP?
Yes.
Can you please try the same comparison in the SQLite shell tool ? You can use
.timer ON
DELETE FROM myTable;
DROP TABLE myTable;
.timer OFF
On 19 April 2016 at 02:01, Cecil Westerhof wrote:
> 2016-04-18 4:04 GMT+02:00 Rowan Worth :
>
> > On 18 April 2016 at 06:55, Cecil Westerhof
> wrote:
> >
> > > ?I put a strace on it. This was what I got:
> > > Process 26455 attached with 20 threads
> > > % time seconds usecs/call calls
On 04/16/2016 04:59 PM, Cecil Westerhof wrote:
> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung. I tried it from DB
> Browser and a Java program.
> I just tried it with a table of 1E7 elements. That was dropped in
> Le 18 avr. 2016 ? 12:30, Dan Kennedy a ?crit :
>
>> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
>> trying to drop this it looked like SQLite got hung. I tried it from DB
>> Browser and a Java program.
>> I just tried it with a table of 1E7 elements. That was
On 18 April 2016 at 06:55, Cecil Westerhof wrote:
> ?I put a strace on it. This was what I got:
> Process 26455 attached with 20 threads
> % time seconds usecs/call callserrors syscall
> -- --- --- - -
> 99.80 11245.498406
2016-04-17 18:23 GMT+02:00 Simon Slavin :
>
> On 17 Apr 2016, at 5:13pm, Cecil Westerhof wrote:
>
> > Or is this normal
> > in Java programs?
>
> Yes. You're discovering that Java is rubbish. 'futex' for Java is what
> most environments call 'mutex' and Java doesn't do locking well. It's this
2016-04-17 21:59 GMT+02:00 Scott Robison :
> On Sun, Apr 17, 2016 at 1:44 PM, Cecil Westerhof
> wrote:
>
> > 2016-04-17 18:13 GMT+02:00 Cecil Westerhof :
> >
> > > 2016-04-17 17:13 GMT+02:00 Keith Medcalf :
> > > The strange thing is that the blob variant takes a lot of time now
> also.
> > >
2016-04-17 18:13 GMT+02:00 Cecil Westerhof :
> 2016-04-17 17:13 GMT+02:00 Keith Medcalf :
> The strange thing is that the blob variant takes a lot of time now also.
> First it took only 4? hour, now it is already busy for eight hours and only
> has come to 8.9E7.
>
> 14:36:01: Inserted
2016-04-17 17:13 GMT+02:00 Keith Medcalf :
>
> Perfectly linear. The time waster in creating the records is the index
> with the completely separate copy of all the data and the native primary
> key (record number) into a duplicate structure (the index btree). Creating
> the index and the base
On 17 Apr 2016, at 5:13pm, Cecil Westerhof wrote:
> Or is this normal
> in Java programs?
Yes. You're discovering that Java is rubbish. 'futex' for Java is what most
environments call 'mutex' and Java doesn't do locking well. It's this which is
causing your delays, not SQLite or anything
On Sun, Apr 17, 2016 at 1:44 PM, Cecil Westerhof
wrote:
> 2016-04-17 18:13 GMT+02:00 Cecil Westerhof :
>
> > 2016-04-17 17:13 GMT+02:00 Keith Medcalf :
> > The strange thing is that the blob variant takes a lot of time now also.
> > First it took only 4? hour, now it is already busy for eight
2016-04-17 12:03 GMT+02:00 Simon Slavin :
>
> On 17 Apr 2016, at 10:38am, Cecil Westerhof
> wrote:
>
> > I start with a:
> >?
> >
> > ?conn.setAutoCommit(false);
> > but that is not the same?
>
> Yes, that does the same as BEGIN ... END. At least, according to the
> documentation it does.
>
2016-04-17 10:13 GMT+02:00 Rob Willett :
> I do not have the whole thread for what you reported but I did read
> somebody ask if you have put your inserts between a BEGIN/END transaction.
> That will make a massive difference to your speed. Also I?m unclear as to
> how Java fits in all of this.
Perfectly linear. The time waster in creating the records is the index with
the completely separate copy of all the data and the native primary key (record
number) into a duplicate structure (the index btree). Creating the index and
the base table at the same time, while linear as well, is
On 17 Apr 2016, at 10:38am, Cecil Westerhof wrote:
> I start with a:
>?
>
> ?conn.setAutoCommit(false);
> but that is not the same?
Yes, that does the same as BEGIN ... END. At least, according to the
documentation it does.
But you caused me to look up how the JDBC works, especially
2016-04-17 4:16 GMT+02:00 R Smith :
>
>
> On 2016/04/16 9:25 PM, Cecil Westerhof wrote:
>
>> 2016-04-16 20:36 GMT+02:00 R Smith :
>>
>> I am filling the database again, but now with text UUID instead of blob
>> UUID. That takes a ?little? more time. When it is filled I try again.
>>
>
> Don't
2016-04-17 1:03 GMT+02:00 Keith Medcalf :
> > Have another problem also. My CPU is about 15%, but the load average is
> > also about 15. (This is on a Linux system.) This results (sometimes) in a
> > very sluggish system. Can the load be a SQLite problem, or is it a Java
> > problem? (When the
Cecil,,
Linux reporting 8 cores is due to hyper threading on the four cores.
Thats normal.
One of the cores reporting a high usage is normal if you have single
threaded app that simply cannot be moved, e.g. many perl programs
exhibit this behaviour. Thats fine and to be expected.
I do not
On 2016/04/16 9:25 PM, Cecil Westerhof wrote:
> 2016-04-16 20:36 GMT+02:00 R Smith :
>
> I am filling the database again, but now with text UUID instead of blob
> UUID. That takes a ?little? more time. When it is filled I try again.
Don't forget to copy the DB file once it is populated, that
2016-04-16 21:44 GMT+02:00 Rob Willett :
> If you have a load average of 15 then that normally means you have a
> massively overloaded Linux box. I don?t know your system but I get worried
> around a load average of 3-4 on our boxes. Load Average is a very crude
> measurement but a high number
2016-04-16 20:36 GMT+02:00 R Smith :
>
>
> On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
>
>> 2016-04-16 14:52 GMT+02:00 R Smith :
>>
>> Let me try the 100 million rows, this may take some time - I will post
>>> again when it is done.
>>>
>>> ?I am curious.
>>
>
> Well, here it is then,
Cecil,
If you have a load average of 15 then that normally means you have a
massively overloaded Linux box. I don?t know your system but I get
worried around a load average of 3-4 on our boxes. Load Average is a
very crude measurement but a high number tends to be bad.
If your CPU is only
On 16 Apr 2016, at 8:25pm, Cecil Westerhof wrote:
> I am filling the database again, but now with text UUID instead of blob
> UUID. That takes a ?little? more time. When it is filled I try again.
I assume you're doing many INSERT commands between BEGIN and END.
> Have another problem also. My
On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
> 2016-04-16 14:52 GMT+02:00 R Smith :
>
>> Let me try the 100 million rows, this may take some time - I will post
>> again when it is done.
>>
> ?I am curious.
Well, here it is then, 100-million rows: The INSERT took a lot of time,
near 5 hours,
> Have another problem also. My CPU is about 15%, but the load average is
> also about 15. (This is on a Linux system.) This results (sometimes) in a
> very sluggish system. Can the load be a SQLite problem, or is it a Java
> problem? (When the program is not running, the load average is a lot
>
2016-04-16 16:19 GMT+02:00 R Smith :
>
>
> On 2016/04/16 4:09 PM, Cecil Westerhof wrote:
>
>> One strange thing the commandline and DB Browser are using ?3.8.10.2
>> while Java is using 3.8.11.
>>
>
> Your command-line is simply outdated - you can download the newest from
>
On 2016/04/16 4:09 PM, Cecil Westerhof wrote:
> One strange thing the commandline and DB Browser are using ?3.8.10.2
> while Java is using 3.8.11.
Your command-line is simply outdated - you can download the newest from
http://sqlite.org/download/
DB-Browser might have a newer version also,
2016-04-16 16:00 GMT+02:00 R Smith :
>
>
> On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
>
>> 2016-04-16 14:52 GMT+02:00 R Smith :
>>
>>-- 2016-04-16 14:44:55.054 | [Success]Script Success.
>>>
>>> As you can see, the INSERT obviously takes some time (even more-so if the
>>> CHECK
2016-04-16 15:41 GMT+02:00 Simon Slavin :
>
> > ?How can I get the journal mode in Jav??
> > With DB Browser I get Delete.
> > But when I in sqlite3 give:
> >PRAGMA schema.journal_mode;
> > I get:
> >Error: unknown database schema
>
> That is not well explained. Try just
>
> PRAGMA
2016-04-16 14:52 GMT+02:00 R Smith :
>
>
> On 2016/04/16 11:59 AM, Cecil Westerhof wrote:
>
>> I am playing a bit with SQLite. I first had a table with 1E8 elements.
>> When
>> trying to drop this it looked like SQLite got hung. I tried it from DB
>> Browser and a Java program.
>> I just tried it
2016-04-16 14:51 GMT+02:00 Simon Slavin :
>
> On 16 Apr 2016, at 10:59am, Cecil Westerhof
> wrote:
>
> > I first had a table with 1E8 elements. When
> > trying to drop this it looked like SQLite got hung.
>
> Please tell us which version of SQLite and which journal mode you're using.
>
?I work
On 2016/04/16 11:59 AM, Cecil Westerhof wrote:
> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung. I tried it from DB
> Browser and a Java program.
> I just tried it with a table of 1E7 elements. That was dropped in
On 16 Apr 2016, at 2:32pm, Cecil Westerhof wrote:
> ?I work with Java. With:
>SELECT SQLITE_VERSION()
> I get:
>3.8.11?
Thanks.
> ?How can I get the journal mode in Jav??
> With DB Browser I get Delete.
> But when I in sqlite3 give:
>PRAGMA schema.journal_mode;
> I get:
>
On 16 Apr 2016, at 10:59am, Cecil Westerhof wrote:
> I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung.
Please tell us which version of SQLite and which journal mode you're using.
My guess is that the operations for 1e7 rows fit in one of the
I am playing a bit with SQLite. I first had a table with 1E8 elements. When
trying to drop this it looked like SQLite got hung. I tried it from DB
Browser and a Java program.
I just tried it with a table of 1E7 elements. That was dropped in about 13
seconds.
I will try it again with 1E8 elements,
73 matches
Mail list logo
