Re: [sqlite] Article on AUTOINC vs. UUIDs

[J Decker]

On Thu, Nov 30, 2017 at 11:52 AM, Keith Medcalf  wrote:

>
> On Thursday, 30 November, 2017 09:27, J Decker  wrote:
>
> >> UUIDs are nice though if you don't have a natural key available and
> >> you are generating keys on multiple systems. Sadly, they are strings
> >> in sqlite, and  it would be more efficient to store and compare them
> >> as their 128-bit representation. Is there an extension that can do
> >> that?
>
> Just store them as a 16-byte blob.  What's the problem?
>
> >I would also like to make a note, that many criticisms are 'there's
> >so many bytes to have to compare', however, because of the highly
> >random nature of good UUIDs failure occurs quickly, usually within
> >4 bytes, which makes it almost as good as an integer
>
> Or just use randomblob(16) ...
>
The problem with that is continual reformatting

>
> >(especialy for things like SQLite that are comparing numbers as
> >strings anyway) the only time the full thing is compared is
> >on the row that exactly matches.
>
> I do not know what version of SQLite3 you are using, but according
> to the source code, only strings are compared as strings.  Everything
> else is compared using the appropriate Affinity -- Double to Double,
> Integer to Integer, BLOB to BLOB, etc.
>

Hmm; I thought it did store numbers as binary; but over the years of
watching various messages on this list, I got the idea that numbers were
being stored as strings also and haven't double checked my bad.  Maybe
it something about dates...

>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple read-only program very slow

[Keith Medcalf]

Is there an index on pos where ppos is the left-most field (or the only field) 
in the index?
What is the column affinity of ppos?  Of the fiold you are passing as a 
parameter?
Is ppos unique?

If you CREATE [UNIQUE] INDEX searchindex ON pos (ppos, pnum);

then your query can be satisfied only using the searchindex covering index.

If there is not an index on ppos, then you will be wasting time recreating the 
index for each query.

You will probably need to increase the cache size beyond the paltry default in 
order for the entire btree structures to be cached in RAM -- you probably want 
to make it as big as you can.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman
>Sent: Saturday, 25 November, 2017 20:14
>To: sqlite-users
>Subject: [sqlite] Simple read-only program very slow
>
>I'm pretty new at SQLite, so this may seem obvious to you.  Be kind.
>I'm using Python on Ubuntu Linux 16.04 LTS, and the sqlite that is
>built
>into Python.  The database
>is using WAL.
>
>I've got a database of some 100 million records, and a file of just
>over
>300 thousand that I want represented in it.  I wanted to check how
>much
>difference it was going to make, so I wrote a super
>simple program to the read the file and count how many records are
>already
>there.  I got impatient waiting for it so I killed the process and
>added an
>output of one dot (".") per 1000 records.  It went very fast for what
>I
>estimate was around 200 dots and hit a wall.  It made progress, but
>very
>very slowly.
>
>So I killed it again and added a commit() call every time it output a
>dot.
>It didn't hit a wall, just some rough road (that is, it slowed down
>at
>about the same spot but not nearly so drastically).
>
>The code makes to changes to the database at all.  Why does commit()
>make a
>difference?  What else should I learn from this?
>
>The field being used for the lookup has an index.
>
>++ kevin
>
>Code follows:
>#!/usr/bin/env python3
>"""Count the number of records that represent rows in the database
>'pos'
>table.
>The database is not modified.
>
> Last Modified: Sat Nov 25 18:56:49 PST 2017
>"""
>
>import os.path  #
>https://docs.python.org/3.5/library/os.path.html
>import sys  #
>https://docs.python.org/3.5/library/sys.html
>import argparse #
>https://docs.python.org/3.5/library/argparse.html
>import sqlite3  #
>https://docs.python.org/3.5/library/sqlite3.html
>import re   # https://docs.python.org/3.5/library/re.html
>
># from /usr/local/lib/python3.5/dist-packages
>import qcreate
>from qerror import *
>import myparser
>
>if __name__ == '__main__':
>parser = argparse.ArgumentParser(description="""A program to read
>positions and count how many are
>in the database""",)
>parser.add_argument("--dbname", default=None,
>help="name of the database to work on (overrides
>qubic.ini
>file)")
>parser.add_argument("file", nargs='?',
>type=argparse.FileType('r'),
>default=sys.stdin,
>help="file containing the qsearch results (default
>stdin)")
>args=parser.parse_args()
>infile = args.file
>
>if args.dbname is None:
>here=os.path.split(os.path.realpath('.'))[1]
>for confdir in
>".",os.path.join(os.environ["HOME"],".config"),"/etc/xdg/":
>f = os.path.join(confdir, "qubic.ini")
>if os.path.exists(f):
>args.dbname = myparser.parse(f, here, "dbname")
>if args.dbname is not None:
>break
>if args.dbname is None:
>print(" *** ERROR: no database name provided and none
>found in
>qubic.ini files")
>sys.exit(1)
>
>present = missing = lines = 0
>with sqlite3.connect(args.dbname) as conn:
>for line in infile:
>fields = line.split()
>pos = fields[0]
>if len(pos) != 64: # Important test to catch grep without
>--no-filename
>raise InputError(" ERROR: input line has wrong-sized
>position: " + line)
>
>pnum = None
>for row in conn.execute("""
>SELECT pnum
>FROM pos
>WHERE ppos=?
>""",(pos,)):
>pnum = row[0]
>break
>if pnum is None:
>missing += 1
>else:
>present += 1
>lines += 1
>if lines % 1000 == 0:
>print(".",flush=True,end="")
>conn.commit()
>print("there were",present,"records on file and",missing," were
>missing")
>print("out of a total of", lines, "records.")
>
>
>
>--
>word of the year: *kakistocracy*
>___
>sqlite-users mailing list

Re: [sqlite] Simple read-only program very slow

[Kevin O'Gorman]

On Sun, Nov 26, 2017 at 12:02 AM, Clemens Ladisch 
wrote:

> Kevin O'Gorman wrote:
> > I wrote a super simple program to the read the file and count how many
> > records are already there.  I got impatient waiting for it so I killed
> > the process and added an output of one dot (".") per 1000 records.  It
> > went very fast for what I estimate was around 200 dots and hit a wall.
> > It made progress, but very very slowly.  [...]
> > The field being used for the lookup has an index.
>
> I'd guess that most records are found, and that the file and the table
> happen to be somewhat sorted.  The search becomes slow when the amount
> of data that needs to be read exceeds the available memory.
>
> > Why does commit() make a difference?
>
> Hmmm ... interesting.
>
> > for row in conn.execute("""
> > SELECT pnum
> > FROM pos
> > WHERE ppos=?
> > """,(pos,)):
> > pnum = row[0]
> > break
> > if pnum is None:
> > missing += 1
> > else:
> > present += 1
>
> Even with the index on ppos, the DB still has to look up the table row
> to read the pnum value.
>
> You do not care about the actual pnum value, so you could replace it
> with a constant value ("SELECT 1 FROM ...").  Or just use EXISTS to
> show what you actually want to do:
>
> cursor = conn.execute("""
> SELECT EXISTS (
> SELECT *
> FROM pos
> WHERE ppos = ?)
> """, (pos,))
> exists = cursor.fetchone()[0]
> if exists:
> present += 1
> else:
> missing += 1
> 


That's real interesting and I'll keep it in mind for the future, but note
that my actual code DOES care about the contents of pmain, so it has to
look in the database anyway.  I'm still left wondering why it ran so slow
and why a commit() helped a read-only program running alone on the machine.

I'm gonna try this on my Xeon with 256 GB of RAM to check out the idea it
was running out of space.  It is true that the database is 50 GB so of
course it's bigger than the RAM on the usual desktop.


-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fix for 32bit compilation on Solaris

[Vladimir Marek]

Hi,

Compilers shipped with Solaris were traditionally compiling 32bit
binaries unless specified otherwise. This changed recently, the default
is 64bit binaries. So if you want to compile 32bit object, you have to
specify -m32. That slightly breaks sqlite configuration script as it
expects 32bit output with no arguments. The fix is simple - specify
'-m32' for 32bit compilation. The change is backwards compatible, -m32
always meant 32bit objects. I am attaching the patch to latest sqlite
release.

Thank you
-- 
Vlad
The sqlite configuration is assuming that compiler with no -mXX flag compiles
32 bit binaries. This is no longer true, as the default is 64bit these days.
This patch fixes the 32bit compilation.

--- sqlite-autoconf-321/tea/tclconfig/tcl.m42017-11-15 
08:52:28.206926381 +
+++ sqlite-autoconf-321/tea/tclconfig/tcl.m42017-11-15 
08:52:24.892793953 +
@@ -1942,7 +1944,7 @@ AC_DEFUN([TEA_CONFIG_CFLAGS], [
# not be necessary for extensions.
SHLIB_LD="$SHLIB_LD -m64 -static-libgcc"
])])
-   ])
+   ], [ SHLIB_LD="$SHLIB_LD -m32" ])
], [
case $system in
SunOS-5.[[1-9]][[0-9]]*)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple read-only program very slow

[Kevin O'Gorman]

On Sun, Nov 26, 2017 at 1:39 AM, Simon Slavin  wrote:

>
>
> On 26 Nov 2017, at 3:13am, Kevin O'Gorman  wrote:
> >
> > I've got a database of some 100 million records, and a file of just over
> > 300 thousand that I want represented in it.  I wanted to check how much
> > difference it was going to make, so I wrote a super
> > simple program to the read the file and count how many records are
> already
> > there.
>
> You can use COUNT(*) to find out how many rows there are in a table.  SQL
> is optimized to handle this faster than reading individual row data.
>
> SELECT COUNT(*) FROM pos
>
> Simon.
>

I know, but that does not help much when I'm trying to match the database
against a file, as I am here.

-- 
Dictionary.com's word of the year: *complicit*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Keith Medcalf]

On Thursday, 30 November, 2017 09:27, J Decker  wrote:

>> UUIDs are nice though if you don't have a natural key available and
>> you are generating keys on multiple systems. Sadly, they are strings
>> in sqlite, and  it would be more efficient to store and compare them 
>> as their 128-bit representation. Is there an extension that can do 
>> that?

Just store them as a 16-byte blob.  What's the problem?

>I would also like to make a note, that many criticisms are 'there's
>so many bytes to have to compare', however, because of the highly 
>random nature of good UUIDs failure occurs quickly, usually within 
>4 bytes, which makes it almost as good as an integer 

Or just use randomblob(16) ...

>(especialy for things like SQLite that are comparing numbers as 
>strings anyway) the only time the full thing is compared is 
>on the row that exactly matches.

I do not know what version of SQLite3 you are using, but according
to the source code, only strings are compared as strings.  Everything
else is compared using the appropriate Affinity -- Double to Double,
Integer to Integer, BLOB to BLOB, etc.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key lint has issues with without rowid

[David Raymond]

3.21.0



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Thursday, November 30, 2017 2:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key lint has issues with without rowid



On 30 Nov 2017, at 7:02pm, David Raymond  wrote:

> I’m using the CLI’s .lint fkey-indexes command, and it appears to be having 
> issues with without rowid tables. A low priority thing to look into when 
> someone's bored.

Please tell us which version of SQLite you’re using.  You should see it when 
the command line tool starts up.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key lint has issues with without rowid

[Simon Slavin]

On 30 Nov 2017, at 7:02pm, David Raymond  wrote:

> I’m using the CLI’s .lint fkey-indexes command, and it appears to be having 
> issues with without rowid tables. A low priority thing to look into when 
> someone's bored.

Please tell us which version of SQLite you’re using.  You should see it when 
the command line tool starts up.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key lint has issues with without rowid

[David Raymond]

I’m using the CLI’s .lint fkey-indexes command, and it appears to be having 
issues with without rowid tables. A low priority thing to look into when 
someone's bored.


sqlite> create table parent (id integer primary key);

sqlite> create table child1 (id integer primary key, parentID int references 
parent);

sqlite> .lint fkey-indexes
CREATE INDEX 'child1_parentID' ON 'child1'('parentID'); --> parent(id)

sqlite> create index idx_child1 on child1 (parentID);

sqlite> .lint fkey-indexes

sqlite> create table child2 (id int primary key, parentID int references 
parent) without rowid;

sqlite> .lint fkey-indexes
no such column: rowid

sqlite> create index idx_child2 on child2 (parentID);

sqlite> .lint fkey-indexes
no such column: rowid

sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Deon Brewis]

I would LOVE for this to be a Forum. I'd be willing to be a paid member/sponsor 
to help pay for running the forum software. 

I prefer XenForo for Forum software personally - it supports clipboard image 
copy/paste into the forum, which vBulletin doesn't.
 
- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, November 21, 2017 6:31 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Many ML emails going to GMail's SPAM

On 11/21/17, Paul Sanderson  wrote:
> Coincidence!  I have just been in my gmail folder marking a load of 
> SQLite email as 'not spam'

I've been seeing mailing list emails go to spam for a while now.
Nothing has changed with MailMan.  I think what we are seeing is the beginning 
of the end of email as a viable communication medium.

I really need to come up with an alternative to the mailing list.
Perhaps some kind of forum system.  Suggestions are welcomed.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7Cdeonb%40outlook.com%7C63e7db9930544a06851808d5318080b7%7C84df9e7fe9f640afb435%7C1%7C0%7C636469350331513693=TlBzqW0Pe0HfIAir0O9QgJgj7uVU%2F7vcb6%2FGSfDv8mg%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Scott Robison]

Perhaps the file sync performed by SQLite is more expensive in the docker
environment than in the host. That would make sense to me.

On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote:

> In my last mail i have multiple table creation and index. It seam that the
> overtime is for all the create statement.
>
> It really looks like a disk pb. But can't identify so much difference in my
> direct disk access ( with DD )
>
> I will try it on an other host.
>
> 2017-11-30 14:59 GMT+01:00 Simon Slavin :
>
> > OP wrote:
> >
> > > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> > > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> >
> > Is this the first content of a new file ?  If so, SQLite has to create
> the
> > file and write some structure information as well as writing the table.
> I
> > suspect that the time taken for the overhead is far more than the time
> > taken for the CREATE command.
> >
> > Could you try changing f.sql to create ten tables ?  For example create
> > the table "f1 as above then create tables "f2" to "f1" with the same
> > columns ?  It would be interesting to see what this does to both timings.
> >
> > Simon.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> *Sébastien HEITZMANN*
> Gérant & Directeur technique
> +33 (0)3 89 333 889
> Plus d'info sur : www.2le.net
>
>
>  lentreprise/194148499368?ref=ts>
>   
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Wout Mertens]

Thanks, very insightful!

On Thu, Nov 30, 2017 at 5:27 PM J Decker  wrote:

> I would also like to make a note, that many criticisms are 'there's so many
> bytes to have to compare', however, because of the highly random nature of
> good UUIDs failure occurs quickly, usually within 4 bytes, which makes it
> almost as good as an integer (especialy for things like SQLite that are
> comparing numbers as strings anyway) the only time the full thing is
> compared is on the row that exactly matches.
>

Aha so that's why the UUIDs are don't have the full time as the first part…
I was wondering why they did not use this easy "sort by creation date"
shortcut…
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[J Decker]

On Thu, Nov 30, 2017 at 7:14 AM, Wout Mertens 
wrote:

> The article is a bit muddled, the only real argument I could find is that
> auto-inc makes next keys easy to guess, which is information leakage, which
> means that is a potential security problem if that information were somehow
> useful.
>
> Seems to me that problem can be resolved by having an auto-inc type that
> skips a random amount on every insert? E.g. the next id is the last ID +
> randomFromRange(1, 1).
>
> UUIDs are nice though if you don't have a natural key available and you are
> generating keys on multiple systems. Sadly, they are strings in sqlite, and
> it would be more efficient to store and compare them as their 128-bit
> representation. Is there an extension that can do that?
>
> I don't know about the collision rate; if your systems are set up in a sane
> way, the MAC address alone would prevent collisions, no? And on the same
> system, are collisions even possible?
>

virtual machines can easily duplicate mac addresses if copied and not
updated correctly.
It's also a leak of information, and if it's the server service that's
creating the UUID's using it's mac, doesn't matter that it's for lots of
clients, the odds of collision increase greatly... even if you have a small
cloud of 10's or 100's of systems the leak of information is why MS
moved away from mac addresses when genertaing GUIDs especially for things
like COM service IDs (that was more than a decade ago, so I'm finding it
hard to find articles on it).  There are also network device manufacturers
that relesaed hardware with duplicate mac addresses; but since they were
between multiple lots of product it wasn't noticed to a great extent.

For the project I used UUIDs extensively for, it was written in C# with
datasets with all foriegn keys modeled in it.   On insert, if there was a
collision, it would regenerate a UUID and update the offending row, which
would automaically propagate through all child rows; and since the parent
had to exist before inserting the children then continuing on from that
point was very little work for the database.  Though it only used a few
thousand IDs and after many years of usage would only be a couple hundred
thousand IDs I still coded it paranoid-like.

Some databases have preference for using Sequential UUIDs.

https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid
Laboratory Test – SQL Server

VS2008 test, 10 concurrent users, no think time, benchmark process with 600
inserts in batch for leaf table
Standard Guid
Avg. Process duration: 10.5 sec
Avg. Request for second: 54.6
Avg. Resp. Time: 0.26

Sequential Guid
Avg. Process duration: 4.6 sec
Avg. Request for second: 87.1
Avg. Resp. Time: 0.12

Results on Oracle (sorry, different tool used for test) 1.327.613 insert on
a table with a Guid PK

Standard Guid, 0.02 sec. elapsed time for each insert, 2.861 sec. of CPU
time, total of 31.049 sec. elapsed

Sequential Guid, 0.00 sec. elapsed time for each insert, 1.142 sec. of CPU
time, total of 3.667 sec. elapsed

Could really wish more languages had DataSet.

I would also like to make a note, that many criticisms are 'there's so many
bytes to have to compare', however, because of the highly random nature of
good UUIDs failure occurs quickly, usually within 4 bytes, which makes it
almost as good as an integer (especialy for things like SQLite that are
comparing numbers as strings anyway) the only time the full thing is
compared is on the row that exactly matches.


'sides storage is cheap...
https://www.linkedin.com/pulse/20140414133905-9970539-peak-hard-drive/
would project 600TB drives by 2020, but another place noted that
commercial(home consumer) hard drives weren't increasing in size as fast as
datacenter/server drive storage.  but I can't find any recent articles that
also say that... looking at graphs of such information they all stop
2010-2013 ish... so no data for the last 4-7 years *shrug*



> On Thu, Nov 30, 2017 at 4:01 PM Keith Medcalf  wrote:
>
> >
> > Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> > is describing as the problems with serial IDs (or using the RowID) are
> > simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing
> the
> > RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> > Buggs Bunny would say "What a maroon!".
> >
> > Nonetheless, I have created an SQLite extension for Windows (2000 or
> > later) that will generate UUIDs using the builtin Windows RPC interface.
> > Apparently similar facilities are available on other OSes though all in
> > different manners (different functions in different libraries).  Note
> that
> > the silly proxies for the RPC functions are so that the compiler can
> > maintain correct linkage to the RPC libraries when using function
> pointers
> > -- the linkage through function pointers cast to 

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Wout Mertens]

For userIds, I generate a unique id from their email address. If they
later change the email address, they keep the id anyway. I really like
natural keys.

Of course, if you want to use that id in URLs, it would be good to use
a second unique id that is not used as a foreign key, so that people
can change their "url-id" if needed.

On 11/30/17, Simon Slavin  wrote:
>
>
> On 30 Nov 2017, at 3:52pm, Stephen Chrzanowski  wrote:
>
>> As one of the security guys here at work say, "Security does not help
>> convenience".  In the debug world, yeah, I agree, looking for 4310 is much
>> easier than 8af7* but, that should stick to a debug environment.
>
> From the user/password system on in, almost all the code I write exists to
> stop people from doing things.  I’m serious.  I’d estimate about 70%
> authentication, cross-site scripting checks and log files, and 20%
> user-interface and 10% report/display.
>
> By the way, using sequence numbers to deduce data was understood a hundred
> year ago (okay, 1920).  Adolf Hitler was the 55th member of the Nazi party
> but his membership number was 555 to make the party look bigger.  And
> pictures of British ship engine rooms and tank engines were not allowed to
> show engine serial numbers until after WW2 ended.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Simon Slavin]

On 30 Nov 2017, at 3:52pm, Stephen Chrzanowski  wrote:

> As one of the security guys here at work say, "Security does not help
> convenience".  In the debug world, yeah, I agree, looking for 4310 is much
> easier than 8af7* but, that should stick to a debug environment.

From the user/password system on in, almost all the code I write exists to stop 
people from doing things.  I’m serious.  I’d estimate about 70% authentication, 
cross-site scripting checks and log files, and 20% user-interface and 10% 
report/display.

By the way, using sequence numbers to deduce data was understood a hundred year 
ago (okay, 1920).  Adolf Hitler was the 55th member of the Nazi party but his 
membership number was 555 to make the party look bigger.  And pictures of 
British ship engine rooms and tank engines were not allowed to show engine 
serial numbers until after WW2 ended.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PLEASE UNSUBSCRIBE

[Simon Slavin]

On 30 Nov 2017, at 3:55pm, oɹɹoɯɐɥɔ ǝƃɹoɾ  wrote:

> I've tried a zillion times via the above link but it does not seem to work.

Clicking on that link just worked fine for me.  It’s not even HTTPS.  You might 
want to try a different browser, try your smartphone, then query your sysadmin.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PLEASE UNSUBSCRIBE

[oɹɹoɯɐɥɔ ǝƃɹoɾ]

> 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


I've tried a zillion times via the above link but it does not seem to work.

Thanks,
-- 
Jorge.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Stephen Chrzanowski]

As one of the security guys here at work say, "Security does not help
convenience".  In the debug world, yeah, I agree, looking for 4310 is much
easier than 8af7* but, that should stick to a debug environment.

But to put a twist on this, and to lessen the paranoia of collisions, you
could implement a 1024 GIGABYTE  PK/FK key structure. {smirk}

On Thu, Nov 30, 2017 at 10:48 AM, Chris Locke 
wrote:

>
> I used to use UUIDs, but when looking at a database using many foreign
> keys, it was a debug nightmare looking for a specific key.  After switching
> to auto increment fields, its nice when debugging to look for
> 'templateId=4310' and not
> 'templateId='8af78580-bb03-4674-92ab-33cef99afdb2'.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Chris Locke]

> if your systems are set up in a sane way, the MAC address alone would
prevent collisions, no?
> And on the same system, are collisions even possible?

Google says "In the case of standard version 1 and 2 UUIDsusing unique MAC
addresses from network cards, collisions can occur only when an
implementation varies from the standards, either inadvertently or
intentionally."

I used to use UUIDs, but when looking at a database using many foreign
keys, it was a debug nightmare looking for a specific key.  After switching
to auto increment fields, its nice when debugging to look for
'templateId=4310' and not
'templateId='8af78580-bb03-4674-92ab-33cef99afdb2'.

On Thu, Nov 30, 2017 at 3:23 PM, Jay Kreibich  wrote:

>
> There are some minor points, but I agree that it basically boils down to
> “serial IDs break security-by-obscurity.”
>
> That’s true, but….
>
>   -j
>
>
>
>
> > On Nov 30, 2017, at 9:00 AM, Keith Medcalf  wrote:
> >
> >
> > Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> is describing as the problems with serial IDs (or using the RowID) are
> simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the
> RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> Buggs Bunny would say "What a maroon!".
>
> >> -Original Message-
> >> From: sqlite-users [mailto:sqlite-users-
> >> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> >> Sent: Thursday, 30 November, 2017 07:16
> >> To: SQLite mailing list
> >> Subject: [sqlite] Article on AUTOINC vs. UUIDs
> >>
> >> Thought some of you might enjoy seeing this article.  I make no
> >> comment on what I think of the reasoning therein.  It’s set in the
> >> PostgreSQL world, but you could make an external function for SQLite
> >> which generates UUIDs.
> >>
> >>  >> increment-is-a-terrible-idea/>
> >>
> >> "Today, I'll talk about why we stopped using serial integers for our
> >> primary keys, and why we're now extensively using Universally Unique
> >> IDs (or UUIDs) almost everywhere."
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Jay Kreibich]

There are some minor points, but I agree that it basically boils down to 
“serial IDs break security-by-obscurity.”

That’s true, but….

  -j




> On Nov 30, 2017, at 9:00 AM, Keith Medcalf  wrote:
> 
> 
> Well, in my opinion the guy is an idiot.  The way to avoid the issues he is 
> describing as the problems with serial IDs (or using the RowID) are simple to 
> avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the RowID in a 
> URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy Buggs Bunny 
> would say "What a maroon!".

>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>> Sent: Thursday, 30 November, 2017 07:16
>> To: SQLite mailing list
>> Subject: [sqlite] Article on AUTOINC vs. UUIDs
>> 
>> Thought some of you might enjoy seeing this article.  I make no
>> comment on what I think of the reasoning therein.  It’s set in the
>> PostgreSQL world, but you could make an external function for SQLite
>> which generates UUIDs.
>> 
>> > increment-is-a-terrible-idea/>
>> 
>> "Today, I'll talk about why we stopped using serial integers for our
>> primary keys, and why we're now extensively using Universally Unique
>> IDs (or UUIDs) almost everywhere."
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Wout Mertens]

The article is a bit muddled, the only real argument I could find is that
auto-inc makes next keys easy to guess, which is information leakage, which
means that is a potential security problem if that information were somehow
useful.

Seems to me that problem can be resolved by having an auto-inc type that
skips a random amount on every insert? E.g. the next id is the last ID +
randomFromRange(1, 1).

UUIDs are nice though if you don't have a natural key available and you are
generating keys on multiple systems. Sadly, they are strings in sqlite, and
it would be more efficient to store and compare them as their 128-bit
representation. Is there an extension that can do that?

I don't know about the collision rate; if your systems are set up in a sane
way, the MAC address alone would prevent collisions, no? And on the same
system, are collisions even possible?

On Thu, Nov 30, 2017 at 4:01 PM Keith Medcalf  wrote:

>
> Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> is describing as the problems with serial IDs (or using the RowID) are
> simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the
> RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> Buggs Bunny would say "What a maroon!".
>
> Nonetheless, I have created an SQLite extension for Windows (2000 or
> later) that will generate UUIDs using the builtin Windows RPC interface.
> Apparently similar facilities are available on other OSes though all in
> different manners (different functions in different libraries).  Note that
> the silly proxies for the RPC functions are so that the compiler can
> maintain correct linkage to the RPC libraries when using function pointers
> -- the linkage through function pointers cast to (void*) works on 64-bit
> Windows but not on 32-bit Windows.  On 32-bit windows not using a proxy
> function to maintain the correct linkage results in the stack frame
> corruption.
>
> Also, uuid generation function for V1/3/4/5 are available in the Python
> standard uuid library, not mentioned in the article.
>
> File is sqlfwin.c located in
> http://www.dessus.com/files/SQLiteExtensions.zip for the 32-bit Windows
> versions; or
> http://www.dessus.com/files/SQLiteExtensions64.zip for the 64-bit Windows
> versions
> Source is the same in both, .dll compiled with MinGW 7.1.0.2 (dependencies
> only to standard windows DLLs and to the subsystem runtime library).
>
> SQLite3 UDF functions returning blobs (16-byte UUID) are:
> uuidCreateV1()
> uuidCreateV4()
> uuidFromString('text-uuid-rendering')
>
> And returning textual renderings are:
> uuidStringCreateV1()
> uuidStringCreateV4()
> uuidToString(uuid-blob)
>
> The create functions are volatile (like the randomblob function), and the
> To/From string functions are deterministic.
>
> sqlfwin.c also contains some other Windows API functions for working with
> the builtin windows security such as looking up names and sids, checking
> whether the current process access token contains a given sid/name, getting
> the current process access token username, computername, FQDN, and a few
> others.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> >Sent: Thursday, 30 November, 2017 07:16
> >To: SQLite mailing list
> >Subject: [sqlite] Article on AUTOINC vs. UUIDs
> >
> >Thought some of you might enjoy seeing this article.  I make no
> >comment on what I think of the reasoning therein.  It’s set in the
> >PostgreSQL world, but you could make an external function for SQLite
> >which generates UUIDs.
> >
> > >increment-is-a-terrible-idea/>
> >
> >"Today, I'll talk about why we stopped using serial integers for our
> >primary keys, and why we're now extensively using Universally Unique
> >IDs (or UUIDs) almost everywhere."
> >
> >Simon.
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Keith Medcalf]

Well, in my opinion the guy is an idiot.  The way to avoid the issues he is 
describing as the problems with serial IDs (or using the RowID) are simple to 
avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the RowID in a URL 
is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy Buggs Bunny would 
say "What a maroon!".

Nonetheless, I have created an SQLite extension for Windows (2000 or later) 
that will generate UUIDs using the builtin Windows RPC interface.  Apparently 
similar facilities are available on other OSes though all in different manners 
(different functions in different libraries).  Note that the silly proxies for 
the RPC functions are so that the compiler can maintain correct linkage to the 
RPC libraries when using function pointers -- the linkage through function 
pointers cast to (void*) works on 64-bit Windows but not on 32-bit Windows.  On 
32-bit windows not using a proxy function to maintain the correct linkage 
results in the stack frame corruption.

Also, uuid generation function for V1/3/4/5 are available in the Python 
standard uuid library, not mentioned in the article.

File is sqlfwin.c located in 
http://www.dessus.com/files/SQLiteExtensions.zip for the 32-bit Windows 
versions; or
http://www.dessus.com/files/SQLiteExtensions64.zip for the 64-bit Windows 
versions
Source is the same in both, .dll compiled with MinGW 7.1.0.2 (dependencies only 
to standard windows DLLs and to the subsystem runtime library).

SQLite3 UDF functions returning blobs (16-byte UUID) are:
uuidCreateV1()
uuidCreateV4()
uuidFromString('text-uuid-rendering')

And returning textual renderings are:
uuidStringCreateV1()
uuidStringCreateV4()
uuidToString(uuid-blob)

The create functions are volatile (like the randomblob function), and the 
To/From string functions are deterministic.

sqlfwin.c also contains some other Windows API functions for working with the 
builtin windows security such as looking up names and sids, checking whether 
the current process access token contains a given sid/name, getting the current 
process access token username, computername, FQDN, and a few others.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 30 November, 2017 07:16
>To: SQLite mailing list
>Subject: [sqlite] Article on AUTOINC vs. UUIDs
>
>Thought some of you might enjoy seeing this article.  I make no
>comment on what I think of the reasoning therein.  It’s set in the
>PostgreSQL world, but you could make an external function for SQLite
>which generates UUIDs.
>
>increment-is-a-terrible-idea/>
>
>"Today, I'll talk about why we stopped using serial integers for our
>primary keys, and why we're now extensively using Universally Unique
>IDs (or UUIDs) almost everywhere."
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article on AUTOINC vs. UUIDs

[Stephen Chrzanowski]

I'm going to read it later, but, I'll be going in skeptical.  Collision is
real, even at 128-bit resolution, but if your code handles the potential of
such a thing, then its no different than an autoinc primary key.  I'm
rather interested in their reasoning.

On Thu, Nov 30, 2017 at 9:15 AM, Simon Slavin  wrote:

> Thought some of you might enjoy seeing this article.  I make no comment on
> what I think of the reasoning therein.  It’s set in the PostgreSQL world,
> but you could make an external function for SQLite which generates UUIDs.
>
>  why-auto-increment-is-a-terrible-idea/>
>
> "Today, I'll talk about why we stopped using serial integers for our
> primary keys, and why we're now extensively using Universally Unique IDs
> (or UUIDs) almost everywhere."
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Article on AUTOINC vs. UUIDs

[Simon Slavin]

Thought some of you might enjoy seeing this article.  I make no comment on what 
I think of the reasoning therein.  It’s set in the PostgreSQL world, but you 
could make an external function for SQLite which generates UUIDs.



"Today, I'll talk about why we stopped using serial integers for our primary 
keys, and why we're now extensively using Universally Unique IDs (or UUIDs) 
almost everywhere."

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

In my last mail i have multiple table creation and index. It seam that the
overtime is for all the create statement.

It really looks like a disk pb. But can't identify so much difference in my
direct disk access ( with DD )

I will try it on an other host.

2017-11-30 14:59 GMT+01:00 Simon Slavin :

> OP wrote:
>
> > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
>
> Is this the first content of a new file ?  If so, SQLite has to create the
> file and write some structure information as well as writing the table.  I
> suspect that the time taken for the overhead is far more than the time
> taken for the CREATE command.
>
> Could you try changing f.sql to create ten tables ?  For example create
> the table "f1 as above then create tables "f2" to "f1" with the same
> columns ?  It would be interesting to see what this does to both timings.
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Simon Slavin]

OP wrote:

> CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);

Is this the first content of a new file ?  If so, SQLite has to create the file 
and write some structure information as well as writing the table.  I suspect 
that the time taken for the overhead is far more than the time taken for the 
CREATE command.

Could you try changing f.sql to create ten tables ?  For example create the 
table "f1 as above then create tables "f2" to "f1" with the same columns ?  It 
would be interesting to see what this does to both timings.

Simon.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

my test script is a little bit more complicated but with .timer it give
this result. Each statement is longer. So no load time or something like
that.

ON HOST

root@dipsy:/usr/share/kserver4# time sqlite3 dbm.db < dbm.sql
Run Time: real 0.027 user 0.00 sys 0.00
Run Time: real 0.020 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.009 user 0.00 sys 0.00
Run Time: real 0.010 user 0.00 sys 0.00
Run Time: real 0.010 user 0.00 sys 0.00
Run Time: real 0.007 user 0.004000 sys 0.00
Run Time: real 0.008 user 0.00 sys 0.00
Run Time: real 0.005 user 0.00 sys 0.00
Run Time: real 0.013 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.018 user 0.00 sys 0.00

real 0m0.157s
user 0m0.004s
sys 0m0.000s

IN CONTAINER

root@1e90b83b1b3f:/tmp# time sqlite3 dbm.db < dbm.sql
Run Time: real 0.062 user 0.00 sys 0.00
Run Time: real 0.069 user 0.00 sys 0.00
Run Time: real 0.068 user 0.00 sys 0.00
Run Time: real 0.065 user 0.00 sys 0.00
Run Time: real 0.068 user 0.00 sys 0.004000
Run Time: real 0.059 user 0.00 sys 0.00
Run Time: real 0.053 user 0.00 sys 0.00
Run Time: real 0.058 user 0.00 sys 0.00
Run Time: real 0.055 user 0.00 sys 0.00
Run Time: real 0.061 user 0.00 sys 0.00
Run Time: real 0.056 user 0.00 sys 0.00
Run Time: real 0.048 user 0.00 sys 0.00
Run Time: real 0.048 user 0.00 sys 0.004000
Run Time: real 0.049 user 0.00 sys 0.00

real 0m0.822s
user 0m0.004s
sys 0m0.008s


2017-11-30 14:41 GMT+01:00 Keith Medcalf :

>
> In addition to the execution time of the SQL you are also measuring time
> to load and link the sqlite3 command.
>
> A perhaps more realistic test would be to change the f.sql to contain:
>
> .timer on
> CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
>
> and then run the same test and see what the actual execution time of the
> SQL statement is.  It will probably come to 1 tick (so between 1 and 2
> ticks), whatever the unit of a tick is in the container or on the native
> host, or perhaps even zero if the execution time was less than a tick.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Sebastien HEITZMANN
> >Sent: Thursday, 30 November, 2017 04:01
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Sqlite and docker performance question
> >
> >Hi,
> >
> >In our application we use sqlite with great satisfaction.
> >
> >We currently benchmark our application and came up to a strange
> >difference
> >in creating a very simple sqlite db.
> >
> >We juste create a sigle table in a new db. In my docker container it
> >take 4
> >time more time than in the host system.
> >
> >time sqlite3 /tmp/foo.db < f.sql
> >
> >on the host machine
> >real 0m0.216s
> >
> >and in the docker container
> >real 0m0.826s
> >
> >
> >the f.sql contain a single sql
> >
> >CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime
> >INTEGER,
> >virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> >
> >I first think about a drive performance difference so i check the
> >write
> >thruput with dd and obtain nearly the same value.
> >
> >root@dipsy:/usr/share/kserver4# time dd if=/dev/zero of=/tmp/test
> >bs=512
> >count=1024000
> >1024000+0 enregistrements lus
> >1024000+0 enregistrements écrits
> >524288000 octets (524 MB) copiés, 7,04168 s, 74,5 MB/s
> >
> >real 0m7.056s
> >user 0m0.228s
> >sys 0m2.688s
> >
> >in the container
> >
> >root@1e90b83b1b3f:/project# time dd if=/dev/zero of=/tmp/test bs=512
> >count=1024000
> >1024000+0 records in
> >1024000+0 records out
> >524288000 bytes (524 MB) copied, 6.79671 s, 77.1 MB/s
> >
> >real 0m6.977s
> >user 0m0.272s
> >sys 0m2.600s
> >
> >the filesystem is ext4 with data=ordered
> >and /tmp is a volume mounted from the host in the [ docker -v
> >/tmp:/tmp ]
> >
> >
> >So my question is where can i investigate to see what happend ?
> >
> >Any idea or suggestion ?
> >
> >
> >
> >--
> >*Sébastien HEITZMANN*
> >Gérant & Directeur technique
> >+33 (0)3 89 333 889
> >Plus d'info sur : www.2le.net
> >
> >
> > >lentreprise/194148499368?ref=ts>
> >  
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> 

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

All my other tests give me some equivalent time in host and in container.

I have reduced the problem to the creation of the database. The rest of my
workload run approximatively in the same time.

You're right, there is some variance between each test but the 4 factor
stay the same. Sometime it's 180ms , sometime 240ms on the host. But on the
container it is always around 800ms.

I also use pysqlite with the same result.

I'm currently test the disk access with bonnie++ to see if any filesystem
or disk setting could be implied.

SEB

2017-11-30 12:48 GMT+01:00 Olivier Mascia :

> > Le 30 nov. 2017 à 12:00, Sebastien HEITZMANN <2...@2le.net> a écrit :
> >
> > We juste create a sigle table in a new db. In my docker container it
> take 4
> > time more time than in the host system.
> >
> > time sqlite3 /tmp/foo.db < f.sql
> >
> > on the host machine
> > real 0m0.216s
> >
> > and in the docker container
> > real 0m0.826s
>
> Couldn't it be a discrepancy in the way 'time' measures on the host or
> through the container?
> Or simply a difference in speed of code execution through the container?
> Instead of an actual difference of behaviour/performance of SQLite itself?
>
> The test (creation of this small db) is so small that timing it has a
> large potential for variance due to the system itself.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Keith Medcalf]

In addition to the execution time of the SQL you are also measuring time to 
load and link the sqlite3 command.  

A perhaps more realistic test would be to change the f.sql to contain:

.timer on
CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);

and then run the same test and see what the actual execution time of the SQL 
statement is.  It will probably come to 1 tick (so between 1 and 2 ticks), 
whatever the unit of a tick is in the container or on the native host, or 
perhaps even zero if the execution time was less than a tick.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Sebastien HEITZMANN
>Sent: Thursday, 30 November, 2017 04:01
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite and docker performance question
>
>Hi,
>
>In our application we use sqlite with great satisfaction.
>
>We currently benchmark our application and came up to a strange
>difference
>in creating a very simple sqlite db.
>
>We juste create a sigle table in a new db. In my docker container it
>take 4
>time more time than in the host system.
>
>time sqlite3 /tmp/foo.db < f.sql
>
>on the host machine
>real 0m0.216s
>
>and in the docker container
>real 0m0.826s
>
>
>the f.sql contain a single sql
>
>CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime
>INTEGER,
>virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
>
>I first think about a drive performance difference so i check the
>write
>thruput with dd and obtain nearly the same value.
>
>root@dipsy:/usr/share/kserver4# time dd if=/dev/zero of=/tmp/test
>bs=512
>count=1024000
>1024000+0 enregistrements lus
>1024000+0 enregistrements écrits
>524288000 octets (524 MB) copiés, 7,04168 s, 74,5 MB/s
>
>real 0m7.056s
>user 0m0.228s
>sys 0m2.688s
>
>in the container
>
>root@1e90b83b1b3f:/project# time dd if=/dev/zero of=/tmp/test bs=512
>count=1024000
>1024000+0 records in
>1024000+0 records out
>524288000 bytes (524 MB) copied, 6.79671 s, 77.1 MB/s
>
>real 0m6.977s
>user 0m0.272s
>sys 0m2.600s
>
>the filesystem is ext4 with data=ordered
>and /tmp is a volume mounted from the host in the [ docker -v
>/tmp:/tmp ]
>
>
>So my question is where can i investigate to see what happend ?
>
>Any idea or suggestion ?
>
>
>
>--
>*Sébastien HEITZMANN*
>Gérant & Directeur technique
>+33 (0)3 89 333 889
>Plus d'info sur : www.2le.net
>
>
>lentreprise/194148499368?ref=ts>
>  
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Olivier Mascia]

> Le 30 nov. 2017 à 12:00, Sebastien HEITZMANN <2...@2le.net> a écrit :
> 
> We juste create a sigle table in a new db. In my docker container it take 4
> time more time than in the host system.
> 
> time sqlite3 /tmp/foo.db < f.sql
> 
> on the host machine
> real 0m0.216s
> 
> and in the docker container
> real 0m0.826s

Couldn't it be a discrepancy in the way 'time' measures on the host or through 
the container?
Or simply a difference in speed of code execution through the container?
Instead of an actual difference of behaviour/performance of SQLite itself?

The test (creation of this small db) is so small that timing it has a large 
potential for variance due to the system itself.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

Hi,

In our application we use sqlite with great satisfaction.

We currently benchmark our application and came up to a strange difference
in creating a very simple sqlite db.

We juste create a sigle table in a new db. In my docker container it take 4
time more time than in the host system.

time sqlite3 /tmp/foo.db < f.sql

on the host machine
real 0m0.216s

and in the docker container
real 0m0.826s


the f.sql contain a single sql

CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);

I first think about a drive performance difference so i check the write
thruput with dd and obtain nearly the same value.

root@dipsy:/usr/share/kserver4# time dd if=/dev/zero of=/tmp/test bs=512
count=1024000
1024000+0 enregistrements lus
1024000+0 enregistrements écrits
524288000 octets (524 MB) copiés, 7,04168 s, 74,5 MB/s

real 0m7.056s
user 0m0.228s
sys 0m2.688s

in the container

root@1e90b83b1b3f:/project# time dd if=/dev/zero of=/tmp/test bs=512
count=1024000
1024000+0 records in
1024000+0 records out
524288000 bytes (524 MB) copied, 6.79671 s, 77.1 MB/s

real 0m6.977s
user 0m0.272s
sys 0m2.600s

the filesystem is ext4 with data=ordered
and /tmp is a volume mounted from the host in the [ docker -v /tmp:/tmp ]


So my question is where can i investigate to see what happend ?

Any idea or suggestion ?



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users